ADO CommandTimeout Components

Question

I have a problem with query execution timeout settings using TADOQuery, TADOCommand or TADODataSet (I tried this with each). I have a tiny application that connects to a database and periodically executes stored procedures that return a data set as a result . My goal is to constantly support this application, but my problem is that when the connection is lost, the timeout of the command just executed (through one of the mentioned components) takes 30 seconds by default. I was looking for a solution, but nothing works. Could you give me advice on how to set CommandTimeout, for example. for 5 seconds or better say how to change ADODB.pas to comply with my own timeout, please?

There were many “solutions” for this, for example, the DataComponent.Connection.CommandTimeout set: = 1; but nothing really works. I am using D2009, MSSQL2005, and a connection together with a data component is created dynamically in the stream.

The last thing I tried is

// protected variable owned and created in the thread with its own connection var Query_Object: TADODataSet; // connection timeout is set to 3 seconds Query_Object.Connection.ConnectionTimeout := 3; ... // this piece of code I'm calling periodically in the only one existing thread ... SQL_Query := 'EXEC my_procedure_which_returns_dataset' with Query_Object do begin Close; CommandType := cmdText; CommandText := SQL_Query; CommandTimeout := 5; // doesn't affect the timeout CursorLocation := clUseServer; // let the dataset retreives prepared data Open; end; // and here I need to get faster than in the default 15 seconds to let the user // know that the reading takes more than mentioned 5 seconds ... 

Many thanks:)

Answer 1

CommandTimeout triggered when you have long requests. There is a CommandTimeout TADOConnection property, but this does not work. Instead, you should use CommandTimeout TADODataSet .

If the server is unavailable, your question says "connection is lost", you need to specify the ConnectionTimeout of the TADOConnection component. The default is 15 seconds before the control is returned to your application.

Edit 1 I think I discovered a situation where CommandTimeout is not working. I checked this against a really big table. It takes a few minutes to return all rows. If my stored procedure does select * from BigTable , the request timeout will never happen. At least I was not patient enough to wait. But if the query looks like this: select * from BigTable order by Col1 and Col1 no Col1 index, CommandTimout works as expected.

The difference between the two requests is obvious when you run them in SSMS. The first begins to return the rows immediately, and the second must “think” about it before it returns the rows. When SQL Server found the rows it needed and started returning them, CommandTimeout does not work.

If you set CursorLocation to clUseServer , then CommandTimeout will work as expected for both requests.

Answer 2

Below we use to set a 300 timeout for our long-term reports.

  //***** Fix setting CommandTimeOut. // CommandTimeOut "should" get the timeout value from its connection. // This is not supported in ADODB (using Delphi5) TADODataSet(qryReport).CommandTimeout := ADOConnection.CommandTimeout; 

Edit

The execution of the next piece of code on my development machine expires in 1 second.

• The query has a connection string to our SQLServer production base.
• script (trying) runs for 10 seconds
• After one second, I get a TimeOut exception

Test

 procedure TForm1.btn1Click(Sender: TObject); const SSQL: string = 'DECLARE @intLoop int '#13#10 + 'SET @intLoop = 10 '#13#10 + 'WHILE @intLoop > 1 '#13#10 + 'BEGIN '#13#10 + ' SELECT @intLoop, GetDate() '#13#10 + ' WAITFOR DELAY ''00:00:01'' '#13#10 + ' SELECT @intLoop = @intLoop -1 '#13#10 + 'END '; begin qry1.SQL.Text := SSQL; TADODataSet(qry1).CommandTimeout := 1; qry1.ExecSQL; end; 

Answer 3

I always used the following code to set the CommandTimeout value to TADOQuery. If you edit the class name, it should work with others as well.

 type TADOQueryHack = class(TADOQuery); ... TADOQueryHack(Qry).CommandTimeout := COMM_TIMEOUT;