[egenix-users] Large Number in ZSQL 'Maximum rows to retrieve' parameter causes Memory Error

[M.-A. Lemburg]

Igor Elbert wrote:
> We have a query with a 'Maximum rows to retrieve' parameter set to a huge
> number 1000000000 (we do not need a limit).
> 
> The query is supposed to work with both ZOracleDA and mxODBCZopeDA adapters.
> With ZOracleDA we do not have any problems. With mxODBCZopeDA we got
> MemoryError even if the query does not return any rows.
> It looks like mxODBCZopeDA tries preallocate memory without checking if it's
> needed.

That's true. mxODBC preallocates the number of rows you want
to fetch in order to make fetching faster (you normally specify
a value smaller than the expected result set size).

> While we did not find it in the manual it seems like setting 'Maximum rows
> to retrieve' to 0 removes the limit but it makes the query incompatible with
> ZOracleDA which does not return any rows if the parameter set to 0.

True again. You can also set it to None. The check done
in mxODBC Zope DA is a simple truth test:

                     if not max_rows:
                         rowset = cursor.fetchall()
                     else:
                         rowset = cursor.fetchmany(max_rows)

The problem is with max_rows is related to the Zope DA
API design: The ZSQL interface forces usage of a max_rows
parameter (which defaults to 1000 rows). The only way to
correctly disable the max_rows limitation is by setting the
parameter to 0.

Perhaps we should provide a way to override the max_rows
setting altogether on a per connection basis ?!

> The traceback:
> 
> Connected database: Microsoft SQL Server, 08.00.0194
> ODBC driver: SQLSRV32.DLL, 03.85.1117
> mxODBC Zope DA 1.0.8
> 
> Error, exceptions.MemoryError
> 
> Module ZPublisher.Publish, line 49, in publish
> Module ZPublisher.mapply, line 32, in mapply
> Module ZPublisher.Publish, line 38, in call_object
> Module Shared.DC.ZRDB.DA, line 295, in manage_test
> Module Shared.DC.ZRDB.DA, line 295, in manage_test
> Module Shared.DC.ZRDB.DA, line 374, in __call__
> Module Products.mxODBCZopeDA.ZopeDA, line 1323, in query
> Module Products.mxODBCZopeDA.ZopeDA, line 1227, in run_cursor_callback
> 
> Regards,
> Igor
> 
> This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
> 
> 
> _______________________________________________________________________
> eGenix.com User Mailing List                     http://www.egenix.com/
> http://lists.egenix.com/mailman/listinfo/egenix-users

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Oct 19 2004)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::