MySQL Bugs: #27679: MySqlCommandBuilder.DeriveParameters ignores UNSIGNED flag

Bug #27679	MySqlCommandBuilder.DeriveParameters ignores UNSIGNED flag
Submitted:	5 Apr 2007 22:57	Modified:	3 May 2007 11:23
Reporter:	Matthew Endsley	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.6	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	DeriveParameters, MySqlCommandBuilder, UNSIGNED

Description:
When using the MySqlCommandBuilder to derive the parameters for a stored procedure, the UNSIGNED flag is ignored.

How to repeat:

SQL:
  Create a stored procedure with an unsigned parameter

mysql> SHOW CREATE PROCEDURE Test\G
*************************** 1. row ***************************
Procedure: Test
sql_mode:
Create Procedure: CREATE DEFINER=`...` PROCEDURE `Test`(testid tinyint unsigned)
BEGIN
select 'hi';
   END
1 row in set (0.00 sec)

C#:

MySqlCommand command = new MySqlCommand( "Test", connection );
MySqlCommandBuilder.DeriveParameters( command );

Debug.Assert( command.Parameters["?testid"].MySqlDbType == MySqlDbType.UByte );
Debug.Assert( command.Parameters["?testid"].DbType == DbType.Byte );

In the above example the MySqlDbType will be set to MySqlDbType.Byte and the DbType will be DbType.SByte.

Hi Matthew and thanks for excellent report. Your private remarks are true in our latest sources.

Verified as described with
 - MySQL 5.0.38BK on WinXP Pro SP2 localhost
 - c/NET 5 rev 675 on NET FW 2

Test case:
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=;PORT=3306;Allow Zero Datetime=True";//;
conn.Open();
MySqlCommand cmdCreateTable = new MySqlCommand("drop procedure if exists spTestBug27679", conn);
cmdCreateTable.CommandTimeout = 0;
cmdCreateTable.ExecuteNonQuery();
cmdCreateTable.CommandText = ("CREATE PROCEDURE spTestBug27679 (IN id TINYINT UNSIGNED, OUT valo TINYINT UNSIGNED) " +
          "BEGIN SET valo = (id-1); END");
cmdCreateTable.ExecuteNonQuery();
MySqlCommand cmd = new MySqlCommand("spTestBug27679", conn);
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?id", (155));
cmd.Parameters[0].DbType = DbType.Byte;
cmd.Parameters[0].Direction = ParameterDirection.Input;
cmd.Parameters.Add("?valo", MySqlDbType.UByte);
cmd.Parameters[1].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
Console.WriteLine("Test 1):");
Console.WriteLine("Param Id (TINYINT UNSIGNED) MySqlDbType: " + cmd.Parameters["?id"].MySqlDbType.ToString());
Console.WriteLine("Param valo (TINYINT UNSIGNED) MySqlDbType: " + cmd.Parameters["?valo"].MySqlDbType.ToString());
Debug.Assert(cmd.Parameters["?id"].MySqlDbType == MySqlDbType.UByte);
Debug.Assert(cmd.Parameters["?id"].DbType == DbType.Byte);
Console.WriteLine("Test 1) done.");
//--
MySqlCommand cmd1 = new MySqlCommand("spTestBug27679", conn);
cmd1.CommandTimeout = 0;
cmd1.CommandType = CommandType.StoredProcedure;
MySqlCommandBuilder.DeriveParameters(cmd1);
Console.WriteLine("Test 2):");
Debug.Assert(cmd1.Parameters["?id"].MySqlDbType == MySqlDbType.UByte, "Assertation failed!", "(IN) Param Id MySQLDBType is " + cmd1.Parameters["?id"].MySqlDbType.ToString() + " instead of UByte");
Debug.Assert(cmd1.Parameters["?id"].DbType == DbType.Byte, "Assertation failed!", "(IN) Param Id DBType is " + cmd1.Parameters["?id"].DbType.ToString() + " instead of Byte");
Debug.Assert(cmd1.Parameters["?valo"].MySqlDbType == MySqlDbType.UByte, "Assertation failed!", "(OUT) Param valo MySQLDBType is " + cmd1.Parameters["?valo"].MySqlDbType.ToString() + " instead of UByte");
Debug.Assert(cmd1.Parameters["?valo"].DbType == DbType.Byte, "Assertation failed!", "(OUT) Param valo DBType is " + cmd1.Parameters["?valo"].DbType.ToString() + " instead of UByte");
Console.WriteLine("Test 2) done.");

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/25852

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/25853

Fixed in 5.0.7 and 5.1.1

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/25854

A note has been added to the 5.0.7 and 5.1.1 changelogs.