[Sqlrelay-discussion] SQLRelay and large numbers in Oracle (other databases too?)
Brought to you by:
mused
|
From: <mac...@co...> - 2006-06-22 11:43:44
|
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
|