List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 7 2005 9:24pm
Subject:Re: Need help in Stored procedures and functions
View as plain text  
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@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
>
>
>
>  
>


-- 
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

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