[egenix-users] Impossible to correct execute stored procedure using cursors

[M.-A. Lemburg]

On 2008-11-05 12:45, Sirio Capizzi wrote:
> Yes, the if clause is intended and the problem doesn't depend on it. The
> problem is that each time you execute the stored procedure you got a
> different numer of rows even if the procedure uses the same set of data.

Have you tried this in the SQL Server Management GUI ?

Since the stored procedure runs entirely on the server
side, I don't think that mxODBC or the ODBC driver
can cause this behavior.

> The python script we are using is the following:
> 
> import mx.ODBC.Windows
> from mx.ODBC.Windows import SQL
> 
> conn=mx.ODBC.Windows.Connect('db3','user','password')
> conn.setconnectoption(SQL.CURSOR_TYPE,SQL.CURSOR_DYNAMIC)

The segfault is likely caused by the above option setting:
SQL.CURSOR_TYPE is a *cursor* option, not a connection
option, so you're adjusting some unknown option on the
connection to some unknown value.

BTW: Please don't change the SQL.CURSOR_TYPE value - mxODBC
uses static cursors for efficiency and also to enable
scrolling in result sets.

> cur = conn.cursor()
> cur.callproc('CursorTest')
> cur.close()
> 
> conn.commit()
> 
> The version of the  SQL Server ODBC driver is  2000.85.1132.00
> 
>> On 2008-11-05 09:50, Sirio Capizzi wrote:
>> >/ Hi all,
>> />/ we have a problem using egenix mxODBC with a stored procedure using
>> />/ cursors. The scenario is the following: a Windows XP based client
>> tries
>> />/ to call a stored procedure on a Microsoft SQL Server 2000 using
>> python
>> />/ 2.5 and mxODBC 3.0.2. The procedure simply copies all the rows of a
>> />/ table to another one using cursors. The call succeeds but a random
>> numer
>> />/ of rows are copied instead of the full table. /
>> See below - there's an IF clause there that applies filtering.
>>
>> >/ We have tried with
>> />/ execute, executedirect and callproc. Execute and executedirect
>> complete
>> />/ but only a random number of rows are copied. Callproc crashes and a
>> />/ windows error report dialog pops up saying that an error occured in
>> />/ mxodbc.pyd. /
>> Could you send us the Python script (or snippet) you used to trigger the
>> segfault ?
>>
>> Segfaults in mxODBC itself are rare - segfaults in the underlying
>> ODBC driver unfortunately not, but we always try to add work-arounds
>> for these buggy drivers if possible, so feedback is appreciated.
>>
>>   
> 

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Nov 05 2008)
>>> 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,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611