OnPrepareSQL Event |
Applies to
TIB_StatementLink
Declaration
TIB_StmtLinkEvent = Procedure(Sender: TIB_StatementLink; Statement: TIB_Statement ) of object;
Description
This is a very useful event to provide refinement at the time the SQL is
being prepared.
Any alterations made to the SQL property while this event is
being called will become a part of the SQL that is sent to the server but not
a part of the actual SQL stored in the SQL property of the associated
statement or dataset component.
Here is a few hints that will probably get you going:
Everytime a statement is prepared or re-prepared the OnPrepareSQL events are
triggered. This initiates a process that starts out with the base SQL from
the SQL property of the dataset. Then, as each phase of the prepare takes
place the base SQL is modified and adjusted to meet other factors of
importance.
For example, if there is a MasterLinks defined then parameters are added to
the WHERE clause in order to bind this dataset to a master dataset. If there
is JoinLinks defined then these are added to the WHERE clause. If there are
any datalinks that contain SearchBuffer criteria then these items are
appended to the WHERE clause as well. If there is an OrderingItems table
and OrderingItemNo is non-zero then the ORDER BY clause is substituted with
the ORDER BY criteria from the OrderingItems property.
All modifications to the SQL and SQLXXXX properties during this PreparingSQL
phase become a part of the SQL that goes to the server and do NOT become a
part of the base SQL property. If the SQL property is modified outside of the
PreparingSQL phase then the change is applied to the base SQL.
So, if you would like a button that toggles whether or not certain records
should be included in the dataset you would set it up as though this were an
item of search criteria. Here is some sample code that should work:
// This is a TCheckBox OnClick event handler.
procedure TfrmMain.cbActiveUsersClick( Sender: TObject );
begin
qrUsers.InvalidateSQL; // Flag it so that it knows to re-prepare.
qrUsers.Refresh;
end;
// This is a TIB_StatementLink OnPrepareSQL event handler.
// This will be used by the TIB_DataSource component as well.
procedure TfrmMain.dsUsers.PrepareSQL(Sender: TIB_StatementLink;
Statement: TIB_Statement);
begin
if cbActiveUsers.Checked then begin
qrUsers.SQLWhereItems.Add( 'STATUS=''A''' );
end else begin
qrUsers.SQLWhereItems.Add( 'STATUS=''I''' );
end;
end;
Then, depending on whether the checkbox cbActiveUsers is checked or not, the
SQL that goes to the server will always have a where clause item of STATUS=A
or STATUS=I. This is the simplest way I can think of to describe how to bind
in SQL criteria to a custom control in the interface.