Export > UI controls

Query result can be displayed in UI controls. To enable this, add sqlgui unit to uses list and pass control to ToObject method.

Following VCL controls can be used:

•DBGrid

•StringGrid

•ValueEditor

•ListView

•ControlList

•Chart

•HtPanel

•VirtualStringTree

Note that reference to IHtSQLResult interface should be stored in some variable while working with control, otherwise UI controller will be destroyed (except TChart).

Correct:

MyFormVar: IHTSQLResult;

MyFormVar := THtSQL.From*(..);

MyFormVar.ToObject(DBGrid1);

Wrong:

THtSQL.From*().ToObject(DBGrid1);

Common rules

Hidden fields

Fields starting with _underscore are not displayed.

Display format

To set display format, add it in parentheses at the end. Example: "Start Date (dd.mm.yyyy)"

Format is supported for

•Date/Time fields: any format supported by FormatDateTime function

•Number fields: any format supported by FormatFloat function and special format bytes to display size with KB, MB, GB postfix.

•String fields: %s is replaced by field value.

Format can contain pascal script expressions when it starts with =. Example:

Received "Received(=iif(value > Date(), 'hh:nn', 'dd MMM yyyy hh:nn'))",

HTML fields

When field contains HTML, to display it as HTML (unescaped) enclose field name in brackets: "<HTML Field>"

Multiline fields.

To display multiline text from field, add + at the end. Example: "Message Body+"

Field templates

To set template for a field, add field with the same name but ending with _template. Example: customer_template.

Template is in Mustache template format and can contain any field. For a value of current field use {{value}}.

Example:

'<div style="font-size:0.7em">{{value}}</div>' body_template

Row and column styles

To setup style for rows and columns use row_style and column_style field. Styles may contain calculated expressions, to refer to row and column index use col and row variables.

Example or row style for highlighing odd rows:

'=iif(odd(row), ''background:#f8faff;'', '''')' row_style,

Groups

Field named group_field is treated as group name. When component supports groups (VirtualTreeView, HtPanel) it is displayed above group.

Selected row

To get values if selected row, use SQLResult.Selected[FieldIndex] property.

Filtering

To apply filter simply start typing.

DBGrid

After export to DBGrid, it is possible to sort grid by clicking on column title and filter grid by entering text in any column.

Simple operators are also supported in filter, for example >100, <>50, <= 5.5.

ControlList

Set field aliases to control name and property, f.e.

select c.name "label1.caption", address "memo1.lines.text" from customers

Some properties names: Caption, Text, HTML, Lines can be omitted, so query above can be written as following:

select c.name label1, address memo1 from customers

List can be filtered by typing search text.

Chart

Export to TeeChart has two modes. When query has more than two columns, and first column is not float, it uses multiseries mode where each row is separate series and values are in float fields.

Otherwise first column is treated as value and second as label. Third (optional) as color.

ToObject parameter may be TChart or TChartSeries instance.

Example:

THtSQL.FromFolder(SelectedFolder,
'select b, a from sample.sheet1 where _row > 0 '+
'pivot(sum(substring(e from 2)) for a in (select distinct a from sample.sheet1 where _row>0))',

[]).ToObject(Chart1);