Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

Re^2: DBI Prematurely Disconnecting

by mje (Curate)
on Oct 09, 2013 at 16:03 UTC ( #1057578=note: print w/replies, xml ) Need Help??

in reply to Re: DBI Prematurely Disconnecting
in thread DBI Prematurely Disconnecting

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.

Replies are listed 'Best First'.
Re^3: DBI Prematurely Disconnecting
by perldc (Initiate) on Oct 09, 2013 at 17:29 UTC
    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 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.
        1. I'm relying on the install of an existing server to which I am not an admin. However we will be upgrading. 2. The do method was only an attempt at trying something new at the time that I posted my code. I was actually using prepare/execute because I knew I was expecting a resultset. I usually work with .net and if I run any SQL using any of the methods, it executes 100% fully before continuing.

        I've come across odbc_more_results in previous research but DBD::ODBC only says "SQL Server supports this feature" so I assumed I didnt have to use this unless I was expecting to see multiple results from using sql such as select * from table1; select * from table2;

        You've been a great help. Thank you.
Re^3: DBI Prematurely Disconnecting
by perldc (Initiate) on Oct 09, 2013 at 17:43 UTC
    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.

      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.

      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.


        That is true which is why I also tested with only select and update. Originally it was doing:
        Select * from tempTable; Update tempTable set done=1 where id=1; Update tempTable set done=1 where id=2; --connection gets broken
        now with more results it executes fully:
        Select * from tempTable; Update tempTable set done=1 where id=1; Update tempTable set done=1 where id=2; Update tempTable set done=1 where id=3; Update tempTable set done=1 where id=4; Update tempTable set done=1 where id=5;
        In the stored procedure the updates are within a while loop that generates the id so in this case no additional print or raiserror was being used.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1057578]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (2)
As of 2018-05-23 23:31 GMT
Find Nodes?
    Voting Booth?