"Anchan, Dinesh" <Dinesh.Anchan@stripped> wrote on 04/07/2005
03:44:07 PM:
> Peter,
>
> Thank you for your reply.
>
> Actually that request was accidentally sent to mysql@stripped
> instead of support@stripped.
>
> 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:peter.brawley@stripped]
> Sent: Thursday, April 07, 2005 12:24 PM
> To: Anchan, Dinesh
> Cc: mysql@stripped
> 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
