I'm working on a new personal project to support coding stored procedures, functions and triggers in MySQL.
Since I'm new to a lot of it, I searched for examples of how to create, maintain and execute these procedures and found that the most common examples lead you to this sort of this:
DELIMITER$$
DROP PROCEDURE IF EXISTS HelloWorld$$
CREATE PROCEDURE HelloWorld()
BEGIN
SELECT 'Hello World!';
END$$
While this looks quite reasonable and the explanations about why you do things this way seemed straightforward, I ran into little issues that were stoppers and could not find anywhere on the web that simply wrapped up how to do this in PHP with the old mysql_ functions. So here it is.
First Issue: I'd get an error like this:
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 $$' at line 1You 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 $$' at line 1I could not ever find a reasonable solution as to why this is an unknown command. Some sites say that it is not in the MySQL definition - it's a client thang, but the MySQL documentation specifically claims that it is, here:
http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.htmlHere's the deal: you would issue the DELIMITER command so that you can issue multiple statements to MySQL ie., the DELETE IF EXISTS command and then the CREATE command. The most simple fix for this is simply to not do this as a compound command. Do it in PHP like this:
<?php
mysql_connect('127.0.0.1', 'user', 'password', true);
mysql_query('DELETE IF EXISTS HelloWorld');
$sql = <<<SQL
CREATE PROCEDURE HelloWorld()
BEGIN
SELECT 'Hello World!';
END
SQL;
mysql_query($sql);
?>
Simply do it as two lines rather than a single one and you sidestep it entirely.
The next issue was executing the procedures. When I tried to "call HelloWorld" I'd get an error like this:
PROCEDURE testdb.HelloWorld can't return a result set in the given contextMost sites said that you need the MySQLi extension to be able to handle a multiple command. Again, this is not the case. This blog post:
http://bobfield.blogspot.com/2006/09/php-and-mysql-stored-procedures.html corroborated a patch that I'd heard of and found as well - that there's a CLIENT_MULTI_STATEMENTS flag on your mysql_connect - this is NOT clear in the documentation, but if you work through mysql_com.h you can find it. The flag value is 65536, so you can connect this way:
mysql_connect('127.0.0.1', 'user', 'pass', true, 65536);
and then execute a stored procedure as such:
mysql_query('call HelloWorld');
and all is fine. I have more research to do because my project will be loading and saving procedures, functions, triggers and views and generally make life easier for the lot of us that are curious about this side of database programming, so I'll post more here as I find other bugs and gotchas.
Good Luck!
