Need help in Stored procedures and functions
Hi, I am trying to write a function which returns multiple values. I don't knowif this is allowed or not but i am not able to create this function. I get the following 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 ' integer, integer, ' at line 7 I have attached the function i am trying to create. Please refer to the attached thread for contract related information. I would appreciate if somebody can give me a call so that i can clear few other questions. Thanks Dinesh Anchan 212-231-7187 From: Pam Keen [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 10:21 AMTo: Marcus, JeffCc: Pisula, Mike; Pedersen, Mads; Mishkind, Scott; Anchan, Dinesh; [EMAIL PROTECTED]; 'Philip Antoniades'; 'Kenneth Moses'Subject: RE: MySQL/24/7 Real Media support contract renewal Jeff, What is your availability to discuss/resolve the below and to discuss the Informix migration effort. I am available early next week - Monday all day and Tuesday, late morning,early afternoon. As an interim measure, I extended support till the end of this week for your team's development efforts. Thank you, Pam Keen 212 566 2714 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help in Stored procedures and functions
Peter, Thank you for your reply. Actually that request was accidentally sent to mysql@lists.mysql.com instead of [EMAIL PROTECTED] MySQL support also suggested the same thing. Problem is, values in the OUT variable can be retrieved only from a future SQL query. But we are trying to call this procedure from a C program. C Program can parse the result set if it is an output from a simple SELECT statement. I am finding it difficult achieve something like below: Here is a sample of informix function: Create function test() Returning integer, smallint, char(1), varchar(65); Foreach select col1, col2 into p_col1, p_col2 from table1 where where_clause foreach select col1, col2, col3, col4 into i_col1, i_col2, i_col3, i_col4 from table2 where i_col1 = p_col1 and i_col2 = p_col2 if p_col3 = some_thing then do_some_calculations; let p_string1 = some_thing end if; if p_col4 = some_thing then do_some_calculations; let p_string2 = some_thing; end if; return i_col3, i_col4, p_string1, p_string2 with resume; end foreach End foreach End function; Thank you for your help Dinesh From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 12:24 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com Subject: Re: Need help in Stored procedures and functions Dinesh, A stored func returns a scalar. To return multiple values, use an sproc declaring the INOUT variables you need, pick up their values in the calling code after the sproc has executed. Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am trying to write a function which returns multiple values. I don't know if this is allowed or not but i am not able to create this function. I get the following 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 ' integer, integer, ' at line 7 I have attached the function i am trying to create. Please refer to the attached thread for contract related information. I would appreciate if somebody can give me a call so that i can clear few other questions. Thanks Dinesh Anchan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help in Stored procedures and functions
Shawn, Thank you for your reply. It looks like this is the only option. But i was trying to avoid the creation of temp table and parsing of data at the application layer since it can potentially slow down the overall performance. Dinesh From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 4:03 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Need help in Stored procedures and functions Anchan, Dinesh [EMAIL PROTECTED] wrote on 04/07/2005 03:44:07 PM: Peter, Thank you for your reply. Actually that request was accidentally sent to mysql@lists.mysql.com instead of [EMAIL PROTECTED] MySQL support also suggested the same thing. Problem is, values in the OUT variable can be retrieved only from a future SQL query. But we are trying to call this procedure from a C program. C Program can parse the result set if it is an output from a simple SELECT statement. I am finding it difficult achieve something like below: Here is a sample of informix function: Create function test() Returning integer, smallint, char(1), varchar(65); Foreach select col1, col2 into p_col1, p_col2 from table1 where where_clause foreach select col1, col2, col3, col4 into i_col1, i_col2, i_col3, i_col4 from table2 where i_col1 = p_col1 and i_col2 = p_col2 if p_col3 = some_thing then do_some_calculations; let p_string1 = some_thing end if; if p_col4 = some_thing then do_some_calculations; let p_string2 = some_thing; end if; return i_col3, i_col4, p_string1, p_string2 with resume; end foreach End foreach End function; Thank you for your help Dinesh From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 12:24 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com Subject: Re: Need help in Stored procedures and functions Dinesh, A stored func returns a scalar. To return multiple values, use an sproc declaring the INOUT variables you need, pick up their values in the calling code after the sproc has executed. Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am trying to write a function which returns multiple values. I don't know if this is allowed or not but i am not able to create this function. I get the following 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 ' integer, integer, ' at line 7 I have attached the function i am trying to create. Please refer to the attached thread for contract related information. I would appreciate if somebody can give me a call so that i can clear few other questions. Thanks Dinesh Anchan I don't speak Informix (and I haven't used MySQL's sprocs yet) but it seems as though you are looping through the JOIN of one table against another and returning a row of calculated information for each JOINed-row combination. Could you possibly perform your calculations and accumulate your return values in a temporary table (I suggest a temporary table because it is connection-specific) then return your results at the end of the statement with a simple SELECT from the temp table? If all of the values you need to return are in variables, you should be able to simulate a single row table by saying SELECT variable1 as variable1name, variable2 as variable2name, ...; You shouldn't need a FROM clause because you are only SELECTing constants. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Can a Function return multiple values?
I am trying to return multiple values from a Function, not sure if it is allowed: delimiter | create function cf_test() returns integer, varchar(255)/* tried with semicolon after varchar(255) */ begin declare p_col2 smallint; declare p_col3 varchar(255); select col2, col3 into p_col2, p_col3 from test where col1 = 1; return p_col1, p_col2; end | and i get the following error when i am trying to create this function: source test5.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 ' varchar(255); begin declare p_col2 smallint; declare p_col3 varch' at line 2 Any advice would be greatly appreciated. Thanks Dinesh
Need help with Stored Procedures iin MySQL 5.0.3-beta
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
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
