Re: [cx-oracle-users] Fetching NaN, -Infinity and +Infinity from BINARY_FLOAT or BINARY_DOUBLE colu
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] Fetching NaN, -Infinity and +Infinity from BINARY_FLOAT or BINARY_DOUBLE columns
|
From: D.R. B. <da...@as...> - 2007-02-05 08:44:59
|
Thank you very much Anthony!
We can wait for the next release and don't need a patch (I hope :).
Thanks again,
Danny
On Sat, Feb 03, 2007 at 12:23:16PM -0700, Anthony Tuininga wrote:
> Thank you for the simple test case. :-) I've managed to now implement
> this so that when native doubles or floats are returned from an Oracle
> 10g database they are automatically returned as a native float or
> double without any unnecessary conversion process. I have __NOT__ done
> the same for binding, however, because of the fact that you cannot use
> those types when using an Oracle 10g client against an Oracle 9i
> server. Did you want a patch for this or do you want to wait until I
> make the next release -- hopefully in the next couple of weeks.
>
> On 2/1/07, D.R. Boxhoorn <da...@as...> wrote:
> >
> > Good morning Anthony,
> >
> > Below you'll find a - hopefully - self-contained example to reproduce
> > the errors that can be caused by fetching NaN and Infinity as "binary"
> > doubles and floats.
> > I have the impression that this cannot be solved with a just few lines of
> > C code, but you are the expert here. :)
> >
> > Ciao,
> >
> > Danny
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > import struct, sys
> > import cx_Oracle
> >
> > connectstring = 'user/password@tnsname'
> >
> > # Clumsy way to obtain Infinity and NaN as float and double
> > floatinf, = struct.unpack('f', struct.pack('L', 0x7F800000))
> > floatnan, = struct.unpack('f', struct.pack('L', 0x7F800001))
> > doubleinf, = struct.unpack('d', struct.pack('Q', 0x7FF0000000000000L))
> > doublenan, = struct.unpack('d', struct.pack('Q', 0x7FF0000000000001L))
> >
> > assert str(floatinf) == 'inf', 'floatinf is not "inf", but %s' % floatinf
> > assert str(floatnan) == 'nan', 'floatnan is not "nan", but %s' % floatnan
> > assert str(doubleinf) == 'inf', 'doubleinf is not "inf", but %s' % doubleinf
> > assert str(doublenan) == 'nan', 'doublenan is not "inf", but %s' % doublenan
> >
> > # Get Infinity and NaN as constants to trigger exceptions
> > connection = cx_Oracle.connect(connectstring)
> > cursor = connection.cursor()
> >
> > for typeconversion in ['TO_BINARY_DOUBLE', 'TO_BINARY_FLOAT']:
> > for number in ['floatinf', 'floatnan', 'doubleinf', 'doublenan']:
> > print 'Trying %(typeconversion)s for %(number)s' % vars()
> > cursor.execute('SELECT %(typeconversion)s(:1) FROM DUAL' % vars(), [eval(number)])
> > try:
> > cursor.fetchone()
> > print 'Ok'
> > except cx_Oracle.DatabaseError, e:
> > print e
> >
> > cursor.close()
> > connection.close()
> >
> > # Expected output
> > # Trying TO_BINARY_DOUBLE for floatinf
> > # OCI-22065: number to text translation for the given format causes overflow
> > #
> > # Trying TO_BINARY_DOUBLE for floatnan
> > # ORA-01722: invalid number
> > #
> > # Trying TO_BINARY_DOUBLE for doubleinf
> > # OCI-22065: number to text translation for the given format causes overflow
> > #
> > # Trying TO_BINARY_DOUBLE for doublenan
> > # ORA-01722: invalid number
> > #
> > # Trying TO_BINARY_FLOAT for floatinf
> > # OCI-22065: number to text translation for the given format causes overflow
> > #
> > # Trying TO_BINARY_FLOAT for floatnan
> > # ORA-01722: invalid number
> > #
> > # Trying TO_BINARY_FLOAT for doubleinf
> > # OCI-22065: number to text translation for the given format causes overflow
> > #
> > # Trying TO_BINARY_FLOAT for doublenan
> > # ORA-01722: invalid number
> > --------------------------------------------------------------------------------
> >
> > On Wed, Jan 31, 2007 at 04:31:21PM -0700, Anthony Tuininga wrote:
> > > I'm assuming the attached script yields these errors but unfortunately
> > > I don't know which incantation is necessary (which parameters to pass)
> > > in order to get the associated error. Can you enlighten me? Thank you.
> > >
> > > On 1/12/07, D.R. Boxhoorn <da...@as...> wrote:
> > > >
> > > >When fetching a NaN number (nan in Python) from a BINARY_FLOAT or
> > > >BINARY_DOUBLE
> > > >column, the following exception is raised.
> > > >
> > > > Traceback (most recent call last):
> > > > File "<stdin>", line 1, in ?
> > > > cx_Oracle.DatabaseError: ORA-01722: invalid number
> > > >
> > > >Similary, fetching Infinity (inf in Python) from a BINARY_FLOAT or
> > > >BINARY_DOUBLE
> > > >column results in
> > > >
> > > > Traceback (most recent call last):
> > > > File "<stdin>", line 1, in ?
> > > > cx_Oracle.DatabaseError: OCI-22065: number to text translation for the
> > > > given format causes overflow
> > > >
> > > >Inserting Infinity and NaN, however, works flawlessly.
> > > >
> > > >
> > > >I've only tried this for cx_Oracle 4.2.1 & 4.1.2, Python 2.3 and Oracle
> > > >10gR2,
> > > >but it would not be surprising if other combinations show the same
> > > >behaviour.
> > > >
> > > >Does anyone have an idea for a solution or clever workaround?
> > > >
> > > >Thanks
> > > >
> > > > Danny
> > > >
> > > >
> > > >Using cx_Oracle 4.2.1
> > > >On Thu, Dec 07, 2006 at 09:30:17AM +0100, Henning von Bargen wrote:
> > > >> Hi Mark,
> > > >>
> > > >> pls try the following script:
> > > >> =============================================
> > > >>
> > > >> #!/bin/env python
> > > >> # -*- coding: iso-8859-1 -*-
> > > >> #
> > > >> import cx_Oracle
> > > >>
> > > >> # Sample 1
> > > >> def doAllCurs (curs, stmt, **args):
> > > >> rset = curs.execute (stmt, **args)
> > > >> for rec in curs:
> > > >> yield rec
> > > >> return
> > > >>
> > > >> # Sample 2
> > > >> def doAllConn (conn, stmt, **args):
> > > >> curs = conn.cursor()
> > > >> for rec in doAllCurs(curs, stmt, **args):
> > > >> yield rec
> > > >> curs.close()
> > > >> return
> > > >>
> > > >> # Print All Records
> > > >> # This could perhaps be recoded in a more functional programming style
> > > >> def printAll (curs, stmt, **args):
> > > >> for rec in doAllCurs(curs, stmt, **args):
> > > >> print rec
> > > >>
> > > >> def test (uid, query, **bindvars):
> > > >> conn = cx_Oracle.connect(uid)
> > > >> curs = conn.cursor()
> > > >> printAll (curs, query, **bindvars)
> > > >>
> > > >> print ""
> > > >> print "The first 20 records as a list:"
> > > >> print list (doAllCurs (curs, query, **bindvars))[:20]
> > > >>
> > > >> curs.close()
> > > >> conn.close()
> > > >>
> > > >> # Test me
> > > >> if __name__ == "__main__":
> > > >> import sys
> > > >> if len(sys.argv) > 1:
> > > >> uid = sys.argv[1]
> > > >> conditions = dict ([tuple(arg.split("=",1))
> > > >> for arg in sys.argv[2:]
> > > >> ])
> > > >> query = """
> > > >> select object_name
> > > >> , object_type
> > > >> from user_objects
> > > >> where 1=1"""
> > > >> for nam,val in conditions.items():
> > > >> query += ("\n and %s = :%s" % (nam,nam))
> > > >> print "Query:"
> > > >> print query
> > > >> print "*" * 70
> > > >> print "Bind Vars:"
> > > >> for nam,val in conditions.items():
> > > >> print "%-20s = %r" % (nam,val)
> > > >> print "*" * 70
> > > >> test (uid, query, **conditions);
> > > >> else:
> > > >> print "Test me like this:"
> > > >> print "%s uid/pwd@dbs object_type=TABLE" % sys.argv[0]
> > > >>
> > > >> =============================================
> > > >>
> > > >> Just save it as test.py, then call it like
> > > >> test.py userid/password@database object_type=TABLE
> > > >>
> > > >> or, with other condiditons:
> > > >> test.py userid/password@database object_type=TABLE object_name=XYZ
> > > >> (This will probably not return any results)
> > > >>
> > > >> HTH
> > > >> Henning
> > > >>
> > > >> -------------------------------------------------------------------------
> > > >> Take Surveys. Earn Cash. Influence the Future of IT
> > > >> Join SourceForge.net's Techsay panel and you'll get the chance to share
> > > >your
> > > >> opinions on IT & business topics through brief surveys - and earn cash
> > > >> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> > > >> _______________________________________________
> > > >> cx-oracle-users mailing list
> > > >> cx-...@li...
> > > >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > >
> > > >-------------------------------------------------------------------------
> > > >Take Surveys. Earn Cash. Influence the Future of IT
> > > >Join SourceForge.net's Techsay panel and you'll get the chance to share
> > > >your
> > > >opinions on IT & business topics through brief surveys - and earn cash
> > > >http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> > > >_______________________________________________
> > > >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
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
