Re: Need help in Stored procedures and functions
Dinesh, Right, sorry for being dense, the C API doesn't yet have a published func for calling sprocs. You could put results in a temp table and query that table after your sproc returns. Or you might be able to rely on a stored procedure trick: even if the sproc issues one or more SELECT ... INTO statements, if its last SELECT has no INTO clause, the sproc will return its result just as if you'd issued that query directly. PB - Anchan, Dinesh wrote: 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 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 = then do_some_calculations; let p_string1 = end if; if p_col4 = then do_some_calculations; let p_string2 = ; 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 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.4 - Release Date: 4/6/2005 -- 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 > >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 = then > do_some_calculations; > let p_string1 = > end if; > > if p_col4 = then > do_some_calculations; > let p_string2 = ; > 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
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 > >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 = then > do_some_calculations; > let p_string1 = > end if; > > if p_col4 = then > do_some_calculations; > let p_string2 = ; > 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
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 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 = then do_some_calculations; let p_string1 = end if; if p_col4 = then do_some_calculations; let p_string2 = ; 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
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 212-231-7187 From: Pam Keen [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 07, 2005 10:21 AM To: Marcus, Jeff Cc: 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 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.4 - Release Date: 4/6/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.4 - Release Date: 4/6/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
