How can I do this in Delphi?

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;

From stackoverflow

• 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.

  JosephStyons : Well, maybe I'm being dense here.. but the Clear method is on the SQL property, which is of type TWideStrings. How can I override the TWideStrings.Clear method on a descendant of TADOQuery? Perhaps that should have been my real question....

  Gamecat : Sorry, misread your question. No you can't override the Clear. But then again it shouldn't give the error message so something else is wrong.

• 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

  JosephStyons : I'm using Delphi 2007 and Delphi 5, which both exhibit the same problem. I find it interesting that D2009 does not have the same problem.

• 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.

  JosephStyons : Yes, I know that... but I am hoping to avoid adding this line to 100s of existing locations in code.

• 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.

  JosephStyons : You are right, and I've looked at that code too. However, modifying my Custom ADO SetQuery to do it just like TQuery does not help (I used exactly the same code, except for a "Close" instead of "Disconnect")

• 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.

• 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;