MySQL Bugs: #5819: ADO.NET Data provider does not support ADO parameter naming

Bug #5819	ADO.NET Data provider does not support ADO parameter naming
Submitted:	30 Sep 2004 3:46	Modified:	13 Oct 2004 18:00
Reporter:	Craig Shield	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.0	OS:	Windows (Windows 2003)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
There was a second issue discussed in Bug Report #5407.  The data provider does not support naming of parameters with the "@" name, but requires a "?" for the parameter name.

How to repeat:
The following does not work:

IDbCommand myCommand = myConnection.CreateCommand();		
myCommand.CommandText = "SELECT SiteName FROM Sites WHERE Sites.SiteId = @SiteId";
IDbDataParameter siteId = myCommand.CreateParameter();
siteId.ParameterName = "@SiteId";
siteId.Value = 1;
myCommand.Parameters.Add(siteId);

The following does work:
IDbCommand myCommand = myConnection.CreateCommand();		
myCommand.CommandText = "SELECT SiteName FROM Sites WHERE Sites.SiteId = ?SiteId";
IDbDataParameter siteId = myCommand.CreateParameter();
siteId.ParameterName = "@SiteId";
siteId.Value = 1;
myCommand.Parameters.Add(siteId);

The first example does not produce an error - it just returns no records.  The second works without a problem, the issue is that it is not compatible with ADO.NET generally.

Suggested fix:
Is there an issue with the tokenisation process that is not properly finding the "@Variablename" token, but is finding "?VariableName" token.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

As far as I'm aware, there is no standard as to the parameter marker used.  OdbcNet and OleDbClient use ?.  Db2 uses ?.  SqlClient uses @

We don't use @ since MySQl user variables start with @.  That is also the reason why example #1 does not complain about a missing parameter.

SELECT SiteName FROM Sites WHERE Sites.SiteId = @SiteId is a perfectly valid MySQL statement that is referencing a user variable named @SiteId.  In this case, @SiteId is null so returns no records.

The documentation reference referred to does not have any documentation about the ADO.NET data provider for MySql!