Thread: [cx-oracle-users] cx_Oracle 4.1.2 mishandles insertion of timestamps
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Tether, S. A. <te...@sl...> - 2006-05-06 01:01:05
|
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
|
|
From: Tether, S. A. <te...@sl...> - 2006-05-08 16:40:32
|
Let me try sending this again with US-ASCII encoding.
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
|
|
From: Anthony T. <ant...@gm...> - 2006-05-08 18:53:21
|
Hmm, I think there's a bit of a misunderstanding here. Currently you
can bind timestamp columns just fine -- but you must call
cursor.setinputsizes() or cursor.var() first in order to tell
cx_Oracle that is what you intend to do.
What you are suggesting is that the default binding type should be
timestamp since that is more precise, right? Unfortunately, doing so
makes it impossible to specify that you really do want DATETIME --
which is necessary when binding an array of date/time values, for
example.
I tried reducing the scope of the patch to only include place where
you are explicitly binding in a datetime instance
(Variable_TypeByValue) but that has the undesirable effect of changing
the default behavior of existing code -- in other words, either
everyone using dates today has to test their code and add the
appropriate setinputsizes() calls to force the old date time type
__OR__ the current behavior must be retained and people wanting to use
timestamps must use setinputsizes(). My vote is for the latter option
and my guess is nearly everyone else will, too. :-) Comments?
On 5/5/06, Tether, Steve A. <te...@sl...> wrote:
> I had to patch to cx_Oracle 4.1.2 because I was having trouble writing da=
tetime.datetime values under Python 2.4.1 to an Oracle 10g table in which t=
he column type was TIMESTAMP(6). The fractional seconds were being stored a=
s zero no matter what the datetime object value. It turns out that cx_Oracl=
e was binding the value to a DATETIME cursor variable instead of a TIMESTAM=
P 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 mod=
ification. 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),), (dateti=
me.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 ----
> );
> }
>
> + /* 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
> + ;
>
> //---------------------------------------------------------------------=
--------
> // 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(valu=
e))
> 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(valu=
e))
> 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
>
>
> -------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job ea=
sier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronim=
o
> http://sel.as-us.falkag.net/sel?cmdlnk&kid=120709&bid&3057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
|
From: Taylor B. <tb...@co...> - 2006-05-09 01:23:41
|
Option 1: -1
Option 2: +1
Cheers,
Taylor
----- Original Message -----
From: "Anthony Tuininga" <ant...@gm...>
To: <cx-...@li...>
Sent: Monday, May 08, 2006 2:53 PM
Subject: [code] Re: [cx-oracle-users] cx_Oracle 4.1.2 mishandles insertion
of timestamps
Hmm, I think there's a bit of a misunderstanding here. Currently you
can bind timestamp columns just fine -- but you must call
cursor.setinputsizes() or cursor.var() first in order to tell
cx_Oracle that is what you intend to do.
What you are suggesting is that the default binding type should be
timestamp since that is more precise, right? Unfortunately, doing so
makes it impossible to specify that you really do want DATETIME --
which is necessary when binding an array of date/time values, for
example.
I tried reducing the scope of the patch to only include place where
you are explicitly binding in a datetime instance
(Variable_TypeByValue) but that has the undesirable effect of changing
the default behavior of existing code -- in other words, either
everyone using dates today has to test their code and add the
appropriate setinputsizes() calls to force the old date time type
__OR__ the current behavior must be retained and people wanting to use
timestamps must use setinputsizes(). My vote is for the latter option
and my guess is nearly everyone else will, too. :-) Comments?
On 5/5/06, Tether, Steve A. <te...@sl...> wrote:
> 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 = cx.connect(...)
> cur = conn.cursor()
>
> cur.execute("insert into tether_foo values(:1)", [dt.now()])
> var = 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 ----
> );
> }
>
> + /* 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 =
> + #ifdef ORACLE_9I
> + &vt_Timestamp
> + #else
> + &vt_DateTime
> + #endif
> + ;
>
>
> //-----------------------------------------------------------------------------
> // Variable_TypeByPythonType()
> ***************
> *** 304,313 ****
> return &vt_Number;
> #ifdef NATIVE_DATETIME
> if (type == (PyObject*) PyDateTimeAPI->DateTimeType)
> ! return &vt_DateTime;
> #else
> if (type == (PyObject*) &g_ExternalDateTimeVarType)
> ! return &vt_DateTime;
> #endif
> #ifdef ORACLE_9I
> if (type == (PyObject*) &g_TimestampVarType)
> --- 315,324 ----
> return &vt_Number;
> #ifdef NATIVE_DATETIME
> if (type == (PyObject*) PyDateTimeAPI->DateTimeType)
> ! return mostPreciseDt;
> #else
> if (type == (PyObject*) &g_ExternalDateTimeVarType)
> ! return mostPreciseDt;
> #endif
> #ifdef ORACLE_9I
> if (type == (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 == &g_ExternalDateTimeVarType)
> ! return &vt_DateTime;
> #endif
> if (value->ob_type == &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 == &g_ExternalDateTimeVarType)
> ! return mostPreciseDt;
> ! #endif
> ! #ifdef ORACLE_9I
> ! if (value->ob_type == &g_TimestampVarType)
> ! return &vt_Timestamp;
> #endif
> if (value->ob_type == &g_CursorType)
> return &vt_Cursor;
>
>
> -----------------------------
> Stephen Tether
> GLAST ISOC Software Developer
> SLAC MS 43A
> te...@sl...
> (650) 926-4706
>
>
> -------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job
> easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmdlnk&kid0709&bid&3057&dat1642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job
easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=k&kid0709&bid&3057&dat1642
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X