errors running WHILE loop
I've had a DEVIL of a time with this problem and I'm sure it's something simple, but I can't find it anywhere in the documentation or online what the problem is. Let me explain. I cannot get any WHILE loop to work from either a CLI or a script on MySQL 5.1.41. For example, this is a simple script that sets a variable, and loops through 10 interations and prints the SQL version each time. It's a silly script, but this is what I'm getting: set @v1 = 0; while @v1 10 do; use mysql; select VERSION(); set @v1 = @v1 + 1; Query OK, 0 rows affected (0.00 sec) 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 'while @v1 10 do' at line 1 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed +-+ | VERSION() | +-+ | 5.1.41-3ubuntu12.10 | +-+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) Here's where I'm stuck. In some cases, I've seen where END WHILE is used, or a BEGIN/END block inside the WHILE, and even outside the WHILE. I'm at a complete loss as to WTF is going on. The documentation tells me little. In fact the test example in the MySQL reference manual online for 5.1 bombs as well: CREATE PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 0 DO USE mysql; SELECT VERSION(); SET v1 = v1 - 1; END WHILE; END; mysql source ~/dowhile.sql 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 'WHILE v1 0 DO USE mysql' at line 1 +-+ | VERSION() | +-+ | 5.1.41-3ubuntu12.10 | +-+ 1 row in set (0.00 sec) ERROR 1193 (HY000): Unknown system variable 'v1' 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 WHILE' at line 1 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 mysql So, what the heck is going on here? I'm at the end of my wits.
Re: errors running WHILE loop
2011/12/27 18:13 +, Mark Haney So, what the heck is going on here? I'm at the end of my wits. I suspect that you have to make the block delimiter something other than semicolon, and set the block in a routine or trigger. Semicolon terminates SQL routine statements, after PL1. If the block delimiter also is semicolon, parsing the block begins too early. In many examples of routine declaration it is changed to //, or another string not found in the block or routine. I often use question mark, but, of course, that is useless in case of PREPARE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: errors running WHILE loop
On 12/27/2011 12:13 PM, Mark Haney wrote: I've had a DEVIL of a time with this problem and I'm sure it's something simple, but I can't find it anywhere in the documentation or online what the problem is. Let me explain. I cannot get any WHILE loop to work from either a CLI or a script on MySQL 5.1.41. For example, this is a simple script that sets a variable, and loops through 10 interations and prints the SQL version each time. It's a silly script, but this is what I'm getting: set @v1 = 0; while @v1 10 do; use mysql; select VERSION(); set @v1 = @v1 + 1; Control constructs are allowed only withing stored routines. Query OK, 0 rows affected (0.00 sec) 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 'while @v1 10 do' at line 1 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed +-+ | VERSION() | +-+ | 5.1.41-3ubuntu12.10 | +-+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) Here's where I'm stuck. In some cases, I've seen where END WHILE is used, or a BEGIN/END block inside the WHILE, and even outside the WHILE. I'm at a complete loss as to WTF is going on. The documentation tells me little. In fact the test example in the MySQL reference manual online for 5.1 bombs as well: CREATE PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 0 DO USE mysql; USE is not allowed in stored routines. PB SELECT VERSION(); SET v1 = v1 - 1; END WHILE; END; mysql source ~/dowhile.sql 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 'WHILE v1 0 DO USE mysql' at line 1 +-+ | VERSION() | +-+ | 5.1.41-3ubuntu12.10 | +-+ 1 row in set (0.00 sec) ERROR 1193 (HY000): Unknown system variable 'v1' 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 WHILE' at line 1 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 mysql So, what the heck is going on here? I'm at the end of my wits. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
