Problem updating DATE column using Oracle 10g R2

From: <ebarrett_at_metastorm.com>
Date: 28 Apr 2006 08:23:55 -0700
Message-ID: <1146237835.835023.124000@i39g2000cwa.googlegroups.com>

I have encountered problems with attempting to do a SQL insert/update to an Oracle DATE column from ADO using a parameter. I wonder if someone could shed some light on this.

Using Oracle 10g Release 2 (reports as 10.2.0.1.0). Using Oracle OLEDB provider, version 10.2.0.1.

The following JScript script illustrates the issue:

function runtest()
{

        var conn = new ActiveXObject( "ADODB.Connection" );

// Need to amend connection string
        var connString = "Provider=OraOLEDB.Oracle.1;Data Source=ORSERVER;User Id=Ed;Password=password;OLE DB Services = -1;FetchSize=100;CacheType=Memory;PLSQLRSet=1";

        var cmd = new ActiveXObject( "ADODB.Command" );

// Sets parameter to datetime of 1st Jan 1753, 00:01
        var param = cmd.CreateParameter("", 135, 1, 19, -53688.000694444447 );

        cmd.Parameters.Append( param );

// Amend this SQL accordingly, but keep the parameter (?)
        cmd.CommandText = "Update MyTable set MyDateTime=? where MyKey = 'XYZ'";         conn.Open( connString );

        cmd.ActiveConnection = conn;
        cmd.Execute;

        conn.Close;
        conn = null;

}

runtest();

All appears to complete successfully. But if I attempt to look at the result in SQL Plus, the following is recorded.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select MyDateTime from MyTable;

MYDATETIME

---

01.01.1753 86:27:16

A time of 86:27 - seems improbable!

The JScript is based on C++ code which has worked perfectly well with previous versions of Oracle and SQL Server. You will find that this script works perfectly well against SQL Server 2000.

I'd be interested to know whether this is a known problem, and how this can be addressed. Thanks for any help.

Ed Barrett Received on Fri Apr 28 2006 - 10:23:55 CDT