MySQL Bugs: #11638: Cannot prepare and execute a stored procedure with OUT parameter

Bug #11638	Cannot prepare and execute a stored procedure with OUT parameter
Submitted:	29 Jun 2005 14:45	Modified:	3 Feb 2009 20:41
Reporter:	Guy Harrison
Status:	Closed
Category:	C API	Severity:	S1 (Critical)
Version:	5.0.9-dev	OS:	Linux (RHAS 3.1)
Assigned to:	Alexander Nozdrin	Target Version:	6.0-rc
Triage:	D1 (Critical)

Description:
I want to prepare a statement for a stored procedure that includes an OUT parameter. 
However, when I EXECUTE I get error 1414.  It seems that the EXECUTE command does not
anticipate output parameters

How to repeat:
mysql> drop procedure if exists test_prepare;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> create  procedure test_prepare(x int,out y int)
    ->  deterministic no sql
    ->  set y=x;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> prepare s1 from 'call test_prepare(?,?)';
Query OK, 0 rows affected (0.03 sec)
Statement prepared

mysql>
mysql> set @x=1;
Query OK, 0 rows affected (0.03 sec)

mysql> set @y=0;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> execute s1 using @x,@y;
ERROR 1414 (42000): OUT or INOUT argument 2 for routine prod.test_prepare is not a
variable

You can workaround by using some global variable like :

mysql> prepare s2 from 'call test_prepare(?,@z);';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute s2 using @x;
Query OK, 0 rows affected (0.00 sec)

mysql> select @z;
+------+
| @z   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

An even better workaround: Don't prepare CALLs at all. There's really no gain in doing
this.

(It's still a bug of course, but anyway...)

Getting the same error via prepared statements via the MySQL C API when I try to pass in
an output parameter.

[Database] 1,414 mysql_execute failed: OUT or INOUT argument 4 for routine
thrall.login_account is not a variable

Then again the docs say CALL isn't supported for prepared statements.

"There's really no gain in doing this." - Where can I find more information on where I
will and won't gain from prepared statements?

I have a same problem with .net

I don't have a strong opinion on this, except that if we disable it, chances that it gets
even more broken are higher.

I'm happy enough with the workaround, but if it affects your fix/nofix decision, this
error prevents the use of OUT or INOUT parameters being retrieved using the bindParam()
method in PHP 5.1 PDO.  

The reason is that PDO creates MYSQL prepared statements to create PDO statement objects
and only PDO statement objects support variable binding.

I did not have any problem using output parameters in .NET or Java.  Perl and Python
don't support output parameters anyway, so the most significant issue is probably support
for PDO.

Regards,Guy

A related issue is that the C client prepared statement API has a hard dependency on only
one result set comming back.  For full support of prepared CALL statements, a next_result
mechanism would have to be added as a many applications of stored procs require multiple
results to be returned.

This is one reason that neither Connector/Net or Connector/J use server-side prepared
statements to execute stored procedures, and instead build dynamic SQL in the client and
execute it.

The other reason, is as noted, that prepared statements can't deal with multiple result
set output, while many users want to do this.

Just ran into the same problem with PHP PDO, so this is to indicate that we have an
interest in the fix as well.

Same problem here

valhalla ~ # mysqladmin version -p
Enter password:
mysqladmin  Ver 8.41 Distrib 5.0.18, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.18-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 3 days 19 hours 45 min 57 sec

Threads: 10  Questions: 40889  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables:
40  Queries per second avg: 0.124

valhalla ~ # php --version
PHP 5.1.2-gentoo (cli) (built: Mar 12 2006 20:03:44)
Copyright (c) 1997-2006 The PHP Group
Zend Engine v2.1.0, Copyright (c) 1998-2006 Zend Technologies

valhalla ~ # equery l pdo
[ Searching for package 'pdo' in all categories among: ]
 * installed packages
[I--] [  ] dev-php5/pecl-pdo-1.0.2 (0)
[I--] [  ] dev-php5/pecl-pdo-mysql-1.0.1 (0)

Got it working!

Problematic way:
$return_code = "@vError";
$stmt = $db->prepare("CALL p_FSE_ListDirectoryContent(?, ?, ?)");
$stmt->bindParam(1, $FSEId, PDO::PARAM_INT);
$stmt->bindParam(2, $userId, PDO::PARAM_INT);
$stmt->bindParam(3, $returnCode, PDO::PARAM_STR, 20);
$stmt->execute();

Working way:
$return_code = "v_Error";
$stmt = $db->prepare("CALL p_FSE_ListDirectoryContent(?, ?, @)");
$stmt->bindParam(1, $FSEId, PDO::PARAM_INT);
$stmt->bindParam(2, $userId, PDO::PARAM_INT);
$stmt->bindParam(3, $returnCode, PDO::PARAM_STR, 20);
$stmt->execute();

It works now

EDIT:
The above works, but it doesn't store the return value in the bound variable.

I have to resign and use server-side variable, and do a SELECT @variable to obtain their
value in PHP...

You cannot prepare the name of the variable that you want to submit in the manner that you
are trying becuase it no longer knows that it is the name of a variable.

The DB essentially sees what you are doing here:

prepare s1 from 'call test_prepare(?,?)';
set @x=1;
set @y=0;
execute s1 using @x,@y;

as:

call test_prepare('0', '1');

and '1' is not a valid out variable...

I'd like to see something like:

       PREPARE delDeps_stmt FROM 'SELECT COUNT(1) FROM t1';
       SET @count := EXECUTE delDeps_stmt;
       DEALLOCATE PREPARE delDeps_stmt;

There needs to be a clean way to get some results back from what is being executed

CJ

See related bug#17898

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46816

ChangeSet@1.2637, 2008-05-19 18:08:10+04:00, anozdrin@quad. +5 -0
  The first part of a patch for Bug#11638: Cannot prepare and execute
  a stored procedure with OUT parameter. This is the easiest part,
  which adds support for OUT-parameters in SQL-layer.

This issue has been reported several times, as several bugs. 

However we still do not have a resolution

It be nice if we could get a release before V6 comes out.

Yes I am being sarcastic, Yes I have been waiting since V5 came out...

Dave

Patch:

http://lists.mysql.com/commits/50950
 2675 Alexander Nozdrin	2008-08-05
      Patch for WL#4435: Support OUT-parameters in prepared statements.
      
      After execution of a prepared statement, send OUT parameters of the invoked
      stored procedure, if any, to the client.
      
      When using the binary protocol, send the parameters in an additional result set
      over the wire.  When using the text protocol, assign out parameters to the user
      variables from the CALL(@var1, @var2, ...) specification.

This is WL#4435, which I also just moved into "In-Documentation".
That was pushed into 6.0.8.

Noted in 6.0.8 changelog.

Previously, prepared CALL statements could be used via the C API only
for stored procedures that produce at most one result set, and
applications could not use placeholders for OUT or INOUT parameters.
For prepared CALL statements used via PREPARE and EXECUTE,
placeholders could not be used for OUT or INOUT parameters.

For the C API, prepared CALL support now is expanded in the following
ways: 

* A stored procedure can produce any number of result sets. The number
  of columns and the data types of the columns need not be the same for
  all result sets.

* The final values of OUT and INOUT parameters are available to the
  calling application after the procedure returns. These parameters are
  returned as an extra single-row result set following any result sets
  produced by the procedure itself. The row contains the values of the
  OUT and INOUT parameters in the order in which they are declared in
  the procedure parameter list.

* A new C API function, mysql_stmt_next_result(), is available for
  processing stored procedure results. See 
  http://dev.mysql.com/doc/refman/6.0/en/c-api-prepared-call-statements.html

* The CLIENT_MULTI_RESULTS flag now is enabled by default. It no longer
  needs to be enabled when you call mysql_real_connect(). (This flag is
  necessary for executing stored procedures because they can produce
  multiple result sets.)

For PREPARE and EXECUTE, placeholder support for OUT and INOUT
parameters is now available. See 
http://dev.mysql.com/doc/refman/6.0/en/call.html