List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 7 2005 8:03pm
Subject:RE: Need help in Stored procedures and functions
View as plain text  
"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



Thread
Need help in Stored procedures and functionsDinesh Anchan7 Apr
  • Re: Need help in Stored procedures and functionsPeter Brawley7 Apr
RE: Need help in Stored procedures and functionsDinesh Anchan7 Apr
  • RE: Need help in Stored procedures and functionsSGreen7 Apr
  • Re: Need help in Stored procedures and functionsPeter Brawley7 Apr
RE: Need help in Stored procedures and functionsDinesh Anchan7 Apr