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

Thanks Bill for the good indepth explanation. Because this was my first
encounter with ZF i got pretty frustrated but your suggestion works
perfectly.

Regards,
Nils Preuß

On 05/03/2010 07:16 PM, Bill Karwin wrote:
> MySQL does support preparing some DDL statements, even in older
> versions. See
> http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html for
> lists of what statements can be prepared.
>
> However, some DDL statements are still not supported as prepared
> statements, for example CREATE FUNCTION, CREATE TRIGGER, CREATE
> PROCEDURE.
>
> DELIMITER is not supported as an executable statement at all, whether
> you prepare it or whether you do an immediate execute. Statements
> like DELIMITER, PAGER, SOURCE, CONNECT, and QUIT and others are
> builtins of the mysql command-line client. These commands are not
> recognized by the MySQL server.
>
> You need to set the DELIMITER only if you're running the CREATE
> FUNCTION statement in an SQL script. The default API for SQL
> statements does not support multiple statements per call. So you
> don't have to delimit statements and you don't have to change the
> delimiter.
>
> So Nils's solution should be the following:
>
> 1. Don't worry about DELIMITER, you don't need it.
>
> 2. You must DROP and CREATE in two separate statements.
>
> 3. Bypass the default ZF query method. Go directly to the
> PDO::query() method when you execute a statement that isn't
> preparable. You can access the PDO object using the getConnection()
> method of your ZF Db adapter:
>
> $db->getConnection()->query( $drop_function_statement );
> $db->getConnection()->query( $create_function_statement );
>
> Regards,
> Bill Karwin
>
> On May 3, 2010, at 8:09 AM, Ralph Schindler wrote:
>
>> 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ß