How can I do this in Delphi?

Question

I am converting an application from BDE to ADO.

Under the BDE, if a query was Open and you called "Sql.Clear", it would automatically close the dataset.

However, this is not the case under TADOQuery, where it will raise an exception "Operation cannot be performed on a closed dataset".

A lot of our legacy code relies on the old BDE behavior, so I get lots of runtime errors from code like the example below.

I want to override the Sql.Clear method of my TADOCustomQuery class, so that it will include a ".Close" command. How can I do that?

The ".Clear" method is on the SQL property, which is of type TWideStrings. My real question is: how can I override the TWideStrings.Clear method on a descendant of TADOQuery?

I have a customized TADOQuery component already, with this for the SQL property:

property SQL: TWideStrings read GetSQL write SetSQL;

Here is some code to demonstrate the problem I'm having:

procedure TForm1.btnBDEDemoClick(Sender: TObject);
var
  qryBDE: TQuery;
begin
  //Both queries complete with no problem
  qryBDE := TQuery.Create(nil);
  try
    with qryBDE do begin
      DatabaseName := 'Test';  //BDE Alias
      Sql.Clear;
      Sql.Add('SELECT SYSDATE AS CURDAT FROM DUAL');
      Open;
      ShowMessage('the current date is: ' + FieldByName('CURDAT').AsString);

      Sql.Clear;  //<<<<<NO ERRORS, WORKS FINE
      Sql.Add('SELECT SYSDATE-1 AS YESDAT FROM DUAL');
      Open;
      ShowMessage('And yesterday was: ' + FieldByName('YESDAT').AsString);
    end;  //with qryBDE
  finally
    FreeAndNil(qryBDE);
  end;  //try-finally
end;

procedure TForm1.btnADODemoClick(Sender: TObject);
const
  c_ConnString = 'Provider=OraOLEDB.Oracle.1;Password=*;'+
    'Persist Security Info=True;User ID=*;Data Source=*';
var
  adoConn: TADOConnection;
  qryADO: TADOQuery;
begin
  //First query completes, but the second one FAILS
  adoConn := TADOConnection.Create(nil);
  qryADO := TADOQuery.Create(nil);
  try
    adoConn.ConnectionString := c_ConnString;
    adoConn.Connected := True;
    with qryADO do begin
      Connection := adoConn;
      Sql.Clear;
      Sql.Add('SELECT SYSDATE AS CURDAT FROM DUAL');
      Open;
      ShowMessage('the current date is: ' + FieldByName('CURDAT').AsString);

      Sql.Clear;//<<<<<<<<===========ERROR AT THIS LINE
      Sql.Add('SELECT SYSDATE-1 AS YESDAT FROM DUAL');
      Open;
      ShowMessage('And yesterday was: ' + FieldByName('YESDAT').AsString);
    end;  //with qryADO
  finally
    FreeAndNil(qryADO);
    FreeAndNil(adoConn);
  end;  //try-finally
end;

Answer 1

Of course, you can create a subclass of TAdoQuery that overwrites the Clear method. But I think it is a bad practice.

Its better to change all the queries. It is maybe some work but in the end it pays of.

If you look at the example of TAdoQuery in the help:

ADOQuery := TADOQuery.Create(Self);
ADOQuery.Connection := ADOConn;
ADOQuery.SQL.Add(SQLStr);

{ Update the parameter that was parsed from the SQL query: AnId }
Param := ADOQuery.Parameters.ParamByName('AnId');
Param.DataType := ftInteger;
Param.Value := 1;

{ Set the query to Prepared - will improve performance }
ADOQuery.Prepared := true;

try
  ADOQuery.Active := True;
except
  on e: EADOError do
  begin
    MessageDlg('Error while doing query', mtError,
                [mbOK], 0);

    Exit;
  end;
end;

You see it is a bit more different than the BDE version.

So it is probably best to create an ExecuteSQL function (first for the BDE) and then rewrite it to be used by ADO.

Answer 2

what version of delphi are you using? i tried to replicate this, but it works just fine in delphi 2009 - no errors reported and it returns the data i expect.

thanks don

Answer 3

The problem is that your dataset is open when you issue the clear. For the ADODataset, the property is wired to update the underlying ADO dataset, and when it changes with the dataset open the exception is raised.

All that you have to do is put a dataset close right before your clear and it will all operate properly.

with qryADO do 
  begin      
    Connection := adoConn;      
    Sql.Clear;      
    Sql.Add('SELECT SYSDATE AS CURDAT FROM DUAL');      
    Open;      
    ShowMessage('the current date is: ' + FieldByName('CURDAT').AsString);
    qryADO.close; // <=== line added to close the database first.
    Sql.Clear;     
    Sql.Add('SELECT SYSDATE-1 AS YESDAT FROM DUAL');      
    Open;      
    ShowMessage('And yesterday was: ' + FieldByName('YESDAT').AsString);    
  end;  //with qryADO

EDIT As an alternative you could create a new form method named SQLCLEAR which looks like the following:

function TYourFormOrDataModule.SqlClear;
begin
  qryAdo.Close;
  qryAdo.Sql.Clear;
  qryBde.Sql.Clear;
end;

and then do a search and replace for " SQL.Clear" to "SqlClear". But I prefer the method of performing the close in my original answer as it is more consistant and will be much easier to maintain long term. Using a tool like gExperts to find all of the instances of Sql.Clear and insert a qryAdo.Close before it is trivial...even if there are a few hundred instances.

Answer 4

This was not a feature of the BDE per se. If you look at the source that ships with Delphi you will see that the behavior you described is implemented on TQuery.SQL's SetQuery method:

procedure TQuery.SetQuery(Value: TStrings);
begin
  if SQL.Text <> Value.Text then
  begin
    Disconnect;
    SQL.BeginUpdate;
    try
      SQL.Assign(Value);
    finally
      SQL.EndUpdate;
    end;
  end;
end;

While TADOQuery's SetQuery is simply:

procedure TADOQuery.SetSQL(const Value: TWideStrings);
begin
  FSQL.Assign(Value);
end;

Why Borland/Codegear decided not to implement it the same is beyond me. Implementing TQuery's SetQuery in your custom ADOQuery should give you the behavior you desire.

Answer 5

Instead of you I'd do like this: check with someone which has D2009 if the behavior is really fixed as Don says - for ex. send him (or to another which has D2009) a test case. If the behavior in D2009 is fixed then the problem is simple.

Copy the ADODB.pas in your project directory. Modify the file in order to have the desired behavior (eg. change the SetSQL method). Recompile. It should work. This will give you time for an eventual upgrade to D2009 when you can remove the old, customized ADODB.pas from your project.

HTH.

Answer 6

Update

I implemented skamradt's solution by writing a small utility to automatically update all our source code. It worked like this:

1 - Recursively get a list of all .PAS files in our project folders

2 - Apply this procedure to all of those files:

procedure ApplyChange(filename: string);
const
  c_FindThis = 'SQL.CLEAR';
var
  inputFile, outputFile: TStringList;
  i, postn, offset: integer;
  newline: string;
begin
  inputFile := TStringList.Create;
  outputFile := TStringList.Create;
  offset := 0;
  try
    inputFile.LoadFromFile(filename);
    outputFile.Assign(inputFile);  //default: they are the same

    for i := 0 to inputFile.Count - 1 do begin
      {
      whenever you find a "Sql.Clear", place a new line before it,
      which consists of everything up to the "Sql.Clear" (which may
      just be whitespace), plus the "Close" command.
      //}
      postn := Pos(c_FindThis,Uppercase(inputFile[i]));
      if (0 < postn) then begin
        newline := Copy(inputFile[i],1,postn-1) + 'Close;';
        outputFile.Insert(i+offset,newline);
        Inc(offset);
      end;
    end;

    //overwrite the existing file with the revised one
    outputFile.SaveToFile(filename);
  finally
    FreeAndNil(inputFile);
    FreeAndNil(outputFile);
  end;  //try-finally
end;