Query Timeout

[tommytran]

Hi,

I'm using Borland C++ Builder 6.0 and Zeoslib with protocol ADO to access MS SQL Server 2005. The default query timeout value of ADO is 30 seconds and I want to change this value.
The components TADOConnection and TADOCommand of BCB6 have one property CommandTimeout which allow to set the query timeout value. I searched on internet but I don't find any way to set the query timeout value in Zeoslib. Do anyone know how to set this query timeout value in zeoslib? Please help me!

Thanks a lot.

Tommy Tran

[tommytran]

I found the property CommandTimeout of the interface Connection15 in the file ZPlainAdo.hpp, I think this property is used to set Query Timeout but I don't know how to set it . I tried to set it as follows:

IZConnection *izConnection = zconnMSSql->DbcConnection;
IZAdoConnection *izAdoConnection = (IZAdoConnection *)izConnection;
izAdoConnection->GetAdoConnection()->CommandTimeout = 60;

zconnMSSql is an instance of TZConnection.

the above code is crashed with exception EAccessViolation.

Thanks a lot.

Tommy Tran

[mdaems]

Hi Tommy,

You'll have to change the Zeoslib source code to enable this. No Zeoslib developers for the ADO protocol known to me foor tehe moment, so I'm afraid you'll have to do and test it yourself, I'm afraid. If you get it working, send us the patch!!

You could add this to TZAdoConnection.Open (ZdbcAdo.pas), I suppose. There you can (should be able to )read the connection options and call the function you tried above. For an example see the mysql (timeout) implementation in TZMysqlConnection.Open. I'm not sure whether you will have to do this before or after the actual connection is opened. Read about that in the ADO documentation.

The code you tried doesn't work because the IZConnection to IZAdoConnection you do doesn't work. Don't ask me why, but the same thing was experienced here : http://zeos.firmos.at/viewtopic.php?t=1746

Mark

Mark

[tommytran]

Hi mdaems,

Thanks for your responses and information very much. I'll try to do your suggestion but I'm not sure I can.

Thanks a lot.

Tommy Tran

[tommytran]

Hi Mark,

I tried to add the code for the query timeout value to TZAdoConnection.Open (ZdbcAdo.pas) as follows:

procedure TZAdoConnection.Open;
var
LogMessage: string;
begin
if not Closed then Exit;

LogMessage := Format('CONNECT TO "%s" AS USER "%s"', [Database, User]);
try
if ReadOnly then
FAdoConnection.Set_Mode(adModeRead)
else
FAdoConnection.Set_Mode(adModeUnknown);
// <--Tommy Added
FAdoConnection.CommandTimeout := 60;
// -->
FAdoConnection.Open(Database, User, Password, -1{adConnectUnspecified});
FAdoConnection.Set_CursorLocation(adUseClient);
DriverManager.LogMessage(lcConnect, FPLainDriver.GetProtocol, LogMessage);
except
on E: Exception do
begin
DriverManager.LogError(lcConnect, FPlainDriver.GetProtocol, LogMessage, 0, E.Message);
raise;
end;
end;

inherited Open;

FAdoConnection.IsolationLevel := IL[GetTransactionIsolation];
ReStartTransactionSupport;
end;

However, the above code didn't effect and the query timeout is still the default value of 30 seconds and this also occurs for TADOConnection component. So I think maybe this is the problem of C++ Builder, and I decided to add the code for constructor TZAdoPreparedStatement.Create() (ZDbcAdoStatement.pas) as follows:

constructor TZAdoPreparedStatement.Create(PlainDriver: IZPlainDriver;
Connection: IZConnection; SQL: string; Info: TStrings);
begin
FAdoCommand := CoCommand.Create;
FAdoCommand.CommandText := SQL;
inherited Create(PlainDriver, Connection, SQL, Info);
FAdoCommand._Set_ActiveConnection((Connection as IZAdoConnection).GetAdoConnection);
//<--Tommy added
FAdoCommand.CommandTimeout := 60;
//-->
FAdoCommand.Prepared := True;
end;

and this worked.

For now, I want to add a property or fix some codes that allow user to set the query timeout value at runtime, but I don't know how to do that. Could you give me some comments?

Thanks a lot.

Tommy Tran

[mdaems]

I think you should just move your first change below
FAdoConnection.Open(Database, User, Password, -1{adConnectUnspecified});
In that case you will probably not need the second change anymore.

Then have a look at this code to make it configurable (copied from mysql driver:

Code: Select all

    ConnectTimeout := StrToIntDef(Info.Values['timeout'], 0);
    if ConnectTimeout >= 0 then
    begin
      FPlainDriver.SetOptions(FHandle, MYSQL_OPT_CONNECT_TIMEOUT,
        PChar(@ConnectTimeout));
    end;

When you add this kind of code you can just add a string to Connection.Properties like 'timeout=5'.

Please test it and I'll add your 5 lines of code to the code base.

Mark

[tommytran]

Hi Mark,

I'm sorry for my response late.
I added the code for the ADO query timeout as you commented and it worked. First I added the code into the procedure TZAdoConnection.Open; (ZDbcAdo.pas), but this code is not enough to the ADO query timeout (for this I also tested with components TADOConnection and TADOQuery in Borland C++ Builder 6, the property TADOConnection's CommandTimeout doesn't work, but TADOQuery's does), so I added more some code into constructor TZAdoPreparedStatement.Create(PlainDriver: IZPlainDriver; Connection: IZConnection; SQL: string; Info: TStrings); in the file ZDbcAdoStatement.pas and it worked as I wanted.

The following is the codes that I added:
1. file ZDbcAdo.pas

Code: Select all

procedure TZAdoConnection.Open;
var
  LogMessage: string;
  //<comment:TommyTran Date=2008-03-24>
  CommandTimeout: Integer;
  //</comment:TommyTran>
begin
  if not Closed then Exit;

  LogMessage := Format('CONNECT TO "%s" AS USER "%s"', [Database, User]);
  try
    if ReadOnly then
      FAdoConnection.Set_Mode(adModeRead)
    else
      FAdoConnection.Set_Mode(adModeUnknown);
    //<comment:TommyTran Date=2008-03-24>
    if (Info.Values['CommandTimeout'] <> '') then
    begin
      CommandTimeout := StrToIntDef(Info.Values['CommandTimeout'], 0);
      FAdoConnection.CommandTimeout := CommandTimeout;
    end;
    //</comment:TommyTran>
    FAdoConnection.Open(Database, User, Password, -1{adConnectUnspecified});
    FAdoConnection.Set_CursorLocation(adUseClient);
    DriverManager.LogMessage(lcConnect, FPLainDriver.GetProtocol, LogMessage);
  except
    on E: Exception do
    begin
      DriverManager.LogError(lcConnect, FPlainDriver.GetProtocol, LogMessage, 0, E.Message);
      raise;
    end;
  end;

  inherited Open;

  FAdoConnection.IsolationLevel := IL[GetTransactionIsolation];
  ReStartTransactionSupport;
end;

2. file ZDbcAdoStatement.pas

Code: Select all

constructor TZAdoPreparedStatement.Create(PlainDriver: IZPlainDriver;
  Connection: IZConnection; SQL: string; Info: TStrings);
//<comment:TommyTran Date=2008-03-24>
var
  CommandTimeout: Integer;
//</comment:TommyTran>
begin
  FAdoCommand := CoCommand.Create;
  FAdoCommand.CommandText := SQL;
  inherited Create(PlainDriver, Connection, SQL, Info);
  FAdoCommand._Set_ActiveConnection((Connection as IZAdoConnection).GetAdoConnection);
  //<comment:TommyTran Date=2008-03-24>
  if (Info.Values['CommandTimeout'] <> '') then
    CommandTimeout := StrToIntDef(Info.Values['CommandTimeout'], 0)
  else
    CommandTimeout := (Connection as IZAdoConnection).GetAdoConnection().CommandTimeout;
  FAdoCommand.CommandTimeout := CommandTimeout;
  //</comment:TommyTran>
  FAdoCommand.Prepared := True;
end;

I tested this code with the components TZConnection and TZQuery in Borland C++ Builder 6. The code works with the pair of name and value CommandTimeout=[QueryTimeout] in property TZConnection's and TZQuery's Properties.

I only know a bit of Delphi, so please help me to fix the code if they aren't suitable.

Thank you for your help very much, Mark.

Regards,
Tommy Tran

[mdaems]

Tommy,
Why didn't you put the new code one line lower? I was really thinking it would work like you expected : for all statements using the connection. I think you had to add the code to ZDbcAdoStatement because you can only set the CommandTimeout of an Opened Connection. Did you try that?

Code: Select all

    FAdoConnection.Open(Database, User, Password, -1{adConnectUnspecified});
    //<comment:TommyTran Date=2008-03-24>
    if (Info.Values['CommandTimeout'] <> '') then
    begin
      CommandTimeout := StrToIntDef(Info.Values['CommandTimeout'], 0);
      FAdoConnection.CommandTimeout := CommandTimeout;
    end;
    //</comment:TommyTran>

[tommytran]

Hi Mark,

I haven't tried this yet. I'll try and test it.

Regards,
Tommy Tran

[tommytran]

Hi Mark,

I'm sorry for my replying very late.

I tried to change the code as you showed above, but this don't get Timeout work. So I decided to use the code that I showed above and they have worked well for now.

Regards,
Tommy Tran

[rnaujack]

Hello,
to use statement_timeout (in miliseconds) with postgres, you have to
execute an SQL-command, which sets this parameter for the active connection:

Query_work.SQL.Text := 'SET STATEMENT_TIMEOUT TO 10000';
Query_work.ExecSQL;


I'd prefer to patch the zeos postgres source. So you can set the statement_timeout like the codepage:

ZConnection1.Properties.Add('statement_timeout=10000');


To patch the RC2-Version:
src\dbc\ZDbcPostgreSql.pas:

152a153
> FStatement_Timeout: string;
378a380
> FStatement_Timeout := Trim(Info.Values['statement_timeout']);
494a497,506
>
> if FStatement_Timeout <> '' then
> begin
> SQL := PChar(Format('SET STATEMENT_TIMEOUT TO ''%s''', [FStatement_Timeout]));
> QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
> CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
> FPlainDriver.Clear(QueryHandle);
> DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
> end;
>


I've tested it with delphi 7 and postgres 8.1
Rudolf