MySQL Bugs: #17814: Stored procedure fails unless DbType set explicitly

Bug #17814	Stored procedure fails unless DbType set explicitly
Submitted:	1 Mar 2006 2:41	Modified:	4 Jun 2006 4:51
Reporter:	Carlo Razzeto	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	1.07	OS:	Windows (Windows XP Pro)
Assigned to:		CPU Architecture:	Any

Description:
When executing a stored procedure, if you have an output paramater that sets the parameter value to a System.String value, a System.FormatException exception is thrown. This exception can be avoided by manually setting the DbType property of the MySql Parameter to "AnsiString".

How to repeat:
            MySqlConnection Connection = new MySqlConnection("Host=localhost;Username=root;Password=<root password>;Pooling=false;Connection Timeout=15");
            Connection.Open();
            MySqlCommand Command = new MySqlCommand("", Connection);
            Command.CommandType  = CommandType.StoredProcedure;
            Command.CommandText  = "beer.get_brew_type";
            Command.Parameters.Add("?id", 22);

            MySqlParameter Param = new MySqlParameter();
            Param.ParameterName = "?type"; //Returns string data
            Param.Direction     = ParameterDirection.Output;
            Console.WriteLine(Param.DbType);  //Prints text "AnsiString"
            Command.Parameters.Add(Param);

Suggested fix:
I'm not sure what's going on internally, but I suggest that you should check the value of DbType when dealing with setting the value of MySqlParameters (direction = output) when executing queries and stored procedures.

Hi. Thanks for your problem report. I was able to verify it as you described. I've also changed severity to S3 according to guidelines:
Severity 3
    Represents a minor loss of service, inconvenient usage; but a workaround is available.

MySQL server 5.1.7-beta-nt-max-log
VS 2005
WINXP SP2

mysql> delimiter //
mysql> CREATE PROCEDURE bug17814 (OUT param1 CHAR(10))
    -> BEGIN
    ->   SELECT "AbCdEfGhIj" INTO param1;
    -> END;
    -> //
Query OK, 0 rows affected (0.03 sec)

A first chance exception of type 'System.FormatException' occurred in mscorlib.dll
MySql error: Input string was not in a correct format.
A first chance exception of type 'System.FormatException' occurred in MySql.Data.dll

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6653

Fixed in 1.0.8

Documented in the changelog.