Bug #21836 DataTable value retrieval bug

• Submitted: 25 Aug 2006 13:48
• Modified: 25 Aug 2006 18:52
• Reporter: Peter Brawley (Basic Quality Contributor)
• Status: Not a Bug
• Category: Connector / NET
• Severity: S1 (Critical)
• Version: 5.0.0.0
• OS: Windows (W2K)
• CPU Architecture: Any

[25 Aug 2006 13:48] Peter Brawley

Description:
This Visual Studio 2005 code works fine against SQL Server 2005:

    private int[] dgvAutoColNos( DataGridView dgv, string sDb, string sTbl ) {
      string sQry = "SELECT IF( extra = 'auto_increment', 1, 0 ) " +
                    "FROM information_schema.columns " +
                    "WHERE table_schema = '" + sDb + "' AND table_name = '" + sTbl + "' " +
                    "ORDER BY ordinal_position";
      DataTable dt = new DataTable();
      MySqlDataAdapter da = new MySqlDataAdapter( sQry, oConn );
      MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
      int[] ret = {};
      try {
        da.Fill(dt);
        ret = new int[dt.Rows.Count];
        for (int i = 0; i < dt.Rows.Count; i++) {
          object o = dt.Rows[i][0].GetType();
          ret[i] = (int)dt.Rows[i][0];
        }
      }
      catch (Exception e) {
        ErrMsg(e);
      }
      finally {
     }
      return ret;
    }

but against MySQL 5.1.11 with Connector/NET 5.0.0.0, the first invocation of

  ret[i] = (int)dt.Rows[i][0];

fails with "Specified cast is not valid", even though the array cell is populated and dt.Rows[i][0].GetType() returns Int64. Referencing the result as (int)dt.Rows[i].ItemArray[0] yields the same error, as does casting to Int32 and Int64.

How to repeat:
As above

Suggested fix:
None known

[25 Aug 2006 18:52] Reggie Burnett

Peter

This is not a bug.  You are seeing the goofy cast semantics of .NET combined with the fact that IF(..) returns long in MySQL.  Consider the following code:

long x = 3;
object y = x;
int z = (int)y;  <!--- gives invalid cast exception
int z2 = (int)(long)y;  <!-- this works

I used the following test case and it passes with current trunk code.

execSQL("DROP TABLE IF EXISTS test");
execSQL("CREATE TABLE test (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY(id))");

string sQry = "SELECT IF( extra = 'auto_increment', 1, 0 ) " +
              "FROM information_schema.columns " +
              "WHERE table_schema = 'test' AND table_name='test' " +
              "ORDER BY ordinal_position";
DataTable dt = new DataTable();
try
{
   MySqlDataAdapter da = new MySqlDataAdapter(sQry, conn);
   da.Fill(dt);
   Assert.AreEqual(2, dt.Rows.Count);
   int row1 = (int)(long)dt.Rows[0][0];
   int row2 = (int)(long)dt.Rows[1][0];
}
/* catch omitted for brevity */