Using dbExpress - conclusions

I've just discovered that if one googles "Porting BDE", the first few entries reference this blog! It transpires that someone has been following my jottings and posting them on the Firebird site. At the moment, I've converted about 20 forms, and the conclusions resulting from these conversions are becoming clear. I don't expect to post any more on the subject, unless I run into a staggering 'gotcha'.

When porting the contents of the database itself, the TSqlQuery should be used with the following statement: insert into values (:p1, :p2, :p3, etc). In the delphi code, one writes as follows:

with SqlQuery1 do
 begin
  close;
  parambyname ('p1').asinteger:= ...
  parambyname ('p2').asstring:= ...
  parambyname ('p3').asdate:= ...
  execsql;
 end;

A date parameter has to be passed AsDate, not AsDateTime. Apparently this is a bug in dbExpress.

In applications, I have discerned three different cases in which previously I would have used a BDE tquery component. These are:

1. For statements which update or delete from a table and for statements which return one or more values which will not be displayed via a data-aware component, a TSqlQuery component can be used which simply replaces the TQuery on almost a one-for-one basis.
   
2. For DBLookupComboBoxes, one can use the SimpleDataSet component, not forgetting to set the 'connection' property to the global connection. This component is considered to be deprecated and not to be used, although I have found that for this simple use (and also for a dbgrid from which values are chosen), it's easier to use this than the 'holy trinity'.
3. Whenever data-aware components are used, the 'trinity' must serve them. The trinity consists of: a SQLDataSet (which is linked to the global sql connection), a DataSetProvider and a ClientDataSet.

I have also developed a new template for data editing forms. Once I would write as follows:

function TEditDiag.Execute (n: longint): boolean;
begin
 with qEditDiag do
  begin
   close;
   if n = -1 then
    begin
     open;
     append;
     caption:= 'Adding'
    end
  else
    begin
     params[0].asinteger:= n;
     open;
     caption:= 'Editing';
    end;

   edit;
   if showmodal = mrOK then
    begin
     result:= true;
     post
    end
   else
    begin
     cancel;
     result:= false
    end;
   end
 end;

The above code takes advantage of the fact that the BDE has autoincrement fields, and so one doesn't have to explicitly create a new key. FB does not have such fields, and so the use of a generator is required. The above code is now replaced with the following:

function TEditDiag.Execute (n: longint): boolean;
var
 gen: longint;

begin
 sdsEditDiag.commandtext:= 'select * from minidiag where id = ' + inttostr (n);
 with qEditDiag do // clientdataset
  begin
   open;
   if n = -1 then
    begin
     caption:= 'Adding';
     insert
    end
   else caption:= 'Editing';

   edit;
   if showmodal = mrOK then
    begin
     result:= true;
     if n = -1 then
      begin
       with qGetID do // get new id number from generator
        begin
         open;
         gen:= fieldbyname ('gen').asinteger;
         close
        end;

       FieldByName ('id').asinteger:= gen
      end;
     post;
     applyupdates (0)
    end
   else
    begin
     cancel;
     result:= false
    end;
   end
 end;