How to call mySQL stored procedure with both input and output parameters
Posted by:
James MA
Date: September 21, 2008 05:28AM
By using the stmt->bind_param, we can call mySQL stored procedure with input
parameters.
And using multi_query with store_result, we can get the output parameters by
adding a "SELECT @" statement after the call statement.
However, the stmt does not support multi_query, and the multi_query does not
support bind_param also.
So, how can we call a stored procedure with both input and output
parameters.
For example, a dummy stored procedure as below,
CREATE PROCEDURE `sp_test_5`(IN x INT, IN y INT, OUT a INT, OUT b INT)
BEGIN
SET a = x + y;
SET b = x - y;
END
What I can do is to combine the input parameter in the query string, eg. if
I want to pass two variable with value 10 & 5 to the stored procedure, I
will first concatenate the value to make a query string "call
sp_test_5(10,5,@a,@b);" then call using the multi_query method.
I'd like to know if I can use a parameter format like "call sp_test_5(?,?,@a,@b);" then set the parameter by using the function like bind_param to set the input
value.
Thanks in advance!
James