There is an event on the IBO dataset components called OnPrepareSQL. It is triggered each time the component is sending a statement to the server in order to allow the application to provide last minute modifications and alterations to make it suit the immediate needs of the environment.
This is a whole concept of allowing your SQL to be "bound" to external conditions and/or controls in your GUI. You might have a checkbox for indicating that only active or inactive accounts should be shown. Thus, at prepare time the state of this control can be looked at and the SQL can be changed to reflect this condition.
In other words, the SQL property of the dataset simply forms a basic outline of the dataset and then in the OnPrepareSQL event furthur refinements are provided. Here's how it can work:
SQL:
SELECT * FROM ACCOUNTS
OnPrepareSQL:
begin
if cbActive.Checked then begin
MyQuery.SQLWhereItems.Add( 'STATUS=''A''' );
end else begin
MyQuery.SQLWhereItems.Add( 'STATUS=''I''' );
end;
end;
cbActive.OnClick:
begin
MyQuery.InvalidateSQL;
MyQuery.Refresh;
end;
SQLWhereItems is a stringList that holds items to be parsed into the SQLWhere
clause and sent to the server. It takes away the grunt work out of dealing
with getting your AND's properly in place along with the WHERE token as
well.
This way you don't have to worry about so much parsing garbage. You let your
GUI store the state directly (SQL binding I call it) and let IBO get it from
you in the OnPrepareSQL phase and send it to the server leaving your
permanent base SQL intact.
So, when the checkbox is clicked on it's state changes. Thus, it calls InvalidateSQL which tells IBO that it needs to be reprepared the next time it is opened. This happens when the dataset is refreshed and so upon refreshing a new statement is sent to the server because it is reprepared. When repreparing it notices that the checkbox is now in a different state so the statement sent to the server properly reflects the new state of the control.
It also improves efficiency to use this approach because when doing a re-prepare there are lots of internal structures that remain intact. If you were to directly alter the SQL property to accomplish this it would do a complete Unprepare and have to do a full prepare for each time the SQL needed to be changed.
The sample application PriceList shows a demonstration of how the OnPrepareSQL event can be put to use.
