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

[M.-A. Lemburg]

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.

> We also tried
> .setconnectoption(SQL.CURSOR_TYPE,SQL.CURSOR_DYNAMIC) but with no results.
> 
> Someone can help us? Thank you in advance.
> 
> The stored procedure looks like the following, attaced you will find the
> odbc connection trace:
> 
> USE [db3]
> GO
> /****** Oggetto:  StoredProcedure [user].[CursorTest]    Data script:
> 11/05/2008 08:44:39 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> 
> ALTER  procedure [user].[CursorTest] as
> 
> truncate table db3.user.CursorTest_test
> 
> 
> declare @codicePaese varchar(100), @assetClass varchar(100),
> @nomeSocieta varchar(100), @description varchar(100), @codicePaesePrec
> varchar(100), @assetClassPrec varchar(100)
> 
> set @codicePaesePrec = 'aaa'
> set @assetClassPrec  = 'bbb'
> 
> DECLARE cursore CURSOR
>   FOR
> 
> select CodicePaese, AssetClass, NomeSocieta, [Description]
> from db3..sourceTable
> order by codicepaese, assetclass, nomesocieta
> 
> OPEN cursore
> 
> FETCH NEXT FROM cursore
> INTO @codicePaese, @assetClass, @nomeSocieta, @description
> 
> WHILE @@FETCH_STATUS = 0
> 
> BEGIN
> 
> IF @codicePaese + '.' + @assetclass = @codicePaesePrec + '.' +
> @assetclassPrec
>    BEGIN
>      set @codicePaesePrec = @codicePaese
>    set @assetclassPrec = @assetclass
>      END
> ELSE

The above IF will likely filter out a few rows. Is that intended ?

> BEGIN
> 
> 
> insert into db3.user.CursorTest_test (codicepaese, assetclass,
> nomesocieta, [description])
> values(@codicePaese, @assetClass, @nomeSocieta, @description)
> set @codicePaesePrec = @codicePaese
> set @assetclassPrec = @assetclass
> 
> END
> 
> FETCH NEXT FROM cursore
> INTO @codicePaese, @assetClass, @nomeSocieta, @description
> 
> END
> 
> CLOSE cursore
> DEALLOCATE cursore

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