2010年5月3日星期一

[fw-db] Problems changing the sql-end-of-statement delimiter

Hiho,

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ß

没有评论: