asked on

Delphi ADO Timeout Issues + Rollback

Hi experts,

I have a delphi 7 application (accounting software) which uses ADO to query SQL Server.

The code looks something like this:

var
qry:TADOQuery;
IsOK:boolean;
begin
qry:=TADOQuery.create(self);
qry.Connection:=MYADOConnection;
qry.CommandTimeout:=30;
qry.SQL.Add('Select Results into #TempTable from ABigTable where AHugeCriteria');
qry.SQL.Add('Do some complicated stuf with #TempTable');
qry.SQL.Add('Select * from #TempTable'); //This is the result set that I am after
qry.SQL.Add('DROP Table #TempTable');
repeat
try
qry.Open;
IsOK:=true;
except
on e:Exception do
begin
if e.Message='Timeout expired' then
qry.CommandTimeout:=300
else
raise;
end;
end;
until IsOK
end;
end;

The Problem: When this query times out the first time the table #TempTable gets created. The second attempt (after setting a larger timeout) doesn't work because #TempTable already exists. Important - I can not close the connection because there are many other open queries.

Related Problem: This timeout drains the server's resources and causes other timeouts for simple tasks for other users, until this user disconnects.

I will split points for answering the following questions (but I think Question 2 and 3 have no answer). So I am most interested in question 1 - but any related info on dealing with Timeouts would be appreciated.

Question 1) How can I test for the existance of #TempTable?

Question 2) Is there a way of releasing / rolling back the resources used by an ADOQuery (other than by disconnecting)?

Question 3) Is there a way of trapping the Timeout within the SQL Script or somehow implementing a "Finally" clause to drop the #TempTable?

I will be away for the rest of this week. I will check responses when I return.

Thanks.

Mike Littlewood

Why not within the exception section delete the table.

You can wrap the whole thing in a transaction.
Using the ADOConnection that the query is attached to.

Try
ADOConnection.BeginTrans

// do everything you need

ADOConnection.CommitTrans
except
ADOConnection.RollbackTrans
end

Mike Littlewood

I believe there is an issue with the ADOQuery.ConnectionTimeout property.
The only way round it I have found is to derive your own ConnectionTimeout property by inheriting from an ADOQuery to create your own.

ASKER CERTIFIED SOLUTION

pcsentinel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

enigmasolutions

ASKER

Thanks for the prompt responses.

For some reason (I am sorry I can't remember why) we could not use the ADOConnection.BeginTransaction. But also a very good idea - in fact we may re-investigate why we steered away from ADOConnection.BeginTransaction - FYI it could have been to do with performance - I will get back to you all on this one.

The try except is a great idea but in this case the application is mutli-tier and the qry.open is occuring in a middle tier application (which has no knowledge of what the qry is trying to do) - so I can't do much in the Except section by way of dropping the #TempTable.

I was aware of the ADOQuery.ConnectionTimeout property issue - actually I read about it in another ee question - but this is only an issue in earlier versions of Delphi.

The last one is the answer I was generally after. We can create a unique #TempTable by concatinating the SPID and a consecutive number to the name of the table. So this will work for me.