From: Peter Brawley
Date: December 28 2011 12:00am
Subject: Re: errors running WHILE loop
List-Archive: http://lists.mysql.com/mysql/226517
Message-Id: <4EFA5C29.7010908@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.
>
>
>