[DB-SIG] DCOracle number handling still broken?

Christopher Petrilli petrilli@amber.org
Thu, 6 Apr 2000 11:24:47 -0400


Guenter Radestock [guenter@ubka.uni-karlsruhe.de] wrote:
> On Thu, Apr 06, 2000 at 10:50:05AM -0400, Christopher Petrilli 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.

> create table numtest (x number, y number(4), z number(4, 2));

OK, so we have a floating point, and a 4 digit int, and 4, 2 fixed
precision.

> insert into numtest values (1, 2, 3.4);
> insert into numtest values (5, 6, 7.8);
> 
> >>> cu.execute('select * from numtest')
> >>> cu.fetchone()
> (1, 2, 3.4)
> cu.description
> (('X', 'NUMBER', 40, 22, 0, 0, 1), ('Y', 'NUMBER', 40, 22, 4, 0, 1),
> ('Z', 'NUMBER', 40, 22, 4, 2, 1))

INTERESTING, it's returning (0,0) as the (p,s) when you use the NUMBER 
by itself.  This is why we're truncating it to an integer.  Our code
does the following est:

if scale:
   turn it into a floating point
elif prec < 10:
   turn it into a integer
elif:
   turn it into a long

We're turning it into an integer, whic his 'correct' except that
Oracle is really giving us something bogus back :-)  

If you'd like, try modifying the code around line 161 of ociCurs.py,
where it says:

      if scale:

change that to be:
      
      if scale or ((not scale) and (not prec)):

That should catch it :-)

> >>> cu.execute('select avg(x), avg(y), avg(z) from numtest');
> >>> cu.fetchone()
> (3, 4, 5)
> >>> cu.description
> (('AVG(X)', 'NUMBER', 40, 22, 0, 0, 1), ('AVG(Y)', 'NUMBER', 40, 22, 0,
> 0, 1), ('AVG(Z)', 'NUMBER', 40, 22, 0, 0, 1))

This should be fixed as well :-)

Chris
-- 
| Christopher Petrilli
| petrilli@amber.org