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.


Reply via email to