ADOConnection ADOQuery CommandTimeout , Override Order ( if at all )-CodePudding

Got here Delphi 10.4.2 . One ADOConnection and one ADOQuery .

ADOConnection uses OleDB (UDL) to connect to MSSQL. And I have left the CommandTimeout 30 . Now I place the ADOQuery on this form set it's Connection property to ADOConnection . All other Values are at default , CommandTimeout is 30 .

Now let's imagine we have a very large table which timeouts. With the default 30 Seconds .

My problem is :

if I set ADOQuery CommandTimeout to 600 it works but If I only set ADOConnection CommandTimeout to 600 it still makes a timeout

What purpose does CommandTimeout on ADOConnection have if it does not simply propagate it's values to it's associated components ( until of course I override them ) ?

Thank you.

EDIT 1 :

Is it that the ADOConnection CommandTimeout is used actually for some background tasks which ADOConnection must do to build the connection ? For example if I in the IDE Activate it , and then have a ADOStoredProcedure and I want to list all the available Stored Procedures on the Server it uses this timeout?

CodePudding user response：

According to Microsoft each ADO object respects its own CommandTimeout:

The CommandTimeout setting on a Connection object has no effect on the CommandTimeout setting on a Command object on the same Connection; that is, the Command object's CommandTimeout property does not inherit the value of the Connection object's CommandTimeout value.

I made a small MRE (Using Delphi 10.3) to prove this point:

program SO69733529;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  ActiveX,
  AdoDb,
  System.SysUtils;

procedure TestADOCommandTimeout;

var
  Conn : TADOConnection;
  Qry  : TADOQuery;
  SQLDelay : Integer;

begin
 Conn := TADOConnection.Create(nil);
 Qry := TADOQuery.Create(nil);
 try
  Conn.LoginPrompt := False;
  Conn.ConnectionString := 'Provider=SQLOLEDB.1;Data Source=localhost\sqlexpress;Initial Catalog=TestCustomer;Integrated Security = SSPI;';
  Conn.Connected := True;
  Qry.Connection := Conn;
  Writeln('Connected to DB');
  SQLDelay := 10;
  Conn.CommandTimeout := 5;
  try
   Writeln(Format('Waiting for %d seconds, connection timeout is %d seconds', [SQLDelay, Conn.CommandTimeout]));
   Conn.Execute(Format('WAITFOR DELAY ''00:00:%.2d''', [SQLDelay]));
   Writeln('No Timeout');
  except
   on E: Exception do
    Writeln(E.ClassName, ': ', E.Message);
  end;
  Qry.CommandTimeout := 30;
  try
   Writeln(Format('Waiting for %d seconds, query timeout is %d seconds, connection timeout is %d seconds', [SQLDelay, Qry.CommandTimeout, Conn.CommandTimeout]));
   Qry.SQL.Text := Format('WAITFOR DELAY ''00:00:%.2d''', [SQLDelay]);
   Qry.ExecSQL;
   Writeln('No Timeout');
  except
   on E: Exception do
    Writeln(E.ClassName, ': ', E.Message);
  end;
  // now redo the same test but with connection commandtimeout same as qry timeout
  Qry.CommandTimeout := 15;
  Conn.CommandTimeout := Qry.CommandTimeout;
  try
   Writeln(Format('Waiting for %d seconds, query timeout is %d seconds, connection timeout is %d seconds', [SQLDelay, Qry.CommandTimeout, Conn.CommandTimeout]));
   Qry.SQL.Text := Format('WAITFOR DELAY ''00:00:%.2d''', [SQLDelay]);
   Qry.ExecSQL;
   Writeln('No Timeout');
  except
   on E: Exception do
    Writeln(E.ClassName, ': ', E.Message);
  end;    
 finally
  Qry.Free;
  Conn.Free;
 end;
end;

begin
  try
   try
    CoInitialize(nil);
    TestADOCommandTimeout;
   finally
     CoUninitialize;
   end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
 Readln;
end.

Output:

 Connected to DB Waiting for 10 seconds, connection timeout is 5 seconds
 EOleException: Query timeout expired 
 Waiting for 10 seconds, query timeout is 30 seconds, connection timeout is 5 seconds  
 No Timeout 
 Waiting for 10 seconds, query timeout is 15 seconds, connection timeout is 15 seconds 
 No Timeout