List:General Discussion« Previous MessageNext Message »
From:Dinesh Anchan Date:April 7 2005 9:05pm
Subject:RE: Need help in Stored procedures and functions
View as plain text  
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: SGreen@stripped [mailto:SGreen@stripped] 
Sent: Thursday, April 07, 2005 4:03 PM
To: Anchan, Dinesh
Cc: mysql@stripped; peter.brawley@stripped
Subject: RE: Need help in Stored procedures and functions




"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