Interbase and standard database components

Problem/Question/Abstract:

How do I use an Interbase database with standard database controls?

Answer:

I have struggled for hours to get to grips with this. Manuals available aren't very clear on this.

What you need:

Set BDE alias for Database (BDE Administrator)
Add table (tblDepartm) ,Datasource,Database and UpdateSQL component to a dataform.
Set the Database field of the database to the name of the database component (Not to the Alias of the BDE, this is cross linked through the database component)
Add Data aware component to main form. Set there source to Datasource above.
Add Post button on the main form. (Set the button to post the table i.e. table1.post)
On the UpdateSQL component right click to open the SQLupdate editor. Generate the code for the various functions (Select, update, delete);
Set the tables UpdateObject to the UpdateSQL component.
Set CachedUpdates to true on the table.

How does it work. Each table has an UpdateSQL component associated with it. This handles updates to a live record set. Updating a live recordset of a Interbase database wouldn't be possible without a UpdateSQL component. The code in the UpdateSQL component handles the changes to the underlying table of the table component. When updating a table (pressing the post button) the Onupdaterecord event (below) is called which then tells the UpdateSQL component which update kind to use (insert,delete,update). Once this is applied to the UpdateSQL component the changes are made to the local cached dataset. Remember these updates will not be applied until the Database component's Applyupdates method is called. (Below)

Type the following into the Onupdaterecord event of the Table:

procedure TDataform.tblDepartmUpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
  {This is a confusing concept, but the Apply below tells the
  update component which SQL commands to use , insert , alter or select and
  when you applyupdates on the database for this dataset the correct
  sql statement is utilised and the dataset is updated. See on closequery
  method of Department form}
  try
    uptDepartm.Apply(Updatekind);
    UpdateAction := uaApplied;
  except
    UpdateAction := uaFail;
  end;

end;

This is the code to close the main form. It checks if any updates are pending on the database and then applies the updates. Only now is the underlying table updated on the server. Keep in mind that you have to close and open the tables to reflect the changes on the client side because a refresh is not allowed on a DBMS like Interbase.

procedure TfrmDepart.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
var
  Res: integer;
begin
  with Dataform do
    if tblDepartm.UpdatesPending then
    begin
      Res := messagedlg('Save changes ?', mtInformation, mbYesNoCancel, 0);
      if Res = mrYes then
      begin
        dbMTS.Applyupdates([tblDepartm]);
        {Somehow the first time you applyupdates on a dataset it is very
        slow, but thereafter it is lightning fast. Must be that the BDE
        only caches the info once you call the applyupdates method for the
        first time!}
        tblDepartm.close;
        tblDepartm.open;
      end;
      Canclose := Res <> mrCancel;
    end;
end;