none
ODBC Data Provider: Coercion? RRS feed

  • Question

  • Hi all,

     

    I'm using the ODBC Data Provider to access an Ingres DB, which extensively uses a proprietary date datatype called 'ingresdate'. 'ingresdate' requires the format 'dd.mm.yyyy hh:mm: ss', while the OdbcDataAdapter tries to insert/update with the TIMESTAMP format 'yyyy-mm-dd hh:mm: ss' from a DataSet.

     

    Now, you can pass a value in TIMESTAMP format to the Ingres DB using coercion with a statement like 'insert into table1 values (TIMESTAMP '2008-12-30 09:01:02')', which is equivalent to 'insert into table1 values ('30.12.2008 09:01:02')'.

     

    Can I force the .net ODBC Data Provider to apply coercion to every DateTime value it writes to the DB? I.e. to execute something like 'insert into table1 values (TIMESTAMP '2008-12-30 09:01:02')' instead of something like 'insert into table1 values ('2008-12-30 09:01:02')'?

     

    Here's sample code from a post in the Ingres community that shows the problem:

     

    Code Block

    static void TestODBCDataAdapterUpdateInt(String connstring)

    {

    OdbcConnection conn = new OdbcConnection(connstring);

    OdbcCommand command;

    OdbcDataAdapter dataAdapter;

     

    int oldvalue = 0;

    int newvalue = 0;

     

    conn.Open();

     

    try

    {

    command = conn.CreateCommand();

    command.CommandText = "drop table1";

    command.ExecuteNonQuery();

    command.Dispose();

    }

    catch (OdbcException ex)

    {

    Console.WriteLine(ex.Message);

    Console.WriteLine("\n");

    }

     

    command = conn.CreateCommand();

    command.CommandText = "create table table1(col1 integer not null primary key, col2 integer, col3 ingresdate with null)";

    command.ExecuteNonQuery();

    command.Dispose();

     

    command = conn.CreateCommand();

    command.CommandText = "insert into table1(col1, col2, col3) values (1,99,'08.10.2007 12:58:00')";

    command.ExecuteNonQuery();

    command.Dispose();

     

    command = conn.CreateCommand();

    command.Parameters.Add(new OdbcParameter("myParameter", (Int32)1));

    command.CommandText = "SELECT * FROM table1 WHERE col1 = ?";

     

    dataAdapter = new OdbcDataAdapter(command);

     

    OdbcCommandBuilder builder = new OdbcCommandBuilder(dataAdapter);

     

    DataSet ds = new DataSet("MyDataSet");

     

    dataAdapter.Fill(ds, "table1"); // Works fine, filled content ok

     

    // Modify content

    DataRow rowOrig = ds.Tables["table1"].Rows[0];

    oldvalue = (int)rowOrig["col2"];

    rowOrig["col2"] = oldvalue + 1; // Datatype of col2: int

     

    // Perform the update

    dataAdapter.Update(ds, "table1"); // -> Exception, can force coercion?

     

    command = conn.CreateCommand();

    command.CommandText = "SELECT col2 FROM table1 WHERE col1 = 1";

    newvalue = (int)command.ExecuteScalar();

     

    Console.WriteLine("oldvalue = " + oldvalue.ToString());

    Console.WriteLine("newvalue = " + newvalue.ToString());

    conn.Close();

    }

     

     

     

    Regards, Cornel

    Monday, November 12, 2007 10:54 AM