Databases - Delphi - KA Dao Queries

KA Dao Queries

KA Dao is a freeware component that provides access to MS Access without using the BDE.

However, the provided help is a little weak. This page helps to fill in a few holes. Since both TKADaoTable and TTable are derived from TDataSet, there are many similarities.

TTable - TDBDataSet - TBDEDataSet - TDataSet - TComponent
TKADaoTable - TDataSet - TComponent

This documents covers those query techniques that are NOT the same.

Note:

SetFindData

Functions that Work the Same

These query-type functions work the same for TKADaoTable and TTable.

Lookup(KeyFields, KeyValues, ResultFields): Variant;: Returns the values in a variant array
FindFirst, FindNext, FindLast, FindPrior: These are almost identical - for TKADaoTable, SetFindData must be called first
Locate(KeyFields, KeyValues, Options): Boolean;: Tries to change the active record
For TDataSet, the KeyFields are separated by commas
For TKADaoTable, use semicolons

Find_First, et al.

Find_First is a type of query used with a TKADaoTable.

Find_First finds the first record - related commands Find_Next Find_Prior Find_Last

Function   Find_First(const KeyFields: string; const KeyValues: 
                               Variant; Options: TLocateOptions):Boolean;

KeyValues - variant array
KeyFields - semicolon delimited string of field names
Options are same as Borland's Ttable.TLocateOptions - (loCaseInsensitive, loPartialKey)

  // This function just tests to see if the ID is in the table
function TUserDataModule.Get_User_ID(User_ID: string): string;
var
 KeyValues: Variant;
 flag : boolean;
begin
  Result := ''; // null string means not found
  KeyValues := VarArrayCreate([0,0], varOleStr);
  KeyValues[0] := User_ID ;

  flag := KADaoTable_Users.Find_First('UserID', KeyValues, [loCaseInsensitive]);
  if flag then begin
     result := User_ID;
  end;
end;

FindFirst, et al.

Find_First is a type of query used with all TDataset descendants ... including TKADaoTable. The only difference is that before calling one of these functions you must call SetFindData method.

Function FindFirst: Boolean;
Function FindLast : Boolean;
Function FindNext : Boolean;
Function FindPrior: Boolean;

This example (from the KA Dao help) should show the data on a form.

Var
 A:Variant;
Begin

  A:=StrToDate('24.4.2000');
  KADaoTable1.SetFindData('Date',A,[]);

  KADaoTable1.FindFirst;
  ShowMessage('FindFirst');

  KADaoTable1.FindNext;
  ShowMessage('FindNext');

  KADaoTable1.FindPrior;
  ShowMessage('FindPrior');

  KADaoTable1.FindLast;
  ShowMessage(FindLast);

End;

Select query

There are basically 2 types of queries

Those that do work - Insert, Add, Delete
Those that return a recordset - Select

Strings inside a query are surrounded with double quotes because the target is an MS Access database ... for a real SQL database, single quotes should be used.

Set SQL to the following string (the trailing semicolon is optional)

Select Sample_ID from Samples_tbl 
 Where SampleHolder="NS123"
   and SamplePosition="03" ;

For

KADaoTable_Query.ExecSQL(SQL);

the error is

Project xyz raised exception class EOleException with message 'Cannot execute a select query'. Process stopped. Use Step or Run to continue.

function TSamplesDataModule1.Get_Sample_ID(SampleHolder,
  SamplePosition: string): integer;
var
  SQL : TStringList;
begin
  Result := 0;
  SQL := TStringList.Create;
  SQL.Add('Select Sample_ID from Samples_tbl ' +
    'Where SampleHolder="'   + SampleHolder   + '" ' +
    '  and SamplePosition="' + SamplePosition + '" ' +
    ' ; ' );
  KADaoTable_Query.ExecSQL(SQL);
  SQL.Free;

  result :=  KADaoTable_Query.FieldByName('Sample_ID').AsInteger;
end;

Using a query as the table name

Here is another approach

  KADaoTable1.Active   := False;
  KADaoTable1.TableName:= 'SELECT * FROM [bnb-1999];';
  KADaoTable1.TableType:= dbOpenDynaset;
  KADaoTable1.Active   := True;

This works

  s :=
    'Select Sample_ID from Samples_tbl ' +
    ' Where Sample_Holder_Barcode="'   + SampleHolder   + '" ' +
    '   and Sample_Holder_Posistion="' + SamplePosition + '" ' +
    '   ' ;

  KADaoTable_Query.Active    := False;
  KADaoTable_Query.TableName := s;
  KADaoTable_Query.TableType := dbOpenDynaset; // must include DAOApi
  KADaoTable_Query.Active    := True;
  SQL.Free;

  result :=  KADaoTable_Query.FieldByName('Sample_ID').AsInteger;

Implementation

KADaoTable_Query.ExecSQL(SQL);

eventually calls

F_Database.CoreDatabase.Execute(F_ComposeSQL(SQL),SQLExecutionType);

Which explains why select cannot be executed.

On the otherhand, using a select query as a table name eventually calls

F_DaoTable:=Database.CoreDatabase.OpenRecordset(TabN,TabType,Options,LoType);

which is ok. Note that dbOpenDynaset is defined in DAOApi, which, therefore, must be included in the uses clause.

KADaoTable SQL Property

The KADaoTable SQL Property provides another way to perform a select query.

In this example, :Group_ID_Parameter is read from the Params property.

Set the KADaoTable.SQL property to

  SELECT Users_tbl.UserID, Group_User_tbl.Group_ID, [Last_Name]+',  '+[First_Name] AS Name
    FROM Users_tbl INNER JOIN Group_User_tbl ON Users_tbl.UserID = Group_User_tbl.User_ID
   WHERE (((Group_User_tbl.Group_ID)=:Group_ID_Parameter))
ORDER BY [Last_Name]+',  '+[First_Name];

Notice that where clauses are normally formatted with double quotes around a string. However, when parameter substitution is used, the double quotes must NOT be present (the object handles it correctly).

   WHERE (((Group_User_tbl.Group_ID)="string"))
       vs
   WHERE (((Group_User_tbl.Group_ID)=:Group_ID_Parameter))

Assuming that KADaoTable.ParamCheck is true (the default), a TParam property is automatically created for each phrase in the SQL property that starts with a colon.

The following sucessfully sets the parameter and queries the database.

  UserDataModule.KADaoTable_Users_View.Active := false;
  UserDataModule.KADaoTable_Users_View.Params.ParamByName('Group_ID_Parameter').AsString 
             := 'Sph_G';
  UserDataModule.KADaoTable_Users_View.Active := true;

The help says to use the open method, but that does not work.

KADaoTable Filter Property

KADaoTable1.Filter     := '[NumericField] > 100 And [TextField] Like "Kiril*"'
KADaoTable1.Filtered   := True;

When Filtered is True and KADaoTable.Filter text is changed the Table automatically refreshes its contents.

Author: Robert Clemenzi - clemenzi@cpcug.org