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

Re: [cx-oracle-users] Results as dict

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

Chris Dunscombe wrote:
> 
> --- Anthony Tuininga <ant...@gm...> wrote:
> 
>> Ah, I see. You can do the same thing with cx_Oracle (create a
>> subclassed cursor that returns rows as dictionaries) quite easily. Its
>> simply not "out of the box" as you say. There is an implicit
>> performance penalty involved (dictionaries cost a great deal more to
>> build than tuples do relatively speaking) so I've tended to avoid such
>> techniques. My personal preference for when performance is not an
>> issue is to return objects with attributes defined for each of the
>> columns. Both of these techniques are possible to use without any help
>> from any module author and should be available globally as a recipe or
>> some such. Would there be appreciation for including such things as
>> part of documentation or sample files?
> 
> I would appreciate these sort things very much.

ll-orasql (http://www.livinglogic.de/Python/orasql/) implements that 
kind of functionality (i.e. column access via row.column and row["column"]).

Servus,
    Walter

Re: [cx-oracle-users] Results as dict

[Chris D. <cdu...@ya...>]

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

> 
> Ah, I see. You can do the same thing with cx_Oracle (create a
> subclassed cursor that returns rows as dictionaries) quite easily. Its
> simply not "out of the box" as you say. There is an implicit
> performance penalty involved (dictionaries cost a great deal more to
> build than tuples do relatively speaking) so I've tended to avoid such
> techniques. My personal preference for when performance is not an
> issue is to return objects with attributes defined for each of the
> columns. Both of these techniques are possible to use without any help
> from any module author and should be available globally as a recipe or
> some such. Would there be appreciation for including such things as
> part of documentation or sample files?
> 

I would appreciate these sort things very much.

Thanks,

Chris Dunscombe


 
____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

[Andy H. <and...@gm...>]

Hi Anthony, thanks for the fast response.

I'd thought about doing something similar to your suggestion for subclassing
cx_Oracle.Cursor and converting the types on the fly, but like you said
there's a performance hit. I'll probably implement this anyway so old
versions of cx_Oracle can be used.

Your second suggestion is definitely something that'd be much appreciated by
me. Both the psycopg2 and MySQLdb modules implement something similar.
Here's a quick summary of what they do:

With MySQLdb you can define a dict, keyed on the modules database types
(much like cx_Oracle.NUMBER) with the values being callables which return
the desired objects type. The callable is passed a string representation of
the data returned, and it returns a python object of the desired type. The
conversion dict is passed as an argument to MySQLdb connect method. See:
http://sourceforge.net/docman/display_doc.php?docid=32071&group_id=22307#functions-and-attributesfor
more details (search for conv)

psycopg2 defines a bunch of methods in its extensions module which do pretty
much the same thing. You define a casting method by tieing a function to a
Postgresql type via the types OID using the method
psycopg2.extensions.new_type and then psycopyg2.extensions.regster_type. As
with MySQLdb a string representing the returned data is passed to the
conversion function, and the conversion function returns a python object.
More info at
http://initd.org/tracker/psycopg/browser/psycopg2/trunk/doc/extensions.rstand
search for "Type casting of SQL types into Python values".

Which really is pretty much the same as your settype(cx_Oracle.DATETIME,
<YourTypeOrMethod>). I guess the one thing they do in common is pass a
string to the conversion method, although it'd probably be as simple to pass
cx_Oracles native value for the given type, i.e. a datetime (or
cx_Oracle.Date) for cx_Oracle.DATETIME and so on. After all if I'm defining
the conversion methods then I should know what I'd be getting.

Thanks again in advance. Sorry for going on so long there.

Andy


On 11/9/06, Anthony Tuininga <ant...@gm...> wrote:
>
> There is no way of hooking into the returning of date objects. You'll
> have to hook the entire result set. You can do that quite easily by
> subclassing cx_Oracle.Cursor and overriding fetchone(), fetchmany()
> and fetchall() to transform any datetime.datetime entities into Zopes
> DateTime objects. Clearly there is a performance penalty in doing
> this. If you can suggest an alternative that would eliminate the
> performance penalty I'd be happy to entertain it. Perhaps something
> along the lines of settype(cx_Oracle.DATETIME, <YourTypeOrMethod>)
> which would then call <YourTypeOrMethod> with a particular set of
> parameters? Any thoughts on this?

Re: [cx-oracle-users] Results as dict

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

On 11/8/06, Christian Klinger <ckl...@no...> wrote:
> Anthony Tuininga schrieb:
> > On 11/8/06, Christian Klinger <ckl...@no...> wrote:
> >> Amaury Forgeot d'Arc schrieb:
> >>> Christian Klinger wrote:
> >>>> Hello cx_oracle users,
> >>>>
> >>>> who knows a simple way to get results from a fetchall as dict=B4s in=
stead
> >>>> of lists??
> >>> You may use the cursor.description to get the column names. Something=
 like:
> >>>
> >>> data =3D cur.fetchall()
> >>> desc =3D cur.description
> >>> dict_data =3D [dict((col[0], value) for col, value in zip(desc, row))
> >>> for row in data]
> >>>
> >>> Hope this helps,
> >>>
> >> Helps perfect,
> >>
> >> btw other database-modules have out of the box solutions for this
> >> (mysql,informix)
> >
> > How do they go about this? Is that all they do or is there some switch
> > or some different method call that they make?
> >
>
> They define a additional CursorClass for this > db1 =3D
> MySQLdb.connect(user =3D "username", passwd=3D"password", host=3D "host",=
 db
>  > =3D "database", cursorclass=3DMySQLdb.cursors.DictCursor)

Ah, I see. You can do the same thing with cx_Oracle (create a
subclassed cursor that returns rows as dictionaries) quite easily. Its
simply not "out of the box" as you say. There is an implicit
performance penalty involved (dictionaries cost a great deal more to
build than tuples do relatively speaking) so I've tended to avoid such
techniques. My personal preference for when performance is not an
issue is to return objects with attributes defined for each of the
columns. Both of these techniques are possible to use without any help
from any module author and should be available globally as a recipe or
some such. Would there be appreciation for including such things as
part of documentation or sample files?

> http://dustman.net/andy/python/MySQLdb_obsolete/doc/MySQLdb-4.html#usage
>
>
>
> >> thx a lot
> >>
> >> christian
> >>
> >>
> >> ----------------------------------------------------------------------=
---
> >> Using Tomcat but need to do more? Need to support web services, securi=
ty?
> >> 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 Gero=
nimo
> >> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&da=
t=3D121642
> >> _______________________________________________
> >> 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, securit=
y?
> > 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 Geron=
imo
> > http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=
=3D121642
>
>
> -------------------------------------------------------------------------
> 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
>

Re: [cx-oracle-users] Results as dict

[Christian K. <ckl...@no...>]

Anthony Tuininga schrieb:
> On 11/8/06, Christian Klinger <ckl...@no...> wrote:
>> Amaury Forgeot d'Arc schrieb:
>>> Christian Klinger wrote:
>>>> Hello cx_oracle users,
>>>>
>>>> who knows a simple way to get results from a fetchall as dict´s instead
>>>> of lists??
>>> You may use the cursor.description to get the column names. Something like:
>>>
>>> data = cur.fetchall()
>>> desc = cur.description
>>> dict_data = [dict((col[0], value) for col, value in zip(desc, row))
>>> for row in data]
>>>
>>> Hope this helps,
>>>
>> Helps perfect,
>>
>> btw other database-modules have out of the box solutions for this
>> (mysql,informix)
> 
> How do they go about this? Is that all they do or is there some switch
> or some different method call that they make?
> 

They define a additional CursorClass for this > db1 = 
MySQLdb.connect(user = "username", passwd="password", host= "host", db
 > = "database", cursorclass=MySQLdb.cursors.DictCursor)

http://dustman.net/andy/python/MySQLdb_obsolete/doc/MySQLdb-4.html#usage



>> thx a lot
>>
>> christian
>>
>>
>> -------------------------------------------------------------------------
>> 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=lnk&kid=120709&bid=263057&dat=121642
>> _______________________________________________
>> 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=lnk&kid=120709&bid=263057&dat=121642

Re: [cx-oracle-users] Results as dict

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

On 11/8/06, Christian Klinger <ckl...@no...> wrote:
> Amaury Forgeot d'Arc schrieb:
> > Christian Klinger wrote:
> >> Hello cx_oracle users,
> >>
> >> who knows a simple way to get results from a fetchall as dict=B4s inst=
ead
> >> of lists??
> >
> > You may use the cursor.description to get the column names. Something l=
ike:
> >
> > data =3D cur.fetchall()
> > desc =3D cur.description
> > dict_data =3D [dict((col[0], value) for col, value in zip(desc, row))
> > for row in data]
> >
> > Hope this helps,
> >
> Helps perfect,
>
> btw other database-modules have out of the box solutions for this
> (mysql,informix)

How do they go about this? Is that all they do or is there some switch
or some different method call that they make?

> thx a lot
>
> christian
>
>
> -------------------------------------------------------------------------
> 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
>

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

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

There is no way of hooking into the returning of date objects. You'll
have to hook the entire result set. You can do that quite easily by
subclassing cx_Oracle.Cursor and overriding fetchone(), fetchmany()
and fetchall() to transform any datetime.datetime entities into Zopes
DateTime objects. Clearly there is a performance penalty in doing
this. If you can suggest an alternative that would eliminate the
performance penalty I'd be happy to entertain it. Perhaps something
along the lines of settype(cx_Oracle.DATETIME, <YourTypeOrMethod>)
which would then call <YourTypeOrMethod> with a particular set of
parameters? Any thoughts on this?

On 11/7/06, Andy Hird <and...@gm...> wrote:
> Hi all.
>
> I've just written a Zope Database Adaptor based on cx_Oracle, unfortunately
> the DCOracle adaptor or DBAPI doesn't appear to be maintained any longer.
>
> The last part of the code I need to do is have the data returned by the zope
> adaptor return Zopes DataTime objects instaed of datetime.datetime objects.
>
> Ideally I'd be able to get cx_Oracle to create objects with Zopes type
> DateTime.DateTime when it's building the fetch list of data. Is there anyway
> of doing this? Can I replace cx_Oracle.Date safely and have it use that to
> build the returned object?
>
> Is there another way of doing type conversions?
>
> Thanks for any pointers or help you can give.
>
> Andy Hird
>
> -------------------------------------------------------------------------
> 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=lnk&kid=120709&bid=263057&dat=121642
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>

Re: [cx-oracle-users] Results as dict

[Christian K. <ckl...@no...>]

Amaury Forgeot d'Arc schrieb:
> Christian Klinger wrote:
>> Hello cx_oracle users,
>>
>> who knows a simple way to get results from a fetchall as dict´s instead
>> of lists??
> 
> You may use the cursor.description to get the column names. Something like:
> 
> data = cur.fetchall()
> desc = cur.description
> dict_data = [dict((col[0], value) for col, value in zip(desc, row))
> for row in data]
> 
> Hope this helps,
> 
Helps perfect,

btw other database-modules have out of the box solutions for this 
(mysql,informix)

thx a lot

christian

Re: [cx-oracle-users] Results as dict

[Amaury F. d'A. <ama...@gm...>]

Christian Klinger wrote:
> Hello cx_oracle users,
>
> who knows a simple way to get results from a fetchall as dict=B4s instead
> of lists??

You may use the cursor.description to get the column names. Something like:

data =3D cur.fetchall()
desc =3D cur.description
dict_data =3D [dict((col[0], value) for col, value in zip(desc, row))
for row in data]

Hope this helps,

--=20
Amaury

[cx-oracle-users] Results as dict

[Christian K. <ckl...@no...>]

Hello cx_oracle users,

who knows a simple way to get results from a fetchall as dict´s instead 
of lists??

Thx in advance Christian

[cx-oracle-users] Modifying fetched data python types for zope adaptor

[Andy H. <and...@gm...>]

Hi all.

I've just written a Zope Database Adaptor based on cx_Oracle, unfortunately
the DCOracle adaptor or DBAPI doesn't appear to be maintained any longer.

The last part of the code I need to do is have the data returned by the zope
adaptor return Zopes DataTime objects instaed of datetime.datetime objects.

Ideally I'd be able to get cx_Oracle to create objects with Zopes type
DateTime.DateTime when it's building the fetch list of data. Is there anyway
of doing this? Can I replace cx_Oracle.Date safely and have it use that to
build the returned object?

Is there another way of doing type conversions?

Thanks for any pointers or help you can give.

Andy Hird

[cx-oracle-users] cx_Oracle cannot parse this convoluted SQL statement

[Peter M. <pma...@ya...>]

cx_Oracle worked for me just fine - thanks.

With cx_Oracle, I developed couple of simple web apps to query 
Oracle database and display results in browser. So far so good.

That is, until DB developer asked me to run this convoluted SQL statement:

--
SELECT x neuron
  FROM TABLE(SDO_RDF_MATCH(
    '(?x r:type c:neuron)',
    SDO_RDF_Models('senselab3'),
    SDO_RDF_Rulebases('RDFS','senselab3_rb'), 
    SDO_RDF_Aliases(SDO_RDF_Alias('c','http://senselab.med.yale.edu/class#'),
                   
SDO_RDF_Alias('i','http://senselab.med.yale.edu/instance#'),
                   
SDO_RDF_Alias('r','http://www.w3.org/1999/02/22-rdf-syntax-ns#')
                    ),                              
    null));

--

It is using new oracle RDF storage facility in Oracle 10g 
enterprise edition release 10.2.0.2.0
python 2.4.2 (#67) cx_Oracle 4.2 (according to history.txt)

All is fine when I do my usual SQL ping excercise, like:

--
import cx_Oracle
con = cx_Oracle.connect("user", "pass", "host/ORCLRDF") 
cur = con.cursor()
sql = "select count(*) from myschema.senselab3_rdf_data"
cur.execute(sql)
res = cur.fetchall() # get results
print res
--

results are OK: [(10220,)]

When I use above convoluted SQL (which works correctly in oracle's own
SQL scratchpad app), I got ORA-00911 error:

Traceback (most recent call last):
  File "<pyshell#15>", line 1, in -toplevel-
    cur1.execute(sql1)
DatabaseError: ORA-00911: invalid character

For me, it looks I have couple problems:

(1) cx_Oracle might not be able to handle SQL statements with 
proprietary Oracle tricks like collections - the TABLE(...) construct.
It it true? If so, i am just wasting my time.

(2) If cx_Oracle can handle even that convoluted SQL, I might be doing some
wrong quoting. 
Maybe I need to pass all string constants as run-time parameters?

(3) I know I need a way to add 'myschema' to convoluted SQL, 
something like 'use myschema' . But I doubt this is a problem:
looks like SQL was not even parsed correctly.

Any help, suggestions, insight?
What other information could be useful to localize the problem?


Peter

"Our enemies ... never stop thinking about new ways 
to harm our country and our people, and neither do we." 
— G.W.Bush, Washington, D.C., Aug. 5, 2004


 
__________________________________________________________________________________________
Check out the New Yahoo! Mail - Fire up a more powerful email and get things done faster. 
(http://advision.webevents.yahoo.com/mailbeta) 

Re: [cx-oracle-users] cx_Oracle cannot parse this convoluted SQL statement

[Amaury F. d'A. <ama...@gm...>]

Peter Masiar wrote:
> cx_Oracle worked for me just fine - thanks.
>
> With cx_Oracle, I developed couple of simple web apps to query
> Oracle database and display results in browser. So far so good.
>
> That is, until DB developer asked me to run this convoluted SQL statement:
>
> --
> SELECT x neuron
>   FROM TABLE(SDO_RDF_MATCH(
>     '(?x r:type c:neuron)',
>     SDO_RDF_Models('senselab3'),
>     SDO_RDF_Rulebases('RDFS','senselab3_rb'),
>     SDO_RDF_Aliases(SDO_RDF_Alias('c','http://senselab.med.yale.edu/class#'),
>
> SDO_RDF_Alias('i','http://senselab.med.yale.edu/instance#'),
>
> SDO_RDF_Alias('r','http://www.w3.org/1999/02/22-rdf-syntax-ns#')
>                     ),
>     null));
>
> --
>
> It is using new oracle RDF storage facility in Oracle 10g
> enterprise edition release 10.2.0.2.0
> python 2.4.2 (#67) cx_Oracle 4.2 (according to history.txt)
>
> All is fine when I do my usual SQL ping excercise, like:
>
> --
> import cx_Oracle
> con = cx_Oracle.connect("user", "pass", "host/ORCLRDF")
> cur = con.cursor()
> sql = "select count(*) from myschema.senselab3_rdf_data"
> cur.execute(sql)
> res = cur.fetchall() # get results
> print res
> --
>
> results are OK: [(10220,)]
>
> When I use above convoluted SQL (which works correctly in oracle's own
> SQL scratchpad app), I got ORA-00911 error:
>
> Traceback (most recent call last):
>   File "<pyshell#15>", line 1, in -toplevel-
>     cur1.execute(sql1)
> DatabaseError: ORA-00911: invalid character
>
> For me, it looks I have couple problems:
>
> (1) cx_Oracle might not be able to handle SQL statements with
> proprietary Oracle tricks like collections - the TABLE(...) construct.
> It it true? If so, i am just wasting my time.
>
> (2) If cx_Oracle can handle even that convoluted SQL, I might be doing some
> wrong quoting.
> Maybe I need to pass all string constants as run-time parameters?
>
> (3) I know I need a way to add 'myschema' to convoluted SQL,
> something like 'use myschema' . But I doubt this is a problem:
> looks like SQL was not even parsed correctly.
>
> Any help, suggestions, insight?
> What other information could be useful to localize the problem?
>

cx_Oracle does not parse queries. It passes the content "as is" to the
Oracle server.
So you should be able to use RDF features without problems.

The Oracle server is complaining about the syntax of your query. What
it its exact content?

A very common error is to leave the semicolon [;] at the end of the
query (did it dozens of times). The semicolon is not part of the SQL
syntax; It belongs to other languages such as PL/SQL or SqlPlus
scripts.

Hope this helps,

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] cx_Oracle cannot parse this convoluted SQL statement

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

I am not familiar with RDF storage facility but I can tell you a
number of things:

1) cx_Oracle can execute any statement that is supported by the OCI
2) SQL*Plus uses the OCI to execute its statements -- except for those
which are specific to itself (like startup/shutdown/set/define etc.)
3) Using bind variables generally avoids problems with quoting and
improves performance

So I would suggest that #1 in your list of possibilities is not a
problem and look into your #2. I've generally triple quoted my SQL
statements if they are large enough and this often eliminates quoting
problems as well.

Hope that helps.
Anthony

On 10/31/06, Peter Masiar <pma...@ya...> wrote:
> cx_Oracle worked for me just fine - thanks.
>
> With cx_Oracle, I developed couple of simple web apps to query
> Oracle database and display results in browser. So far so good.
>
> That is, until DB developer asked me to run this convoluted SQL statement=
:
>
> --
> SELECT x neuron
>   FROM TABLE(SDO_RDF_MATCH(
>     '(?x r:type c:neuron)',
>     SDO_RDF_Models('senselab3'),
>     SDO_RDF_Rulebases('RDFS','senselab3_rb'),
>     SDO_RDF_Aliases(SDO_RDF_Alias('c','http://senselab.med.yale.edu/class=
#'),
>
> SDO_RDF_Alias('i','http://senselab.med.yale.edu/instance#'),
>
> SDO_RDF_Alias('r','http://www.w3.org/1999/02/22-rdf-syntax-ns#')
>                     ),
>     null));
>
> --
>
> It is using new oracle RDF storage facility in Oracle 10g
> enterprise edition release 10.2.0.2.0
> python 2.4.2 (#67) cx_Oracle 4.2 (according to history.txt)
>
> All is fine when I do my usual SQL ping excercise, like:
>
> --
> import cx_Oracle
> con =3D cx_Oracle.connect("user", "pass", "host/ORCLRDF")
> cur =3D con.cursor()
> sql =3D "select count(*) from myschema.senselab3_rdf_data"
> cur.execute(sql)
> res =3D cur.fetchall() # get results
> print res
> --
>
> results are OK: [(10220,)]
>
> When I use above convoluted SQL (which works correctly in oracle's own
> SQL scratchpad app), I got ORA-00911 error:
>
> Traceback (most recent call last):
>   File "<pyshell#15>", line 1, in -toplevel-
>     cur1.execute(sql1)
> DatabaseError: ORA-00911: invalid character
>
> For me, it looks I have couple problems:
>
> (1) cx_Oracle might not be able to handle SQL statements with
> proprietary Oracle tricks like collections - the TABLE(...) construct.
> It it true? If so, i am just wasting my time.
>
> (2) If cx_Oracle can handle even that convoluted SQL, I might be doing so=
me
> wrong quoting.
> Maybe I need to pass all string constants as run-time parameters?
>
> (3) I know I need a way to add 'myschema' to convoluted SQL,
> something like 'use myschema' . But I doubt this is a problem:
> looks like SQL was not even parsed correctly.
>
> Any help, suggestions, insight?
> What other information could be useful to localize the problem?
>
>
> Peter
>
> "Our enemies ... never stop thinking about new ways
> to harm our country and our people, and neither do we."
> =97 G.W.Bush, Washington, D.C., Aug. 5, 2004
>
>
>
> _________________________________________________________________________=
___________
> Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call =
rates
> (http://voice.yahoo.com)
>
>
> -------------------------------------------------------------------------
> 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] cx_Oracle cannot parse this convoluted SQL statement

[Peter M. <pma...@ya...>]

cx_Oracle worked for me just fine - thanks.

With cx_Oracle, I developed couple of simple web apps to query 
Oracle database and display results in browser. So far so good.

That is, until DB developer asked me to run this convoluted SQL statement:

--
SELECT x neuron
  FROM TABLE(SDO_RDF_MATCH(
    '(?x r:type c:neuron)',
    SDO_RDF_Models('senselab3'),
    SDO_RDF_Rulebases('RDFS','senselab3_rb'), 
    SDO_RDF_Aliases(SDO_RDF_Alias('c','http://senselab.med.yale.edu/class#'),
                   
SDO_RDF_Alias('i','http://senselab.med.yale.edu/instance#'),
                   
SDO_RDF_Alias('r','http://www.w3.org/1999/02/22-rdf-syntax-ns#')
                    ),                              
    null));

--

It is using new oracle RDF storage facility in Oracle 10g 
enterprise edition release 10.2.0.2.0
python 2.4.2 (#67) cx_Oracle 4.2 (according to history.txt)

All is fine when I do my usual SQL ping excercise, like:

--
import cx_Oracle
con = cx_Oracle.connect("user", "pass", "host/ORCLRDF") 
cur = con.cursor()
sql = "select count(*) from myschema.senselab3_rdf_data"
cur.execute(sql)
res = cur.fetchall() # get results
print res
--

results are OK: [(10220,)]

When I use above convoluted SQL (which works correctly in oracle's own
SQL scratchpad app), I got ORA-00911 error:

Traceback (most recent call last):
  File "<pyshell#15>", line 1, in -toplevel-
    cur1.execute(sql1)
DatabaseError: ORA-00911: invalid character

For me, it looks I have couple problems:

(1) cx_Oracle might not be able to handle SQL statements with 
proprietary Oracle tricks like collections - the TABLE(...) construct.
It it true? If so, i am just wasting my time.

(2) If cx_Oracle can handle even that convoluted SQL, I might be doing some
wrong quoting. 
Maybe I need to pass all string constants as run-time parameters?

(3) I know I need a way to add 'myschema' to convoluted SQL, 
something like 'use myschema' . But I doubt this is a problem:
looks like SQL was not even parsed correctly.

Any help, suggestions, insight?
What other information could be useful to localize the problem?


Peter

"Our enemies ... never stop thinking about new ways 
to harm our country and our people, and neither do we." 
— G.W.Bush, Washington, D.C., Aug. 5, 2004


 
____________________________________________________________________________________
Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call rates 
(http://voice.yahoo.com)

Re: [cx-oracle-users] RuntimeError: Unable to acquire Oracle environment handle

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

This is a common issue. The cgi script does __not__ have the same
environment as your console/x-term does. Setting the environment
variable ORACLE_HOME is thus necessary one way or the other before
starting to use the Oracle client. There are a number of other
messages to this list that indicate what you did is (one of) the
correct solution(s).

On 10/16/06, M. A. Wani <maw...@gm...> wrote:
> Hi All,
>
>  i  am executing python script as cgi, path of this script is in html page
>  but got error
>  RuntimeError: Unable to acquire Oracle environment handle
>
>   (i solve this error , but not sure is this proper way)
>
>  funny thing is
>  i used sqlplus  "user/password@connection"
>   it worked fine
>  got sql> prompt
>
>
>  then  i wrote a script file test.py
>
>  import cx_Oracle
>  db = cx_Oracle.connect("user/password@connection")
>  cur = db.cursor()
>  cur.execute("select * from emp")
>  r = cur.fetchall()
>  for i in r:
>   print i
>
>
>  it also worked fine,  i thought my environment is proper.
>
>
>  then i wrote cgi-script
>
>  #!/usr/bin/python
>  print 'content-type: text/html','\n';
>  import cgi
>  import sys
>  import cx_Oracle
>  import os
>  sys.stderr = sys.stdout
>  db = cx_Oracle.connect('mukhtar/mukhtar@navi')
>
>  but got error on browser (RuntimeError: Unable to acquire Oracle
> environment handle )
>
>  then explicitly I set  ORACLE_HOME in script itself, it worked
>
>  #!/usr/bin/python
>  print 'content-type: text/html','\n';
>
>  import cgi
>  import sys
>  import cx_Oracle
>  import os
>  sys.stderr = sys.stdout
>  os.putenv('ORACLE_HOME','/u01/app/oracle/product/9.2.0')
> # i just add this line,
>  db = cx_Oracle.connect('mukhtar/mukhtar@navi')
>
>
>  but got confused it ORACLE_HOME is set and working with normal script, why
> i need to put this line in cgi-script
>
>  Thanks in Advance,
>  Mukhtar
>
>
>
>
> -------------------------------------------------------------------------
> 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=lnk&kid=120709&bid=263057&dat=121642
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>

[cx-oracle-users] RuntimeError: Unable to acquire Oracle environment handle

[M. A. W. <maw...@gm...>]

Hi All,

i  am executing python script as cgi, path of this script is in html page
but got error
RuntimeError: Unable to acquire Oracle environment handle

 (i solve this error , but not sure is this proper way)

funny thing is
i used sqlplus  "user/password@connection"
 it worked fine
got sql> prompt


then  i wrote a script file test.py

import cx_Oracle
db = cx_Oracle.connect("user/password@connection")
cur = db.cursor()
cur.execute("select * from emp")
r = cur.fetchall()
for i in r:
 print i


it also worked fine,  i thought my environment is proper.


then i wrote cgi-script

#!/usr/bin/python
print 'content-type: text/html','\n';
import cgi
import sys
import cx_Oracle
import os
sys.stderr = sys.stdout
db = cx_Oracle.connect('mukhtar/mukhtar@navi')

but got error on browser (RuntimeError: Unable to acquire Oracle environment
handle )

then explicitly I set  ORACLE_HOME in script itself, it worked

#!/usr/bin/python
print 'content-type: text/html','\n';

import cgi
import sys
import cx_Oracle
import os
sys.stderr = sys.stdout
os.putenv('ORACLE_HOME','/u01/app/oracle/product/9.2.0')  # i just add this
line,
db = cx_Oracle.connect('mukhtar/mukhtar@navi')


but got confused it ORACLE_HOME is set and working with normal script, why
i need to put this line in cgi-script

Thanks in Advance,
Mukhtar

Re: [cx-oracle-users] Feedback desired on types and binding

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

On 10/12/06, Amaury Forgeot d'Arc <ama...@gm...> wrote:
> Anthony Tuininga wrote:
> > The methods cursor.setinputsizes(), cursor.var() and cursor.callfunc()
> > accept types to indicate what sort of bind variable is desired. These
> > types are generally the types defined by the DB API but I have added a
> > number of additional ones to support data types supported by Oracle
> > but not covered by the DB API. There are a number of cases where the
> > type indicated by Oracle covers multiple Python types. This is seen
> > primarily in two places (that I am aware of at the moment). The first
> > is the desire to return integers from callfunc() and the second is the
> > desire to return unicode strings directly from cx_Oracle (rather than
> > have to decode them yourself). A couple of patches have been sent to
> > me to do both of these things by adding another set of high-level
> > types. The problem with this is that it further confuses the already
> > confused type hierarchy. So far all of the types are types from the
> > Oracle perspective only and I'd like to keep it that way. So, I have
> > an alternative suggestion which I'd like some feedback on: allow
> > setinputsizes(), var() and calfunc() to accept Python types and use
> > these to tweak the type of Python object that will be returned. In
> > other words
> >
> > cursor.callfunc("SomeFunction", int, (parm1, parm2))
> >
> > or
> >
> > cursor.setinputsizes(someparm = int)
> >
> > or
> >
> > cursor.var(int)
> >
> > In all of the above cases a NUMBER bind variable would be created but
> > whenever the value of the variable is acquired the type of object
> > returned would be an integer. The same thing would occur with unicode.
> >
> > Does this seem reasonable to you? Any comments appreciated. Thank you.
>
> +1.
> Most of the time, user code knows the datatype used by the database,
> and the desired python type, but doesn't want to care about the type
> of the intermediate OCI variable, as long as no information is lost.

Thank you. From this and the other comments it would appear this
change is supported. I'll see if I can make these changes for the next
release.

> BTW, is there a corresponding python type for LOB locators? file? StringIO?
> It may be necessary because cx_Oracle.STRING seems to be limited to 32K.

The corresponding Python type for LOB locators is str -- for those you
will have to use cx_Oracle.BLOB or cx_Oracle.CLOB as appropriate. Yes,
cx_Oracle.STRING (Oracle type of string) is limited to 32K so if you
need more you have to use cx_Oracle.CLOB.

> --
> Amaury Forgeot d'Arc
>
> -------------------------------------------------------------------------
> 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=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Feedback desired on types and binding

[Amaury F. d'A. <ama...@gm...>]

Anthony Tuininga wrote:
> The methods cursor.setinputsizes(), cursor.var() and cursor.callfunc()
> accept types to indicate what sort of bind variable is desired. These
> types are generally the types defined by the DB API but I have added a
> number of additional ones to support data types supported by Oracle
> but not covered by the DB API. There are a number of cases where the
> type indicated by Oracle covers multiple Python types. This is seen
> primarily in two places (that I am aware of at the moment). The first
> is the desire to return integers from callfunc() and the second is the
> desire to return unicode strings directly from cx_Oracle (rather than
> have to decode them yourself). A couple of patches have been sent to
> me to do both of these things by adding another set of high-level
> types. The problem with this is that it further confuses the already
> confused type hierarchy. So far all of the types are types from the
> Oracle perspective only and I'd like to keep it that way. So, I have
> an alternative suggestion which I'd like some feedback on: allow
> setinputsizes(), var() and calfunc() to accept Python types and use
> these to tweak the type of Python object that will be returned. In
> other words
>
> cursor.callfunc("SomeFunction", int, (parm1, parm2))
>
> or
>
> cursor.setinputsizes(someparm = int)
>
> or
>
> cursor.var(int)
>
> In all of the above cases a NUMBER bind variable would be created but
> whenever the value of the variable is acquired the type of object
> returned would be an integer. The same thing would occur with unicode.
>
> Does this seem reasonable to you? Any comments appreciated. Thank you.

+1.
Most of the time, user code knows the datatype used by the database,
and the desired python type, but doesn't want to care about the type
of the intermediate OCI variable, as long as no information is lost.

BTW, is there a corresponding python type for LOB locators? file? StringIO?
It may be necessary because cx_Oracle.STRING seems to be limited to 32K.

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Feedback desired on types and binding

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

Henning von Bargen wrote:

>> From: "Anthony Tuininga" <ant...@gm...>
>> Subject: [cx-oracle-users] Feedback desired on types and binding
>>
>> [...]
>> cursor.callfunc("SomeFunction", int, (parm1, parm2))
>>
>> or
>>
>> cursor.setinputsizes(someparm =3D int)
>>
>> or
>>
>> cursor.var(int)
>>
>> In all of the above cases a NUMBER bind variable would be created but
>> whenever the value of the variable is acquired the type of object
>> returned would be an integer. The same thing would occur with unicode.
>>
>> Does this seem reasonable to you? Any comments appreciated. Thank you.
>>
>> Anthony Tuininga
>=20
> +1

+1 from me too.

Bye,
    Walter D=F6rwald

Re: [cx-oracle-users] Feedback desired on types and binding

[Henning v. B. <hen...@ar...>]

> From: "Anthony Tuininga" <ant...@gm...>
> Subject: [cx-oracle-users] Feedback desired on types and binding
> 
> The methods cursor.setinputsizes(), cursor.var() and cursor.callfunc()
> accept types to indicate what sort of bind variable is desired. These
> types are generally the types defined by the DB API but I have added a
> number of additional ones to support data types supported by Oracle
> but not covered by the DB API. There are a number of cases where the
> type indicated by Oracle covers multiple Python types. This is seen
> primarily in two places (that I am aware of at the moment). The first
> is the desire to return integers from callfunc() and the second is the
> desire to return unicode strings directly from cx_Oracle (rather than
> have to decode them yourself). A couple of patches have been sent to
> me to do both of these things by adding another set of high-level
> types. The problem with this is that it further confuses the already
> confused type hierarchy. So far all of the types are types from the
> Oracle perspective only and I'd like to keep it that way. So, I have
> an alternative suggestion which I'd like some feedback on: allow
> setinputsizes(), var() and calfunc() to accept Python types and use
> these to tweak the type of Python object that will be returned. In
> other words
> 
> cursor.callfunc("SomeFunction", int, (parm1, parm2))
> 
> or
> 
> cursor.setinputsizes(someparm = int)
> 
> or
> 
> cursor.var(int)
> 
> In all of the above cases a NUMBER bind variable would be created but
> whenever the value of the variable is acquired the type of object
> returned would be an integer. The same thing would occur with unicode.
> 
> Does this seem reasonable to you? Any comments appreciated. Thank you.
> 
> Anthony Tuininga

+1 

[cx-oracle-users] Feedback desired on types and binding

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

The methods cursor.setinputsizes(), cursor.var() and cursor.callfunc()
accept types to indicate what sort of bind variable is desired. These
types are generally the types defined by the DB API but I have added a
number of additional ones to support data types supported by Oracle
but not covered by the DB API. There are a number of cases where the
type indicated by Oracle covers multiple Python types. This is seen
primarily in two places (that I am aware of at the moment). The first
is the desire to return integers from callfunc() and the second is the
desire to return unicode strings directly from cx_Oracle (rather than
have to decode them yourself). A couple of patches have been sent to
me to do both of these things by adding another set of high-level
types. The problem with this is that it further confuses the already
confused type hierarchy. So far all of the types are types from the
Oracle perspective only and I'd like to keep it that way. So, I have
an alternative suggestion which I'd like some feedback on: allow
setinputsizes(), var() and calfunc() to accept Python types and use
these to tweak the type of Python object that will be returned. In
other words

cursor.callfunc("SomeFunction", int, (parm1, parm2))

or

cursor.setinputsizes(someparm = int)

or

cursor.var(int)

In all of the above cases a NUMBER bind variable would be created but
whenever the value of the variable is acquired the type of object
returned would be an integer. The same thing would occur with unicode.

Does this seem reasonable to you? Any comments appreciated. Thank you.

Anthony Tuininga

Re: [cx-oracle-users] Problem with Python 2.5 and named bind values?

[David R. <dri...@ri...>]

Thanks for the data point.

Yes, Python 2.5 final.

Unfortunately, it's HP/UX 11.23 on Itanium.  Oracle is 10.1.x on both
client and server.

Glad it's probably only a problem on this weird platform.  I have two
good workarounds (Python 2.4.3, or numbered bind values), so I'll wait
for a really rainy day before diving in and trying to fix it.

Thanks.

On 2006.10.11 07:41:17 -0600, Anthony Tuininga wrote:
> I tried it in my own environment and it worked for me -- on a Fedora
> Core 5 Linux box. Perhaps you could indicate a few more pieces of your
> environment? Specifically the Oracle client version, Oracle server
> version, operating system version. I'm assuming this is also with
> Python 2.5 final, right?
> 
> On 10/10/06, David Ripton <dri...@ri...> wrote:
> > Here's a small script to test named bind values.
> >
> > # CODE   testbind.py
> > """Run me like this: python testbind.py user passwd dsn"
> > import sys
> > import cx_Oracle
> > user, passwd, dsn = sys.argv[1:4]
> > conn = cx_Oracle.Connection(user, passwd, dsn)
> > cursor = conn.cursor()
> > sql = "select owner from all_tables where table_name = :table_name"
> > kwparams = {"table_name": "DUAL"}
> > columns = cursor.execute(sql, kwparams)
> > row = cursor.fetchone()
> > print row[0]
> > # END CODE
> >
> > With cx_Oracle 4.2 and Python 2.4.3, the test works fine.
> >
> > With cx_Oracle 4.2 and Python 2.5, I get an exception
> > "cx_Oracle.DatabaseError: ORA-01008: not all variables bound"
> > on the cursor.execute line.
> >
> > Can others reproduce this, or is the problem a quirk of my local
> > environment?
> >
> > Thanks.

-- 
David Ripton    dri...@ri...

Re: [cx-oracle-users] Problem with Python 2.5 and named bind values?

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

I tried it in my own environment and it worked for me -- on a Fedora
Core 5 Linux box. Perhaps you could indicate a few more pieces of your
environment? Specifically the Oracle client version, Oracle server
version, operating system version. I'm assuming this is also with
Python 2.5 final, right?

On 10/10/06, David Ripton <dri...@ri...> wrote:
> Here's a small script to test named bind values.
>
> # CODE   testbind.py
> """Run me like this: python testbind.py user passwd dsn"
> import sys
> import cx_Oracle
> user, passwd, dsn = sys.argv[1:4]
> conn = cx_Oracle.Connection(user, passwd, dsn)
> cursor = conn.cursor()
> sql = "select owner from all_tables where table_name = :table_name"
> kwparams = {"table_name": "DUAL"}
> columns = cursor.execute(sql, kwparams)
> row = cursor.fetchone()
> print row[0]
> # END CODE
>
> With cx_Oracle 4.2 and Python 2.4.3, the test works fine.
>
> With cx_Oracle 4.2 and Python 2.5, I get an exception
> "cx_Oracle.DatabaseError: ORA-01008: not all variables bound"
> on the cursor.execute line.
>
> Can others reproduce this, or is the problem a quirk of my local
> environment?
>
> Thanks.
>
> --
> David Ripton    dri...@ri...
>
> -------------------------------------------------------------------------
> 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=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Problem with Python 2.5 and named bind values?

[David R. <dri...@ri...>]

Here's a small script to test named bind values.

# CODE   testbind.py
"""Run me like this: python testbind.py user passwd dsn"
import sys
import cx_Oracle
user, passwd, dsn = sys.argv[1:4]
conn = cx_Oracle.Connection(user, passwd, dsn)
cursor = conn.cursor()
sql = "select owner from all_tables where table_name = :table_name"
kwparams = {"table_name": "DUAL"}
columns = cursor.execute(sql, kwparams)
row = cursor.fetchone()
print row[0]
# END CODE

With cx_Oracle 4.2 and Python 2.4.3, the test works fine.

With cx_Oracle 4.2 and Python 2.5, I get an exception
"cx_Oracle.DatabaseError: ORA-01008: not all variables bound"
on the cursor.execute line.

Can others reproduce this, or is the problem a quirk of my local
environment?

Thanks.

-- 
David Ripton    dri...@ri...