i created simple stored procedure loops through rows of 1 table , inserts them another. reason end while loop throwing missing semicolon error. code looked right me, , delimiters set right. can't figure out why throwing these errors, googling problem pointed me improperly used delimiter answers, nothing more. nice!
use test; delimiter $$ drop procedure if exists `testloop`$$ create procedure `testloop`() begin declare n int default 0; declare int default 0; select count(*) `test_dropship_upload` n; set i=0; while i<n insert `test_2` (sku, qty) values(sku, qty) `test_dropship_upload` limit i,1; set = + 1; end while; end $$ delimiter ;
when stuck problem in large block of code , can't find problem is, split code in smaller chunks , test them 1 @ time:
test 1:
delimiter $$ drop procedure if exists `testloop`$$ create procedure testloop() begin end $$ delimiter ; no errors: procedure declaration , use of delimiters ok.
test 2:
delimiter $$ drop procedure if exists `testloop`$$ create procedure `testloop`() begin declare n int default 0; declare int default 0; end $$ delimiter ; no errors: declaration of variables within procedure ok.
test 3:
delimiter $$ drop procedure if exists `testloop`$$ create procedure `testloop`() begin declare n int default 0; declare int default 0; select count(*) `test_dropship_upload` n; set i=0; end $$ delimiter ; no errors: select query , variable assignment ok.
test 4:
delimiter $$ drop procedure if exists `testloop`$$ create procedure `testloop`() begin declare n int default 0; declare int default 0; select count(*) `test_dropship_upload` n; set i=0; while i<n set = + 1; end while; end $$ delimiter ; no errors: while loop ok.
test 5:
the untested part insert query:
insert `test_2` (sku, qty) values(sku, qty) `test_dropship_upload` limit i,1; looking documentation insert , insert ... select, can see query not valid: apparently missing select part , shouldn't have values part if want insert values table:
delimiter $$ drop procedure if exists `testloop`$$ create procedure `testloop`() begin declare n int default 0; declare int default 0; select count(*) `test_dropship_upload` n; set i=0; while i<n insert `test_2` (sku, qty) select sku, qty `test_dropship_upload` limit i, 1; set = + 1; end while; end $$ delimiter ; the procedure creation completes without errors.
test 6:
however, syntax error on select query when executing procedure: mysql doesn't accept using limit variable.
make work, need use prepared statement.
prepare stmt "insert `test_2` (sku, qty) select sku, qty `test_dropship_upload` limit ?, 1"; execute stmt using @i; deallocate prepare stmt; it not allowed used local variables in prepared statements:
because local variables in scope during stored program execution, references them not permitted in prepared statements created within stored program. prepared statement scope current session, not stored program, statement executed after program ends, @ point variables no longer in scope. example, select ... local_var cannot used prepared statement. restriction applies stored procedure , function parameters.
to circumvent problem, use session variable @i instead of local variable i:
final version of procedure:
delimiter $$ drop procedure if exists `testloop`$$ create procedure `testloop`() begin declare n int default 0; select count(*) `test_dropship_upload` n; set @i=0; while @i<n prepare stmt "insert `test_2`(sku, qty) select sku, qty `test_dropship_upload` limit ?, 1"; execute stmt using @i; deallocate prepare stmt; set @i = @i + 1; end while; end $$ delimiter ; you can apply same method debug many complex programming problems: start simple version of code, test it. if works test again more code, if not locate , fix errors before continuing.
Comments
Post a Comment