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

Reply via email to