sqlobject-discuss

[SQLObject] Two cursors in Iteration

[Oleg B. <ph...@ph...>]

Hello!

   atable.select(... lazyColumns=True ...) pases lazyColumns to
SelectResults(), that passes it (in self.__iter__) to
dbconnection.Iteration(), whose .next() method creates an object by
calling self.select.sourceClass.get(), and the .get() calls val._init()
that seeing no selectResults in its turn calls
self._connection._SO_selectOne() that creates another cursor to fetch
the data.

   Another cursor is created in the same connection while the first one
is still being iterated. Is it correct? Some DB API adapters allows it.
For example MySQLdb emulates cursors by copying all data. But what about
other adapters and databases?
   I heard rumors that Sybase (and hence MSSQL) will have problems with
two cursors. There is not SybaseConnection yet, but certainly it will
appear sooner or later.

   The situation becomes even worse with Danila Savard's inheritance
patch, because parent's .get() fetches a child calling .get() without
selectResults even if lazyColumns=False.

   Any thoughts?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Two cursors in Iteration

[Oleg B. <ph...@ma...>]

Oops, followup to myself.

On Mon, Nov 15, 2004 at 05:40:39PM +0300, Oleg Broytmann wrote:
>    Another cursor is created in the same connection while the first one
> is still being iterated. Is it correct? Some DB API adapters allows it.
> For example MySQLdb emulates cursors by copying all data. But what about
> other adapters and databases?

   For every complex problem, there is a solution that is simple, neat,
and wrong. Actuall, I can see two simple solutions.

1. Open another connection. Very bad for transactions.

2. Use cursor.fetchall() instead of .fetchone(). Very bad for huge data
set.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Re: Two cursors in Iteration

[Oleg B. <ph...@ph...>]

On Mon, Nov 15, 2004 at 05:40:39PM +0300, Oleg Broytmann wrote:
>    I heard rumors that Sybase (and hence MSSQL) will have problems with
> two cursors. There is not SybaseConnection yet, but certainly it will
> appear sooner or later.

   Well, this is not just rumor anymore.

import adodbapi
conn = adodbapi.connect("Provider=sqloledb;SERVER=grad2000;DATABASE=prog3510;UID=sa;PWD=secret;Auto Translate=FALSE")
cur1 = conn.cursor()
cur2 = conn.cursor()
cur1.execute("select 1 union select 2")
cur2.execute("select getdate()")

   Error:

Traceback (most recent call last):
  File "D:\Work\adotest\Debug\test.py", line 7, in ?
    cur2.execute("select getdate()")
  File "D:\Python23\lib\site-packages\adodbapi\adodbapi.py", line 592, in execute
    self.executeHelper(operation,0,parameters)
  File "D:\Python23\lib\site-packages\adodbapi\adodbapi.py", line 552, in executeHelper
    self._raiseCursorError(DatabaseError,tracebackhistory)
  File "D:\Python23\lib\site-packages\adodbapi\adodbapi.py", line 407, in _raiseCursorError
    eh(self.conn,self,errorclass,errorvalue)
  File "D:\Python23\lib\site-packages\adodbapi\adodbapi.py", line 38, in standardErrorHandler
    raise errorclass(errorvalue)
adodbapi.adodbapi.DatabaseError: 
-----------
Strategy 1: Traceback:Traceback (most recent call last):
   File "D:\Python23\lib\site-packages\adodbapi\adodbapi.py", line 540, in executeHelper
    adoRetVal=self.cmd.Execute()
   File "D:\Python23\lib\site-packages\win32com\gen_py\EF53050B-882E-4776-B643-EDA472E8E3F2x0x2x7.py", line 1845, in Execute
    return self._ApplyTypes_(5, 1, (9, 0), ((16396, 18), (16396, 17), (3, 49)), 'Execute', '{00000556-0000-0010-8000-00AA006D2EA4}',RecordsAffected, Parameters, Options)
   File "D:\Python23\lib\site-packages\win32com\client\__init__.py", line
446, in _ApplyTypes_
    return self._get_good_object_(com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB
Provider for SQL Server', 'Cannot create new connection because in manual or distributed transaction mode.', None, 0, -2147467259), None)

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.