MySQL Bugs: #28879: Problem with dynamic queries and variables

Bug #28879	Problem with dynamic queries and variables
Submitted:	4 Jun 2007 16:19	Modified:	4 Jul 2007 18:34
Reporter:	Ivan Ramirez	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.33	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	dynamic queries, execute, prepare, variables

Description:
Hi,

I have a problem with the current stored procedure, when I declare a variable I can't use it into the dynamic query, for example:

DECLARE pExists int default -1;
SET @stringsql = CONCAT("SELECT count(*) FROM user_",pUserID," INTO pExists;");

In mysql (from console) the stored procedure works, but not when I call this from php. In this case if I use this query without prepare ('... from user_5 ...') it works fine, but inside the variable @stringsql I can't do reference to the variable pExists... php return an error like 'the value is NULL' 

I tried to use @pExists and I have the same problem.

	
somebody can help me?

How to repeat:
My sql
------
DECLARE pExists int default -1;
SET @stringsql = CONCAT("SELECT count(*) FROM user_",pUserID," INTO pExists;");

Php (Zend_db)
-------------
$db = Zend_Db::factory('PDO_MYSQL', $connect_params);
$user_info = $db->prepare('CALL user_info(10)');
$user_info->execute();

Thank you for the report.

Please be sure you read and understood correctly how you should write a program that use the CALL SQL statement to execute stored procedures that produce result sets. Please note PHP uses C API. So all rules for C programs can be applied to PHP program as well.

If you still think problem is MySQL bug, please provide output of SHOW CREATE PROCEDURE user_info and try to create simple test case which doesn't use any framework.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi. i have the same problem. Mysql report an error:
Error Code : 1327
Undeclared variable: myvariable

I am having this basic issue as well. Here's some simple code:

DROP PROCEDURE IF EXISTS `sum`|

CREATE PROCEDURE `sum`
(
IN `inCol` VARCHAR(50),
OUT `outCol` VARCHAR(10)
)
BEGIN
DECLARE temp int default 0;

set @queryText = concat("SELECT SUM(",inCol,") INTO outCol FROM test");
prepare query1 from @queryText;
execute query1;
set outCol=temp;

END |

You can mess around with this code, but no matter what, outCol is never set correctly. You can also see my forum posting here: http://forums.mysql.com/read.php?98,210627,210627#msg-210627