Bug #50448 Create stored procedure with session variables starting with a '@'
Submitted: 19 Jan 2010 16:10 Modified: 19 Jan 2010 23:26
Reporter: marcel ruiter Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:5.* OS:Windows
Assigned to: CPU Architecture:Any
Tags: MySQL, session variables, stored procedure

[19 Jan 2010 16:10] marcel ruiter
Description:
Hi there! 

I cannot create a stored procedure with session variables (starting with a @) in C#. Creating stored procedures without session variables are ok! 

I'm using the MyqlConnector .NET/C#

How to repeat:
This is my code: 

USING (MySqlCommand cmd = new MySqlCommand()) { 
cmd.Connection = new MySqlConnection("my connectionstring"); 
cmd.CommandType = CommandType.Text; 
cmd.CommandText = @" 
CREATE PROCEDURE `sp_Test`() 
BEGIN 
SELECT @MyVar := 'hi there'; 
END 
"; 

cmd.Connection.Open(); 
cmd.ExecuteScalar(); 
cmd.Connection.Close(); 

} 

Please help me !
[19 Jan 2010 20:37] Reggie Burnett
This is not a bug.  The provider, by default, will attempt to find a parameter with the given name when it sees @<some name>.  If you are using session variables, then you need to change how the connector behaves. You do this with the 'allow user variables' connection string option.  Setting this option like this:  'allow user variables=true' will cause the connector to attempt to find a parameter with that name but now throw any type of exception if it doesn't find one.
[19 Jan 2010 23:19] marcel ruiter
thanks a lot! it works!

I wasn't able to find this in de documentation part of the Connector, maybe I didn't read it good enough. Could you tell me where this is documented?

THX!
[19 Jan 2010 23:26] marcel ruiter
thanks a lot! it works!

I wasn't able to find this in de documentation part of the Connector, maybe I didn't read it good enough. Could you tell me where this is documented?

THX!