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

[M.-A. Lemburg]

On 2008-11-06 12:22, M.-A. Lemburg wrote:
> 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.

FYI: We've upgraded to SP4 (8.00.2039) now, but still can't recreate
the problem.

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