Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

DBI Prematurely Disconnecting

by perldc (Initiate)
on Oct 08, 2013 at 16:17 UTC ( #1057434=perlquestion: print w/ replies, xml ) Need Help??
perldc has asked for the wisdom of the Perl Monks concerning the following question:

I have a simple DBI do that is running a stored procedure on sql server. The stored procedure may return informational messages and throw various low priority errors during execution. Running the stored procedure from sql server managements studio executes perfectly fine, and I see the informational messages as intended. However, running this from my perl script terminates prematurely. I seem to be able to link it to DBI possibly automatically terminating the connection as soon as it receives the first select/response. Any idea how I can tell DBI to wait until the stored procedure actually completes before disconnecting?

Comment on DBI Prematurely Disconnecting
Re: DBI Prematurely Disconnecting
by davido (Archbishop) on Oct 08, 2013 at 16:45 UTC

    Is RaiseError set, or barring that, are you checking $h->err and/or $h->errstr after each database operation? It's unusual behavior for DBI to disconnect without any reason.

    If all else fails, there is DBIx::Connector which can quietly handle ensuring that you always have a valid database handle available, but that's not a real solution for this problem; I would sure want to know why it's happening in the first place, and error checking might just divulge such information.


    Dave

Re: DBI Prematurely Disconnecting
by Corion (Pope) on Oct 08, 2013 at 16:54 UTC

    I think at least DBD::Sybase can return multiple resultsets, but you need to explicitly enable that feature.

    For other DBDs, you will also have to review the respective documentation I guess.

Re: DBI Prematurely Disconnecting
by mje (Deacon) on Oct 09, 2013 at 10:30 UTC

    DBI will not disconnect unless you tell it to or there is an error. Which DBD are you using? Show us your code for calling the procedure and getting the results.

Re: DBI Prematurely Disconnecting
by perldc (Initiate) on Oct 09, 2013 at 14:37 UTC
    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";
    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
    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.

      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.

        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?
        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});
        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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1057434]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (12)
As of 2014-08-20 18:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (121 votes), past polls