Re: Need help in Stored procedures and functions

[Peter Brawley]

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@lists.mysql.com
instead of [EMAIL PROTECTED]  

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:[EMAIL PROTECTED] 
Sent: Thursday, April 07, 2005 12:24 PM
To: Anchan, Dinesh
Cc: mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]