i have a problem that is looking very strange to me. I need to create a
user defined function within mysql.
To define the function i need to set the sql-end-of statement char to
something else than ";". On the command line it can be easily done with
DELIMITER //
and afterwards the delimiter is "//". But with Zend_Db_Adapter i cannot
get it to work.
the code i am using:
$sql =
DROP FUNCTION IF EXISTS lastContactInChain;
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `lastContactInChain`(id
INT(11)) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE next_id INT;
label1: LOOP
SELECT CONID_NEXT INTO next_id
FROM ECUSCON
WHERE CONID = id
LIMIT 1;
IF next_id IS NULL THEN
LEAVE label1;
END IF;
SET id = next_id;
END LOOP label1;
RETURN id;
END;
//
DELIMITER ;
and that is what i am trying with the Db_Adapter:
$this->zendDbAdapter->query($sql);
but i constantly get the following error :
General error 1064: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `lastContactInChain`(id ' at
line 1"
My first idea was to use ->exec($sql) instead of ->query() but that only
changes the error that occurs to:
PHP Fatal error: Uncaught exception 'PDOException' with message
'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other
unbuffered queries are active. Consider using
PDOStatement::fetchAll(). Alternatively, if your code is only ever
going to run against mysql, you may enable query buffering by setting
the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in
/usr/share/php/Zend/Db/Statement/Pdo.php:228
Is there anything else i can do to either set the Delimiter or to use my
sql statement in a way it is excecuted completely corrct?
Nils Preuß
没有评论:
发表评论