sqlrelay-discussion

[Sqlrelay-discussion] Numeric types limited to 2147483647 and type of date field

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

Hi!

I'm using SQLRelay with Oracle database in python environment and Zope.

I've found that numeric data types are not returned correctly.
I have column ST_NUMBER in database of type NUMERIC(14),
with value 99999999999999. Value returned by SQLRelay getField
function is correct, but getRow and similiar functions return: 2147483647.

I realized that the problem is possibly in that line in CSQLRelay.C:

PyList_SetItem(my_list, counter, Py_BuildValue("i", 
charstring::toLong(row_data[counter])));

toLong function defined in rudiments/charstring.C calls strtol function 
which
is limited to return values between LONG_MIN and LONG_MAX
(http://home.att.net/~jackklein/c/inttypes.html). Am I correct??
If so, then is it a bug or fature? Can I submit this thing somewhere?
Is it possible that SQLRelay source will be changed to support such numbers?
or what are the availaible solutions of this problem (dirty hacks?)


Another problem for applications in my company is date format. Is it
possible to change easily something in CSQLRelay.C (?) to return
date as DateTime object instead of string?
We're trying to move to SQLRelay from DCOracle2 and this incompability
is big problem in process of switch our existing applications. I've already
created simple Zope product to execute stored oracle procedures that
returns values (IN/OUT, result set) from within Zope, but few other
problems is still stopping us...

-- 
Maciej Wisniowski

Re: [Sqlrelay-discussion] Numeric types limited to 2147483647 and type of date field

[Firstworks/4access <dav...@fi...>]

On Tue, 2005-06-07 at 15:39 +0200, Maciej Wi=C5=9Bniowski wrote:
> Hi!
>=20
> I'm using SQLRelay with Oracle database in python environment and Zope.
>=20
> I've found that numeric data types are not returned correctly.
> I have column ST_NUMBER in database of type NUMERIC(14),
> with value 99999999999999. Value returned by SQLRelay getField
> function is correct, but getRow and similiar functions return: 21474836=
47.
>=20
> I realized that the problem is possibly in that line in CSQLRelay.C:
>=20
> PyList_SetItem(my_list, counter, Py_BuildValue("i",=20
> charstring::toLong(row_data[counter])));
>=20
> toLong function defined in rudiments/charstring.C calls strtol function=
=20
> which
> is limited to return values between LONG_MIN and LONG_MAX
> (http://home.att.net/~jackklein/c/inttypes.html). Am I correct??
> If so, then is it a bug or fature? Can I submit this thing somewhere?
> Is it possible that SQLRelay source will be changed to support such num=
bers?
> or what are the availaible solutions of this problem (dirty hacks?)

I believe you're right.  It's a bug.  I'll fix it in the next release.
I believe you can change the charstring::toLong call to a
charstring::toLongLong call and that should fix the problem.  You also
need to change the "i" to an "L" in the Py_BuildValue call.

Note that long ago python didn't support long long's.  I'm not sure what
version they started supporting them in.

>=20
>=20
> Another problem for applications in my company is date format. Is it
> possible to change easily something in CSQLRelay.C (?) to return
> date as DateTime object instead of string?
> We're trying to move to SQLRelay from DCOracle2 and this incompability
> is big problem in process of switch our existing applications. I've alr=
eady
> created simple Zope product to execute stored oracle procedures that
> returns values (IN/OUT, result set) from within Zope, but few other
> problems is still stopping us...


This one is a little more challenging.  It would be easier to modify
PySQLRDB.py than CSQLRelay.C.  Manipulating python objects from C is
non-trivial.  The real challenge though, even in python, is figuring out
whether a column is a date type or not and then parsing the date string
properly for each database.  Databases have different names for their
date/time type and format the date differently.

Does Zope generally expect dates to come back as DateTime objects or as
strings?

Dave

Re: [Sqlrelay-discussion] Numeric types limited to 2147483647 and type of date field

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

> I believe you're right.  It's a bug.  I'll fix it in the next release.
> I believe you can change the charstring::toLong call to a
> charstring::toLongLong call and that should fix the problem.  You also
> need to change the "i" to an "L" in the Py_BuildValue call.

> Note that long ago python didn't support long long's.  I'm not sure what
> version they started supporting them in.

Great! Thank you :)


> Does Zope generally expect dates to come back as DateTime objects or as
> strings?
Zope/Python is not the point. It'll accept everything ;) The problem 
are our existing applications that are using DCOracle2 now. DCOracle 
returns dates as DateTime. We want to switch to SQLRelay without pain and
changing dates to strings would cause a lot of work and we simply have 
no time do do it. Date returned as string is good but for our new apps.

> This one is a little more challenging.  It would be easier to modify
> PySQLRDB.py than CSQLRelay.C.  Manipulating python objects from C is
> non-trivial.  The real challenge though, even in python, is figuring out
> whether a column is a date type or not and then parsing the date string
> properly for each database.  Databases have different names for their
> date/time type and format the date differently.
I think you are right - it is much simpler to get this to work in python.
DateTime is Zope data type so it's best to do it in Zope environment.

I've changed db.py in ZSQLRealayDA Zope adapter and I get this to 
work: dates are returned as DateTime, and even big numbers are returned 
correctly. I've changed db.py to use cursor from PySQLRClient.py instead 
of PySQLRDB.py and used getField(x, y) function that returns all values 
as strings. I've added type conversions based on columns' descriptions 
and it works correctly for me, but I'm assuming that date format (in the 
returned string is constant). For our Oracle environment it is enough, 
with other databases it may be really difficult. I think you shouldn't 
change anything in SQLRelay. But we will use this dirty hack in our
existing apps.

It all seemed to work correctly till I've received: 

"Fatal Python error: deallocating None" :-/

When I'm using select with large number of columns returned eg.:
 
select * from table 

I get this error. With one or two columns it seems to 
be OK but with three or more it causes fatal error. I don't know whether
number of columns/rows is important but the most important thing that 
causes this error is 

cur.getNullsAsNone()

that I'm using. 

Without getNullsAsNone setting it works correctly. It is really big 
problem for me. We need SQLRelay to return data types exactly in 
the same way as DCOracle2 does so we have to use None when null appears.

This is a bug that causes getNullsAsNone is useless :-/ Can you help? 
Is there a way to recognize nulls without this setting?

We are using Oracle8i client and now we're moving to Oracle9i if it is
important.

-- 
Maciej Wisniowski