Databases - Delphi and IBDataset

Using IBDataset

With Delphi 5, Firebird tables should be accessed via an IBDataset instead of an IBTable - there is a significant speed difference.

Basic Form | SQL | Read a Value | Insert/Update | References

Basic Form

On the form place

IBDatabase

Connect this to the database (*.fdb file) and the transaction

IBTransaction

Verify that this is connected to the IBDatabase

IBDataset

Right click - select Edit SQL

Select a table from the list
Under Fields, double click the asterisk (*)
If it is mixed case, place the the table name in double quotes
Do not add a where clause that uses parameters (identifiers that start with a colon)

Right click - select Dataset Editor... (for Delphi 5, this requires an upgrade)

Highlight the appropriate key fields
Check Quote Identifiers - required only if table or field names use mixed case
Press Generate SQL

Note: I personally prefer to use mixed case table and field names ... I think that they are easier to read.

SQL

These are typical SQL statements (the last 4 are generated by the Dataset Editor...) - most of the fields are removed for simplicity.

SelectSQL
select * from "Sphere_Port_Details_tbl"
InsertSQL
insert into "Sphere_Port_Details_tbl" ("Sphere_Port_Details_tbl"."Port_BC", "Sphere_Port_Details_tbl"."Sample_BC", "Sphere_Port_Details_tbl"."RH_Target", "Sphere_Port_Details_tbl"."Dry_Flow", "Sphere_Port_Details_tbl"."ModifiedDate") values (:"Port_BC", :"Sample_BC", :"RH_Target", :"Dry_Flow", :"ModifiedDate")
ModifySQL
update "Sphere_Port_Details_tbl" set "Sphere_Port_Details_tbl"."Sample_BC" = :"Sample_BC", "Sphere_Port_Details_tbl"."RH_Target" = :"RH_Target", "Sphere_Port_Details_tbl"."Dry_Flow" = :"Dry_Flow", "Sphere_Port_Details_tbl"."ModifiedDate" = :"ModifiedDate" where "Sphere_Port_Details_tbl"."Port_BC" = :"OLD_Port_BC"
DeleteSQL
delete from "Sphere_Port_Details_tbl" where "Sphere_Port_Details_tbl"."Port_BC" = :"OLD_Port_BC"
RefreshSQL
Select "Sphere_Port_Details_tbl"."Port_BC", "Sphere_Port_Details_tbl"."Sample_BC", "Sphere_Port_Details_tbl"."RH_Target", "Sphere_Port_Details_tbl"."Dry_Flow", "Sphere_Port_Details_tbl"."ModifiedDate" from "Sphere_Port_Details_tbl" where "Sphere_Port_Details_tbl"."Port_BC" = :"Port_BC"

Read a Value

This is one way to get a value from a specific record

function TSphere_Status_DataModule.Get_Port_Configuration(Port_BC: string): string;
var
  tbl : TIBDataSet;
begin
  // This returns the associated *Parameters* field
  tbl := IBDataSet_Sphere_Port_Config_tbl ;

  if tbl.Transaction.Active then tbl.Transaction.Commit;
  if tbl.Active = false then tbl.Open
                        else tbl.Refresh; // to get changes

  if tbl.Locate('Port_Barcode', VarArrayOf([Port_BC]), [loCaseInsensitive]) then begin
    result := tbl.FieldByName('Parameters').AsString;
  end else begin
    result := '';
  end;
end;  // Get_Port_Configuration(Port_BC)

Insert/Update

This code will update an existing record ... or create a new record if necessary. (Most of the parameters were removed for clarity - the original function accepted 35 parameters.)

function TSphere_Status_DataModule.Update_Port_Detail_Record(
  Port_BC, Sample_BC: string;
  ModifiedDate      : TDateTime;
  RH_Target         : Single;
  Dry_Flow          : integer;
    ): boolean;
var
  tbl : TIBDataSet; // This allows a shorter name to be used in the code
begin

  tbl := IBDataSet_Sphere_Port_Details_tbl ;

  if tbl.Active = false then tbl.Open;

  if tbl.Locate('Port_BC', VarArrayOf([Port_BC]), [loCaseInsensitive]) then begin
    tbl.Edit
  end else begin
    tbl.Insert;
    tbl.FieldByName('Port_BC')   .AsString   := Port_BC; // not set for edit
  end;

  tbl.FieldByName('Sample_BC')   .AsString   := Sample_BC;

  tbl.FieldByName('ModifiedDate').AsDateTime := ModifiedDate;

  tbl.FieldByName('RH_Target')   .AsFloat    := RH_Target;

  tbl.FieldByName('Dry_Flow')    .AsInteger  := Dry_Flow;

  tbl.Post;

  tbl.Transaction.Commit;

  result := true; // if there is an error, this is never executed

end;  // Update_Port_Detail_Record

Note: I normally line up the dots and assignment operators as shown above ... I think that this makes the code easier to read.

Note: This table has only one record per key - the data is updated once every 6 minutes.

References

Introduction To InterBase Express (IBX) by Bill Todd is a must read. It actually explains how to use the IBX components ... in particular IBDataSet. Notice that the IBDataSet Dataset Editor is NOT available in Delphi 5 (until you update the IBX components ... by default, the editor is available with Delphi 6). (Notice that, since the article never indicates which version of Delphi it applies to, there may be additional differences.)
Notice that the Dataset Editor combo box used to select a table will not work until the SelectSQL property is set and a transaction is assigned. If you use mixed case, dialect 3 tablenames, don't forget to place them in double quotes.

Author: Robert Clemenzi - clemenzi@cpcug.org

Using IBDataset

Basic Form

SQL

Read a Value

Insert/Update

References

URL: http:// cpcug.org / user / clemenzi / technical / Databases / Delphi / IBDataset.html