OK, I am new to MySQL but have been writing stored procedures for years on SQL Server and Oracle. This shouldn't be this hard. Surely there's some small thing I'm overlooking. I am trying to create the following procedure via Query Browser on a MySQL 4.1.8 database.
create procedure MigrateStates
begin
declare done int default 0;
declare max_uid int default 0;
declare abbr varchar(2);
declare name varchar(30);
declare curState as cursor for select state_abbr, state_name from test.state order by state_name;
declare continue handler for SQLSTATE '02000' set done = 1;
open curState;
repeat
fetch curState into abbr, name;
if not done then
select max(key_uid) + 1 into max_uid from lookup;
insert into lookup (key_uid, key_type, key_value, key_abbr) values (max_uid, 'state_key', name, abbr);
end if;
until done end repeat;
close curState;
end
When I try to execute this script, I get a good old 1064 error with a message that's useless for telling where or what the problem is. I attempted to pattern the code above off an example from the documentation that can be found here:
http://dev.mysql.com/doc/mysql/en/cursors.html
However, when I cut and paste the example procedure into a Query Browser window and execute it, I get the same error. So right now it appears to me that the example is broken too. Please, somebody shed some light on this before I go nuts.