[Sqlrelay-discussion] SQLRelay and large numbers in Oracle (other databases too?)

[<mac...@co...>]

Hi

We've found serious problem with SQLRelay.
It doesn't support large numbers (at last with 
Oracle and python).


Try this structure in OracleDB (I've used
free Oracle Express edition):

-------------------------------
CREATE TABLE  "TNUMBERS" 
   (	"ID" NUMBER, 
	"NUMBER1" NUMBER(20,2), 
	"NUMBER2" NUMBER(20,2), 
	 CONSTRAINT "TNUMBERS_PK" PRIMARY KEY ("ID") ENABLE
   )
/


insert into tnumbers (id, number1, number2) 
     values (1, 143393735125.58, 9999999999999.04)
insert into tnumbers (id, number1, number2) 
     values (2, 143393735125.58, 9999999999999.05)
insert into tnumbers (id, number1, number2) 
     values (3, 999999999999999999.04, 999999999999999999.05)
insert into tnumbers (id, number1, number2) 
     values (4, 999999999999999.04, 999999999999999.05)

-------------------------------


In Python I get the result as ('%f' used):

1.0	143393735125.579987	    9999999999999.039062
2.0	143393735125.579987	    9999999999999.050781
3.0	1000000000000000000.000000  1000000000000000000.000000
4.0     999999999999999.000000      999999999999999.000000

So it is incorrect.

There are two problems we've found. 
First is the problem with range of C datatypes used 
by SQLRelay for numbers and second is the problem 
with float data type in Python which fails with more 
than 13 digits:

>>> a = 99999999999999.04
>>> b = 99999999999999.05
>>> a
99999999999999.047
>>> b
99999999999999.047

I'm not sure what can/should be done with this. 
AFAIR largest numeric type in C is long double 
and (depending on compiler) it is 15 digits long. 
We need to have 20.2 numbers...

I think the good solution is implemented in psycopg2.
PostgreSQL 'money' types are returned as python's 
Decimal data type (availaible in python2.4 and with 
addition of decimal.py file in python2.3 too) which 
can support such a large numbers, but is different 
than float, eg.:

>>> import decimal
>>> a = decimal.Decimal('12.23')
>>> a + 1.0
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
TypeError: unsupported operand type(s) for +: 'Decimal' and 'float'


In DCOracle2 we've changed it's source to make it always 
return Decimal (never float from database). I think 
the better is to be able to customize returned data 
type somewhere in connection parameters. Any 
other ideas/solutions?


-- 
Maciej Wisniowski