Databases - Delphi Queries

Queries

Delphi provides many ways to query a database, unfortunately, there is no tutorial to tie them all together.

Lookup

Lookup(KeyFields, KeyValues, ResultFields): Variant;

Returns a single record in a variant array

Only finds the first matching record. There is no documented way to step through the records with the same query conditions.

Locate

Locate(KeyFields, KeyValues, Options): Boolean;
LocateNext(KeyFields, KeyValues, Options): Boolean;

Tries to change the active record

First, FindFirst

First, Next, and Last use the current filter property (when Filtered is true). You must use bof and eof to determine if a record is available.

According to the Delphi 5 help, FindFirst, FindNext, and FindLast are suppose to use the current filter property. However, for the IB (Interbase / Firebird) components, these virtual functions are NOT implemented and, therefore, always return false.

SQL

Just a couple of examples ... for Firebird

select * from "tableName"

select * from "tableName"
where "field" = 'string'             Exact case sensitive match

select * from "tableName"
where "field" like 'string%'         Case sensitive wildcard match
                                      % - any number of characters
                                      _ - any single character (underscore)

select * from "tableName"
where "field" containing 'string'    Case insensitive wildcard match
                                      Wildcard characters are not used

MS Access searches are not case sensitive.

DBLookupComboBox

The DBLookupComboBox provides a way to select the value for a data field. It allows you to see one value (descriptive text) while selecting another (the actual value stored in the table).

I had an application where I had several DBLookupComboBox components connected to the same table and where the permitted values were controled via a "type" field. The following code controlled the options displayed.

procedure TAdd_Port_Form.Filter_DBLookupComboBoxDropDown(Sender: TObject);
begin
  SamplesDataModule.Barcodes_DataSource.DataSet.Filter :=
       'Barcode_Type_ID= ''F_Hld''';
  SamplesDataModule.Barcodes_DataSource.DataSet.Filtered := true;
end;

procedure TAdd_Port_Form.FormHide(Sender: TObject);
begin
  SamplesDataModule.Barcodes_DataSource.DataSet.Filtered := false;
  SamplesDataModule.Barcodes_DataSource.DataSet.Filter   := '';
end;

That worked perfectly with KADAO components (and MS Access), but was a complete disaster with the IB components (Firebird).

Variant Arrays

A number of qurey type commands use Variant Arrays.

It appears that Variants (and related commands) are built-in to Delphi 5, but require something like

uses
  Variants;

in Delphi 6 and above.

One solution is to use conditional source code such as this

{$IFDEF VER140}  // Delphi 6
  {$DEFINE D6UP}
{$ENDIF}
{$IFDEF VER150}  // Delphi 7
  {$DEFINE D6UP}
  {$DEFINE D7UP}
{$ENDIF}

{$IFDEF  D6UP}
uses
  Variants; // Delphi 6 only 
{$ENDIF}

If more than one unit is included, structure the source more like this

uses
  {$IFDEF  D6UP}Variants, {$ENDIF} Forms, Dialogs;

Note that this code needs to test for each version of Delphi to work with that version. This means that each time a new version is released, you need to modify your source code.

VER130 - Delphi 5
VER140 - Delphi 6
VER150 - Delphi 7

See KADao's KADaoCommonDirectives.pas for a nice include file and more information.

Another approach is to test for version 5 or below.

{$IFDEF VER130}       // Delphi 5
  {$DEFINE mc_LE_D5}  // Less than or equal to Delphi 5
                      // "mc" helps to avoid conflicts
{$ENDIF}

{$IFNDEF mc_LE_D5}
uses
  Variants; // Delphi 6 and above only 
{$ENDIF}

Hopefully, this will allow your code to work with future version without having to modify the source.

var
 v : variant;
begin
  v := VarArrayCreate([0, 4], varVariant); // 1 dimensional array with 5 elements

See TVarData type for a list of valid element types.

Search Warning

Don't rely on Windows Explorer search tools (such as Find or Agent Ransack) to search for *.dfm files that contain query strings because Delphi breaks long strings into 64 character chunks. In the following example, Element_Record_int is not found eventhough it is part of the actual query string.

    QueryDefSQLText.Strings = (
      
        'SELECT Batches_tbl.Batch_ID, Batches_tbl.Batch_Name, Batches_tbl' +
        '.Description, Batches_tbl.SOP, SOPs_tbl.Procedure_ID, SOPs_tbl.N' +
        'ame, SOPs_tbl.Description, Val([Element_Record]) AS Element_Reco' +
        'rd_int, Experiment_Detail_View.Element_Table, Experiment_Detail_' +
        'View.Experiment_ID'
      
        'FROM Experiment_Detail_View INNER JOIN (Batches_tbl LEFT JOIN SO' +
        'Ps_tbl ON Batches_tbl.SOP=SOPs_tbl.SOP_ID) ON Experiment_Detail_' +
        'View.Exp_ID=Batches_tbl.Batch_ID'
      'WHERE (((Experiment_Detail_View.Element_Table)="Batches_tbl"));')

         123456789 123456789 123456789 123456789 123456789 123456789 123456789

Author: Robert Clemenzi - clemenzi@cpcug.org