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

Bug #27679	MySqlCommandBuilder.DeriveParameters ignores UNSIGNED flag
Submitted:	6 Apr 2007 0:57	Modified:	3 May 2007 13:23
Reporter:	Matthew Endsley
Status:	Closed
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	5.0.6	OS:	Microsoft Windows (XP SP2)
Assigned to:		Target Version:
Tags:	UNSIGNED, DeriveParameters, MySqlCommandBuilder

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.