Stored Procedures Setup

bpat1434

Hi all. This is geared more towards the DB "gurus" out there; however, if anyone has any insight, I'd appreciate it.

I'm trying to get an installation script completed, and as part of the installation I need to set up a few stored procedures in MySQL 5. I'm using 5.0.27 on my dev server with centOS 5 and Apache 2.2.x and php5 in case you're wondering.

Now, my installation script opens each ".sproc" file and parses it like so:

<?php
	$sprocs = glob('./install/install/*.sproc');
	natsort($sprocs);

foreach($sprocs as $file)
{
	$sql = file_get_contents($file);
	$lines = explode("\n", $sql);
	$current_query = '';

	foreach($lines as $count=>$line)
	{
		if(substr($line, 0, 2) != '--')
			$current_query = "\n" . trim($line);
		if(substr($line, 0, 9) == 'DELIMITER')
		{
			if(preg_match('~DELIMITER (.*?)~is', $line, $matches) > 0)
			{
				$delimiter = $matches[1];
			}

			if(mysql_query($line) === false)
			{
				$failures['sproc'][$file][$count] = array('errno'=>mysql_errno(), 'error'=>mysql_error());
				// Skip to the next file since the delimiter can't be changed...
				continue 2;
			}
		}

		if(empty($current_query) || preg_match('~'.$delimiter.'[\s]*$~is', $line) == 0 && $count != count($lines))
			continue;

		if(($rslt = mysql_query($current_query)) === false)
			$failures['sproc'][$file][$count] = array('errno'=>mysql_errno(), 'error'=>mysql_error());

		$current_query = '';
	}
}

Now, when I go to run this, thinking that "DELIMITER |" or "DELIMITER //" would actually be a valid query, it fails with a 1064 error (SQL syntax). Now, since changing the delimiter fails, I just scrap the entire file and continue on (hence the "continue 2;" line) rather than generate almost 100 errors for each query.

Is there something I'm missing? The parser actually gives a valid chunks of code to use (trust me, I've checked) and it just doesn't seem to work.

Is it possible to install stored procedures through PHP using mysql_query() or will I have to manually set up the procedures? phpMyAdmin doesn't even support the installation of stored procedures (that I know of). Is there an alternative other than using exec() or shellexec()?

Thanks for any help anyone can offer.

YAOMK

I had the same problem trying to get stored procedures in a shared host. PhpMyAdmin actually defines the delimiter in a a small box as ";" change that to "//" and it should work, it did for me.

bpat1434

Well, the problem is I can't do this through phpMyAdmin as I'm developing an application that I won't always administer, so I need to have my install script (which I can only really guarantee will correctly run the mysql_query() function) run a bunch of mysql_query() calls to set up the stored procs.

YAOMK

Yes, I understand your needs. I'm not a mysql guru, in fact I been coding for just a few month, but here is a thought: you may be able to accomplish what you want storing your procedures in .sql files and then dumping them by calling:

mysql_query("source c:\path\myprocedure.sql");

This may not work, it is a mere suggestion from someone randomly up this late at night. Good luck tho.

bpat1434

Nope, won't work. Trust me, tried that already 😉 MySQL complains that SOURCE isn't a valid query... even phpMyAdmin doesn't use the SOURCE command... it actually parses the SQL inside the file...

I'm totally at a loss here... I'd hate to have to tell my other dev that stored procs can't be done...

I'm tired... I'm hungry... it's essentially Thanksgiving here (just 15 more hours 😉 ) so I'm going to bed....

wilku

You're qetting the error, because DELIMITER command is supposed to be parsed by mysql client - not the server. Obviously it isn't, because you get the error responce from the server. I have found some workaround, though I don't know if it'd work. It forces using mysqli instead of mysql. There is a command [man]mysqli_options[/man] which allows you to set different .cnf file to use. In that other file you can (I hope) specify delimiter=|.

bpat1434

I'm not sure that's 100% true... if the delimiter command were parsed by the client, how would you insert Stored Procedures in the first place? MySQL documentation says that

mysql> DELIMITER //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->    SELECT COUNT(*) INTO param1 FROM `table`;
    -> END;
    -> //

mysql> DELIMITER ;

will change the delimiter, create the stored procedure, and then change the delimiter again. There is no client, just the command-line to the mySQL server. So I'm pretty sure the server can interpret the DELIMITER call since it has to differentiate between what is inserted as the stored procedure, and what is the end of it.

MarkR

Don't even consider creating a stored procedure from PHP. Use the mysql command line.

Keep your stored procedures in a number of .sql files, each of which drops (if it exists) then re-creates the procedure or procedures.

Have a script which runs these.

Add this into your deployment plan when you release a new build of your application.

That way you can version your stored procedures in your SCM system the same way as the rest of your source (e.g. PHP).

Mark

wilku

from mysql documentation

mysql sends each SQL statement that you issue to the server to be executed. There is also a set of commands that mysql itself interprets. For a list of these commands, type help or \h at the mysql> prompt:

One of those commands is "delimiter". Mysql is a command line client to the mysql server.

Anyway I didn't have to create any procedures yet, so it's just me guessing. Sounds like MarkR is more experienced in this matter, so follow his solution rather then mine.

MarkR

The reason I suggest not creating SPs from PHP is because of the bad "smell". It smells like code-writing-code. Code-writing-code is an antipattern and really needs to be avoided.

You should follow the simplest method possible of installing the procedures, which is to drop them all and recreate them using some scripts, from the command-line.

If you are concerned about versioning, create a procedure which returns the version installed so you can query it at runtime (and somehow have that defintion created during your build process).

Mark

bpat1434

Mark, thanks for the insight. It's not code-writing-code. Rather it's code reading a "*.sproc" file which is just the stored procedure SQL file renamed so I can differentiate between regular SQL and the stored procedures.

As far as creating a script that runs these files, what would you suggest? Would the above work or is there something better? When I go to use the above script, I would execute the following queries in order:

DELIMITER |

DROP PROCEDURE IF EXISTS addBoycott;|

CREATE PROCEDURE addBoycott (IN orgID SMALLINT,IN userID SMALLINT,IN boycottName VARCHAR(60),IN boycottDesc LONGTEXT,IN endDate Date, IN goalAmount SMALLINT, IN invitationMessage LONGTEXT, IN thankyouMessage LONGTEXT, IN recipientFormalName TEXT, IN recipientEmail TEXT, IN instructions LONGTEXT, IN messageSubject TEXT, IN messageHeader LONGTEXT, IN messageBody LONGTEXT, IN messageFooter LONGTEXT, IN talkingPoints TEXT, OUT boycottID SMALLINT)
BEGIN
DECLARE error_msg TEXT;
DECLARE lineageID SMALLINT;
DECLARE campaignID SMALLINT;
DECLARE userCampaignID SMALLINT;
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';

DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;
INSERT INTO `boycott` (`org_id`, `user_id`, `name`, `description`, `end_date`, `goal_amount`, `invitation_message`, `thankyou_message`, `recipient_formal_name`, `recipient_email`, `instructions`, `message_subject`, `message_header`, `message_body`, `message_footer`, `talking_points`)
VALUES (orgID, userID, boycottName, boycottDesc, endDate, goalAmount, invitationMessage, thankyouMessage, recipientFormalName, recipientEmail, instructions, messageSubject, messageHeader, messageBody, messageFooter, talkingPoints);

SET boycottID = last_insert_id();

CALL addBoycottLineage(userID, boycottID, userID, lineageID);

INSERT INTO `campaign` (`fk_id`, `type_id`, `name`)
VALUES (boycottID, 1, boycottName);

SET campaignID = last_insert_id();

CALL addUserCampaign (userID, campaignID, goalAmount, userID, userCampaignID);
COMMIT;
END|

Now that's done multiple times (with the delimiter changing at the beginning and end of every file). Of course, I'm doing those through the [man]mysql_query/man call.... should i just be using: [man]exec[/man]('mysql ' . $current_query); instead ?

MarkR

I would suggest that you ALWAYS create stored procedures ONLY from the mysql command line utility.

I expect there is a way of creating them from PHP, but don't do it. You can't use the "DELIMITER" command from PHP because that's a mysql command line built-in.

The reason not to do it is that it's obfuscated, and nobody else does it that way, so it will render your code unmaintainable. It's also more complicated to call a PHP script than simply run the thing from the command line.

Mark

bpat1434

Eww..... well that's a let down.... I'll let the other dev know....

frankevans

MarkR wrote:
I would suggest that you ALWAYS create stored procedures ONLY from the mysql command line utility.

I expect there is a way of creating them from PHP, but don't do it. You can't use the "DELIMITER" command from PHP because that's a mysql command line built-in.

The reason not to do it is that it's obfuscated, and nobody else does it that way, so it will render your code unmaintainable. It's also more complicated to call a PHP script than simply run the thing from the command line.

Mark

MarkR, I agree with all your comments about code-generating-code and obfuscation. If he is storing the stored-procedure in PHP (or any other language) its definitely a problem.

However if he has a web-based-installer, which iterates through a list of "TABLES.SQL" files (which are stored in CM and bundled with the installer), reads each SQL file, and passes the contents to MySQL it makes for a clean deployment solution. Imagine an installshield for a web-application which deploys the PHP files, configures the web-server, then opens a web-page for configuring the DB-Connection and installing the schema.

The nice thing about this approach is that it allows remote database setup. The web-app and the database might not be on the same box. The web-app server might not have the mysql command-line installed. If the web-app is on windows, and the database on linux, the user running the install might not have any knowledge of linux systems.

Of course, in the end I'm just sour grapes, and your recommendation is the only one that works. ;-) The table creation scripts deployed fine remotely, but the stored-procedures failed

bpat1434

Yeah, that's what I was doing. I was creating an installer and we wanted to utilize SP so I was trying to see if there was a way round it.

I guess if I really wanted to, I could use fopen() and fput and fread to make my own MySQL utility specifically for the stored procedures... but that's a lot of work for right now.