www.mwasoftware.co.uk

TDBControlGrid

 

TDBControlGrid is a lookalike rather than a clone for the Delphi TDBCrtlGrid. TDBControlGrid is a single column grid that replicates a TWinControl - typically a TPanel or a TFrame in each row. Each row corresponds to a row of the linked DataSource. Any data aware control on the replicated (e.g.) TPanel will then appear to have the appropriate value for the row.

Unlike the Delphi TDBCtrlGrid, there are no restrictions on which controls can be used on the replicated panel. In principle, any visual control may be used. The “csReplicable” property is not used by TDBControlGrid. However, there can be performance issues with a large number of controls on the panel or when there is a high latency to draw one or more controls.

To use the new control, simply drop it on to a form at design time and size it appropriately. Then separately drop a TPanel on to the same form and populate it with appropriate child controls, typically data aware controls using the same DataSource.

Now link to TDBControlGrid DrawPanel property to this panel. The panel should then be repositioned as a child control of the TDBControlGrid and occupying the top and only row of the grid. The row height should be set to the panel height and the panel width will the set to the with of the grid row. The panel can be unlinked at any time.

Now set the TDBControlGrid.DataSource to the common data source for the controls on the panel.

When you build and run your project and open the DataSource's dataset, the TDBControlGrid should show a row for each row in the dataset and the child controls on each row should have the appropriate values for the row.

When the grid has the focus, you can move between rows using the up and down arrow keys, page Up and Page Down, Ctrl+Home and Ctrl+End jump to beginning and end respectively. You can also use the mouse to change between rows, either by clicking on a row or the scroll bar.

Pressing the down arrow key on the last row should append a new row – as long as the “Disable Insert” TDBControlGrid.Option is not selected.

All rows may be edited in situ. Moving between rows should automatically post the changes. The “escape” key may be used to cancel row edits before they are posted.

A row may be deleted by calling the underlying DataSet's Delete method.

See the TDBControlGrid example code for guidance on how to use the control. This example requires IBX and uses the Firebird example employee database.

Note: With GTK the row image is created by a screen grab (in GTK itself). This can give rise to unexpected results if the control is obscured when caching takes place.

TDBControlGrid Properties

DrawPanel

TWinControl

This control will be replicated for each row in the DataSet. Typically a TPanel or a Tframe.

Options

TPanelGridOptions

Similar to a TDBGrid, but limited to:

  • Cancel On Exit

  • Disable Insert

  • Show Indicator Column

DataSource

TDataSource

A row is replicated for every row in this dataset.

DefaultPositionAtEnd

Boolean

When the dataset is opened then it is initially positioned at the last record if this property is true,

 

TDBControlGrid new Events

OnKeyDownHander

The TDBControlGrid uses a KeyDown handler to intercept edit keys while the Draw Panel is active. For example, to process an “escape” key as a cancel edit. You can write your own keydown handler to modify this behaviour.

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.

TSelectSQLParser1

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.

TIBTreeView1 

TIBTreeView is a data aware descendent of a TCustomTreeView and is used to present a hierarchically organised data set in a tree view. Tree Node Insertion, Deletion and Modification are supported, as is moving (e.g. using drag and drop) nodes from one part of the tree to another. The underlying dataset cursor is always positioned to reflect the currently selected tree node. It can thus be used to select a row for detailed editing. SQL Manipulation is used to load the tree as a series of separate queries.

The above picture Is taken from ibx/examples/ibtreeview and uses the Firebird example “employee” database. This database contains a hierarchically organised table “DEPARTMENT” and which is used for the example.

To use a TIBTreeView, simply drop it on to a form, set the DataSource property, and, as a minimum, the TextField, ParentField and KeyField properties as defined below.

The DataSet must have a single primary key field.

TIBTreeView Properties

DataSource

TDataSource

Identifies the source of the data to present using the tree view

TextField

string

The field name of the column used to source each node's display text

KeyField

string

The field name of the column used to source each node's primary key.

ParentField

string

The field name of the column used to identify the primary key of the parent row. This field is null for a root element.

HasChildField

string

Optional. The field name of the column used to indicate whether or not the row has child nodes. When present, the field should return an integer value with non-zero values implying that child nodes exist.

RelationName

string

Optional. The Child Field is typically the result of joining the table to itself and is a count of child rows. However, this can result in ambiguous column names when the SQL is manipulated. This property should contain the Table Alias used to select the Key, Text and Parent Fields (see example).

 

TIBTreeView Methods

function GetNodePath(Node: TTreeNode): TVariantArray

Returns a Variant array containing the primary key values of the Node and its parents from the root node downwards.

function FindNode(KeyValuePath: array of variant; SelectNode: boolean): TIBTreeNode;

Returns the TTreeNode identified by the KeyValuePath. The KeyValuePath is an array comprising a list of primary key values walking the tree down from the root node to the requested node.

If SelectNode is true then the returned node is also selected.

This function can be used to select the tree node using the node path returned by an earlier call to the function GetNodePath.

function FindNode(KeyValue: variant): TIBTreeNode;

Returns the tree node with the primary key given by KeyValue. Note: this forces the whole tree to be loaded by a call to TcustomTreeView.FullExpand.

Drag and Drop

Drag and drop is supported by TCustomTreeView without the need for additional support from TIBTreeView. In the example, drag and drop is enabled by:

  • DragMode set to automatic

  • The OnDragOver Event handled by:

procedure TForm1.IBTreeView1DragOver(Sender, Source: TObject; X, Y: Integer;
State: TDragState; var Accept: Boolean);
begin
  Accept := Source = Sender
end;

  • The OnDragDrop Event Handled by:

procedure TForm1.IBTreeView1DragDrop(Sender, Source: TObject; X, Y: Integer);
var Node: TTreeNode;
tv: TTreeView;
begin
if Source = Sender then {Dragging within Tree View}
begin
tv := TTreeView(Sender);;
Node := tv.GetNodeAt(X,Y); {Drop Point}
if assigned(tv.Selected) and (tv.Selected <> Node) then
begin
if Node = nil then
tv.Selected.MoveTo(nil,naAdd) {Move to Top Level}
else
begin
if ssCtrl in GetKeyShiftState then
begin
Node.Expand(false);
tv.Selected.MoveTo(Node,naAddChildFirst)
end
else
tv.Selected.MoveTo(Node,naInsertBehind)
end;
end;
end;
end;

Note that the above applies the convention that if the “control” key is held down while the node is “dropped” then it is added as a child node. Otherwise, it is added as a sibling.

TIBLookupComboEditBox is a TDBLookupComboBox descendent that implements "autocomplete" of typed in text and "autoinsert" of new entries.

  • Autocomplete uses SQL manipulation to revise the available list and restrict it to items that are prefixed by the typed text (either case sensitive or case insensitive).

  • Autoinsert allows a newly typed entry to be added to the list dataset and included in the available list items.

Although TDBLookupComboBox also supports auto-complete, the benefit of using TIBLookupComboEditBox comes with long lookup lists as typing in one or more characters forces the list to be queried again and restricted to list members beginning with the same characters. The list of alternatives becomes much shorter.

Auto-insert normally uses the list dataset's insert query to add a new row and depends upon the dataset's “After Insert” event handler to set the other fields of the row to appropriate values and/or the generator assigned to the dataset.

TIBLookupComboEditBox Example

TIBLookupComboEditBox1

The above example can be found in ibx/examples/lookupcombobox and uses the Firebird “employee” example database. The “Employee Name” is a TIBLookupComboEditBox and is used here to:

  1. Select an employee record for editing

  2. Initiate the entry of a new employee record.

First, you should explore the use of the new control. Click on the drop down arrow and a drop down list of all employee names (in lastname/firstname syntax) will be shown. This is typically longer than can be displayed on a single screen.

TIBLookupComboEditBox2

Now close the drop down list, select all characters in the Employee Name edit box and enter “pa”. After a short (600ms) delay, after you stop typing, the employee details should change to that shown in Illustration 2 i.e. for the first employee with a lastname beginning with “pa”, i.e. Mary Page.

Of course, auto-complete to the first employee beginning “pa” may not get the actual employee you want. Now click on the drop down list and this will show all employees with a last name starting with “pa”. This is a much shorter list than the full list and allows you to quickly focus in on the employee you want.

Indeed, this can also be done from the keyboard. Start again, and enter “pa”, now press the down arrow and you can cycle quickly through all employees starting “pa”. The up arrow also works. Use the Enter key to select the employee record.

Alternatively, after entering “pa” and seeing the entry for Mary Page, then press “r” to extend the entry to “par” and you get the record for Bill Parker.

To return to the full list, just press the escape key while the control has the focus.

Auto-insert

Auto-insert allows quick insertion of new employee records. For example, start by selecting all text in the Employee Name edit box and enter the name of the new employee (e.g. Smith, John), and press the “Enter” key. You should now get a prompt confirming the entry of the new employee record:

TIBLookupComboEditBox3

If you click on “yes” then a new employee record is created and displayed as show below.

 TIBLookupComboEditBox4

The employee name is parsed from the text entered into the Employee Name box. The remaining fields come from defaults taken from the “OnInsert” event handler. You can now amend the defaults as required.

TIBLookupComboEditBox Properties

TIBLookupComboEditBox inherits TDBLookupComboBox properties. In addition, it defines:

AutoInsert

Boolean

Set to true to enable auto-insert

AutoComplete

Boolean

Default: true in TIBLookupComboEditBox

KeyPressInterval

Integer

Delay in milliseconds between last key press and auto-complete (Default: 500ms).

RelationName

String

TIBLookupComboEditBox updates the “Where” clause in the ListSource select SQL query in order to refine the list, and uses the value of the “ListField” property as the column name. If this name is ambiguous in the SQL query then the “RelationName” property must be set to the name of the table or table alias to qualify the column name and remove the ambiguity.

TIBLookupComboEditBox Event Handlers

OnAutoInsert

TIBLookupComboEditBox will normally use the ListSource's Insert query to perform auto-insert. If this is not possible or inappropriate then an OnAutoInsert handler must be provided to perform the insertion. The handler is provided with the value of the display text to insert and must return the new key value.

OnCanAutoInsert

This handler is called immediately before auto-insertion is performed and is typically used to validate the insert and obtain user agreement (e.g. via a dialog box). The handler is provided with the value of the display text to insert and must set the “Accept” boolean on return to true to accept the insert or to false to reject it.