MySQL Bugs: #42776: Cannot use variables in sql

Bug #42776	Cannot use variables in sql
Submitted:	12 Feb 2009 3:02	Modified:	31 Mar 2009 16:35
Reporter:	Matt Zyzik	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	variables

Description:
When I try to use variables in sql in "CommandText", mysql complains that parameters aren't set. This is a new issue, as a much older mysql connector library didn't have this issue.

How to repeat:
Set the CommandText to this:

	select floor(rand() * count(*)) into @randrow from images;
	prepare stmt from 'select * from images limit ?, 1';
	execute stmt using @randrow;

Suggested fix:
I can only guess it has something to do with your changes to the way you deal with parameters in the 5.2 version of the connector.

Hi Matt and thanks for your report.

Can you please attach complete test case including your connect string so I can check.

test case

Attachment: case.tar.gz (application/gzip, text), 116.42 KiB.

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".

Sorry, my mistake. Analyzing.

This is not a bug.  The default behavior is that the provider will look for variables (strings starting with ? or @) and assume they are parameters that should be set.  If not, it throws an error.  If you want to run sql that contains these strings, then you need to set 'Allow User Variables=true' on your connection string.  This will cause the provider to find the variables and see if there is a parameter of that name.  If there is, it uses it.  If not, it assumes it is a user variable and accepts it.