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

[David M. <dav...@fi...>]

Those sound like good possible solutions.  I'll look into it a bit and
see what I can come up with.  Hopefully I'll get something in place for
0.38.

Dave
dav...@fi...

On Thu, 2006-06-22 at 13:43 +0200, Maciej Wiśniowski wrote:
> 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?
> 
>