MySQL Query Not Working

MySQL with Do While Lopp not working

[The_Warden]

Hi. I hope this is the correct spot to post this topic. I wasn't able to figure out where a general discussion would be regarding MySQL or databases for that matter. Anyways here's my problem.

I'm trying to write a query that does an insert statement multiple times until the while loop returns false. I tried executing the below code but MySQL server returns the following error. Oh also I'm running MySQL 3.23.37. I tried to check and see if the problem was due to the version I have but was unable to locate anything on MySQL's web site. For all I know maybe I'm going about this query the wrong way, never done a dowhile loop before in SQL. Any ideas to solve the problem would be greatly appreciated.

ERROR 1064: You have an error in your SQL syntax near 'PROCEDURE dowhile()
ERROR 1064: You have an error in your SQL syntax near 'WHILE v1 <= 41675 DO INSERT INTO PostalCodeReps(Postal_Code_Info_ID, Company' at line 1

ERROR 1064: You have an error in your SQL syntax near 'END WHILE' at line 1
ERROR 1064: You have an error in your SQL syntax near 'END' at line 1

CREATE PROCEDURE dowhile() 
BEGIN 
 DECLARE v1 INT DEFAULT 41190; 
WHILE v1 <= 41675 DO 
 INSERT INTO PostalCodeReps(Postal_Code_Info_ID, Company_ID_Unitary, Company_ID_Applied) VALUES(v1, 1332,1332) 
 SET v1 = v1 + 1; 
 END WHILE; 
END 

[drbrain]

It looks like you'll have to wait, upgrade to a real database, or get creative with your procedure paramaters, as MySQL doesn't yet support references to tables in procedures/functions. See the Note and examples at the bottom of:

[dev.mysql.com...]

[killroy]

MySQL has only strated adding Stored Procedures, and in a limited format. If you rely on stored procedures use soemthing like Oracle. MySQL is designed to be lean and mean.

SN

[The_Warden]

Okay that is fine. Is there a way to do a while loop then without using a procedure?

[coopster]

A server-side scripting language such as PHP would work well here. Are you trying to do this through a web server-side script, or from a command line?

[The_Warden]

I'm trying to achieve this via MySQL Control Center which is basiclly the same as doing it command line. Looks like I might have to do something up quick in PHP. So nothing else I can do then in MySQL it self then?

[coopster]

To the best of my knowledge I would have to say no, not without a stored procedure. You would have to script something. Easy in PHP...

<?php 
// Connection: 
$db_server = 'localhost'; 
$db_user  = 'userid'; 
$db_pwd  = 'password'; 
$db_name  = 'test'; 
$db_link = mysql_connect($db_server, $db_user, $db_pwd) 
 or exit('Could not connect (' . mysql_errno() . '): ' 
 . mysql_error()); # Test mode only 
$db = mysql_select_db($db_name, $db_link) 
 or exit('Could not select database (' . mysql_errno() . '): ' 
 . mysql_error()); # Test mode only 
// Process: 
for ($v1 = 41190; $v1 <= 41675; $v1++) { 
 mysql_query("INSERT INTO PostalCodeReps 
 (Postal_Code_Info_ID, Company_ID_Unitary, Company_ID_Applied) 
 VALUES($v1, 1332, 1332)"); 
} 
?>

[The_Warden]

Okay thanks. I've already started on doing a PHP solution. Thanks everyone for your help.