asked on

MySQL WHILE ... DO syntax error

I am getting the following error message:
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 'Declare i INT Default 0; While i < 36500 DO Insert Into Calendar (_Date, _Day, _' at line 1

I have set my delimiter:
Delimiter $$

And my code's below.

Can anyone spot my syntax error? I'm trying to follow the example at http://dev.mysql.com/doc/refman/5.0/en/while-statement.html

thanks!

{edited to adjust formatting of code}

Begin 
Declare i INT Default 0; 
While i < 36500 DO 
Insert Into Calendar (_Date, _Day, _Month, _Year) Values ('2009-01-01' + i, Day('2009-01-01'+i), Month('2009-01-01'+i), Year('2009-01-01' +i)); 
set i = i +1; 
End while; 
End$$

Open in new window

Daniel Wilson

ASKER

It's in the DECLARE statement. That statement alone throws the error ...

Daniel Wilson

ASKER

So ... where's my DECLARE going wrong?
http://dev.mysql.com/doc/refman/5.0/en/declare.html

mysql> BEGIN Declare i INT; set i = 0; END$$
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 'Declare i INT; set i = 0; END' at line 1

rockiroads

whats with the $$ at the end of END?

remove $$ then try it

Daniel Wilson

ASKER

I had previously set my delimiter to $$ so that I could use ; for the statements within the BEGIN ... END block.

Using ; as the delimiter, I get:

So ... any ideas what's wrong with my DECLARE statement?

Thanks!

mysql> BEGIN Declare i INT; set i =  0; END;                                    
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 'Declare i INT' at line 1
ERROR 1193 (HY000): Unknown system variable 'i'
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

Open in new window

rockiroads

hmm. I took your code and tweaked it to fit into my db

Begin
Declare i INT Default 0;
      While i < 10 DO
            Insert Into tblScrap (intfield, txtfield, datefield) Values (i, 'xxx', '2009-01-01');
            set i = i +1;
      End while;
End

And the above worked.

Can you try something simple like this first? What are you using to run your procedure? I am using Navicat (Lite version available and its free)

Daniel Wilson

ASKER

I simplified it further:

Begin
Declare i INT Default 0;
While i < 10 DO
Select i;
set i = i +1;
End while;
End

With the ; as a delimiter (I'm using command-line MySQL):

mysql> Begin
    -> Declare i INT Default 0;
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 'Declare i INT Default 0' at line 2
mysql>       While i < 10 DO
    ->            Select i;
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 i < 10 DO
           Select i' at line 1
mysql>             set i = i +1;
ERROR 1193 (HY000): Unknown system variable 'i'
mysql>       End while;
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
mysql> End

Open in new window

SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daniel Wilson

ASKER

And with the $$ delimiter ...

mysql> Begin
    -> Declare i INT Default 0;
    ->       While i < 10 DO
    ->             Select i;
    ->            set i = i +1;
    ->       End while;
    -> End $$
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 'Declare i INT Default 0;
      While i < 10 DO
            Select i;
           ' at line 2

Open in new window

rockiroads

>> (I'm using command-line MySQL)

I havent used that. Seems much easier to use a gui app. Very easy to use. http://www.navicat.com/download/download.html

when I go to command line client, I enter password then it hangs for ages then closes. Dont know whats wrong but having no problems with Navicat to mysql so cant be bothered to find out why its not working.

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

ah, I thought it was being done in that. I guess Navicat hides that from you. When you create a new procedure or function, you specify the input arguments then it takes you straight to the BEGIN/END code.
you can later change it and see the full stored procedure code.

Now I know better what to ask lol! Thanks

Daniel Wilson

ASKER

Thanks for the effort!