Problem with WHILE statement in MySQL

[James E Koehler]

I can't get the WHILE statement to work in MySQL.

The version of MySQL that I am using is:
Ver 12.16 Distrib 4.0.6-gamma, for Win95/Win98 (i32)
running on Windows MX.

Here is the relevant section from the manual:

20.1.9.7 WHILE Statement

[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]

The statement or statements within a WHILE statement are repeated as
long as the search_condition is true.

begin_label and end_label must be the same, if both are specified.

For example:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO
....
SET v1 = v1 - 1;
END WHILE;
END

- - - - - - - - -

I tried to execute the example code - slightly modified:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO

SET v1 = v1 - 1;
END WHILE;
END

I got the following error message:

"You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL version for the right syntax to use near
‘PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5' at line 1."

Then I tried it again with @ in front of the variables:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE @v1 INT DEFAULT 5;

WHILE @v1 > 0 DO

SET @v1 = @v1 - 1;
END WHILE;
END

I got the same error message.

- - - - - - - - -

Apart from the above, I have tried numerous other variations including
table searches.

What am I doing wrong?

Thanks much for your help.

[Chris Hope]

James E Koehler wrote:

I can't get the WHILE statement to work in MySQL.

The version of MySQL that I am using is:
Ver 12.16 Distrib 4.0.6-gamma, for Win95/Win98 (i32)
running on Windows MX.

Here is the relevant section from the manual:

20.1.9.7 WHILE Statement

[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]

The statement or statements within a WHILE statement are repeated as
long as the search_condition is true.

begin_label and end_label must be the same, if both are specified.

For example:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
END

- - - - - - - - -

I tried to execute the example code - slightly modified:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO

SET v1 = v1 - 1;
END WHILE;
END

I got the following error message:

"You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL version for the right syntax to use near
‘PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5' at line 1."

Then I tried it again with @ in front of the variables:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE @v1 INT DEFAULT 5;

WHILE @v1 > 0 DO

SET @v1 = @v1 - 1;
END WHILE;
END

I got the same error message.

- - - - - - - - -

Apart from the above, I have tried numerous other variations including
table searches.

What am I doing wrong?

Thanks much for your help.

Stored procedures aren't supported in MySQL until 5.x which is currently in
alpha development. You are using 4.0.6. This is why you are getting these
errors.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[James E Koehler]

Many thanks to Chris Hope for his help.

I am working on my own and I don't have quick access to any local
MySQL gurus. So I appreciate very much any help that participants of
this newsgroup may supply.

I went to http://dev.mysql.com/downloads/mysql/5.0.html and downloaded
MySQL version 5.0.0a-alpha.

I assume that version 5 is not intended for general use as it only
seems to handle one database called "test".

I set up the demo example:
CREATE PROCEDURE dowhile()
BEGIN
DECLARE @v1 INT DEFAULT 5;

WHILE @v1 > 0 DO

SET @v1 = @v1 - 1;
END WHILE;
END

but it still won't work - yielding the same unhelpful error message.

Am I right in thinking tat version 5 won't be available for general
use for quite a while - the info on http://www.mysql.com/ indicate hat
version 4.1 has just become production-ready.

- - - - - - - - - -

What I want to with a WHILE statement can very likely be done with one
or two MySQL statements.

What I want to do is fill in the zeroed values with copies of the
non-zero values resulting in the modified version of the table as
shown in VERSION 2 below.

In other words, I want to replicate entries following the entries as
originally supplied.

CREATE TABLE `table1` (
`record_number` int(8) NOT NULL auto_increment,
`field_A` int(11) default NULL,
PRIMARY KEY (`record_number`),
UNIQUE KEY `record_number` (`record_number`)
) TYPE=MyISAM

VERSION 1
1 0
2 0
3 0
4 99
5 0
6 0
7 0
8 0
9 33
10 0
11 0
12 0

VERSION 2
1 0
2 0
3 0
4 99
5 99
6 99
7 99
8 99
9 33
10 33
11 33
12 33

This would be trivial with a WHILE statement. However I cant figure
out how to do this in MySQL without one.

Your help would be much appreciated.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Chris Hope <bl*******@electrictoolbox.com> wrote in message news:<10**************@216.128.74.129>...

James E Koehler wrote:

I can't get the WHILE statement to work in MySQL.

The version of MySQL that I am using is:
Ver 12.16 Distrib 4.0.6-gamma, for Win95/Win98 (i32)
running on Windows MX.

Here is the relevant section from the manual:

20.1.9.7 WHILE Statement

[begin label:] WHILE search condition DO
statement list
END WHILE [end label]

The statement or statements within a WHILE statement are repeated as
long as the search condition is true.

begin label and end label must be the same, if both are specified.

For example:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
END

- - - - - - - - -

I tried to execute the example code - slightly modified:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO

SET v1 = v1 - 1;
END WHILE;
END

I got the following error message:

"You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL version for the right syntax to use near
PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5' at line 1."

Then I tried it again with @ in front of the variables:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE @v1 INT DEFAULT 5;

WHILE @v1 > 0 DO

SET @v1 = @v1 - 1;
END WHILE;
END

I got the same error message.

- - - - - - - - -

Apart from the above, I have tried numerous other variations includin

g

table searches.

What am I doing wrong?

Thanks much for your help.

Stored procedures aren't supported in MySQL until 5.x which is currentl
y in
alpha development. You are using 4.0.6. This is why you are getting the
se
errors.

[Wouter]

:) I see why you want a while loop

SELECT * FROM table1 WHERE field_A>0

And then with the result array you want to fill the record_numers between
result 0 and 1 that have value 0 for field_A with the value of field_A of
result 0.
And then the same for the record_nubers between result 1 and 2 etc.

:)
Maybe use PHP for that loop?

Wouter

"James E Koehler" <ko*****@btinternet.com> wrote in message
news:d7**************************@posting.google.c om...
: Many thanks to Chris Hope for his help.
:
: I am working on my own and I don't have quick access to any local
: MySQL gurus. So I appreciate very much any help that participants of
: this newsgroup may supply.
:
: I went to http://dev.mysql.com/downloads/mysql/5.0.html and downloaded
: MySQL version 5.0.0a-alpha.
:
: I assume that version 5 is not intended for general use as it only
: seems to handle one database called "test".
:
: I set up the demo example:
: CREATE PROCEDURE dowhile()
: BEGIN
: DECLARE @v1 INT DEFAULT 5;
:
: WHILE @v1 > 0 DO
:
: SET @v1 = @v1 - 1;
: END WHILE;
: END
:
: but it still won't work - yielding the same unhelpful error message.
:
: Am I right in thinking tat version 5 won't be available for general
: use for quite a while - the info on http://www.mysql.com/ indicate hat
: version 4.1 has just become production-ready.
:
: - - - - - - - - - -
:
: What I want to with a WHILE statement can very likely be done with one
: or two MySQL statements.
:
: What I want to do is fill in the zeroed values with copies of the
: non-zero values resulting in the modified version of the table as
: shown in VERSION 2 below.
:
: In other words, I want to replicate entries following the entries as
: originally supplied.
:
: CREATE TABLE `table1` (
: `record_number` int(8) NOT NULL auto_increment,
: `field_A` int(11) default NULL,
: PRIMARY KEY (`record_number`),
: UNIQUE KEY `record_number` (`record_number`)
: ) TYPE=MyISAM
:
: VERSION 1
: 1 0
: 2 0
: 3 0
: 4 99
: 5 0
: 6 0
: 7 0
: 8 0
: 9 33
: 10 0
: 11 0
: 12 0
:
: VERSION 2
: 1 0
: 2 0
: 3 0
: 4 99
: 5 99
: 6 99
: 7 99
: 8 99
: 9 33
: 10 33
: 11 33
: 12 33
:
: This would be trivial with a WHILE statement. However I cant figure
: out how to do this in MySQL without one.
:
: Your help would be much appreciated.
:
: - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
:
: Chris Hope <bl*******@electrictoolbox.com> wrote in message
news:<10**************@216.128.74.129>...
: > James E Koehler wrote:
: >
: > > I can't get the WHILE statement to work in MySQL.
: > >
: > > The version of MySQL that I am using is:
: > > Ver 12.16 Distrib 4.0.6-gamma, for Win95/Win98 (i32)
: > > running on Windows MX.
: > >
: > > Here is the relevant section from the manual:
: > >
: > > 20.1.9.7 WHILE Statement
: > >
: > > [begin label:] WHILE search condition DO
: > > statement list
: > > END WHILE [end label]
: > >
: > > The statement or statements within a WHILE statement are repeated as
: > > long as the search condition is true.
: > >
: > > begin label and end label must be the same, if both are specified.
: > >
: > > For example:
: > >
: > > CREATE PROCEDURE dowhile()
: > > BEGIN
: > > DECLARE v1 INT DEFAULT 5;
: > >
: > > WHILE v1 > 0 DO
: > > ...
: > > SET v1 = v1 - 1;
: > > END WHILE;
: > > END
: > >
: > > - - - - - - - - -
: > >
: > > I tried to execute the example code - slightly modified:
: > >
: > > CREATE PROCEDURE dowhile()
: > > BEGIN
: > > DECLARE v1 INT DEFAULT 5;
: > >
: > > WHILE v1 > 0 DO
: > >
: > > SET v1 = v1 - 1;
: > > END WHILE;
: > > END
: > >
: > > I got the following error message:
: > >
: > > "You have an error in your SQL syntax. Check the manual that
: > > corresponds to your MySQL version for the right syntax to use near
: > > PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5' at line 1."
: > >
: > > Then I tried it again with @ in front of the variables:
: > >
: > > CREATE PROCEDURE dowhile()
: > > BEGIN
: > > DECLARE @v1 INT DEFAULT 5;
: > >
: > > WHILE @v1 > 0 DO
: > >
: > > SET @v1 = @v1 - 1;
: > > END WHILE;
: > > END
: > >
: > > I got the same error message.
: > >
: > > - - - - - - - - -
: > >
: > > Apart from the above, I have tried numerous other variations includin
: > g
: > > table searches.
: > >
: > > What am I doing wrong?
: > >
: > > Thanks much for your help.
: >
: > Stored procedures aren't supported in MySQL until 5.x which is currentl
: > y in
: > alpha development. You are using 4.0.6. This is why you are getting the
: > se
: > errors.

[Bill Karwin]

James E Koehler wrote:

I set up the demo example:
CREATE PROCEDURE dowhile()
BEGIN
DECLARE @v1 INT DEFAULT 5;

WHILE @v1 > 0 DO

SET @v1 = @v1 - 1;
END WHILE;
END

but it still won't work - yielding the same unhelpful error message.
You're using what appears to be Microsoft SQL Server syntax.
Did you try reading the docs on the MySQL stored procedure language?

There's an example of a WHILE loop here:
http://dev.mysql.com/doc/mysql/en/WHILE_Statement.html
Am I right in thinking tat version 5 won't be available for general
use for quite a while - the info on http://www.mysql.com/ indicate hat
version 4.1 has just become production-ready.

Correct, 5.0 is in a state of "alpha" according to MySQL.com.
Features are no doubt subject to change, and the release date is not stated.

Regards,
Bill K.