Problem updating DATE column using Oracle 10g R2
- From: ebarrett@xxxxxxxxxxxxx
- Date: 28 Apr 2006 08:23:55 -0700
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
.
- Prev by Date: Re: 8.1.7.4 standby database - missing some log files???
- Next by Date: Re: what sort of dba can you get for 100K nowdays
- Previous by thread: how to shutdown immediate in Pro*C
- Next by thread: error in the output
- Index(es):
Relevant Pages
|
