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
