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;






    command = conn.CreateCommand();

    command.CommandText = "drop table1";




    catch (OdbcException ex)






    command = conn.CreateCommand();

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




    command = conn.CreateCommand();

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




    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());






    Regards, Cornel

    Monday, November 12, 2007 10:54 AM