Queries

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

Lookup | Locate | First, FindFirst | SQL | DBLookupComboBox | Variant Arrays | Search Warning


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 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.

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

in Delphi 6 and above.

One solution is to use conditional source code such as this

If more than one unit is included, structure the source more like this 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. See KADao's KADaoCommonDirectives.pas for a nice include file and more information.

Another approach is to test for version 5 or below.

Hopefully, this will allow your code to work with future version without having to modify the source. 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.


Author: Robert Clemenzi - clemenzi@cpcug.org
URL: http:// cpcug.org / user / clemenzi / technical / Databases / Delphi / Queries.html