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

I do not think you are going to be able to use $dbAdapter->Query() to
run this type of query (DDL queries). The query() method does a prepare
and execute, and PDO and/or MySQLi do not like the preparation of DLD
queries so much.

(See the notes here: http://php.net/manual/en/mysqli.prepare.php)

You'll have to do something like this:

// if PDO
$dbAdapter->getConnection()->exec($sql);

// if MySQLi
$dbAdapter->getConnection()->query($sql); // or ->multi_query($sql);

-ralph

havoc wrote:
> 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ß
>
>
>
>
>