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

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