Re: Need help with Stored Procedures iin MySQL 5.0.3-beta
Dinesh, Do you have something like DELIMITER | before the CREATE PROCEDURE call, and | DELIMITER ; after it? Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am looking to migrate an Informix database to MySQL and trying to write a simple stored procedure in MySQL 5.0.3. I referred to few examples posted and used the statements from them but i get errors while creating this procedure. CREATE PROCEDURE sp_test ( IN user_idinteger, IN seg_id integer ) BEGIN declare p_rows int; set p_rows = 0; select count(*) into p_rows from test where user_id = user_id and seg_id = seg_id; if p_rows = 1 then update test set visits = visits+1 where user_id = user_id and seg_id = seg_id; else INSERT INTO test VALUES (user_id,seg_id, 1); end if; END ERROR: 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 7 ERROR 1193 (HY000): Unknown system variable 'p_rows' ERROR 1327 (42000): Undeclared variable: p_rows 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 'if p_rows = 1 then update test set visits = visits+1 wher' 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 'else INSERT INTO test VALUES (user_id,seg_id, 1)' 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 if' 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 Any help would be appreciated. Thanks Dinesh No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with Stored Procedures iin MySQL 5.0.3-beta
Yes it is the delimiter which was giving the errors. I had tried putting those statements inside the script, which didn't work. But when i set it from the command line before executing the script to create the procedure it worked. I am still not clear on how to use delimiter without much pain but i will get there. Thanks for your help. Dinesh From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Friday, April 01, 2005 12:03 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com Subject: Re: Need help with Stored Procedures iin MySQL 5.0.3-beta Dinesh, Do you have something like DELIMITER | before the CREATE PROCEDURE call, and | DELIMITER ; after it? Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am looking to migrate an Informix database to MySQL and trying to write a simple stored procedure in MySQL 5.0.3. I referred to few examples posted and used the statements from them but i get errors while creating this procedure. CREATE PROCEDURE sp_test ( IN user_idinteger, IN seg_id integer ) BEGIN declare p_rows int; set p_rows = 0; select count(*) into p_rows from test where user_id = user_id and seg_id = seg_id; if p_rows = 1 then update test set visits = visits+1 where user_id = user_id and seg_id = seg_id; else INSERT INTO test VALUES (user_id,seg_id, 1); end if; END ERROR: 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 7 ERROR 1193 (HY000): Unknown system variable 'p_rows' ERROR 1327 (42000): Undeclared variable: p_rows 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 'if p_rows = 1 then update test set visits = visits+1 wher' 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 'else INSERT INTO test VALUES (user_id,seg_id, 1)' 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 if' 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 Any help would be appreciated. Thanks Dinesh No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005
Re: Need help with Stored Procedures iin MySQL 5.0.3-beta
What I do is prepare the CREATE PROC script in a text editor template which starts with DELIMITER | and ends with | DELIMITER ; PB - Anchan, Dinesh wrote: Yes itis the delimiter which was giving the errors. I had tried putting those statements inside the script, which didn't work. But when i set it from the command line before executing the script to create the procedure it worked. I am still not clear on how to use delimiter without much pain but i will get there. Thanks for your help. Dinesh From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Friday, April 01, 2005 12:03 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com Subject: Re: Need help with Stored Procedures iin MySQL 5.0.3-beta Dinesh, Do you have something like DELIMITER | before the CREATE PROCEDURE call, and | DELIMITER ; after it? Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am looking to migrate an Informix database to MySQL and trying to write a simple stored procedure in MySQL 5.0.3. I referred to few examples posted and used the statements from them but i get errors while creating this procedure. CREATE PROCEDURE sp_test ( IN user_idinteger, IN seg_id integer ) BEGIN declare p_rows int; set p_rows = 0; select count(*) into p_rows from test where user_id = user_id and seg_id = seg_id; if p_rows = 1 then update test set visits = visits+1 where user_id = user_id and seg_id = seg_id; else INSERT INTO test VALUES (user_id,seg_id, 1); end if; END ERROR: 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 7 ERROR 1193 (HY000): Unknown system variable 'p_rows' ERROR 1327 (42000): Undeclared variable: p_rows 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 'if p_rows = 1 then update test set visits = visits+1 wher' 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 'else INSERT INTO test VALUES (user_id,seg_id, 1)' 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 if' 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 Any help would be appreciated. Thanks Dinesh No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
