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

[1 Mar 2006 2:41] Carlo Razzeto
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.
[7 Apr 2006 20:56] Tonci Grgin
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
[19 May 2006 18:44] Bugs System
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
[1 Jun 2006 18:39] Reggie Burnett
Fixed in 1.0.8
[4 Jun 2006 4:51] MC Brown
Documented in the changelog.