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

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ß