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: | |
Category: | Connector / NET | Severity: | S2 (Serious) |
Version: | OS: | Any | |
Assigned to: | CPU Architecture: | Any |
[10 Jun 2007 23:25]
Claudio Biagioli
[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.