MySQL Bugs: #9722: Connector does not recognize parameters separated by a linefeed

Bug #9722	Connector does not recognize parameters separated by a linefeed
Submitted:	7 Apr 2005 15:06	Modified:	8 Jun 2005 21:53
Reporter:	Nathan Lewis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	1.0.4	OS:	Windows (Windows XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
If a stored procedure is created such that the parameters are separated by a linefeed, they are not parsed correctly.

How to repeat:
create procedure TestProc (foo int,
 bar int)
BEGIN
SELECT 1;
END$$

if you try to call this procedure with the connector, it only parses out foo, not bar.

Suggested fix:
Add \n to the delimiters when parsing out a stored procedure's parameters:

change:

string[] parts = Utility.ContextSplit( paramDef.ToLower(), " \t", "");

to:

string[] parts = Utility.ContextSplit( paramDef.ToLower(), " \t\n", "");

in MySqlClient\StoredProcedure.cs Line 90

There should probably be a default case that throws an error if the direction can't be parsed in this same file, instead of blindly skipping that parameter.

I would like to add "\r" to the list as well - I managed to create a stored procedure that had \r\n between the parameters and it, again, messed up the parsing code.

I, too, have experienced this bug.  I suggest that it is more than just parameters separated by white space.  I have a stored procedure with no parameters.  The exact sequence of characters stored in param_list in mysql.proc is:
1. space
2. carriage-return
3. line-feed.

The call to Utility.ContextSplit on line 86 of storedprocedure.cs:
SELECT param_list FROM  mysql.proc WHERE db=_latin1 'Recipe' AND name=_latin1 'mj_spListAuthors
returns an array of one element containing exactly the three character sequence from param_list.  This later causes an exception to be thrown, during attempted parsing of this "parameter".

I suggest a fix of adding the following line to line 36 of common\utility.cs:
src = src.Trim();
This removes leading and trailing whitespaces from the string.  Nathan's suggested fix works for the case given (parameters separated by linefeeds and carriage returns).  However, it does not work for the case where the parameter list is empty, but simply contains whitespace.  My suggested fix covers both cases.

I am using 5.0.4 of MySql, and 1.0.4 of Connector/NET.

Oops.  Line 86 of storedprocedure.cs reads:
string[] paramDefs = Utility.ContextSplit( param_list, ",", "()" );

This line begins parsing the string returned from the select statement that I had erroneously pasted there.

Apologies for posting so many comments.  Upon further reflection, I think that both Nathan's and my suggested fixes should be applied.  This is because Nathan's can be expected to handle whitespace within a string, such as "OUT\tvarname\tINT".  Simply passing this string to trim would not alter the internal whitespace.

Could you please to write here part of your code how you call stored procedure from .NET.

I don't think there's anything special about the code.  Any time one calls a stored procedure, Connector/NET queries MySQL to obtain the arguments.  The white space causes the parsing to fail.  So it's the white space in the stored procedures that is special.  With that in mind, here's the code formatted exactly as used.

Code to call stored procedure (case with arguments)  Please excuse all the "^M"s It's a DOS formatted file:
                void ISourceWrite.AddAuthor(string FirstName, string Initial, string LastName, out int
 AuthorID)^M
                {^M
                        MySqlCommand cmd;^M
                        MySqlParameter prm;^M
                        MySqlConnection con = null;^M
^M
                        try^M
                        {^M
                                AuthorID = 0;^M
^M
                                // Validate arguments^M
                                if (FirstName.Length > 50)^M
                                        throw new ArgumentOutOfRangeException("FirstName", "First Name
 is limited to 50 letters.");^M
                                if (Initial.Length > 2)^M
                                        throw new ArgumentOutOfRangeException("Initial", "Initial shou
ld be one letter with a period.");^M
                                if (LastName.Length > 50)^M
                                        throw new ArgumentOutOfRangeException("LastName", "Last Name i
s limited to 50 letters.");^M
^M
                                con = new MySqlConnection(Util.GetConnectionString());^M
^M
                                cmd = new MySqlCommand();^M
                                cmd.CommandText = "mj_spAddAuthor";^M
                                cmd.CommandType = CommandType.StoredProcedure;^M
                                cmd.Connection = con;^M
^M
                                prm = cmd.Parameters.Add("FirstName", MySqlDbType.VarChar, 50);^M
                                prm.Value = FirstName;^M
                                prm = cmd.Parameters.Add("Initial", MySqlDbType.VarChar, 2);^M
                                if (Initial == "")^M
                                        prm.Value = DBNull.Value;^M
                                else^M
                                        prm.Value = Initial;^M
^M
                                prm = cmd.Parameters.Add("LastName", MySqlDbType.VarChar, 50);^M
                                prm.Value = LastName;^M
^M
                                prm = cmd.Parameters.Add("ID", MySqlDbType.Int32);^M
                                prm.Direction = ParameterDirection.Output;^M
^M
                                con.Open();^M
                                cmd.ExecuteNonQuery();^M
                                con.Close();^M
^M
                                AuthorID = (int) prm.Value;^M
                        }^M
                        catch(Exception exc)^M
                        {^M
                                Util.LogError(exc);^M
                                if (con != null && con.State == ConnectionState.Open)^M
                                        con.Close();^M
                                throw;^M
                        }^M
                }^M
Code of the stored procedure (Here the ^M emphasizes the white space contributing to the problem):
CREATE PROCEDURE mj_spAddAuthor( ^M
FirstName VARCHAR(50),^M
Initial CHAR(2),^M
LastName VARCHAR(50),^M
OUT ID INT^M
)^M
BEGIN^M
^M
INSERT Author^M
(FirstName, Initial, LastName)^M
VALUES (FirstName, Initial, LastName);^M
^M
SET ID = LAST_INSERT_ID();^M
END$$^M

Code to call Stored Procedure (no args case):
                void ISourceRead.ListAuthors(out DataSet list)^M
                {^M
                        MySqlCommand cmd;^M
                        MySqlDataAdapter da;^M
^M
                        try^M
                        {^M
                                list = null;^M
^M
                                cmd = new MySqlCommand();^M
                                cmd.CommandText = "mj_spListAuthors";^M
                                cmd.CommandType = CommandType.StoredProcedure;^M
                                cmd.Connection = new MySqlConnection(Util.GetConnectionString());^M
^M
                                da = new MySqlDataAdapter();^M
                                da.SelectCommand = cmd;^M
^M
                                list = new DataSet();^M
                                da.Fill(list);^M
                        }^M
                        catch(Exception exc)^M
                        {^M
                                Util.LogError(exc);^M
                                throw;^M
                        }^M
                }^M

Stored Procedure (no args case):
CREATE PROCEDURE mj_spListAuthors( ^M
)^M
BEGIN^M
^M
SELECT id, Lastname, FirstName, Initial^M
FROM Author^M
ORDER BY LastName, FirstName;^M
END$$^M

NOTES:
1. the formatting is important to reproducing the bug.
2. The "^M" represents a carriage return, and should be replaced by a carriage return character.  All lines end with the "\r\n" sequence of a DOS formatted text file.

The bug was repeated.
Tested on Win 2000 Sp4, Microsoft Visual C# .NET , Connector .NET 1.0.4

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fixed in 1.0.5

Documented in C/NET changelog.