[DB-SIG] DCOracle number handling still broken?

alexander smishlajev alex@ank-sia.com
Wed, 17 May 2000 12:19:34 +0200


Christopher Petrilli wrote:
> 
> Guenter Radestock [guenter@ubka.uni-karlsruhe.de] wrote:
> >
> > I do use the description "number" in my schema and I only store integer
> > values in the database itself.  Cursor.description after a select
> > returns the tuple (('AVG(AUSGELIEFERT-ERZEUGT)', 'NUMBER', 40, 22, 0, 0,
> > 1),)     The problem must be with avg() here.  I find the following:
> 
> So long as we're clear, NUMBER by itself creates a floating-point with
> a precision of 38. Scale is not relevent.  These should be handled
> correctly, though I don't know if they've been tested recently.

they aren't.  i have encountered this error yesterday.  columns
created as NUMBER without (p,s) are converted to ints as well as
NUMBER(p,0).

the following patch may be applied to obtain best-fit result for
numbers: long if possible (i.e. no decimal digits, and no more
than 18 digits) or float otherwise:

=== cut ===
diff -ruN DCOracle/ociCurs.py DCOracle.my/ociCurs.py
--- DCOracle/ociCurs.py	Fri Aug 27 20:11:31 1999
+++ DCOracle.my/ociCurs.py	Wed May 17 11:50:12 2000
@@ -47,7 +47,7 @@
 """DBI-Compliant Oracle Database Interface
 """
 
-jimwashere=0
+jimwashere=1
 
 import oci_, dbi, ociProc
 
@@ -58,7 +58,7 @@
 
 from Buffer import Buffer
 import ociBind
-from ociUtil import RowID, oDate, error
+from ociUtil import varnum, RowID, oDate, error
 from string import join
 try:
     import Missing
@@ -133,10 +133,10 @@
                 continue
             elif dbtype==OCI_INTERNAL_NUMBER:
                 if jimwashere:
-                    buf=Buffer(1,21)
+                    buf=Buffer(arraysize,21)
                     dbtype=OCI_EXTERNAL_VARNUM
                     dbsize=21
-                    f=None
+                    f=varnum
                 else:
                     if scale:
                         buf=Buffer(arraysize,'d')
diff -ruN DCOracle/ociUtil.py DCOracle.my/ociUtil.py
--- DCOracle/ociUtil.py	Fri Aug 27 16:12:05 1999
+++ DCOracle.my/ociUtil.py	Tue May 16 20:48:30 2000
@@ -51,8 +51,25 @@
 from Buffer import Buffer, BufferType
 from time import mktime
 import time
+from array import array
 
 error="oci.error"
+
+def varnum(buf):
+    """convert OCI_EXTERNAL_VARNUM to number (long or float)"""
+    a =array('b', buf).tolist()
+    _mlen =a[0] -1 # mantissa length
+    _exp =a[1]     # exponent & sign
+    if _exp <0: s ='+0'; _neg =0
+    else:       s ='-0'; _neg =1
+    _exp =_exp & 0177 -65 # exponent
+    for _byte in a[2:_mlen+2]:
+        if _exp ==-1: s =s +'.'
+        if _neg: s =s +'%02i' %(101 -_byte)
+        else: s =s +'%02i' %(_byte -1)
+        _exp =_exp -1
+    if ('.' not in s) and (len(s) <20): return long(s)
+    else: return float(s)
 
 def oDate(s, ts=time.localtime(time.time())[6:]):
     'Convert Oracle date data to a dbiDate.'
diff -ruN DCOracle/ocidb.py DCOracle.my/ocidb.py
--- DCOracle/ocidb.py	Fri Aug 27 20:12:10 1999
+++ DCOracle.my/ocidb.py	Tue Oct 05 16:59:42 1999
@@ -136,6 +136,7 @@
             c=self.cursor() # Allocate a cursor for procs
             d[name]=p=c.procedures
             return p
+        if name =="_d": return d["_d"]
         if d.has_key('_c'): c=d['_c']
         else: d['_c']=c=self.cursor()
         if name=='_c': return c
=== cut ===

best wishes,
alex.