Re^3: DBI Prematurely Disconnecting

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.

Comment on Re^3: DBI Prematurely Disconnecting Download Code

Replies are listed 'Best First'.
Re^4: DBI Prematurely Disconnecting by mje (Curate) on Oct 09, 2013 at 17:52 UTC
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]
Re^4: DBI Prematurely Disconnecting by mpeppler (Vicar) on Oct 11, 2013 at 05:13 UTC
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]
Re^5: DBI Prematurely Disconnecting by perldc (Initiate) on Oct 14, 2013 at 15:40 UTC
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` [download] 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;` [download] 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.	[reply] [d/l] [select]