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

[M.-A. Lemburg]

M.-A. Lemburg wrote:
> On 2008-11-05 15:55, Sirio Capizzi wrote:
>>
>> M.-A. Lemburg ha scritto:
>>> mxODBC 3.0 tries to use the ODBC 3.x API where possible,
>>> so that may explain why you are seeing different behavior
>>> compared to mxODBC 2.0.
>>>
>>> The SQL Server 2000 ODBC driver is rather old, so it's possible
>>> that it doesn't support the ODBC 3.x API all that well.
>>>
>>> However, our tests pass just fine for that driver as well, but
>>> AFAIK we don't use cursors in the tested stored procedures, so
>>> that's something to add to the test suite.
>>>   
>> Very interesting... It is possible to force mxODBC to use only the ODBC
>> 2.0 API? There are newer drivers for SQL Server 2000?
> 
> No that's not possible without patching mxODBC.
> 
> The ODBC manager will normally translate between the application and the
> driver, however, it only does this if the driver itself advertises itself
> as being a 2.0 driver.
> 
> We'll setup a few new tests to try to reproduce the problem.

We've added a test similar to what you're doing to out suite,
but cannot reproduce the problem:

Loaded mx.ODBC.Windows version 3.0.2

Testing package mx.ODBC.Windows version: 3.0.2
        compiled with Unicode support
        using Python version: 2.5

Test suite:
 Connecting to the database.
 Connected to DSN
   DSN: sqlserver; Database: master;
   Server: PICASSO; User: dbo;
   DBMS: Microsoft SQL Server; Version: 08.00.0760;
   ODBC driver: SQLSRV32.DLL 03.85.1132; ODBC Version: 03.52
 String encoding used by mxODBC: utf-8

StoredProcedureTest: executing create table mxODBC0001 (col1 int, col2 int)
StoredProcedureTest: executing
        CREATE PROCEDURE sp_mxODBC0001 AS
          DECLARE @var1 int, @var2 int
          DECLARE cursor1 CURSOR FOR
            SELECT col1, col2 FROM mxODBC0001
            ORDER BY col1
          OPEN cursor1
          FETCH NEXT FROM cursor1
            INTO @var1, @var2
          WHILE @@FETCH_STATUS = 0
            BEGIN
              SELECT @var1 AS col1, @var2 AS col2
              FETCH NEXT FROM cursor1
                INTO @var1, @var2
            END
          CLOSE cursor1
          DEALLOCATE cursor1

 SP: Cursors in procedures                         : function supported

 Disconnecting.

Note that we're using the same ODBC driver version. Please check
whether the SQL Server version also matches yours.

>>> Could you send us an ODBC trace that shows the crash (ie. stops
>>> after the crash) ?
>> Of course, you will find it in the attachment. Thank you for your help.
> 
> Thanks.

The log indicates that the cursor state becomes invalid after
having called the procedure. However, that should normally not
result in a segfault.

-- 
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