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.
| [reply]
[d/l]
[select] |
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.
| [reply] |
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.
| [reply] |
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. | [reply]
[d/l]
[select] |
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.
- 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)
- 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.
- 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.
- You might want to read about set nocount on in SQL Server and its implications.
- 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 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. | [reply]
[d/l] |