The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 23, 2019, 02:01:29 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: MySQL Stored Procedure Function Trigger with PHP  (Read 10382 times)
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« on: May 16, 2008, 05:53:45 PM »

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:

Code:
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 1

I 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.html

Here'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:

Code:
<?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 context

Most 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!
« Last Edit: May 16, 2008, 05:55:40 PM by perkiset » Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
arms
Expert
****
Offline Offline

Posts: 235



View Profile
« Reply #1 on: May 16, 2008, 06:08:08 PM »

fuck i spent months doing oracle stored procedures, can barely remeber anything about plsql. i hated it, but those stored procedure were FAST, at least compared to similar logic coded in java.

those delimiter errors - did you run the code from the mysql cmd line client or the gui? imported a script?
Logged
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #2 on: May 16, 2008, 06:48:17 PM »

I tried the MySQL command utility, the GUI tools in both Windows and OS-X, phpMyAdmin and writing my own scripts. Screwed me at every turn. I had 5.0.24 running on my box, thought it was a version bug, recompiled and updated to 5.0.51b, exact same thing.

I THINK that if I'd stayed with the MySQL command utility and worked through it I could have made it work... but that was utterly outside of what I needed to do, so it was not really an option.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
arms
Expert
****
Offline Offline

Posts: 235



View Profile
« Reply #3 on: May 16, 2008, 09:47:35 PM »

just a guess - in the examples i looked at (quickly) there was a space following the DELIMITER keyword bofore the character.
also the example you posted would need a semicolon on the last line:
Code:
...

END$$;
Logged
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #4 on: May 16, 2008, 10:05:58 PM »

just a guess - in the examples i looked at (quickly) there was a space following the DELIMITER keyword bofore the character.
also the example you posted would need a semicolon on the last line:
Code:
...

END$$;

You're right about the space... that was a typo while posting. But I don't think so on the semicolon after the END... not sure, but I don't think so... in any case, the methods above eliminate the need, which made me really frigging happy when I got it to work, let me tell you
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!