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.