[Zope] ZOracleDA and long numbers

TFE MMS JARVIS JOHN jarvis.sd082@ex.tel.co.jp
Wed, 24 Mar 1999 19:40:44 +0900


 Thanks for the pointer!

 I have to retract what I said in a previous post about not having a
 problem with the ZODBCDA--it has the same problem. I had made a small
 hack of a product for my own study that connected through ODBC and
 that didn't have the problem because I used Python longs. I somehow
 got the two confused...

 This works, but everything gets converted to floats like you said. In
 my case, this is not so much a problem because I use a lot of large
 numbers but doesn't seem practical for general use.
 I found that if, in Buffer.c, I change lines 143 and 144 to

      case 'd':
         return PyLong_FromDouble(self->val.d[ii]);

 I can get the same results as my product--everything is a
 long. Unfortunately, this causes everything to be printed with
 Python's 'L' appended instead of '.0'. Either can be fixed by
 using fmt="%.f" to all of my number fields, but that seems like a 
 lot of extra work (maybe a quick hack to the Z Search Interface
 would do the trick?)

 Plus, what if I really have floats somewhere? Would something like:

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

 in ociCurs.py (lines 123-131) handle this? I haven't tested it yet on
 float fields. It might be better to just leave everything as floats.

 John Jarvis
 Tokyo Electron FE, Ltd.
 

> -----Original Message-----
> From:	Jim Fulton [SMTP:jim@digicool.com]
> Sent:	Wednesday, March 24, 1999 12:00 AM
> To:	TFE MMS JARVIS JOHN
> Cc:	zope@zope.org
> Subject:	Re: [Zope] ZOracleDA and long numbers
> 
> 
> 
> 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.