Delphi ADOConnection connection timeout property does not work

Question

I have a function to control server connection. If ado cannot connect in 5 seconds, it should give an error. But connectiontimeout property cannot work.

Here is the code I am using :

function AdoConnectionTester(strServerName, strUserName, strPassword,
    strDBName: string; boolShowMessage: boolean): Boolean;
var
  ADOConn: TADOConnection;
begin
  try
    Result := True;
    ADOConn := TADOConnection.Create(nil);
    ADOConn.LoginPrompt :=False;
    ADOConn.Close;
    ADOConn.ConnectionString := 'Provider=SQLOLEDB.1;    Password='+strPassword+';'+
                                 'Persist Security Info=True;User ID='+strUserName+';'+
                                 'Initial Catalog='+strDBName+';'+
                                 'Data Source='+strServerName;
try
  ADOConn.ConnectionTimeout := 5;
  ADOConn.Open;
except
  on E: Exception do
  begin
    Result := False;
    ShowMessage(E.Message);
  end;
end;
if Result then
  if boolShowMessage = True then
    ShowMessage('OK');
  finally
    ADOConn.Free;
  end;
end;

How can i solve this problem ?

Which Delphi version? Istr there was a problem with TAdoConnection's timeout value many versions ago, round about the D5 era iirc. — MartynA, Apr 01 '16 at 11:58
Oh, ok. Bt, why are you calling .Close on a connection you've only just created? — MartynA, Apr 01 '16 at 12:05
I don't think it gives a exception when it time's out even if you receive a message in the debug state. Try to see if there isn't a `ontimeout` command in the events panel — Jacques Koekemoer, Apr 01 '16 at 12:54
@JacquesKoekemoer: It does indeed generate an exception, I made use of the fact that it does to write my answer. — MartynA, Apr 01 '16 at 13:02
You need to take into account the win api calls and the TCP/IP layer: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e28b8700-4971-4d43-8edd-99294a7a6dc1/sql-connection-timeout-doesnt-work-why?forum=sqlexpress — kobik, Jan 11 '17 at 19:27

MartynA · Answer 1 · 2017-01-11T12:01:14.790

You didn't say what you mean by "does not work", but I assume you mean that the connection does not time out at the end of the period you specify.

Using the code below and a ConnectionString that is guaranteed to fail (by specifying a non-existent server), it seems that a TAdoConnection will not time-out in under about 9 seconds on my LAN. If a timeout period longer than that is specified, it does indeed time out after a longer period, but the actual time is not closely correlated with the specified time-out value. So specifying a time-out value does "work" in the sense of having an effect, but not seemingly if you want a time-out value as low as yours.

I would be surprised if there is anything that can be done via Ado from Delphi to overcome this limitation. The AdoConnection1.ConnectionTimeout is simply passed on to the Ado ConnectionObject attached to it, and that's on the "other side" of the COM interface, so if it doesn't respond how you want, there is, afaik, nothing you can do about it. Something in the machine's TCPIP settings may have an effect, but I'm not disturbing mine to find out ;=)

Update Prompted by another SO q, I retested my code and the minimum time for the AdoConnection to time out on my LAN has gone donwn to 2.8 seconds, without any hardware change or conscious change of software. Maybe the difference has been caused by something in a Win10 update.

procedure TForm1.TestConnectionTimeOut(Timeout : Integer);
var
  T1 : Integer;
begin
  T1 := GetTickCount;
  AdoConnection1.ConnectionTimeout := Timeout;
  try
    AdoConnection1.Connected := True;
  except
    ShowMessage(Exception(ExceptObject).Message + #13#10 + IntToStr(GetTickCount - T1));
  end;
end;

Exactly I wanted to do: within 5 seconds if the ADOconnection cannot connect, exit the function. I don't want to wailt 30 or 60 second. — Sayat Ertüfenk, Apr 01 '16 at 15:13
Well, I've tried my code in D7 .. Seattle and what the results tell me is that the AdoConnection will not time out in under about 9.2 seconds, regardless of what you set the value to be. The AdoConnection1.ConnectionTimeout is simply passed on to the Ado ConnectionObject attached to it, and that's on the "other side" of the COM interface, so if it doesn't respond how you want, there is, afaik, nothing you can do about it. — MartynA, Apr 01 '16 at 15:26

Delphi ADOConnection connection timeout property does not work

1 Answers1