List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:December 28 2011 12:00am
Subject:Re: errors running WHILE loop
View as plain text  
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.
>
>
>
Thread
errors running WHILE loopMark Haney27 Dec
  • Re: errors running WHILE loophsv27 Dec
  • Re: errors running WHILE loopPeter Brawley28 Dec