cx-oracle-users — Primary discussion list for users (and developers) of cx_Oracle

Re: [cx-oracle-users] Getting timezone info out of systimestamp

[<wa...@li...>]

Anthony Tuininga wrote:

> I am currently ignoring timezone stuff completely within cx_Oracle -- a lot of that seems black magic to me which is why I
> haven't attempted it. :-) Are there any other modules that have implemented this sort of thing?

I don't know.

> I know you can get at the
> timezone for the session by "select sessiontimezone from dual".

Actually what I need isn't the current timezone, but the timezone of a TIMESTAMP WITH TIME ZONE field. When the record is
inserted this field would be set to SYSTIMESTAMP. The content of this record will be published by a CherryPy webserver and I
want to send a proper Last-Modified header which requires an UTC timestamp so I have to substract the timezone offset.
> If you know what to do with that perhaps I can add the code
> to cx_Oracle -- naturally Oracle has a different
> concept of time zones than Python does which makes it a little more difficult. Comments?

There seems to be a OCIDateTimeGetTimeZoneOffset() in ociap.h. This function returns the hour and minute timezone offset and a
DateTime object. I tried putting a call to this into DateTimeVar.c::DateTimeVar_GetValue(), but that didn't work. I also don't
know what OCIDateTimeGetTimeZoneOffset() does if the datetime object doesn't have a timezone. In fact I don't even know if
OCIDateTimeGetTimeZoneOffset() is the correct function. :-/
Servus,
    Walter

Re: [cx-oracle-users] Getting timezone info out of systimestamp

[Anthony T. <ant...@gm...>]

I am currently ignoring timezone stuff completely within cx_Oracle --
a lot of that seems black magic to me which is why I haven't attempted
it. :-) Are there any other modules that have implemented this sort of
thing? I know you can get at the timezone for the session by "select
sessiontimezone from dual". If you know what to do with that perhaps I
can add the code to cx_Oracle -- naturally Oracle has a different
concept of time zones than Python does which makes it a little more
difficult. Comments?

On 5/9/06, Walter D=F6rwald <wa...@li...> wrote:
> Hello list!
>
> Is there a way to get the timezone information out of systimestamp? The
> following code doesn't seem to work:
>
> In [1]: import cx_Oracle
> In [2]: db =3D cx_Oracle.connect("...")
> In [3]: c =3D db.cursor()
> In [4]: c.execute("select systimestamp from dual")
> Out[4]: [<cx_Oracle.TIMESTAMP object at 0xb3aa62a0>]
> In [5]: d =3D c.fetchone()
> In [6]: d[0].tzinfo
> In [7]:
>
> I'm using Python 2.4.2/cx_Oracle 4.1.2
>
> Servus,
>    Walter
>
>
> -------------------------------------------------------
> 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?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=
=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Getting timezone info out of systimestamp

[<wa...@li...>]

Hello list!

Is there a way to get the timezone information out of systimestamp? The
following code doesn't seem to work:

In [1]: import cx_Oracle
In [2]: db = cx_Oracle.connect("...")
In [3]: c = db.cursor()
In [4]: c.execute("select systimestamp from dual")
Out[4]: [<cx_Oracle.TIMESTAMP object at 0xb3aa62a0>]
In [5]: d = c.fetchone()
In [6]: d[0].tzinfo
In [7]:

I'm using Python 2.4.2/cx_Oracle 4.1.2

Servus,
   Walter

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

[Taylor B. <tb...@co...>]

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

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

[Anthony T. <ant...@gm...>]

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
>

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

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

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

[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

Re: [cx-oracle-users] problem with numbers when sizeof(long) > sizeof(int)

[Anthony T. <ant...@gm...>]

Apologies for the delay in getting back to you on this. I finally
confirmed the problem and your solution. NumberVar_GetValue() also had
problems as you suspected and I've fixed them as well. I've also added
a test case to the test suite to test for this particular situation.
Thank you for notifying me of the problem and providing a solution.

On 4/20/06, Uwe Hoffmann <qu...@ti...> wrote:
>
> Hi ,
> i think that there is a serious problem within cx_Oracle when dealing
> with large numbers (not python longs) for example 6088343244 on
> platforms where sizeof(long) !=3D sizeof(int)
>
> The attached fix solves the problem for me (Solaris) but maybe there are
> other similar problems (NumberVar_GetValue)
>
> regards
>         Uwe
>
>
> *** cx_Oracle-4.1.2/NumberVar.c 2006-04-20 12:17:28.000000000 +0200
> --- cx_Oracle-4.1.2_org/NumberVar.c     2004-11-18 16:55:56.000000000 +01=
00
> ***************
> *** 146,152 ****
>    {
>        PyObject *textValue;
>        double doubleValue;
> !     long integerValue;
>        sword status;
>
>        // make sure a number is being bound
> --- 146,152 ----
>    {
>        PyObject *textValue;
>        double doubleValue;
> !     int integerValue;
>        sword status;
>
>        // make sure a number is being bound
> ***************
> *** 160,166 ****
>        if (PyInt_Check(value)) {
>            integerValue =3D PyInt_AS_LONG(value);
>            status =3D OCINumberFromInt(var->environment->errorHandle,
> &integerValue,
> !                 sizeof(long), OCI_NUMBER_SIGNED, &var->data[pos]);
>            if (var->returnType =3D=3D gc_NumberAsUnknown)
>                var->returnType =3D gc_NumberAsInteger;
>        } else if (PyFloat_Check(value)) {
> --- 160,166 ----
>        if (PyInt_Check(value)) {
>            integerValue =3D PyInt_AS_LONG(value);
>            status =3D OCINumberFromInt(var->environment->errorHandle,
> &integerValue,
> !                 sizeof(int), OCI_NUMBER_SIGNED, &var->data[pos]);
>            if (var->returnType =3D=3D gc_NumberAsUnknown)
>                var->returnType =3D gc_NumberAsInteger;
>        } else if (PyFloat_Check(value)) {
>
>
>
> -------------------------------------------------------
> 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?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=
=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] problem with numbers when sizeof(long) > sizeof(int)

[Uwe H. <qu...@ti...>]

Hi ,
i think that there is a serious problem within cx_Oracle when dealing 
with large numbers (not python longs) for example 6088343244 on 
platforms where sizeof(long) != sizeof(int)

The attached fix solves the problem for me (Solaris) but maybe there are 
other similar problems (NumberVar_GetValue)

regards
	Uwe


*** cx_Oracle-4.1.2/NumberVar.c 2006-04-20 12:17:28.000000000 +0200
--- cx_Oracle-4.1.2_org/NumberVar.c     2004-11-18 16:55:56.000000000 +0100
***************
*** 146,152 ****
   {
       PyObject *textValue;
       double doubleValue;
!     long integerValue;
       sword status;

       // make sure a number is being bound
--- 146,152 ----
   {
       PyObject *textValue;
       double doubleValue;
!     int integerValue;
       sword status;

       // make sure a number is being bound
***************
*** 160,166 ****
       if (PyInt_Check(value)) {
           integerValue = PyInt_AS_LONG(value);
           status = OCINumberFromInt(var->environment->errorHandle, 
&integerValue,
!                 sizeof(long), OCI_NUMBER_SIGNED, &var->data[pos]);
           if (var->returnType == gc_NumberAsUnknown)
               var->returnType = gc_NumberAsInteger;
       } else if (PyFloat_Check(value)) {
--- 160,166 ----
       if (PyInt_Check(value)) {
           integerValue = PyInt_AS_LONG(value);
           status = OCINumberFromInt(var->environment->errorHandle, 
&integerValue,
!                 sizeof(int), OCI_NUMBER_SIGNED, &var->data[pos]);
           if (var->returnType == gc_NumberAsUnknown)
               var->returnType = gc_NumberAsInteger;
       } else if (PyFloat_Check(value)) {

Re: [cx-oracle-users] Tuple of Dictionaries query results

[Tom H. <tom...@sc...>]

Hi Anthony,

Thanks for the tip. And thanks to others for their feedback as well. I
went with this idea as it suited me best in this case, but the other
comments gave be ideas for other approaches as well.

Thanks, Tom

On Tue, 2006-04-18 at 09:35 -0600, Anthony Tuininga wrote:
> If you want to do this you should be able to do it as follows. Note
> that this will reduce performance quite dramatically but if you are
> only dealing with a handful of rows the additional clarity might be
> worthwhile.
> 
> class Cursor(cx_Oracle.Cursor):
> 
>     def __TransformToDict(self, row):
>         return dict([(info[0], value) for info, value in
> zip(cursor.description, row)])
> 
>     def fetchone(self):
>         row = super(Cursor, self).fetchone()
>         return self.__TransformToDict(row)
> 
> etc. (for fetchall() and fetchmany())
> 
> Note that this code has not actually been tested but it should give
> you an idea of how to go about doing this if you have a mind to do so.
> :-)
> 
> On 4/18/06, Tom Haddon <tom...@sc...> wrote:
> > Hi Folks,
> >
> > I was wondering if anyone knows if there's a way to grab the results of
> > an Oracle query into a tuple of dictionaries (one for each row with the
> > key as the column name and the value as the value of the column)?
> >
> > In mysql I do this as below:
> >
> > ----------------
> >
> > import MySQLdb
> > from MySQLdb.cursors import DictCursor
> > cursortype = DictCursor
> >
> > ...
> >
> > curs = conn.cursor(cursortype)
> > result = curs.execute("%s" % (sql, ))
> > return curs.fetchall()
> >
> > -------------------
> >
> > And in Postgres I do it like this:
> >
> > -----------------
> >
> > import pg
> >
> > ...
> >
> > result = conn.query("%s" % (sql, ))
> > return result.dictresult()
> >
> > ------------------
> >
> > I can't seem to see if there are any cursor types as with MySQLdb
> > module, or a dictresult query return method as with the pg module.
> >
> > Basically this then allows me to reference the data as follows:
> >
> > for row in TupleOfDicts():
> >         print "%s %s" % (row['column1'], row['column2'])
> >
> > Any help appreciated.
> >
> > Thanks, Tom
> > --
> > Tom Haddon
> > Systems Engineer
> >
> > 198 High Holborn
> > London, WC1V 7BD
> >
> > T +44 (0) 20 7959 0630
> > www.scansafe.com
> >
> > ScanSafe - Pure Internet
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> > that extends applications into web and mobile media. Attend the live webcast
> > and join the prime developer group breaking into this new coding territory!
> > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> that extends applications into web and mobile media. Attend the live webcast
> and join the prime developer group breaking into this new coding territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-- 
Tom Haddon
Systems Engineer

198 High Holborn
London, WC1V 7BD
 
T +44 (0) 20 7959 0630 
www.scansafe.com
 
ScanSafe - Pure Internet

RE: [cx-oracle-users] Tuple of Dictionaries query results

[Hancock, D. \(DHANCOCK\) <DHA...@ar...>]

I think that the module dtuple.py (written by Greg Stein) will do what =
you want (it's very flexible in how you can refer to the query results). =
 Actually, I'm not 100% sure that you'll get the tuple of dictionaries, =
but that would be a very simple wrapper.
=20
    http://www.lyra.org/greg/python/dtuple.py
=20
Cheers!
--
David Hancock

________________________________

From: cx-...@li... on behalf of Tom =
Haddon
Sent: Tue 4/18/2006 11:23 AM
To: cx-...@li...
Subject: [cx-oracle-users] Tuple of Dictionaries query results



Hi Folks,

I was wondering if anyone knows if there's a way to grab the results of
an Oracle query into a tuple of dictionaries (one for each row with the
key as the column name and the value as the value of the column)?

In mysql I do this as below:

----------------

import MySQLdb
from MySQLdb.cursors import DictCursor
cursortype =3D DictCursor

...

curs =3D conn.cursor(cursortype)
result =3D curs.execute("%s" % (sql, ))
return curs.fetchall()

-------------------

And in Postgres I do it like this:

-----------------

import pg

...

result =3D conn.query("%s" % (sql, ))
return result.dictresult()

------------------

I can't seem to see if there are any cursor types as with MySQLdb
module, or a dictresult query return method as with the pg module.

Basically this then allows me to reference the data as follows:

for row in TupleOfDicts():
        print "%s %s" % (row['column1'], row['column2'])

Any help appreciated.

Thanks, Tom
--
Tom Haddon
Systems Engineer

198 High Holborn
London, WC1V 7BD

T +44 (0) 20 7959 0630
www.scansafe.com

ScanSafe - Pure Internet


-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting =
language
that extends applications into web and mobile media. Attend the live =
webcast
and join the prime developer group breaking into this new coding =
territory!
http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D110944&bid=3D241720&dat=3D=
121642
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Tuple of Dictionaries query results

[<wa...@li...>]

Anthony Tuininga wrote:

> If you want to do this you should be able to do it as follows. Note
> that this will reduce performance quite dramatically but if you are
> only dealing with a handful of rows the additional clarity might be
> worthwhile.
> 
> class Cursor(cx_Oracle.Cursor):
> 
>     def __TransformToDict(self, row):
>         return dict([(info[0], value) for info, value in
> zip(cursor.description, row)])
> 
>     def fetchone(self):
>         row = super(Cursor, self).fetchone()
>         return self.__TransformToDict(row)
> 
> etc. (for fetchall() and fetchmany())

Or you could use ll-orasql (http://www.livinglogic.de/Python/orasql/)
which does exactly that (and makes the fields available as attributes too).

Bye,
   Walter Dörwald

Re: [cx-oracle-users] Tuple of Dictionaries query results

[Anthony T. <ant...@gm...>]

If you want to do this you should be able to do it as follows. Note
that this will reduce performance quite dramatically but if you are
only dealing with a handful of rows the additional clarity might be
worthwhile.

class Cursor(cx_Oracle.Cursor):

    def __TransformToDict(self, row):
        return dict([(info[0], value) for info, value in
zip(cursor.description, row)])

    def fetchone(self):
        row =3D super(Cursor, self).fetchone()
        return self.__TransformToDict(row)

etc. (for fetchall() and fetchmany())

Note that this code has not actually been tested but it should give
you an idea of how to go about doing this if you have a mind to do so.
:-)

On 4/18/06, Tom Haddon <tom...@sc...> wrote:
> Hi Folks,
>
> I was wondering if anyone knows if there's a way to grab the results of
> an Oracle query into a tuple of dictionaries (one for each row with the
> key as the column name and the value as the value of the column)?
>
> In mysql I do this as below:
>
> ----------------
>
> import MySQLdb
> from MySQLdb.cursors import DictCursor
> cursortype =3D DictCursor
>
> ...
>
> curs =3D conn.cursor(cursortype)
> result =3D curs.execute("%s" % (sql, ))
> return curs.fetchall()
>
> -------------------
>
> And in Postgres I do it like this:
>
> -----------------
>
> import pg
>
> ...
>
> result =3D conn.query("%s" % (sql, ))
> return result.dictresult()
>
> ------------------
>
> I can't seem to see if there are any cursor types as with MySQLdb
> module, or a dictresult query return method as with the pg module.
>
> Basically this then allows me to reference the data as follows:
>
> for row in TupleOfDicts():
>         print "%s %s" % (row['column1'], row['column2'])
>
> Any help appreciated.
>
> Thanks, Tom
> --
> Tom Haddon
> Systems Engineer
>
> 198 High Holborn
> London, WC1V 7BD
>
> T +44 (0) 20 7959 0630
> www.scansafe.com
>
> ScanSafe - Pure Internet
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting langua=
ge
> that extends applications into web and mobile media. Attend the live webc=
ast
> and join the prime developer group breaking into this new coding territor=
y!
> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D110944&bid=3D241720&dat=
=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Tuple of Dictionaries query results

[Tom H. <tom...@sc...>]

Hi Folks,

I was wondering if anyone knows if there's a way to grab the results of
an Oracle query into a tuple of dictionaries (one for each row with the
key as the column name and the value as the value of the column)?

In mysql I do this as below:

----------------

import MySQLdb
from MySQLdb.cursors import DictCursor
cursortype = DictCursor

...

curs = conn.cursor(cursortype)
result = curs.execute("%s" % (sql, ))
return curs.fetchall()

-------------------

And in Postgres I do it like this:

-----------------

import pg

...

result = conn.query("%s" % (sql, ))
return result.dictresult()

------------------

I can't seem to see if there are any cursor types as with MySQLdb
module, or a dictresult query return method as with the pg module.

Basically this then allows me to reference the data as follows:

for row in TupleOfDicts():
	print "%s %s" % (row['column1'], row['column2'])

Any help appreciated.

Thanks, Tom
-- 
Tom Haddon
Systems Engineer

198 High Holborn
London, WC1V 7BD
 
T +44 (0) 20 7959 0630 
www.scansafe.com
 
ScanSafe - Pure Internet

Re: [cx-oracle-users] Question on calling stored procedures using cx_Oracle

[Anthony T. <ant...@gm...>]

See the test suite for an example of how to call a stored procedure
with an out cursor that you can then fetch from. Specifically, look at
the file called "CursorVar.py". If you need more specific pointers,
please let me know.

On 4/10/06, Lear, Russell <Rus...@xe...> wrote:
> Hi all,
>
> I'm a (mostly) happy user of cx_Oracle, but I've found one hole that I'd
> like to find a solution to.
>
> I'm running I have a stored procedure looking like
>
>   PROCEDURE RestartableJobs(c_djob OUT djob_cursor) IS
>     BEGIN
>
>       OPEN c_djob FOR 'SELECT distinct job_id from jobtable
>         where status < cnStatusRejected and exists (select null from
> activejobs where activejobs.job_id =3D jobtable.job_id)';
>       NULL;
>
>         END RestartableJobs;
>
> How do I call this thing?  (or do I?)  Clearly, in this case I could
> pass in the sql myself, but duplicating existing PL/SQL code isn't too
> attractive.
>
>     >>> import cx_Oracle as cxo
>     >>> connection =3D cxo.connect('scott/tiger')
>     >>> cur =3D connection.cursor()
>     >>> r =3D cur.callproc('rlpkg.RestartableJobs')
>     Traceback (most recent call last):
>       File "<stdin>", line 1, in ?
>     cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
>     PLS-00306: wrong number or types of arguments in call to
> 'RESTARTABLEJOBS'
>     ORA-06550: line 1, column 7:
>     PL/SQL: Statement ignored
>
> But I can't figure out what to pass in to it.  Any hints?  For what it's
> worth, I'm on WinXP, using Oracle 9i, with cx_Oracle 4.1.2.
>
> Thanks,
> Russell.
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting langua=
ge
> that extends applications into web and mobile media. Attend the live webc=
ast
> and join the prime developer group breaking into this new coding territor=
y!
> http://sel.as-us.falkag.net/sel?cmdlnk&kid=110944&bid$1720&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Question on calling stored procedures using cx_Oracle

[Lear, R. <Rus...@xe...>]

Hi all,

I'm a (mostly) happy user of cx_Oracle, but I've found one hole that I'd
like to find a solution to.

I'm running I have a stored procedure looking like=20

  PROCEDURE RestartableJobs(c_djob OUT djob_cursor) IS  =20
    BEGIN
    =09
      OPEN c_djob FOR 'SELECT distinct job_id from jobtable=20
        where status < cnStatusRejected and exists (select null from
activejobs where activejobs.job_id =3D jobtable.job_id)';
      NULL;=09

	END RestartableJobs;=09

How do I call this thing?  (or do I?)  Clearly, in this case I could
pass in the sql myself, but duplicating existing PL/SQL code isn't too
attractive.

    >>> import cx_Oracle as cxo
    >>> connection =3D cxo.connect('scott/tiger')
    >>> cur =3D connection.cursor()
    >>> r =3D cur.callproc('rlpkg.RestartableJobs')
    Traceback (most recent call last):
      File "<stdin>", line 1, in ?
    cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to
'RESTARTABLEJOBS'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

But I can't figure out what to pass in to it.  Any hints?  For what it's
worth, I'm on WinXP, using Oracle 9i, with cx_Oracle 4.1.2.

Thanks,
Russell.

[cx-oracle-users] Re: build on aix - unresolved symbols .OCI...

[Nicola P. <nic...@it...>]

Thank you for your support.

Suggestion to link lib32 instead of lib solved my compilation problem.
Now I'm starting learning cx_oracle usage.

Regards
Nicola Piazza


                                                                           
             Nicola                                                        
             Piazza/Italy/IBM                                              
                                                                        To 
             04/06/2006 11:54          cx-...@li...urceforge.n 
             AM                        et                                  
                                                                        cc 
                                                                           
                                                                   Subject 
                                       build on aix - unresolved symbols   
                                       .OCI...                             
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           



Hi all,
I'm trying to compile cx_Oracle-4.1 on Aix, with oracle 9.2. I've modified
......./....../lib/python2.3/config/Makefile to use xlc as compiler.
I've exported my ORACLE_HOME variable, then I've run a python setup.py
bdist, in order to create a binary version (I haven't compilers
everywhere).

Compilation seems ok, while I've got a problem during link phase:

# python setup.py bdist
running bdist
running bdist_dumb
running build
running build_ext
building 'cx_Oracle' extension
creating build
creating build/temp.aix-5.2-2.3
xlc -DNDEBUG -O -qmaxmem=-1 -qcpluscmt -DAIX_GENUINE_CPLUSCPLUS
-D_LARGE_FILES -I/ora01/ora9.2/rdbms/demo -I/ora01/ora9.
2/rdbms/public -I/ora01/ora9.2/network/public
-I/opt/freeware/include/python2.3 -c cx_Oracle.c -o build/temp.aix-5.2-2.3
/cx_Oracle.o -DBUILD_TIME="April 06, 2006 11:49:23"
creating build/lib.aix-5.2-2.3
/opt/freeware/lib/python2.3/config/ld_so_aix xlc
-bI:/opt/freeware/lib/python2.3/config/python.exp build/temp.aix-5.2-2.
3/cx_Oracle.o -L/ora01/ora9.2/lib -lclntsh -o build/lib.aix-5.2-2.3
/cx_Oracle.so -s
ld: 0711-317 ERROR: Undefined symbol: .OCIAttrGet
ld: 0711-317 ERROR: Undefined symbol: .OCIStmtFetch
ld: 0711-317 ERROR: Undefined symbol: .OCIStmtPrepare2
ld: 0711-317 ERROR: Undefined symbol: .OCIParamGet
ld: 0711-317 ERROR: Undefined symbol: .OCIDescriptorFree
ld: 0711-317 ERROR: Undefined symbol: .OCIStmtExecute
ld: 0711-317 ERROR: Undefined symbol: .OCIStmtGetBindInfo
ld: 0711-317 ERROR: Undefined symbol: .OCIDefineByPos
ld: 0711-317 ERROR: Undefined symbol: .OCILobCreateTemporary
ld: 0711-317 ERROR: Undefined symbol: .OCILobTrim
ld: 0711-317 ERROR: Undefined symbol: .OCILobWrite
ld: 0711-317 ERROR: Undefined symbol: .OCILobFreeTemporary
ld: 0711-317 ERROR: Undefined symbol: .OCIDescriptorAlloc
ld: 0711-317 ERROR: Undefined symbol: .OCILobRead
ld: 0711-317 ERROR: Undefined symbol: .OCILobGetLength
ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeGetDate
ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeGetTime
ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeConstruct
ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeCheck
ld: 0711-317 ERROR: Undefined symbol: .OCIDateCheck
ld: 0711-317 ERROR: Undefined symbol: .OCINumberToInt
ld: 0711-317 ERROR: Undefined symbol: .OCINumberToText
ld: 0711-317 ERROR: Undefined symbol: .OCINumberToReal
ld: 0711-317 ERROR: Undefined symbol: .OCINumberFromText
ld: 0711-317 ERROR: Undefined symbol: .OCINumberFromReal
ld: 0711-317 ERROR: Undefined symbol: .OCINumberFromInt
ld: 0711-317 ERROR: Undefined symbol: .OCIBindByName
ld: 0711-317 ERROR: Undefined symbol: .OCIBindByPos
ld: 0711-317 ERROR: Undefined symbol: .OCIHandleAlloc
ld: 0711-317 ERROR: Undefined symbol: .OCIServerAttach
ld: 0711-317 ERROR: Undefined symbol: .OCIAttrSet
ld: 0711-317 ERROR: Undefined symbol: .OCISessionBegin
ld: 0711-317 ERROR: Undefined symbol: .OCISessionGet
ld: 0711-317 ERROR: Undefined symbol: .OCIUserCallbackGet
ld: 0711-317 ERROR: Undefined symbol: .OCIUserCallbackRegister
ld: 0711-317 ERROR: Undefined symbol: .OCIBreak
ld: 0711-317 ERROR: Undefined symbol: .OCITransRollback
ld: 0711-317 ERROR: Undefined symbol: .OCITransPrepare
ld: 0711-317 ERROR: Undefined symbol: .OCITransStart
ld: 0711-317 ERROR: Undefined symbol: .OCITransCommit
ld: 0711-317 ERROR: Undefined symbol: .OCISessionEnd
ld: 0711-317 ERROR: Undefined symbol: .OCIHandleFree
ld: 0711-317 ERROR: Undefined symbol: .OCISessionRelease
ld: 0711-317 ERROR: Undefined symbol: .OCIServerDetach
ld: 0711-317 ERROR: Undefined symbol: .OCISessionPoolDestroy
ld: 0711-317 ERROR: Undefined symbol: .OCISessionPoolCreate
ld: 0711-317 ERROR: Undefined symbol: .OCIEnvCreate
ld: 0711-317 ERROR: Undefined symbol: .OCINlsNumericInfoGet
ld: 0711-317 ERROR: Undefined symbol: .OCIErrorGet
ld: 0711-317 ERROR: Undefined symbol: .OCIStmtRelease
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more
information.
installing to build/bdist.aix-5.2/dumb
running install
running install_lib
creating build/bdist.aix-5.2
creating build/bdist.aix-5.2/dumb
creating build/bdist.aix-5.2/dumb/opt
creating build/bdist.aix-5.2/dumb/opt/freeware
creating build/bdist.aix-5.2/dumb/opt/freeware/lib
creating build/bdist.aix-5.2/dumb/opt/freeware/lib/python2.3
creating build/bdist.aix-5.2/dumb/opt/freeware/lib/python2.3/site-packages
tar -cf /tmp/python/source/cx_Oracle-4.1/dist/cx_Oracle-4.1.aix-5.2.tar .
gzip -f9 /tmp/python/source/cx_Oracle-4.1/dist/cx_Oracle-4.1.aix-5.2.tar
removing 'build/bdist.aix-5.2/dumb' (and everything under it)

Any suggestion ?
I've used nm in $ORACLE_HOME/lib to verify who exports these symbols and
found them in libjmisc.so and in hsdb_ora.so.

Compiler and linker seems ok, since I've correctly compiled PyDB2 module.
Thank you for your support.

[cx-oracle-users] Catching ORA-28002 warning

[Hubert B H. <hhi...@un...>]

I would to catch the warning that comes back from Oracle that tells the 
user that their password will expire soon.  Via JDBC and Java (just as 
example) it looks something like this:

           this.conn = DriverManager.getConnection (url, u, p);
            SQLWarning sqlw = this.conn.getWarnings();
            if (sqlw != null ) {
                System.out.println("verifyPassword warning for " + u + ": 
" + sqlw.getMessage());
                if (sqlw.getErrorCode() == 28002) {
                        /*
                         * This is the password expiration warning that 
will be passed back to the user
                         */
                <do some work here >

This doesn't come back as an exception from Oracle, and I can't figure out 
how to see the warning from cx_Oracle. Catching cx_Oracle.Warning doesn't 
get catch this warning.  Is there a way to catch this warning?


Thanks!

Hubert Hickman

Re: [cx-oracle-users] build on aix - unresolved symbols .OCI...

[inkbottle <ink...@gm...>]

Your ORACLE_HOME is pointing to 64-bit Oracle libraries, but you are 
building 32-bit so cx_Oracle cannot load the 64-bit Oracle libraries. 
You'll want to use $ORACLE_HOME/lib32 to build 32-bit. This will require 
that you edit the setup.py script to replace all the lib references to 
lib32. Also I believe you will want to build with xlc_r instead of xlc. 
I wouldn't muck with the Makefiles except as a last resort, but rather 
the setup.py script, which only vaguely works out of the box on AIX. I 
don't know what pyDB2 has to do with this, but then I have never built 
with python earlier than 2.4.
Nicola Piazza wrote:
> Hi all,
> I'm trying to compile cx_Oracle-4.1 on Aix, with oracle 9.2. I've modified
> ......./....../lib/python2.3/config/Makefile to use xlc as compiler.
> I've exported my ORACLE_HOME variable, then I've run a python setup.py
> bdist, in order to create a binary version (I haven't compilers
> everywhere).
>
> Compilation seems ok, while I've got a problem during link phase:
>
> # python setup.py bdist
> running bdist
> running bdist_dumb
> running build
> running build_ext
> building 'cx_Oracle' extension
> creating build
> creating build/temp.aix-5.2-2.3
> xlc -DNDEBUG -O -qmaxmem=-1 -qcpluscmt -DAIX_GENUINE_CPLUSCPLUS
> -D_LARGE_FILES -I/ora01/ora9.2/rdbms/demo -I/ora01/ora9.
> 2/rdbms/public -I/ora01/ora9.2/network/public
> -I/opt/freeware/include/python2.3 -c cx_Oracle.c -o build/temp.aix-5.2-2.3
> /cx_Oracle.o -DBUILD_TIME="April 06, 2006 11:49:23"
> creating build/lib.aix-5.2-2.3
> /opt/freeware/lib/python2.3/config/ld_so_aix xlc
> -bI:/opt/freeware/lib/python2.3/config/python.exp build/temp.aix-5.2-2.
> 3/cx_Oracle.o -L/ora01/ora9.2/lib -lclntsh -o build/lib.aix-5.2-2.3
> /cx_Oracle.so -s
> ld: 0711-317 ERROR: Undefined symbol: .OCIAttrGet
> ld: 0711-317 ERROR: Undefined symbol: .OCIStmtFetch
> ld: 0711-317 ERROR: Undefined symbol: .OCIStmtPrepare2
> ld: 0711-317 ERROR: Undefined symbol: .OCIParamGet
> ld: 0711-317 ERROR: Undefined symbol: .OCIDescriptorFree
> ld: 0711-317 ERROR: Undefined symbol: .OCIStmtExecute
> ld: 0711-317 ERROR: Undefined symbol: .OCIStmtGetBindInfo
> ld: 0711-317 ERROR: Undefined symbol: .OCIDefineByPos
> ld: 0711-317 ERROR: Undefined symbol: .OCILobCreateTemporary
> ld: 0711-317 ERROR: Undefined symbol: .OCILobTrim
> ld: 0711-317 ERROR: Undefined symbol: .OCILobWrite
> ld: 0711-317 ERROR: Undefined symbol: .OCILobFreeTemporary
> ld: 0711-317 ERROR: Undefined symbol: .OCIDescriptorAlloc
> ld: 0711-317 ERROR: Undefined symbol: .OCILobRead
> ld: 0711-317 ERROR: Undefined symbol: .OCILobGetLength
> ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeGetDate
> ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeGetTime
> ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeConstruct
> ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeCheck
> ld: 0711-317 ERROR: Undefined symbol: .OCIDateCheck
> ld: 0711-317 ERROR: Undefined symbol: .OCINumberToInt
> ld: 0711-317 ERROR: Undefined symbol: .OCINumberToText
> ld: 0711-317 ERROR: Undefined symbol: .OCINumberToReal
> ld: 0711-317 ERROR: Undefined symbol: .OCINumberFromText
> ld: 0711-317 ERROR: Undefined symbol: .OCINumberFromReal
> ld: 0711-317 ERROR: Undefined symbol: .OCINumberFromInt
> ld: 0711-317 ERROR: Undefined symbol: .OCIBindByName
> ld: 0711-317 ERROR: Undefined symbol: .OCIBindByPos
> ld: 0711-317 ERROR: Undefined symbol: .OCIHandleAlloc
> ld: 0711-317 ERROR: Undefined symbol: .OCIServerAttach
> ld: 0711-317 ERROR: Undefined symbol: .OCIAttrSet
> ld: 0711-317 ERROR: Undefined symbol: .OCISessionBegin
> ld: 0711-317 ERROR: Undefined symbol: .OCISessionGet
> ld: 0711-317 ERROR: Undefined symbol: .OCIUserCallbackGet
> ld: 0711-317 ERROR: Undefined symbol: .OCIUserCallbackRegister
> ld: 0711-317 ERROR: Undefined symbol: .OCIBreak
> ld: 0711-317 ERROR: Undefined symbol: .OCITransRollback
> ld: 0711-317 ERROR: Undefined symbol: .OCITransPrepare
> ld: 0711-317 ERROR: Undefined symbol: .OCITransStart
> ld: 0711-317 ERROR: Undefined symbol: .OCITransCommit
> ld: 0711-317 ERROR: Undefined symbol: .OCISessionEnd
> ld: 0711-317 ERROR: Undefined symbol: .OCIHandleFree
> ld: 0711-317 ERROR: Undefined symbol: .OCISessionRelease
> ld: 0711-317 ERROR: Undefined symbol: .OCIServerDetach
> ld: 0711-317 ERROR: Undefined symbol: .OCISessionPoolDestroy
> ld: 0711-317 ERROR: Undefined symbol: .OCISessionPoolCreate
> ld: 0711-317 ERROR: Undefined symbol: .OCIEnvCreate
> ld: 0711-317 ERROR: Undefined symbol: .OCINlsNumericInfoGet
> ld: 0711-317 ERROR: Undefined symbol: .OCIErrorGet
> ld: 0711-317 ERROR: Undefined symbol: .OCIStmtRelease
> ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more
> information.
> installing to build/bdist.aix-5.2/dumb
> running install
> running install_lib
> creating build/bdist.aix-5.2
> creating build/bdist.aix-5.2/dumb
> creating build/bdist.aix-5.2/dumb/opt
> creating build/bdist.aix-5.2/dumb/opt/freeware
> creating build/bdist.aix-5.2/dumb/opt/freeware/lib
> creating build/bdist.aix-5.2/dumb/opt/freeware/lib/python2.3
> creating build/bdist.aix-5.2/dumb/opt/freeware/lib/python2.3/site-packages
> tar -cf /tmp/python/source/cx_Oracle-4.1/dist/cx_Oracle-4.1.aix-5.2.tar .
> gzip -f9 /tmp/python/source/cx_Oracle-4.1/dist/cx_Oracle-4.1.aix-5.2.tar
> removing 'build/bdist.aix-5.2/dumb' (and everything under it)
>
> Any suggestion ?
> I've used nm in $ORACLE_HOME/lib to verify who exports these symbols and
> found them in libjmisc.so and in hsdb_ora.so.
>
> Compiler and linker seems ok, since I've correctly compiled PyDB2 module.
> Thank you for your support.
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> that extends applications into web and mobile media. Attend the live webcast
> and join the prime developer group breaking into this new coding territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>   

[cx-oracle-users] build on aix - unresolved symbols .OCI...

[Nicola P. <nic...@it...>]

Hi all,
I'm trying to compile cx_Oracle-4.1 on Aix, with oracle 9.2. I've modified
......./....../lib/python2.3/config/Makefile to use xlc as compiler.
I've exported my ORACLE_HOME variable, then I've run a python setup.py
bdist, in order to create a binary version (I haven't compilers
everywhere).

Compilation seems ok, while I've got a problem during link phase:

# python setup.py bdist
running bdist
running bdist_dumb
running build
running build_ext
building 'cx_Oracle' extension
creating build
creating build/temp.aix-5.2-2.3
xlc -DNDEBUG -O -qmaxmem=-1 -qcpluscmt -DAIX_GENUINE_CPLUSCPLUS
-D_LARGE_FILES -I/ora01/ora9.2/rdbms/demo -I/ora01/ora9.
2/rdbms/public -I/ora01/ora9.2/network/public
-I/opt/freeware/include/python2.3 -c cx_Oracle.c -o build/temp.aix-5.2-2.3
/cx_Oracle.o -DBUILD_TIME="April 06, 2006 11:49:23"
creating build/lib.aix-5.2-2.3
/opt/freeware/lib/python2.3/config/ld_so_aix xlc
-bI:/opt/freeware/lib/python2.3/config/python.exp build/temp.aix-5.2-2.
3/cx_Oracle.o -L/ora01/ora9.2/lib -lclntsh -o build/lib.aix-5.2-2.3
/cx_Oracle.so -s
ld: 0711-317 ERROR: Undefined symbol: .OCIAttrGet
ld: 0711-317 ERROR: Undefined symbol: .OCIStmtFetch
ld: 0711-317 ERROR: Undefined symbol: .OCIStmtPrepare2
ld: 0711-317 ERROR: Undefined symbol: .OCIParamGet
ld: 0711-317 ERROR: Undefined symbol: .OCIDescriptorFree
ld: 0711-317 ERROR: Undefined symbol: .OCIStmtExecute
ld: 0711-317 ERROR: Undefined symbol: .OCIStmtGetBindInfo
ld: 0711-317 ERROR: Undefined symbol: .OCIDefineByPos
ld: 0711-317 ERROR: Undefined symbol: .OCILobCreateTemporary
ld: 0711-317 ERROR: Undefined symbol: .OCILobTrim
ld: 0711-317 ERROR: Undefined symbol: .OCILobWrite
ld: 0711-317 ERROR: Undefined symbol: .OCILobFreeTemporary
ld: 0711-317 ERROR: Undefined symbol: .OCIDescriptorAlloc
ld: 0711-317 ERROR: Undefined symbol: .OCILobRead
ld: 0711-317 ERROR: Undefined symbol: .OCILobGetLength
ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeGetDate
ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeGetTime
ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeConstruct
ld: 0711-317 ERROR: Undefined symbol: .OCIDateTimeCheck
ld: 0711-317 ERROR: Undefined symbol: .OCIDateCheck
ld: 0711-317 ERROR: Undefined symbol: .OCINumberToInt
ld: 0711-317 ERROR: Undefined symbol: .OCINumberToText
ld: 0711-317 ERROR: Undefined symbol: .OCINumberToReal
ld: 0711-317 ERROR: Undefined symbol: .OCINumberFromText
ld: 0711-317 ERROR: Undefined symbol: .OCINumberFromReal
ld: 0711-317 ERROR: Undefined symbol: .OCINumberFromInt
ld: 0711-317 ERROR: Undefined symbol: .OCIBindByName
ld: 0711-317 ERROR: Undefined symbol: .OCIBindByPos
ld: 0711-317 ERROR: Undefined symbol: .OCIHandleAlloc
ld: 0711-317 ERROR: Undefined symbol: .OCIServerAttach
ld: 0711-317 ERROR: Undefined symbol: .OCIAttrSet
ld: 0711-317 ERROR: Undefined symbol: .OCISessionBegin
ld: 0711-317 ERROR: Undefined symbol: .OCISessionGet
ld: 0711-317 ERROR: Undefined symbol: .OCIUserCallbackGet
ld: 0711-317 ERROR: Undefined symbol: .OCIUserCallbackRegister
ld: 0711-317 ERROR: Undefined symbol: .OCIBreak
ld: 0711-317 ERROR: Undefined symbol: .OCITransRollback
ld: 0711-317 ERROR: Undefined symbol: .OCITransPrepare
ld: 0711-317 ERROR: Undefined symbol: .OCITransStart
ld: 0711-317 ERROR: Undefined symbol: .OCITransCommit
ld: 0711-317 ERROR: Undefined symbol: .OCISessionEnd
ld: 0711-317 ERROR: Undefined symbol: .OCIHandleFree
ld: 0711-317 ERROR: Undefined symbol: .OCISessionRelease
ld: 0711-317 ERROR: Undefined symbol: .OCIServerDetach
ld: 0711-317 ERROR: Undefined symbol: .OCISessionPoolDestroy
ld: 0711-317 ERROR: Undefined symbol: .OCISessionPoolCreate
ld: 0711-317 ERROR: Undefined symbol: .OCIEnvCreate
ld: 0711-317 ERROR: Undefined symbol: .OCINlsNumericInfoGet
ld: 0711-317 ERROR: Undefined symbol: .OCIErrorGet
ld: 0711-317 ERROR: Undefined symbol: .OCIStmtRelease
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more
information.
installing to build/bdist.aix-5.2/dumb
running install
running install_lib
creating build/bdist.aix-5.2
creating build/bdist.aix-5.2/dumb
creating build/bdist.aix-5.2/dumb/opt
creating build/bdist.aix-5.2/dumb/opt/freeware
creating build/bdist.aix-5.2/dumb/opt/freeware/lib
creating build/bdist.aix-5.2/dumb/opt/freeware/lib/python2.3
creating build/bdist.aix-5.2/dumb/opt/freeware/lib/python2.3/site-packages
tar -cf /tmp/python/source/cx_Oracle-4.1/dist/cx_Oracle-4.1.aix-5.2.tar .
gzip -f9 /tmp/python/source/cx_Oracle-4.1/dist/cx_Oracle-4.1.aix-5.2.tar
removing 'build/bdist.aix-5.2/dumb' (and everything under it)

Any suggestion ?
I've used nm in $ORACLE_HOME/lib to verify who exports these symbols and
found them in libjmisc.so and in hsdb_ora.so.

Compiler and linker seems ok, since I've correctly compiled PyDB2 module.
Thank you for your support.

Re: [cx-oracle-users] SEGV

[Lars I. <la...@ib...>]

Following up to myself for the sake of the archives, since I got my 
problems resolved.

I had what looked like a deadlock problem and a SEGV.

As it turned out, the deadlock wasn't a real deadlock, but the system 
was thashing wildly due to a memory leak, which I could eventually track 
down to Python issue: [ 1089632 ] _DummyThread() objects not freed from 
threading._active map:
 
https://sourceforge.net/tracker/?func=detail&atid=105470&aid=1089632&group_id=5470

(I am starting my threads from C).

>> Another user has noticed an intermittent segfault due to faulty module
>> initialization. I have fixed the problem but that code is unreleased
>> as of yet. I can give you the source or binary for that if you want to
>> try that.
> 
> That would be good, and source would be best. I'd like to compile 
> against the Oracle version I have installed.
> 
> I couldn't reproduce the problem with 4.1.2 yet, but that doesn't mean 
> much - my current tests hasn't run nearly long enough yet.

The SEGV has disappeared with the 4.2 prerelease that Anthony sent me.

Thanks a lot.

- Lars

Re: [cx-oracle-users] Followup: cx_oracle, returning insert id

[Steve S. <ssw...@ve...>]

One way to get it as dynamic as you want would be to create a stored =20
procedure in the database that accepts the table, column, and value, =20
then generates the SQL as a string and runs it via EXECUTE IMMEDIATE.

Of course, you could just as easily construct the SQL string in =20
Python and run via execute()...

Also, if your trigger is just generating IDs, you could just make use =20=

of the nextval property of a sequence object and generate the ID as =20
part of the insert, or as a separate select before the insert.

Just my $0.02...

On Apr 3, 2006, at 5:53 PM, kellie hobbs wrote:

> That was what I was trying to get at.. oh well.
>
> Thanks.
>
> Kellie
>
> --- Anthony Tuininga <ant...@gm...> wrote:
>
>> You require a string like the one in the example. You cannot pass the
>> table name and columns to update as bind variables, if that is what
>> you are trying to get at. :-)
>>
>> On 4/3/06, kellie hobbs <kel...@ya...> wrote:
>>> Hello,
>>>
>>> Thanks for your help so far. Now can you tell me how to pass the
>> table
>>> name and columns to be updated to the execute statement?
>>>
>>> Thanks again.
>>>
>>> Kellie Hobbs
>>>
>>> --- Anthony Tuininga <ant...@gm...> wrote:
>>>
>>>> Sure. Its quite straightforward but not covered by the DB API.
>>>>
>>>> # this creates the bind variable for use by Oracle
>>>> idVar =3D cursor.var(cx_Oracle.NUMBER)
>>>>
>>>> # execute the statement exactly as you normally would,
>>>> # binding the variable you just created above
>>>> cursor.execute("""
>>>>             insert into SomeTable (
>>>>               SomeOtherColumn
>>>>             ) values (
>>>>                :someValue
>>>>             ) returning Id
>>>>               into :id""",
>>>>             id =3D idVar,
>>>>             someValue =3D "SomeValue")
>>>>
>>>> # get the value after the statement is executed
>>>> id =3D idVar.getvalue()
>>>>
>>>> Hope that explains things.
>>>>
>>>> On 3/22/06, kellie hobbs <kel...@ya...> wrote:
>>>>> Hello,
>>>>>
>>>>> I am trying to get back the id of a record after inserting it
>> into
>>>> the
>>>>> table. My Oracle database has a trigger that automatically
>> creates
>>>> the
>>>>> id on insert. The PL/SQL statement that works for me is:
>>>>> SQL> declare id int;
>>>>>   2  begin
>>>>>   3  insert into table (field) values ('foo') returning tableid
>>>> into
>>>>> id;
>>>>>   4  dbms_output.put_line(id);
>>>>>   5  end;
>>>>>   6  /
>>>>>
>>>>> How can I run this through cx_oracle and get the id stored in a
>>>>> variable so I can use it?
>>>>>
>>>>> Many thanks.
>>>>>
>>>>> Kellie Hobbs, UC Berkeley
>>>>>
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by xPML, a groundbreaking scripting
>> language
>> that extends applications into web and mobile media. Attend the live
>> webcast
>> and join the prime developer group breaking into this new coding
>> territory!
>> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=110944&bid$1720&dat=12164=
2
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting =20
> language
> that extends applications into web and mobile media. Attend the =20
> live webcast
> and join the prime developer group breaking into this new coding =20
> territory!
> http://sel.as-us.falkag.net/sel?=20
> cmd=3Dlnk&kid=3D110944&bid=3D241720&dat=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users


-------------------------------
Steve Swartzlander
ssw...@ve...

Re: [cx-oracle-users] Followup: cx_oracle, returning insert id

[kellie h. <kel...@ya...>]

That was what I was trying to get at.. oh well.

Thanks.

Kellie

--- Anthony Tuininga <ant...@gm...> wrote:

> You require a string like the one in the example. You cannot pass the
> table name and columns to update as bind variables, if that is what
> you are trying to get at. :-)
> 
> On 4/3/06, kellie hobbs <kel...@ya...> wrote:
> > Hello,
> >
> > Thanks for your help so far. Now can you tell me how to pass the
> table
> > name and columns to be updated to the execute statement?
> >
> > Thanks again.
> >
> > Kellie Hobbs
> >
> > --- Anthony Tuininga <ant...@gm...> wrote:
> >
> > > Sure. Its quite straightforward but not covered by the DB API.
> > >
> > > # this creates the bind variable for use by Oracle
> > > idVar = cursor.var(cx_Oracle.NUMBER)
> > >
> > > # execute the statement exactly as you normally would,
> > > # binding the variable you just created above
> > > cursor.execute("""
> > >             insert into SomeTable (
> > >               SomeOtherColumn
> > >             ) values (
> > >                :someValue
> > >             ) returning Id
> > >               into :id""",
> > >             id = idVar,
> > >             someValue = "SomeValue")
> > >
> > > # get the value after the statement is executed
> > > id = idVar.getvalue()
> > >
> > > Hope that explains things.
> > >
> > > On 3/22/06, kellie hobbs <kel...@ya...> wrote:
> > > > Hello,
> > > >
> > > > I am trying to get back the id of a record after inserting it
> into
> > > the
> > > > table. My Oracle database has a trigger that automatically
> creates
> > > the
> > > > id on insert. The PL/SQL statement that works for me is:
> > > > SQL> declare id int;
> > > >   2  begin
> > > >   3  insert into table (field) values ('foo') returning tableid
> > > into
> > > > id;
> > > >   4  dbms_output.put_line(id);
> > > >   5  end;
> > > >   6  /
> > > >
> > > > How can I run this through cx_oracle and get the id stored in a
> > > > variable so I can use it?
> > > >
> > > > Many thanks.
> > > >
> > > > Kellie Hobbs, UC Berkeley
> > > >
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> that extends applications into web and mobile media. Attend the live
> webcast
> and join the prime developer group breaking into this new coding
> territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [cx-oracle-users] Followup: cx_oracle, returning insert id

[Anthony T. <ant...@gm...>]

You require a string like the one in the example. You cannot pass the
table name and columns to update as bind variables, if that is what
you are trying to get at. :-)

On 4/3/06, kellie hobbs <kel...@ya...> wrote:
> Hello,
>
> Thanks for your help so far. Now can you tell me how to pass the table
> name and columns to be updated to the execute statement?
>
> Thanks again.
>
> Kellie Hobbs
>
> --- Anthony Tuininga <ant...@gm...> wrote:
>
> > Sure. Its quite straightforward but not covered by the DB API.
> >
> > # this creates the bind variable for use by Oracle
> > idVar =3D cursor.var(cx_Oracle.NUMBER)
> >
> > # execute the statement exactly as you normally would,
> > # binding the variable you just created above
> > cursor.execute("""
> >             insert into SomeTable (
> >               SomeOtherColumn
> >             ) values (
> >                :someValue
> >             ) returning Id
> >               into :id""",
> >             id =3D idVar,
> >             someValue =3D "SomeValue")
> >
> > # get the value after the statement is executed
> > id =3D idVar.getvalue()
> >
> > Hope that explains things.
> >
> > On 3/22/06, kellie hobbs <kel...@ya...> wrote:
> > > Hello,
> > >
> > > I am trying to get back the id of a record after inserting it into
> > the
> > > table. My Oracle database has a trigger that automatically creates
> > the
> > > id on insert. The PL/SQL statement that works for me is:
> > > SQL> declare id int;
> > >   2  begin
> > >   3  insert into table (field) values ('foo') returning tableid
> > into
> > > id;
> > >   4  dbms_output.put_line(id);
> > >   5  end;
> > >   6  /
> > >
> > > How can I run this through cx_oracle and get the id stored in a
> > > variable so I can use it?
> > >
> > > Many thanks.
> > >
> > > Kellie Hobbs, UC Berkeley
> > >

[cx-oracle-users] Followup: cx_oracle, returning insert id

[kellie h. <kel...@ya...>]

Hello,

Thanks for your help so far. Now can you tell me how to pass the table
name and columns to be updated to the execute statement?

Thanks again.

Kellie Hobbs

--- Anthony Tuininga <ant...@gm...> wrote:

> Sure. Its quite straightforward but not covered by the DB API.
> 
> # this creates the bind variable for use by Oracle
> idVar = cursor.var(cx_Oracle.NUMBER)
> 
> # execute the statement exactly as you normally would,
> # binding the variable you just created above
> cursor.execute("""
>             insert into SomeTable (
>               SomeOtherColumn
>             ) values (
>                :someValue
>             ) returning Id
>               into :id""",
>             id = idVar,
>             someValue = "SomeValue")
> 
> # get the value after the statement is executed
> id = idVar.getvalue()
> 
> Hope that explains things.
> 
> On 3/22/06, kellie hobbs <kel...@ya...> wrote:
> > Hello,
> >
> > I am trying to get back the id of a record after inserting it into
> the
> > table. My Oracle database has a trigger that automatically creates
> the
> > id on insert. The PL/SQL statement that works for me is:
> > SQL> declare id int;
> >   2  begin
> >   3  insert into table (field) values ('foo') returning tableid
> into
> > id;
> >   4  dbms_output.put_line(id);
> >   5  end;
> >   6  /
> >
> > How can I run this through cx_oracle and get the id stored in a
> > variable so I can use it?
> >
> > Many thanks.
> >
> > Kellie Hobbs, UC Berkeley
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> > that extends applications into web and mobile media. Attend the
> live webcast
> > and join the prime developer group breaking into this new coding
> territory!
> >
>
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> that extends applications into web and mobile media. Attend the live
> webcast
> and join the prime developer group breaking into this new coding
> territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com