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 12:45	Modified:	3 Feb 2009 19:41
Reporter:	Guy Harrison	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S1 (Critical)
Version:	5.0.9-dev	OS:	Linux (RHAS 3.1)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

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

Noted in 5.5.3 changelog.

Still an issue in MySQL 5.6.10:
I am testing on Apache 2.2.22/PHP 5.4.3/MySQL 5.6.10

The following PHP and a PDO Connection:
$testParam = 'Bill';
$sth = $dbh->prepare('CALL TestProcedure(?)');
$sth->bindParam(1, $testParam, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 100);
$sth->execute();
print($testParam);

Should return "Hi Bill!!", but instead produces the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 1 for routine phptestdb.TestProcedure is not a variable or NEW pseudo-variable in BEFORE trigger' in [path-removed]\TestPDO.php on line 65

I can't find a patch or any way around this?
So since 2005 when this was first reported it has never been resolved to now... is that right?

Regards,

Scott

Scott,

I guess, you have the sources since you were looking for a patch.
You can have a look at mysql-test/t/ps.test and look there for
"WL#4435: Support OUT-parameters in prepared statements" section.
There are several tests there checking OUT-parameters in prepared
statements.

If you still believe, you've encountered a bug, please submit a new
bug report.

Thanks!

Alexander,

Thanks for responding.  The link you gave goes to a page that says Task Not Found.
WL#4435: http://dev.mysql.com/worklog/task/?id=4435

I don't have sources, I just downloaded the latest version of MySQL.  When I said patch I was assuming it was like an exe file that you installed over the install of the latest version.
As the issue I have described above is happening in the latest version of MySQL 5.6.10.

Regards,

Scott

I would like to chime in confirming that this is still a bug with MySQL 5.5.30, at least when accessed through PHP 5.4.14 on Mac OS X 10.8.3.

This is the sample PHP code I'm using:

$connectOpts = array
(
    PDO::ATTR_PERSISTENT => FALSE,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8, @@sql_mode = STRICT_ALL_TABLES, @@foreign_key_checks = 1'
);
$pdo = new PDO('mysql:host=' . $dbHost . ';dbname=' . $dbName, $dbUsername, $dbPassword, $connectOpts);

$foo = 1;
$bar = 1;
$query = '
    CALL
        baz(?, ?, ?)';

$stmt = $pdo->prepare($query);
$stmt->bindValue(1, $foo, PDO::PARAM_INT);
$stmt->bindValue(2, $bar, PDO::PARAM_INT);
$stmt->bindParam(3, $returnVal, PDO::PARAM_INT, 4000);

$result = $stmt->execute();
print "Result set:\n";
print_r($resultl);
print "Out param: $returnVal\n";
die();

The "baz" stored procedure is defined as accepting two IN parameters and one OUT parameter, the last one. Internally, the procedure performs a simple select (not stored in a local variable, i.e. buffered back to the caller) and then sets the OUT parameter to some test value, which is the one I'm expecting to receive back in PHP thanks to the PDOStatement->bindParam() method.

However, when executing that code I get the following exception:

SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 3 for routine test_table.baz is not a variable or NEW pseudo-variable in BEFORE trigger

I've looked and looked all over the web and the closest thing I've found describing the problem is this very bug report, which states that it was fixed back in MySQL 5.5.3 by allowing the C API to return multiple results sets.

However, I'm still experiencing the problem on MySQL 5.5.30; added to that, I don't understand what the ability to return multiple result sets has to do with this issue. I understand that without that ability the caller would not be able to receive the result of, say, a select statement, *and* at the same time retrieve the values of the INOUT or OUT parameters in the procedure, fine. But this problem here is about the failure to call the stored procedure in the first place because MySQL is not seeing the (IN)OUT bound parameter as legal variables. Therefore I'm inclined to think that this issue is of a different nature from what was reported previously in this bug report as being fixed in MySQL 5.5.3.

Do let me know if I can help in any way in debugging the problem. Thanks in advance!

Juan,

the problem is that PHP connectors do not support
OUT/INOUT parameters properly.

If you need to work with OUT-parameters from PHP scripts,
you can use user-variables. Or you can use libmysql API.

Thanks for responding, Alexander!

So you're saying this is actually a PHP/PDO/mysqlnd bug? Just yesterday I entered bug report #69206 about the same issue since this one was closed, and in it I noted several other bug reports on the problem that are also closed, but that talk about it from other programming environments, not just PHP.

Would you be so kind to take a look? http://bugs.mysql.com/bug.php?id=69206 My comments there are very similar to what I wrote here, with the exception of the references to the other bug numbers.

Thanks in advance!

Replied in the report for Bug#69206.

Thank you!