Bug #61114 ODBC doesn't support Uint32 / Uint64 ? <-> OdbcType.Int / BigInt

• Submitted: 10 May 2011 11:02
• Modified: 10 May 2011 13:12
• Reporter: Aswin Coolsaet
• Status: Verified
• Category: Connector / ODBC
• Severity: S3 (Non-critical)
• Version: connector 5.1.8
• OS: Windows (7)
• Assigned to: Bogdan Degtyariov
• CPU Architecture: Any
• Tags: ODBC Unsigned Int, uint, Uint32, Uint64

[10 May 2011 11:02] Aswin Coolsaet

Description:
 
Mysql Supports UINT32 & UINT64  == Unsigned Int  & Unsigned BigInt

If you set the ODBC type to OdbcType.BigInt;
C# complains that you cannot cast a Uint32 to Int
or a Uint64 to Int64

Below the example code for Uint64 

How to repeat:
Create a table with colum BigInt Unsigned:
C#: 

OdbcParameter[] oParameters = new OdbcParameter[1];

oParameters[0] = new OdbcParameter("myUINT64",  UInt64.MaxValue);
oParameters[0].OdbcType = OdbcType.BigInt;

String SQLCommand = "UPDATE TestDB   set myUINT64 = ?";
clsDatabase.ExecuteNonQuery(SQLCommand, oParameters); // --> see below

/* clsDatabase.ExecuteNonQuery(SQLCommand, oParameters);
OdbcConnection oCon = getConnection(); // connString: @"Driver={MySQL ODBC 5.1 Driver};Server=xxx;Database=xxx;User=xx;Password=xxx;";

OdbcCommand oCommand = oCon.CreateCommand();
oCommand.CommandType = System.Data.CommandType.Text;
oCommand.CommandText = SqlCommand;
oCommand.CommandTimeout = TimeOut;
foreach (OdbcParameter oParameter in oParameters)
{
   oCommand.Parameters.Add(oParameter);
}
 oCommand.ExecuteNonQuery();

*/

[10 May 2011 13:11] Aswin Coolsaet

According to: Configuring Parameters and Parameter Data Types (ADO.NET) 
http://msdn.microsoft.com/en-us/library/yy6y35y8(v=VS.100).aspx

Uint32 -> BigInt 
Uint64 ->  Numeric

I was focussing on:  OdbcType Enumeration 
http://msdn.microsoft.com/en-us/library/system.data.odbc.odbctype.aspx 

OdbcType.Int : Exact numeric value with precision 10 and scale 0 (signed: –2[31] <= n <= 2[31] – 1, unsigned:0 <= n <= 2[32] – 1) (SQL_INTEGER).

[10 May 2011 13:12] Bogdan Degtyariov

Hi Aswin,

Thanks for reporting the bug in MySQL Connector/ODBC.
I tried your test case and it failed with the following exception:

Failed to convert parameter value from a UInt64 to a Int64.   at System.Data.Odbc.OdbcParameter.ProcessAndGetParameterValue()
   at System.Data.Odbc.OdbcParameter.PrepareForBind(OdbcCommand command, Int16 ordinal, Int32& parameterBufferSize)
   at System.Data.Odbc.OdbcParameterCollection.CalcParameterBufferSize(OdbcCommand command)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()

Setting the bug status to Verified.

[20 Dec 2022 5:02] Bogdan Degtyariov

Posted by developer:
 
Re-verified against 8.0.32. It still fails when the parameter value is UInt64.MaxValue.
Otherwise it works.

////////////////////////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Odbc;

namespace bug26474476
{
  class Program
  {
    static void Main(string[] args)
    {
      OdbcConnection con = new OdbcConnection("DSN=test");
      con.Open();
      OdbcCommand cmd = new OdbcCommand("DROP TABLE IF EXISTS tab26474476", con);
      cmd.ExecuteNonQuery();
      cmd.CommandText = "CREATE TABLE tab26474476 (my_uint_64 BIGINT UNSIGNED)";
      cmd.ExecuteNonQuery();
      cmd.CommandText = "INSERT INTO tab26474476 (my_uint_64) VALUES (1)";
      cmd.ExecuteNonQuery();

      OdbcParameter odbc_param = new OdbcParameter("my_uint_64", UInt64.MaxValue);

      cmd.CommandText = "UPDATE tab26474476 set my_uint_64 = ?";
      cmd.Parameters.Add(odbc_param);
      cmd.ExecuteNonQuery();
      Console.WriteLine("DONE!");
    }
  }
}