cx-oracle-users

[cx-oracle-users] Re: cx_Oracle Python utility

[Anthony T. <an...@co...>]

I hope you don't mind me sending this to the mailing list for cx_Oracle
for their enlightenment as well.

The method you are using is not supported. It could be, but there is a
simpler method that works. :-) I just added another test to the suite
which demonstrates how this is done (which will be released with the
next version).

The snippet assumes that you have the table TestStrings from the
cx_Oracle test suite created and populated along with the following
package:

create or replace package cx_Oracle.pkg_TestOutCursors as
                                                                                
  type udt_RefCursor is ref cursor;
                                                                                
  procedure TestOutCursor (
    a_MaxIntValue               number,
    a_Cursor                    out udt_RefCursor
  );
                                                                                
end;
/
                                                                                
create or replace package body cx_Oracle.pkg_TestOutCursors as
                                                                                
  procedure TestOutCursor (
    a_MaxIntValue               number,
    a_Cursor                    out udt_RefCursor
  ) is
  begin
    open a_Cursor for
      select
        IntCol,
        StringCol
      from TestStrings
      where IntCol <= a_MaxIntValue
      order by IntCol;
  end;
                                                                                
end;
/

Then the following snippet of Python code does the trick:

import cx_Oracle
                                                                                
connection = cx_Oracle.connect("cx_Oracle/pw@dsn")
cursor = connection.cursor()
refcursor = connection.cursor()
cursor.callproc("pkg_TestOutCursors.TestOutCursor", (2, refcursor))
print "Desc:", refcursor.description
print "Results:", refcursor.fetchall()

In essence, instead of calling

refcursorvar = cursor.var(cx_Oracle.CURSOR)
refcursor = refcursorvar.getvalue()

simply use

refcursor = connection.cursor()

and bind the cursor directly.

Make sense?

On Mon, 2003-08-25 at 10:28, Schedler, Kurt wrote:
> Hi Anthony,
> 
> I'm working on a Python module which uses cx_Oracle for connecting to
> our Oracle 9i database.  I'm working on calling stored procedures
> which return cursors via an OUT parameter.  I'm having problems
> creating refcursor variables with cx_Oracle:
> 
> >>> import cx_Oracle as ora
> >>> ora.version
> '3.1'
> >>> con = ora.connect("###/######@####")
> >>> cur = con.cursor()
> >>> refcur = cur.var(ora.CURSOR)
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> NotSupportedError: Variable_TypeByPythonType(): unhandled data type
> >>> 
> 
> Is this a bug or am I doing something wrong?
> 
> Thanks for your help!
> 
> Kurt.
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] float vs. int

[Anthony T. <an...@co...>]

On Sat, 2003-09-27 at 21:27, Geoff Gerrietts wrote:
> Quoting Geoff Gerrietts (ge...@ge...):
> > What are the criteria for deciding when a NUMBER will be returned as
> > a float, or when it will be returned as an int or long?
> 
> Terribly sorry; found the answer in the mailing list archives.

No problem. This isn't the first time the problem has appeared. It is
one of those things that annoys me every time I think about it. But I
haven't found a really useful solution so far.

> I think for my uses, anything with a scale 0 < sys.maxint should be an
> integer, and anything with a scale 0 > sys.maxint should be a long.
> This does not violate the principle of least surprise, and it also
> relieves me of the burden of post-processing every query result so
> CORBA doesn't barf.

That should be quite reasonable and simple to implement. I am assuming
that you mean "value" when you state "scale" above, right? Does anyone
have any objections to that implementation? Of course, it doesn't solve
the problem that most people immediately complain about, which is "Why
do I get a float back when I issue the query 'select count(*) from
table'"? Unfortunately, Oracle returns a scale of "0" and a precision of
"0" meaning no information is available as to whether an integer or a
floating point number is being returned.

> With decimals, it would be nice to work with fixed point rather than
> floats, but that would likely be a substantial change in my
> application: floats are everywhere, and in several places, I expect
> them to be floats.

True. I've considered adding an extension which would allow
specification of what is to be returned. I still haven't come up with a
reasonable name for it or whether I should overload setoutputsize() in
some way. Essentially, it would mean the following:

cursor.somefunc(1, cx_Oracle.INTEGER)
cursor.somefunc(2, cx_Oracle.FLOAT)
cursor.somefunc(3, cx_Oracle.FIXED_POINT)

where of course, the constants defined above don't exist yet and
"somefunc" needs to be named properly so that it makes sense. Any
suggestions?

> Thanks,
> --G.
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] float vs. int

[Marcos P. <msa...@gr...>]

But other interfaces do return the right thing, =BFdon't they? I mean,
odbc and SQL*Plus. (Can't check right now though)

El lun, 06-10-2003 a las 19:17, Anthony Tuininga escribi=F3:
> On Sat, 2003-09-27 at 21:27, Geoff Gerrietts wrote:
> > Quoting Geoff Gerrietts (ge...@ge...):
> > > What are the criteria for deciding when a NUMBER will be returned as
> > > a float, or when it will be returned as an int or long?
> >=20
> > Terribly sorry; found the answer in the mailing list archives.
>=20
> No problem. This isn't the first time the problem has appeared. It is
> one of those things that annoys me every time I think about it. But I
> haven't found a really useful solution so far.
>=20
> > I think for my uses, anything with a scale 0 < sys.maxint should be an
> > integer, and anything with a scale 0 > sys.maxint should be a long.
> > This does not violate the principle of least surprise, and it also
> > relieves me of the burden of post-processing every query result so
> > CORBA doesn't barf.
>=20
> That should be quite reasonable and simple to implement. I am assuming
> that you mean "value" when you state "scale" above, right? Does anyone
> have any objections to that implementation? Of course, it doesn't solve
> the problem that most people immediately complain about, which is "Why
> do I get a float back when I issue the query 'select count(*) from
> table'"? Unfortunately, Oracle returns a scale of "0" and a precision of
> "0" meaning no information is available as to whether an integer or a
> floating point number is being returned.
>=20
> > With decimals, it would be nice to work with fixed point rather than
> > floats, but that would likely be a substantial change in my
> > application: floats are everywhere, and in several places, I expect
> > them to be floats.
>=20
> True. I've considered adding an extension which would allow
> specification of what is to be returned. I still haven't come up with a
> reasonable name for it or whether I should overload setoutputsize() in
> some way. Essentially, it would mean the following:
>=20
> cursor.somefunc(1, cx_Oracle.INTEGER)
> cursor.somefunc(2, cx_Oracle.FLOAT)
> cursor.somefunc(3, cx_Oracle.FIXED_POINT)
>=20
> where of course, the constants defined above don't exist yet and
> "somefunc" needs to be named properly so that it makes sense. Any
> suggestions?
>=20
> > Thanks,
> > --G.
--=20
Marcos S=E1nchez Provencio <msa...@gr...>
www.burke.es

Re: [SPAM-bayes] Re: [cx-oracle-users] float vs. int

[Anthony T. <an...@co...>]

The only possibility that I can think of right now is that they incur
the overhead of checking if trunc(result) == result and returning an
integer as a result. That has two problems with it, though. (1) the
performance penalty and (2) the possibility of getting back an integer
when you wanted a float

On Mon, 2003-10-06 at 12:25, Marcos Sánchez Provencio wrote:
> But other interfaces do return the right thing, ¿don't they? I mean,
> odbc and SQL*Plus. (Can't check right now though)
> 
> El lun, 06-10-2003 a las 19:17, Anthony Tuininga escribió:
> > On Sat, 2003-09-27 at 21:27, Geoff Gerrietts wrote:
> > > Quoting Geoff Gerrietts (ge...@ge...):
> > > > What are the criteria for deciding when a NUMBER will be returned as
> > > > a float, or when it will be returned as an int or long?
> > > 
> > > Terribly sorry; found the answer in the mailing list archives.
> > 
> > No problem. This isn't the first time the problem has appeared. It is
> > one of those things that annoys me every time I think about it. But I
> > haven't found a really useful solution so far.
> > 
> > > I think for my uses, anything with a scale 0 < sys.maxint should be an
> > > integer, and anything with a scale 0 > sys.maxint should be a long.
> > > This does not violate the principle of least surprise, and it also
> > > relieves me of the burden of post-processing every query result so
> > > CORBA doesn't barf.
> > 
> > That should be quite reasonable and simple to implement. I am assuming
> > that you mean "value" when you state "scale" above, right? Does anyone
> > have any objections to that implementation? Of course, it doesn't solve
> > the problem that most people immediately complain about, which is "Why
> > do I get a float back when I issue the query 'select count(*) from
> > table'"? Unfortunately, Oracle returns a scale of "0" and a precision of
> > "0" meaning no information is available as to whether an integer or a
> > floating point number is being returned.
> > 
> > > With decimals, it would be nice to work with fixed point rather than
> > > floats, but that would likely be a substantial change in my
> > > application: floats are everywhere, and in several places, I expect
> > > them to be floats.
> > 
> > True. I've considered adding an extension which would allow
> > specification of what is to be returned. I still haven't come up with a
> > reasonable name for it or whether I should overload setoutputsize() in
> > some way. Essentially, it would mean the following:
> > 
> > cursor.somefunc(1, cx_Oracle.INTEGER)
> > cursor.somefunc(2, cx_Oracle.FLOAT)
> > cursor.somefunc(3, cx_Oracle.FIXED_POINT)
> > 
> > where of course, the constants defined above don't exist yet and
> > "somefunc" needs to be named properly so that it makes sense. Any
> > suggestions?
> > 
> > > Thanks,
> > > --G.
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] float vs. int

[Neil H. <nho...@eb...>]

Anthony Tuininga:

> Just thought I'd let all of you know that I have implemented the
> returning of longs when an integer with more than 9 digits of precision
> is returned. 

    Good.

> I have also implemented the ability to subclass
> connections and cursors (requiring Python 2.2 and up) which works quite
> well. 

    Even better. Currently we have wrappers for connections, cursors, 
and pools and these would be simpler as subclasses. The wrappers allow 
easy switching between DCOracle2 and cx_Oracle, which are both good 
libraries but currently cx_Oracle appears a little better maintained.

> Are there any of you out there that are just dying for these
> features? Or is it something that can wait for a month or two?

    We won't go out of business without them but they would help.

    Neil

Re: [cx-oracle-users] float vs. int

[Anthony T. <an...@co...>]

Thanks for the feedback. Currently, the plans for the release are
sometime before Christmas. I'll let you know if those plans change
substantially. I also intend to release these changes as 4.0 and
indicate that Python 2.2 and up is required. I have branched in CVS,
though, so if necessary a Python 2.1 and earlier friendly release can be
made.

On Mon, 2003-11-24 at 15:12, Neil Hodgson wrote:
> Anthony Tuininga:
> 
> > Just thought I'd let all of you know that I have implemented the
> > returning of longs when an integer with more than 9 digits of precision
> > is returned. 
> 
>     Good.
> 
> > I have also implemented the ability to subclass
> > connections and cursors (requiring Python 2.2 and up) which works quite
> > well. 
> 
>     Even better. Currently we have wrappers for connections, cursors, 
> and pools and these would be simpler as subclasses. The wrappers allow 
> easy switching between DCOracle2 and cx_Oracle, which are both good 
> libraries but currently cx_Oracle appears a little better maintained.
> 
> > Are there any of you out there that are just dying for these
> > features? Or is it something that can wait for a month or two?
> 
>     We won't go out of business without them but they would help.
> 
>     Neil
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: SF.net Giveback Program.
> Does SourceForge.net help you be more productive?  Does it
> help you create better code?  SHARE THE LOVE, and help us help
> YOU!  Click Here: http://sourceforge.net/donate/
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] Re: cx_Oracle Python utility

[Geoff G. <ge...@ge...>]

cx_Oracle 3.1 builds acceptably under Python 2.1.3, but fails to run.
Apparently 3.1 has used PyString_FromFormat, and that doesn't exist
prior to some version of 2.2.

I have a patch -- it's not exactly elegant, but it gets the job done
and appears to run correctly. I would submit as a bug and a proposed
patch on sourceforge (and will if you tell me to) but I wasn't sure it
wasn't actually a "feature".

Patch included below .sig.

Thanks,
--G.

-- 
Geoff Gerrietts <geoff at gerrietts dot net>      http://www.gerrietts.net/
   "If I were two-faced, would I be wearing this one?" --Abraham Lincoln


Index: cx_Oracle.c
===================================================================
RCS file: /src/3rdparty/cxoracle/cx_Oracle.c,v
retrieving revision 1.1.1.1
diff -u -r1.1.1.1 cx_Oracle.c
--- cx_Oracle.c 2003/08/22 13:37:37 1.1.1.1
+++ cx_Oracle.c 2003/08/29 23:15:27
@@ -146,9 +146,10 @@
         return NULL;
          
               // return the formatted string
               -    return
                    PyString_FromFormat("(DESCRIPTION=(ADDRESS_LIST=(ADDRESS="
                    -            "(PROTOCOL=TCP)(HOST=%s)(PORT=%d)))(CONNECT_DATA=(SID=%s)))",
                    -            host, port, sid);
                    +    return PyString_Format(PyString_FromString(
                    +
                    "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS="
                    +
                    "(PROTOCOL=TCP)(HOST=%s)(PORT=%d)))(CONNECT_DATA=(SID=%s)))"),
                    +            args);
                     }

Re: [cx-oracle-users] Re: cx_Oracle Python utility

[Anthony T. <an...@co...>]

Its not a "feature" but rather a result of the fact that I only actually
use Python 2.2 and Python 2.3 now. I have no problem with a patch of
that nature. Have you run the test suite after building with that patch?
And does it pass completely (other than the Python 2.2 specific stuff)?

I don't have any problem applying such a patch. I am a little confused,
though. You say that cx_Oracle 3.1 builds acceptably under Python 2.1.3.
I assume that means that you got a warning about PyString_FromFormat
during compilation which turned into a dynamic link error at runtime?
You can either post this on SourceForge or simply resend me the patch in
an attachment -- either will do.

On Fri, 2003-08-29 at 17:15, Geoff Gerrietts wrote:
> cx_Oracle 3.1 builds acceptably under Python 2.1.3, but fails to run.
> Apparently 3.1 has used PyString_FromFormat, and that doesn't exist
> prior to some version of 2.2.
> 
> I have a patch -- it's not exactly elegant, but it gets the job done
> and appears to run correctly. I would submit as a bug and a proposed
> patch on sourceforge (and will if you tell me to) but I wasn't sure it
> wasn't actually a "feature".
> 
> Patch included below .sig.
> 
> Thanks,
> --G.
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] Re: cx_Oracle Python utility

[Geoff G. <ge...@ge...>]

Quoting Anthony Tuininga (an...@co...):
> Its not a "feature" but rather a result of the fact that I only actually
> use Python 2.2 and Python 2.3 now. I have no problem with a patch of
> that nature. Have you run the test suite after building with that patch?
> And does it pass completely (other than the Python 2.2 specific stuff)?

Ah, would that I could be in your situation.

I have not run the test suite. When I just tried (python test.py) to
run it under the unpatched version, I got an error:

  Traceback (most recent call last):
    File "test.py", line 42, in ?
      loader = unittest.TestLoader()
  AttributeError: 'unittest' module has no attribute 'TestLoader'

But maybe I'm running the tests improperly; I haven't spent a lot of
time trying to figure this out. Regardless, it's only a one line fix
in a fairly isolated location. If it appears that this patch causes a
problem, I'd be surprised. Well, any problem other than aesthetic. As
I said, it lacks a bit of the elegance you might desire.

> I don't have any problem applying such a patch. I am a little confused,
> though. You say that cx_Oracle 3.1 builds acceptably under Python 2.1.3.
> I assume that means that you got a warning about PyString_FromFormat
> during compilation which turned into a dynamic link error at runtime?
> You can either post this on SourceForge or simply resend me the patch in
> an attachment -- either will do.

I didn't notice the first few times, because the Oracle (8.1.5)
headers generate several warnings of their own, but there it is:

  cx_Oracle.c: In function `MakeDSN':
  cx_Oracle.c:149: warning: implicit declaration of function `PyString_FromFormat'
  cx_Oracle.c:151: warning: return makes pointer from integer without a cast

I've sent the patch as an attachment this time. If you have any
questions, please let me know.

Thanks,
--G.

-- 
Geoff Gerrietts                                 <geoff at gerrietts net> 
"A man can't be too careful in the choice of his enemies." --Oscar Wilde

[cx-oracle-users] float vs. int

[Geoff G. <ge...@ge...>]

What are the criteria for deciding when a NUMBER will be returned as a
float, or when it will be returned as an int or long?

Thanks,
--G.

-- 
Geoff Gerrietts <geoff at gerrietts dot net>
-rw-rw-rw-:         permissions of the beast

Re: [cx-oracle-users] float vs. int

[Geoff G. <ge...@ge...>]

Quoting Geoff Gerrietts (ge...@ge...):
> What are the criteria for deciding when a NUMBER will be returned as
> a float, or when it will be returned as an int or long?

Terribly sorry; found the answer in the mailing list archives.

I think for my uses, anything with a scale 0 < sys.maxint should be an
integer, and anything with a scale 0 > sys.maxint should be a long.
This does not violate the principle of least surprise, and it also
relieves me of the burden of post-processing every query result so
CORBA doesn't barf.

With decimals, it would be nice to work with fixed point rather than
floats, but that would likely be a substantial change in my
application: floats are everywhere, and in several places, I expect
them to be floats.

Thanks,
--G.

-- 
Geoff Gerrietts                        "Evolution takes no prisoners."
<ge...@ge...>                                       -- Mandy

Re: [cx-oracle-users] float vs. int

[Anthony T. <an...@co...>]

Just thought I'd let all of you know that I have implemented the
returning of longs when an integer with more than 9 digits of precision
is returned. This will be available in the next release of cx_Oracle,
whenever that is. :-) I have also implemented the ability to subclass
connections and cursors (requiring Python 2.2 and up) which works quite
well. Are there any of you out there that are just dying for these
features? Or is it something that can wait for a month or two?

On Sat, 2003-09-27 at 21:27, Geoff Gerrietts wrote:
> Quoting Geoff Gerrietts (ge...@ge...):
> > What are the criteria for deciding when a NUMBER will be returned as
> > a float, or when it will be returned as an int or long?
> 
> Terribly sorry; found the answer in the mailing list archives.
> 
> I think for my uses, anything with a scale 0 < sys.maxint should be an
> integer, and anything with a scale 0 > sys.maxint should be a long.
> This does not violate the principle of least surprise, and it also
> relieves me of the burden of post-processing every query result so
> CORBA doesn't barf.
> 
> With decimals, it would be nice to work with fixed point rather than
> floats, but that would likely be a substantial change in my
> application: floats are everywhere, and in several places, I expect
> them to be floats.
> 
> Thanks,
> --G.
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] float vs. int

[Geoff G. <ge...@ge...>]

Quoting Anthony Tuininga (an...@co...):
> Just thought I'd let all of you know that I have implemented the
> returning of longs when an integer with more than 9 digits of precision
> is returned. This will be available in the next release of cx_Oracle,
> whenever that is. :-) I have also implemented the ability to subclass
> connections and cursors (requiring Python 2.2 and up) which works quite
> well. Are there any of you out there that are just dying for these
> features? Or is it something that can wait for a month or two?

Sorry about the tardy response. Two weeks ago or more, I would have
begged for immediate access. We go live in production sometime next
week, though, so I think it's too late to switch out the Oracle
package.

I probably won't be able to make real use of the new code until
sometime in January or February, so a Christmas release is just fine.

Thanks!
--G.

-- 
Geoff Gerrietts <geoff @ gerrietts.net> "Many a man's reputation would not 
                                         know his character if they met on 
http://www.gerrietts.net/                the street." --Elbert Hubbard