Mailing List Archive: Zope: Users

Weird SQL Relay conversion error in Zope

Index | Next | Previous | View Threaded

mdriscoll at co

May 9, 2008, 6:31 AM

Post #1 of 3 (43 views)
Permalink

Weird SQL Relay conversion error in Zope

Hi,

I am working on a web app that interfaces with Microsoft SQL Server from Ubuntu through SQL Relay. I will be getting data from multiple databases. The issue seems to be that either Zope or SQL Relay cannot handle the Decimal type if the value is a null. If I do a query like this:

SELECT * from myDatabase

I get the following:

<error>

Site Error

An error was encountered while publishing this resource.

InvalidOperation
Sorry, a site error occurred.

Traceback (innermost last):

* Module ZPublisher.Publish, line 194, in publish_module_standard
* Module Products.PlacelessTranslationService.PatchStringIO, line 34, in new_publish
* Module ZPublisher.Publish, line 146, in publish
* Module Zope2.App.startup, line 222, in zpublisher_exception_hook
* Module ZPublisher.Publish, line 115, in publish
* Module ZPublisher.mapply, line 88, in mapply
* Module ZPublisher.Publish, line 41, in call_object
* Module Shared.DC.ZRDB.Connection, line 115, in manage_test
* Module Products.ZSQLRelayDA.db, line 86, in query
* Module decimal, line 571, in __new__
* Module decimal, line 2267, in _raise_error

InvalidOperation: (Also, the following error occurred while attempting to render the standard error message, please see the event log for full details: '@@plone')

</error>

If I change it to "SELECT empid, reg from myDatabase" it will work as long as reg is not a NULL. I can get it to work if I use "isnull" functions on all my decimal type fields, but I would like to avoid that. Is this a known issue with a workaround?

I am using Zope 2.9.4-final on Ubuntu with SQL Relay 0.39.4.

Mike Driscoll
Applications Specialist
MCIS - Technology Center
_______________________________________________
Zope maillist - Zope[at]zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )

dieter at handshake

May 16, 2008, 12:59 PM

Post #2 of 3 (2 views)
Permalink

Re: Weird SQL Relay conversion error in Zope [In reply to]

Mike Driscoll wrote at 2008-5-9 08:31 -0500:
> ...
>I am working on a web app that interfaces with Microsoft SQL Server from Ubuntu through SQL Relay. I will be getting data from multiple databases. The issue seems to be that either Zope or SQL Relay cannot handle the Decimal type if the value is a null. If I do a query like this:
> ...
> * Module Products.ZSQLRelayDA.db, line 86, in query
> * Module decimal, line 571, in __new__
> * Module decimal, line 2267, in _raise_error
>
>InvalidOperation: (Also, the following error occurred while attempting to render the standard error message, please see the event log for full details: '@@plone')

Looks like a bug in "ZSQLRelayDA.db".

Apparently, it passes SQL decimal values to Python's "decimal" module --
even if the SQL value is "Null" (almost surely mapped to Python's "None").
For obvious reasons, "decimal" does not like to convert "None"
into a "decimal" value.

"ZSQLRelayDA.db" should test for "None" and call the "decimal"
constructor only for non "None" values.

--
Dieter
_______________________________________________
Zope maillist - Zope[at]zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )

mdriscoll at co

May 16, 2008, 1:02 PM

Post #3 of 3 (2 views)
Permalink

Re: Weird SQL Relay conversion error in Zope [In reply to]

Dieter,
> Mike Driscoll wrote at 2008-5-9 08:31 -0500:
>
>> ...
>> I am working on a web app that interfaces with Microsoft SQL Server from Ubuntu through SQL Relay. I will be getting data from multiple databases. The issue seems to be that either Zope or SQL Relay cannot handle the Decimal type if the value is a null. If I do a query like this:
>> ...
>> * Module Products.ZSQLRelayDA.db, line 86, in query
>> * Module decimal, line 571, in __new__
>> * Module decimal, line 2267, in _raise_error
>>
>> InvalidOperation: (Also, the following error occurred while attempting to render the standard error message, please see the event log for full details: '@@plone')
>>
>
> Looks like a bug in "ZSQLRelayDA.db".
>
> Apparently, it passes SQL decimal values to Python's "decimal" module --
> even if the SQL value is "Null" (almost surely mapped to Python's "None").
> For obvious reasons, "decimal" does not like to convert "None"
> into a "decimal" value.
>
> "ZSQLRelayDA.db" should test for "None" and call the "decimal"
> constructor only for non "None" values.
>
>
>

I suspected as much. I mentioned trying SQLAlchemy to my boss and he
tried it to see if it could handle this issue and it does. So we'll
probably stick with that, but thanks for the clarification.

Mike

_______________________________________________
Zope maillist - Zope[at]zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact lists@gossamer-threads.com