[Zope] ZOracleDA and long numbers

[Jim Fulton]

TFE MMS JARVIS JOHN wrote:
> 
> Hi all!
> 
> I'm having a problem with running queries containing long numbers.
> I found in db.py that the data type NUMBER is converted to an
> integer which overflows in my case.
> 
> The errors:
> 
> Error, oci.error: (1455, 'ORA-01455: converting column overflows integer
> datatype\012')
> 
> Traceback (innermost last):
>   File /usr/lib/python1.5/ZPublisher/Publish.py, line 877, in publish_module
>   File /usr/lib/python1.5/ZPublisher/Publish.py, line 590, in publish
>     (Info: /queries/wisdom/customer-Line/manage_test)
>   File /usr/lib/python1.5/Shared/DC/ZRDB/DA.py, line 305, in manage_test
>     (Object: CopySource)
>   File /usr/lib/python1.5/Shared/DC/ZRDB/DA.py, line 286, in manage_test
>     (Object: CopySource)
>   File /usr/lib/python1.5/Shared/DC/ZRDB/DA.py, line 390, in __call__
>     (Object: CopySource)
>   File db.py, line 198, in query
>   File /usr/lib/python1.5/Products/ZOracleDA/DCOracle/ociCurs.py, line 317,
> in fetchmany
>   File /usr/lib/python1.5/Products/ZOracleDA/DCOracle/ociCurs.py, line 279,
> in fetchone
>   File /usr/lib/python1.5/Products/ZOracleDA/DCOracle/ociCurs.py, line 75,
> in _error
> oci.error: (see above)
> 
> I tried changing line 219 in query() in db.py:
> from
>                 if scale==0: type='i'
> to
>                 if scale==0: type='f'
> 
> in order to force a float type, but this has no effect, it seems.

This typr only affects meta-data seen by some Zope tools.

> I'm sure that I'm misunderstanding something here.

Nope, you aren't.

> Any pointers to where I can look to fix this would be greatly
> appreciated.

There is a similar change that needs to be made to *work around*
this problem.  In DCOracle/ociCurs.py, change:

            elif dbtype==2: # Number
                if scale:
                    buf=Buffer(arraysize,'d')
                    dbtype=4
                    dbsize=8
                else:
                    buf=Buffer(arraysize,'i')
                    dbtype=3
                    dbsize=4
                f=None

to:

            elif dbtype==2: # Number
                if scale or 1:
                    buf=Buffer(arraysize,'d')
                    dbtype=4
                    dbsize=8
                else:
                    buf=Buffer(arraysize,'i')
                    dbtype=3
                    dbsize=4
                f=None

This has the undesirable effect of causing all
numbers to be treated as ints. (This is what oracledb
does) :(  Unfortunately, you can't tell from Oracle column
meta data whether values in a column will fit into Python ints.

The right way to fix this is to implement Oracle's
NUMBER or VARNUM C data types and convert to Python
ints or longs on a case by case basis.

Jim

--
Jim Fulton           mailto:jim@digicool.com   Python Powered!        
Technical Director   (888) 344-4332            http://www.python.org  
Digital Creations    http://www.digicool.com   http://www.zope.org    

Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email
address may not be added to any commercial mail list with out my
permission.  Violation of my privacy with advertising or SPAM will
result in a suit for a MINIMUM of $500 damages/incident, $1500 for
repeats.