May 10
10
MySQL does support preparing some DDL statements, However…
Bill Karwin gives some insight into some work arounds when creating functions, triggers and procedures using Zend Framework;
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
