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

[M.-A. Lemburg]

On 2008-11-06 11:01, Sirio Capizzi wrote:
> I have checked the version  of our DBMS and it is 8.00.2039.

Hmm, looks like we're missing some service pack on ours. But then: your
version should have fewer bugs - at least in theory.

> Moreover I have done some tests using your stored procedure and another
> one that is more similar to our situation: in every case the callproc
> function crashes. I have attached the related odbc log files
> SQL_sb_XXX_callproc_crash.log. More important for me is resolving the
> problem with the random number of rows that are copied using the stored
> procedure. So I have have done some tests using the followings table and
> stored procedure:
> 
> create table mxODBC0002(col1 int primary key,col2 int)
> 
> CREATE PROCEDURE sp_mxODBC0002 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
>              insert into mxODBC0002 values (@var1, @var2)
>              FETCH NEXT FROM cursor1
>                INTO @var1, @var2
>            END
>          CLOSE cursor1
>          DEALLOCATE cursor1
> 
> The table mxODBC0001 is the same of your previous mail. Using the new
> simplified stored procedure the strange behavior continues to exist but
> sometimes all rows are copied in the destination table. I have attached
> two logs regarding this problem: SQL_sp_mxODBC0002_all_rows.LOG is the
> log when the insertion completes sucessfully whereas
> SQL_sp_mxODBC0002_some_rows.LOG is a log when only some rows are
> inserted in the destination tables. I think that this is the simplest
> scenario representing our situation and I hope that these logs can help
> you in identifying the problem.

We have added a similar test to our test suite and ran the test
several times. All passed and no crashes.

I've also had a look at the ODBC log and all I could find is the
same problem that you find in the crashing ones: the cursor state
is always invalid after you call the procedure.

I can't tell from here, but this looks a lot like a problem in the
ODBC driver or your setup.

> I can provide you with more information if you need it. I have read on
> the manual that the library can be compiled in a debug mode. Where can I
> found such a library? If you can give me this type of library I can send
> you the logs.

We can't reproduce the problem with our setup and have already invested
a fair amount of time into trying to help you with this without being able
to really determine a problem in mxODBC.

As a result, we have to ask you to get a support ticket(s) to continue
working on your problem:

    http://www.egenix.com/services/support/

We can then provide you with a special debug build of mxODBC
and analyze the logs it generates.

Thanks,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Nov 06 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