MySQL Bugs: #37486: MySQL Connector Net 5.2.2 Unable to Parse @

Bug #37486	MySQL Connector Net 5.2.2 Unable to Parse @
Submitted:	18 Jun 2008 17:18	Modified:	3 Jul 2008 21:05
Reporter:	Kevin Stowe	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ConnectorNet, Parsing @

Description:
We are using the MySQL Connector Net 5.2.2, We have a embedded resource file which is parsed with the CreateCommand ect.

In the embedded resource we have a Stored Procedure that looks like this:
CREATE TABLE IF NOT EXISTS Schedulers(
    Name varchar(200) NOT NULL
    );
DROP PROCEDURE IF EXISTS IfNotColExists;
 
delimiter //
CREATE PROCEDURE IfNotColExists(IN tablename VARCHAR(100), IN colname VARCHAR(100), IN SQLCode VARCHAR(255))
BEGIN
 IF (SELECT COUNT(*) AS `Hello` FROM information_schema.columns WHERE COLUMN_NAME = colname AND TABLE_NAME = tablename) = 0 
 THEN
 set @sa = SQLCode;
  PREPARE stmt FROM @sa;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
 END IF;
END //
delimiter ;

CALL IfNotColExists('task', 'SchedulerName', 'ALTER TABLE task ADD SchedulerName varchar(200)');
-- Scheduler

Now, this Create Procedure works fine when using the mysql client. but when running from the Connector Net, it causes the error message:

Parameter @sp must be defined.

I beleive its a bug in the connector net making it unable to correctly parse out the @ symbol. 

How to repeat:
Make a c# program to prepare and execute the given commands, this will replicate the error message.

\\Wrong Category Selection

I would Like to note a detail, the problem is not that it is not correctly parsing the @ symbol, more so that it is treating it as if it is a Parameter of the stored procedure.

For anyone who wants to know what was causing the problem, is the connector\net is unable to parse comments, i had a comment

-- Scheduler

so take comments out, it fixes it but it still remains a bug. I decompiled the connector\net code and found that it indeed had no parsing functionality to parse out comments. should i post a new bug report for this or will you guys handle it from here?

Kevin

This is not a bug.  With 5.2 we are using @ for parameters but that means that any SQL that uses user variables will cause a problem.  To avoid this you have to set a connection string option.  Add 'allow user variables=yes' to your connection string and it should start working.