...

View Full Version : Getting nowhere with procedures, please help!



XmisterIS
06-07-2011, 11:44 AM
Hi all,

I am trying to write MySQL procedures for the first time.

I'm using MySQL 5.1 on Debian Squeeze.

I tried to copy and paste the following example from the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/while-statement.html

That threw up a whole load of errors, so I have simplified it right down to the following:


drop procedure if exists mytest;

create procedure mytest()
begin
declare myval int default 5;
end;

The "drop procedure" bit works fine, but the create procedure block fails, with the following errors:

ERROR 1064 (42000): 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 '' at line 3
ERROR 1064 (42000): 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 'end' at line 1


I don't think it can get much simpler than that, what am I doing wrong??! :(:confused:

guelphdad
06-07-2011, 02:15 PM
If you are running this SP in the mysql client, you need to change your delimiter first, otherwise it will think you have finished your SP at the first ; it comes to.

DELIMITER #

will change your delimiter to # so you'll have to type that at the very end of your SP to complete it. It now won't trip over the ; when it comes to it though. Then change back to ; after you have created your SP.

XmisterIS
06-07-2011, 05:24 PM
Many thanks for that, but goodness, the people at MySQL don't want to make it easy do they?! I would class that little tidbit of information regarding the change of delimiter as "crucial" ... but they make no mention of it at all in the examples!

Also, I've noticed that sometime I'll run a command at the MySQL command line and I'll get back a glib little report like "Query OK, 0 rows affected, 1 warning (0.00 sec)". In other words, "there is a warning, but we're not going to tell you what it is". Great ... !! :rolleyes:

Old Pedant
06-07-2011, 08:56 PM
???

The first section of the docs re procedures shows using the delimiter. Discusses why it is needed and what you can use. Etc. Includes example.

http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html

And as for warnings:

http://dev.mysql.com/doc/refman/5.1/en/show-warnings.html



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum