[cx-oracle-users] cx_Oracle 4.1.2 mishandles insertion of timestamps

[Tether, S. A. <te...@sl...>]

I had to patch to cx_Oracle 4.1.2 because I was having trouble writing =
datetime.datetime values under Python 2.4.1 to an Oracle 10g table in =
which the column type was TIMESTAMP(6). The fractional seconds were =
being stored as zero no matter what the datetime object value. It turns =
out that cx_Oracle was binding the value to a DATETIME cursor variable =
instead of a TIMESTAMP variable. It has to use the latter since the =
column type is not known at the time of binding. I ran the following =
program successfully after the modification. The table has one column of =
type TIMESTAMP(6).

     from datetime import datetime as dt
     import cx_Oracle as cx

     conn =3D cx.connect(...)
     cur =3D conn.cursor()

     cur.execute("insert into tether_foo values(:1)", [dt.now()])
     var =3D cur.var(cx.TIMESTAMP)
     var.setvalue(0, dt.now())
     cur.execute("insert into tether_foo values(:1)", [var])
     conn.commit()

     cur.execute("select * from tether_foo")
     print cur.fetchall()

The output: [(datetime.datetime(2006, 5, 5, 17, 53, 3, 536025),), =
(datetime.datetime(2006, 5, 5, 17, 53, 3, 538567),)]


Here's the patch:

*** Variable.c.orig	2005-03-31 08:22:14.000000000 -0800
--- Variable.c	2006-05-05 17:08:28.000000000 -0700
***************
*** 275,280 ****
--- 275,291 ----
              );
  }
 =20
+ /* Factor out the decision as to which date-time-like type to use. For
+    Oracle 9i and later we must preserve fractions of a second in case
+    the column type turns out to be TIMESTAMP(n) with n > 0.
+ */
+ static const udt_VariableType *mostPreciseDt =3D
+ #ifdef ORACLE_9I
+         &vt_Timestamp
+ #else
+         &vt_DateTime
+ #endif
+ ;
 =20
  =
//-----------------------------------------------------------------------=
------
  // Variable_TypeByPythonType()
***************
*** 304,313 ****
          return &vt_Number;
  #ifdef NATIVE_DATETIME
      if (type =3D=3D (PyObject*) PyDateTimeAPI->DateTimeType)
!         return &vt_DateTime;
  #else
      if (type =3D=3D (PyObject*) &g_ExternalDateTimeVarType)
!         return &vt_DateTime;
  #endif
  #ifdef ORACLE_9I
      if (type =3D=3D (PyObject*) &g_TimestampVarType)
--- 315,324 ----
          return &vt_Number;
  #ifdef NATIVE_DATETIME
      if (type =3D=3D (PyObject*) PyDateTimeAPI->DateTimeType)
!         return mostPreciseDt;
  #else
      if (type =3D=3D (PyObject*) &g_ExternalDateTimeVarType)
!         return mostPreciseDt;
  #endif
  #ifdef ORACLE_9I
      if (type =3D=3D (PyObject*) &g_TimestampVarType)
***************
*** 340,350 ****
      if (PyInt_Check(value) || PyFloat_Check(value) || =
PyLong_Check(value))
          return &vt_Number;
  #ifdef NATIVE_DATETIME
!     if (PyDateTime_Check(value) || PyDate_Check(value))
          return &vt_DateTime;
  #else
      if (value->ob_type =3D=3D &g_ExternalDateTimeVarType)
!         return &vt_DateTime;
  #endif
      if (value->ob_type =3D=3D &g_CursorType)
          return &vt_Cursor;
--- 351,367 ----
      if (PyInt_Check(value) || PyFloat_Check(value) || =
PyLong_Check(value))
          return &vt_Number;
  #ifdef NATIVE_DATETIME
!     if (PyDateTime_Check(value))
!         return mostPreciseDt;
!     if (PyDate_Check(value))
          return &vt_DateTime;
  #else
      if (value->ob_type =3D=3D &g_ExternalDateTimeVarType)
!         return mostPreciseDt;
! #endif
! #ifdef ORACLE_9I
!     if (value->ob_type =3D=3D &g_TimestampVarType)
!         return &vt_Timestamp;
  #endif
      if (value->ob_type =3D=3D &g_CursorType)
          return &vt_Cursor;


-----------------------------
Stephen Tether
GLAST ISOC Software Developer
SLAC MS 43A
te...@sl...
(650) 926-4706