in reply to DBI Prematurely Disconnecting

I have proven my theory by removing all print and raiserror statements from my stored procedure and it executes fully now. DBI: v1.53 DBD::ODBC: v1.13 I am calling from perl using: 
my $dbh11 = DBI->connect("dbi:ODBC:driver={SQL Server};Server=$dbserve
+r;database=$DBName;",$uid,$pwd, {AutoCommit => 1, RaiseError=>1, Hand
+leError=>\&HandleDBError})
        or die "Can't connect to $dbserver/$DBName:  $DBI::errstr";
DBI->trace(9,"dbiDOtrace.log");
    my $sthExec = $dbh11->do(qq/exec spMyProcedure/) or die "error pre
+paring execute transfers sql";

    
    $dbh11->disconnect();
    print LOGFILE "Done exec\n";

[download]
The only error that i've ever encountered with this is MicrosoftODBC Sql Server DriverInvalid cursor state (SQL-24000)(DBD: dbd_describe/SQLNumResultsCols err=-1) I can reproduce this by using a stored procedure that includes RAISERROR('my test progress message',1,10) WITH NOWAIT I use that to help notify me of progress as a long running procedure executes from SSMS. I understand if maybe it disconnects automatically when receiving this message although a low code such as 10 is considered informational and noncritical as far as sql server goes. Of course I can simply remove these and it works but i'd like to keep them if possible. My biggest problem is that I get the premature termination behavior if a SELECT returns results before the rest of the procedure is finished. In my case I return a list of records I will update within the procedure and then it uses While loop to do the updates. Again if I use the stored procedure directly in Sql Server Management Studio, it works perfectly. From within perl, as soon as it receives the results from the first select, the server only has enough time to update 10-20 records and then it is terminated by something in perl with no errors. 100% reproducible but it has to be 1000+ records 
SELECT id, col2, col3 from LargeTable
DECLARE @i int
SET @i=1
While @i<1000
BEGIN
UPDATE LargeTable SET col2=getdate() WHERE id=@i
END

[download]
If I remove the initial select, all records get updated. If I include the select, only a random number are able to finish by the time perl terminates it. Number is different everytime depending on the execution time between client and server but averages only about 10-20 updates.

Replies are listed 'Best First'.
Re^2: DBI Prematurely Disconnecting
by mje (Curate) on Oct 09, 2013 at 16:03 UTC

    UPDATE: I didn't realise you were using the ANCIENT version 1.13 from November 2004! Some of what I say below may post date that.

    I'm not saying there are no bugs in DBD::ODBC but your code is not using DBD::ODBC correctly for this example. You have not "proven your theory" at all.

    1. Do not use the do method for statements that can return results. Think about it. Something which returns a result needs to return a statement handle to retrieve the result on BUT do doesn't. Use the prepare/execute methods or the select* methods (but see 2)
    2. Procedures like yours generate multiple results. You might think they don't but they do. The first is the select statement and the second is the update. You might think update does not return anything but in actual fact it returns a count of the rows updated. You should read DBD::ODBC pod and look for odbc_more_results.
    3. print statements in procedures generate yet another result. You need odbc_more_results again. There are examples included with DBD::ODBC and the 20SqlServer.t test which you might want to look at. You can catch the print statements in an error handler - see odbc_err_handler as well.
    4. You might want to read about set nocount on in SQL Server and its implications.
    5. Procedures don't complete in ODBC until odbc_more_results returns false. This is because SQL Server batches resultes.

    If after, reading the above and making the appropriate changes you still have an issue by all means come back here with revised code and I'll help work out what is going wrong

    By the way, I maintain DBD::ODBC.

[reply]
      I don't mean to sound like i'm blaming DBD directly. I am an amateur and I may be doing something wrong or using something inappropriately. By proving my theory I only meant to communicate that my script operates fully within ssms and the return of the select seems to 'break' what I expect to happen. I have tried both do and prepare/execute.I understand how this would return multiple results. I have set nocount on and I understand what it does. It seems like I have to get both DBI and DBD upgraded and try odbc_more_results again. Am I accurate to say then that if a stored procedure does anything complex (e.g. multiple select / update / delete statements), I must use odbc_more_results if nocount is off. Or does it sound like I am having an abnormal issue as a result of my own doing and/or from not knowing how it works?
[reply]

        I answered your later post where it sounds like you are working now. I did not take your reponse as meaning you were blaming anything, I was just simply pointing out there were more parameters than you had considered.

        I'm interested in 2 things in particular (both of which could help me).

        1. Why are you using so old a DBI and DBD::ODBC?
        2. Why do you think you got to the point where you were calling a procedure with the do method and never got to odbc_more_results? Did you read any of DBD::ODBC and if so, how could I make it clearer so the next person who hits this doesn't have to go through your pain.
[reply]
      I have confirmed that using the following code allows the stored procedure to run as expected to completion. 
      my $sthTestProc1 = $dbhTestInsert->prepare(qq/exec spTestSelectAndUpda
+te/);
$sthTestProc1->execute();

do {
   my @row;
   while (@row = $sthTestProc1->fetchrow_array()) {
      # do stuff here
   }
} while ($sthTestProc1->{odbc_more_results});

      [download]
      I guess any complex procedures should always use this just in case. I just assume that DBI/DBD would somehow know if a stored procedure is still running or not regardless of how many resultsets it has received. Thank you for your help.
[reply]
[d/l]

        The new code you show should be your template for calling ANY procedure via DBD::ODBC. I can explain why but it would get into a load of discussion of the ODBC API. The main reasons are in my previous post.

        Now put your other logic with raise_error and prints back in and see where you get to. But most importantly, remember, NO procedure in SQL Server has completed UNTIL odbc_more_results (aka SQLMoreResults) returns false. This is particularly important if you have any output parameters as they are not available until then.

[reply]
        Also keep in mind that using RAISERROR in your SQL code to return a message can potentially cause the DBI/DBD code to think that an error has occurred, and cause the fetch loop to terminate.

        Michael

[reply]

In Section Seekers of Perl Wisdom