Calling stored procedures with supported databases / Stored procedure call with MySQL |
Since MySQL C API (version 5.0) does not support an output parameter specification, the IN/OUT/INOUT technique cannot be used.
In order to return values from a MySQL stored procedure or stored function, you must use SQL variables. There are three steps to execute the procedure or function:
In order to retrieve returning values into program variables, you must use an INTO clause in the EXECUTE instruction.
The following example shows how to call a stored procedure with output parameters:
MySQL version 5.0 does not allow you to prepare the CREATE PROCEDURE statement; you may need to execute this statement from the mysql command line tool.
MySQL version 5.0 cannot execute "SELECT @variable" with server-side cursors. Since the MySQL driver uses server-side cursors to support multiple active result sets, it is not possible to execute the SELECT statement to return output parameter values.
MAIN DEFINE n INTEGER DEFINE d DECIMAL(6,2) DEFINE c VARCHAR(200) DATABASE test1 EXECUTE IMMEDIATE "create procedure proc1(" || " p1 integer," || " out p2 numeric(6,2)," || " out p3 varchar(200) || " )" || " no sql begin" || " set p2 = p1 + 0.23;" || " set p3 = concat( 'Value = ', p1 );" || " end;" LET n = 111 EXECUTE IMMEDIATE "set @p1 = ", n EXECUTE IMMEDIATE "set @p2 = NULL" EXECUTE IMMEDIATE "set @p3 = NULL" EXECUTE IMMEDIATE "call proc1(@p1, @p2, @p3)" PREPARE stmt FROM "select @p2, substring(@p3,1,200)" EXECUTE stmt INTO d, c DISPLAY d DISPLAY c END MAIN