IBX 1.2 introduced the TSelectSQLParser class (IBSQLParser unit). This class supports the parsing and modification of Firebird Select SQL statements. It is intended to parse all such statements including UNIONs and Common Table Expressions. Note: its purpose is to permit reliable modification of “Where”, “Having” and “Order by” clauses in particular, and is not an SQL validator. While invalid SQL will often generate an exception, this is not guaranteed.
The parser can be used as a standalone class, but is typically accessed using the “Parser” property of a TIBDataSet or a TIBQuery, and in a “BeforeOpen” event handler. Accessing the Parser property causes a TSelectSQLParser object to be created and its result is used when the dataset is opened.
An example of use may be found in ibx/examples/employee where it is used to filter the EMPLOYEE table query according to user selectable criteria. In this example, the BeforeOpen handler is
procedure TForm1.EmployeesBeforeOpen(DataSet: TDataSet);
begin
if BeforeDate.Date > 0 then
(DataSet as TIBParserDataSet).Parser.Add2WhereClause('HIRE_DATE < :BeforeDate');
if AfterDate.Date > 0 then
(DataSet as TIBParserDataSet).Parser.Add2WhereClause('HIRE_DATE > :AfterDate');
case SalaryRange.ItemIndex of
1:
(DataSet as TIBParserDataSet).Parser.Add2WhereClause('Salary < 40000');
2:
(DataSet as TIBParserDataSet).Parser.Add2WhereClause('Salary >= 40000 and Salary < 100000');
3:
(DataSet as TIBParserDataSet).Parser.Add2WhereClause('Salary >= 100000');
end;
{Parameter value must be set after all SQL changes have been made}
if BeforeDate.Date > 0 then
(DataSet as TIBParserDataSet).ParamByName('BeforeDate').AsDateTime := BeforeDate.Date;
if AfterDate.Date > 0 then
(DataSet as TIBParserDataSet).ParamByName('AfterDate').AsDateTime := AfterDate.Date;
end;
In the example, two filters are available for user use:
-
Restriction of “Hire Date” to a selected date range
-
Restriction of salary to a drop down list of salary bands.
In each case, the filters need to be added to the SQL“Where” clause.
When the Parser object is first invoked, it is created using the origin SQL text as set at design time. Calls to the method “Add2WhereClause” then do as expected – the supplied condition is ANDed with the existing “Where” Clause. An optional second parameter to Add2WhereClause is also available to OR the condition with the current “Where” clause (not shown).
In the above example, if the user has selected a given filter, then the SQL is updated as appropriate. Add2WhereClause can be called multiple times and each time it adds to the current text of the “Where” clause. Parentheses are automatically added to ensure that the semantics of the original condition are maintained.
In this example, the requested Hire Date could have been formatted as text e.g.
HIRE_DATE < '2015-01-01'
However, it is generally more reliable to let IBX handle date time conversions and so a parametrised query is used instead, with the parameter value being applied later on in the event handler.
Note that the example also illustrates an important rule: in a BeforeOpen event handler, parameter values must be set only after all SQL manipulation is complete. This is because the query must be “prepared” before parameter values are set and modifying the SQL always causes the query to be “unprepared” with the consequence that any parameter values are discarded.
In use, when a user changes a filter selection, the DataSet is closed and re-opened causing the SQL to be re-generated and the result set appears with the filter applied.
A TIBDataSet or TIBQuery SelectSQL statement can still be updated at runtime. As before, this will close the dataset and unprepare the query. In addition, the initial SQL used for the Parser is also changed to the new value set at runtime.
Use with IBControls
The TSelectSQLParser is used by other IBControls. These controls also use the Parser property and access it before the BeforeOpen event handler is called.
Example
An example of direct use of the TSelectSQLParser can be found in ibx/examples/sqlparser. This is a simple form that can be used to experiment with the parser and see how the SQL statement is affected by calling methods such as Add2WhereClause.
As shown in Illustration 1, you can use the example program to test out the parser by:
-
pasting an SQL Query into the “Original SQL” text box
-
entering an SQL Condition into one or more of the text boxes below
-
selecting the required options,
-
and clicking on the “Generate Updated SQL” button.
The updated SQL Statement should now appear in the right hand text box.
The example chosen here is a fairly trivial one taken from the ibx/examples/employee program and shows a single filter clause being added to the SQL used to generate the employees list.
TSelectSQLParser Reference
For all properties and methods consult the source code. The following are those intended to be used in a BeforeOpen event handler:
-
procedure Add2WhereClause(const Condition: string; OrClause: boolean=false; IncludeUnions: boolean = false);
This method is used to add an SQL condition to an SQL “Where” clause. If one does not exist in the original query, then the clause is added. By default, the condition is ANDed with the current “Where” condition. If the “OrClause” argument is true, then it is ORed.
By default, the condition is only added to the first select statement in a UNION. If the “IncludeUnions” argument is true, then it is added to every select statement in the UNION.
-
procedure Add2HavingClause(const Condition: string; OrClause: boolean=false; IncludeUnions: boolean = false);
The behaviour of this method is identical to Add2WhereClause, except that it applies to the “Having” clause of the select statement.
-
property Union: TselectSQLParser;
When the select statement is a union, the second select statement is accessible through the “Union” property. Each select statement in the union is recursively added to the preceding statement via this property.
-
property OrderByClause: string;
The current “Order By” is accessed and replaced via this property. The text is the clause less the “Order by” keyword.
-
property SQLText: string
This property returns the current SQL statement complete with any modifications. This property may be useful when debugging.