Bug #29010 Error with comments in Stored Procedure parameters list
Submitted: 10 Jun 2007 23:25 Modified: 15 Jun 2007 20:41
Reporter: Claudio Biagioli Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[10 Jun 2007 23:25] Claudio Biagioli
Description:
In a DB ported from MSSQLServer, while converting the SP I commented the default values since mySQL doesn't support them:

Comments in SP parameter list appear to be considered like good text.
When the SP is called we have error:

"Parameter '?--' not found in the collection."

How to repeat:
example:

CREATE DEFINER=`root`@`localhost` PROCEDURE `dspGetText`(
	_idLanguage	int, -- = 1,
	_Page varchar(50) -- = NULL

)
BEGIN
...
END
[15 Jun 2007 8:01] Tonci Grgin
Hi Claudio and thanks for your report.

This is not a bug. If you check general query log, you'll see that command is sent like "CREATE PROCEDURE `SP_BUG29010`(IN P_ID INT, -- com. param1IN P_NAME VARCHAR(80) -- com. param2) BEGIN INSERT INTO bug29010 (`ID`, NAME) VALUES (P_ID, P_NAME); END" which is not suitable for parser and throws error regardless of c/NET; cmd.CommandText is of type String and this is to be expected. All you have to do is add \n at the end of lines (like in C++) and things work as expected:
    MySqlConnection conn = new MySqlConnection();
    conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=;PORT=3306;Allow Zero Datetime=True;logging=True";
    conn.Open();
    MySqlCommand cmdCreateTable = new MySqlCommand("DROP PROCEDURE IF EXISTS `test`.`SP_BUG29010`", conn);
    cmdCreateTable.CommandType = CommandType.Text;
    cmdCreateTable.CommandTimeout = 0;
    cmdCreateTable.ExecuteNonQuery();
    cmdCreateTable.CommandText = "DROP TABLE IF EXISTS `bug29010`";
    cmdCreateTable.ExecuteNonQuery();
    cmdCreateTable.CommandText = "CREATE TABLE `bug29010` (" +
        " `ID` int(10) unsigned NOT NULL," +
        " `NAME` varchar(80) default NULL," +
        " PRIMARY KEY  (`ID`)" +
        " ) ENGINE=MyISAM";
    cmdCreateTable.ExecuteNonQuery();
    cmdCreateTable.CommandText = ("CREATE PROCEDURE `SP_BUG29010`(\n" +
        "IN P_ID INT, -- com. param1 \n" +
        "IN P_NAME VARCHAR(80) -- com. param2 \n" +
        ") \n" +
        "BEGIN INSERT INTO bug29010 (`ID`, NAME) VALUES (P_ID, P_NAME); END\n");
    try
      {
        cmdCreateTable.ExecuteNonQuery();
      }
    finally
      {
        cmdCreateTable.CommandType = CommandType.Text;
        cmdCreateTable.CommandText = "DROP TABLE IF EXISTS `bug29010`";
        cmdCreateTable.ExecuteNonQuery();
        cmdCreateTable.CommandText = "DROP PROCEDURE IF EXISTS `test`.`SP_BUG29010`";
        cmdCreateTable.ExecuteNonQuery();
        cmdCreateTable.Dispose();
        conn.Close();
      }

	10 Connect     root@localhost on test
	10 Query       SHOW VARIABLES
	10 Query       SHOW COLLATION
	10 Query       SET NAMES utf8;SET character_set_results=NULL
	10 Init DB     test
	10 Query       DROP PROCEDURE IF EXISTS `test`.`SP_BUG29010`
	10 Query       DROP TABLE IF EXISTS `bug29010`
	10 Query       CREATE TABLE `bug29010` ( `ID` int(10) unsigned NOT NULL, `NAME` varchar(80) default NULL, PRIMARY KEY  (`ID`) ) ENGINE=MyISAM
	10 Query       CREATE PROCEDURE `SP_BUG29010`(
IN P_ID INT, -- com. param1 
IN P_NAME VARCHAR(80) -- com. param2 
) 
BEGIN INSERT INTO bug29010 (`ID`, NAME) VALUES (P_ID, P_NAME); END
	10 Query       DROP TABLE IF EXISTS `bug29010`
	10 Query       DROP PROCEDURE IF EXISTS `test`.`SP_BUG29010`

Now, this can, at most, be server.parser / c/NET feature request but then again, you can make it MS feature request too (add NL at the end of comment in dump)...
[15 Jun 2007 20:16] Tonci Grgin
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Explanation: Claudio, it is decided that fix for comments in SP's shall be added to 5.1 branch as it is too large a change for c/net 5.0 and earlier.
[15 Jun 2007 20:23] Claudio Biagioli
Sorry, due to the hurry I submitted a report not clear enough.
The problem was NOT IN CREATE PROCEDURE: the procedure (with the line feed) had already been created using mySQL Administrator.

The error arises when I CALL the procedure through C/.Net (from MySQL Query Browser it works perfectly). 
Since I urgently needed a solution I patched myself. Look at the bottom.

How to reproduce:
1. You must have any stored procedure with comments in parameter list 
i.e.
-------------------------------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`(
 _foo1 int, -- I'm a foo
 _foo2 int  -- one more
)
BEGIN
select (_foo1 + _foo2);
END
------------------------------------------------------------------

2. Call the procedure Using C/.net 
i.e. (VB Code)
-----------------------------------------------------------
Dim conn As New MySqlConnection("server=localhost;user id=root; password=pw; database=domina_dbo; pooling=false")
	
Dim com As New MySqlCommand("foo", conn)
com.CommandType = CommandType.StoredProcedure
Dim P1 As New MySqlParameter("_foo1", 1)
P1.Direction = ParameterDirection.Input
Dim P2 As New MySqlParameter("_foo2", 2)
P2.Direction = ParameterDirection.Input
com.Parameters.Add(P1)
com.Parameters.Add(P2)
conn.Open()
Try
  MsgBox(com.ExecuteScalar())
Finally
  conn.Close()
End Try
------------------------------------------------------------------------

Running it, you have ERROR: Parameter '?--' not found in the collection.
Same problem with /* */ comments.
It comes from the fact that the parameters string is simply splitted through commas and used as is, we need to remove comments.

Since I urged a solution, I had to patch it myself. 
Check the solution and fill free to use it for the official code if you want:

I created a removeComments() function and called it at row 440 of ISSchemaProvider.cs
---------------------------------
			// trim off the first part
			body = body.Substring(leftParen + 1);

			int rightParen = FindRightParen(body, quotePattern);
			Debug.Assert(rightParen != -1);
			string parms = body.Substring(0, rightParen).Trim();
			parms = removeComments(parms); //this is the added line
-----------------------------------

and this is the function that remove comments...
-------------------------------------------------------------
		private string removeComments(string s)
		{
			
			int n = s.IndexOf("--");
			int n2 = s.IndexOf("/*");
			if (n == -1 && n2 == -1) return s;

			bool aredashes, ends;
			int skip = 0;

			StringBuilder sb = new StringBuilder();

			do
			{
				
				aredashes = (n2 == -1 || (n < n2 && n!=-1)) ;
				if (aredashes){
					sb.Append(s.Substring(skip, n-skip));
					ends = (skip = s.IndexOf('\n', n)+1) != -1+1;
				}
				else {
					sb.Append(s.Substring(skip, n2-skip));
					ends = (skip = s.IndexOf("*/", n2)+2) != -1+2;
				}

				if (ends)
				{
					 n = s.IndexOf("--", skip);
					 n2 = s.IndexOf("/*", skip);
				}

			
			}
			while (ends && (n != -1 || n2 != -1));

			if (ends) 
				sb.Append(s.Substring(skip));

			return sb.ToString();
	
			
		}
------------------------------------------------------------

Thanks...
[15 Jun 2007 20:41] Tonci Grgin
Claudio, your first post was misleading but my answer still stands, fixed in 5.1 branch.

Thanks for your interest in MySQL!
[15 Apr 2008 15:28] Mathew Thomas
Has this bug been fixed in 5.2.1?  I am encountering "Parameter '@_id' not found in the collection."
[15 Apr 2008 17:46] Mathew Thomas
Never mind.  I found my bug and fixed it.  Thanks.