Delphi - Database methods and events


1.TTable methods, properties and events
1.1 Navigating through a dataset
1.2 Searching in a dataset
1.3 Editing a dataset
1.4 Limiting the number of records with filtering
1.5 TTable events

2. TQuery methods, properties and events
2.1 Using the TQuery component

With Delphi you can program a small sophisticated database application without writing one line of  code.
In the Object Repository of Delphi you can use a wizard to make a database application in just 5 minutes.
You can build  programs with one table, but also complexer programs with related tables. (Master-Detail).
For the real complex applications you will need object Pascal.
This paper discusses the most common methods and events of the components as discussed in the previous paper of this tutorial
starting with the TTable component.

1. TTable methods, properties and events

The TTable component is an important database component, and communicates with the table on disk.

1.1 Navigating through a dataset

Before you can navigate through a dataset, you must first open it with the method Open.
Table1.Open; This method sets the property Active to true. With the method Close you can close a dataset.

With navigating through the dataset, we mean moving the so called cursor. The cursor points to the active record.
You can use this methods to navigate through a dataset :

With the methods listed above you access the records on an indirect manner. You never know which records shows up.
In the next paragraph we take a look at  methods to search for a specific record and make that record the active one.

1.2 Searching in a  dataset

Before we take a look at some examples we first must know how we can get a tables field value.
As discussed before you can access individually fields with the methods of type TField.
The fields in the list Fields can be accessed through their indexnumber as if it was an array.
The  indexnumbers are set in runtime and can be used as follows :
To read a field : Label1.Caption : = Table1.Fields[0].value;
This is not a very 'handy' way of doing things. It is easier to access a field through its fieldname.
This can be done by the FieldByName method.
The TTable Method FieldByName gives the value of a field as type TField.
The examples :

Reading a field value :
Table1.Open;//Open the dataset by setting the active property to true
aText := Table1.FieldByName('Name').AsString;
Table1.Close;

Searching with SetKey, GotoKey, FindKey, GotoNearest, FindNearest in Table1:

With Table1 do
Begin
  SetKey;
  FieldByName('NAME').AsString := EditSearch.Text;//Search for this text

Search with GotoKey :

  If GotoKey then 
    LabelResult.Caption := 'Found!';
  else
    LabelResult.Caption := 'Not found!' + FieldByName('NAME').AsString;

Search with FindKey :

  If FindKey([EditSearch.Text]) then
    LabelResult.Caption := 'Found!;
  else
    LabelResult.Caption := 'Not found!' + FieldByName('NAME').AsString;

Search with GotoNearest/FindNearest :

  FindNearest([EditSearch.Text]); {of GotoNearest; both find always something}
  LabelResultaat.Caption := 'Found!' + FieldByName('NAME').AsString;

end;

Search with Locate :

if Locate('Name;Description;', VarArrayOf(['Borland', 'A nice tool']),
[loPartialKey]) then
 LabelResultaat.Caption := 'Found!, cursor has moved to this record ' + FieldByName('NAME').AsString;

1.3 Editing a  dataset

Here you'll find the methods for editing (adding and removing) a TTable :

A few code examples :

Adding a new record :

With Table1 do
Begin
  Insert;      //Set the table in the insert mode
  FieldByName('NAME').AsString := 'Delphi 4.0';//Fill in the values
  FieldByName('STUDY').AsBoolean := True;
  Post;
end;

Editing a record :

With Table1 do
Begin
  Edit;//Set the table in the edit mode
  FieldByName('NAME').AsString := 'Delphi 5.0';//Fill in the values
  Post;
end;

The methods edit and  insert give the database table another mode. (state. There are several modes.
With the property State (Table1.State) you determine the current state of the TTable.

property State: TDataSetState;

Underneath you'll find the mostly used states :

Besides this states there are a few internal states which are being used by Delphi itself. (See the Delphi Help)

Using the state property :

If Table1.State in [dsEdit,dsInsert] then
  Table1.Post;

1.4 Limiting the number of records with filtering

With filters you can limit the records (which are shown) in a dataset.
You can use two properties :

The property Filter can be used for simple 'filter' strings. like QUANTITY   > 100 etc.
Another way is to write an OnFilterRecord event, which can be used to make more complicated filters.
You can do this as follows :

Filtering a dataset with OnFilterRecord :

procedure TForm1.Table1FilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
  Accept := Table1.FieldByName('CATEGORY').AsString = 'Prog';
end;  //Shows alle records with field Category's value 'Prog'

Filtering is done record-by-record, so it is not very efficient. (Speed)
It may be better to use query's instead of filters. (We discuss queries later on)

1.5 TTable events

TTable has many events. They can't all be discussed in this paper.
Most TTable events have to do with the methods for editing the table.
Beneath you will find a short collection :
(For an overview off all the events see the Delphi help file)

Just try all the events to learn from it.

2. TQuery methods, properties and events

With a TQuery component you can use SQL - query's to limit the records in the dataset. With Structured Query Language (SQL) query's you can
for example filter the dataset on different values.

2.1 Using the TQuery component (see previous paper)

A TQuery component is similar to a TTable component, the difference is that a TQuery component uses a TTable component as source (the dataset)
and you can use SQL to limit the number of records in the dataset.
Below you find some TQuery properties and there meaning :

The Params property contains a list of parameters which are used in the SQL query. This parameters can be added in the SQL property with a leading ':'

TQuery SQL property example :

With Query1.SQL do
begin
  Add('SELECT * FROM Table');
  Add('WHERE');
  Add('NAME = :aParam'); // of NAME = 'Delphi'
end;

Assign a value to a parameter in code :
Query1.ParamByName('aParam').AsString := 'Delphi';
Query1.Open;

Params gives the possibility to build very flexible query's.
The method Close closes a Query.
The resultset of a query can be shown in the normal dataware components like a DBGrid.

Again the overview :

Finally ::
The database part of Delphi is huge.With this paper we've seen a very little part of it.
In the final paper of this tutorial we'll build a database application.

Click here for the next paper in this tutorial :  Delphi - Building a database application

Top