[egenix-users] Invalid cursor state

[Dan Fairs]

Another update, for the benefit of the archives.

>>
>> Putting a time.sleep(1) before the update and the second select  
>> seems to
>> prevent the error occurring. Googling the error does seem to indicate
>> that this happens (at the C level) when two results handles are open
>> simultaneously; the sleep seems to allow some opportunity for  
>> cleanup.
>
>

Unfortunately, the sleep just seemed to reduce the frequency of the  
error occurring. A colleague (working on Mac OS X with iODBC on  
FreeTDS rather than Linux, unixODBC and FreeTDS) has also just  
experienced the problem, leading to the conclusion that the problem is  
likely with FreeTDS.

> It is possible that the ODBC driver tries to do some optimization
> by keeping the result set and the cursor from the first SELECT
> alive even after the Zope DA has closed it.
>
> This would explain the invalid cursor state, since SQL Server
> does not support multiple active statements on the same connection
> unless you enable the MARS feature available in SQL Server 2005
> and later:
>
> http://msdn.microsoft.com/en-us/library/ms131686.aspx
>
> (you can enable this via a connection parameter "Mars_Connection=on")

We tried this, but we continued to get the problem.

The avenue we're going down now is to move all the 'problematic' SQL  
into stored procedures on a case-by-case basis. So far, this has  
stopped the problem recurring in each place where the change is made.

Thanks
Dan

--
Dan Fairs <dan.fairs at gmail.com> | http://www.fezconsulting.com/