This action retrieves from a database table (or view) all records that match specific selection condition and returns multiple data structures, each containing the content of one of the retrieved records.

The database table/view is identified by the tableName property of the data type of <Records> (unless <Table> is provided).

This action does a "complex find". If all that is needed is to select all records from the table/view, or records in which specific fields contain exactly specific values, use Find. On the other hand, when more complex retrieval conditions than supported Advanced Find by are required, use Database Query.

To learn more on the way data structures are saved as database records, refer to Insert.

Triggers

<Match>, <From>, <To> (non-repetitive triggers [none required]): Filtering conditions specifying which records should be retrieved.

The data type of each of these 3 triggers, when provided, should be identical to the data type of the <Records> exit (reuse the same model). Each of their non-empty leaf elements specifies the expected value of the corresponding element in the retrieved records - retrieving only records with exactly the values specified in <Match>, greater or equal to the values specified in <From>, and smaller or equal to the values specified in <To>. Empty leaf elements are ignored, so set value only to the elements you want to use as constraints. The effect of providing <Match> only is similar to that achieved in Find by specifying the constraints through separate triggers.

<Match> can specify wildcard-based matches (using a LIKE clause in SQL). A LIKE clause is created if a leaf element contains a '%' character. For example, a leaf element 'X' with the value 'J' creates a condition X='J', while a leaf element 'X' with the value 'J%' creates a condition X like 'J%'.

<Filter> (non-repetitive trigger [not required]): An additional filtering condition specifying which records should be retrieved.

In its simplest form, <Filter> contains constraints on the values of specific fields in the records. E.g. if the output is a structure with leaves Name, Family Name and Age, and <Filter> is "Name='John' AND Age>21", then the action retrieves only records in which the value of the Name field is "John" and the Age field is greater than 21.

Technically, <Filter> is included in the "WHERE clause" of an SQL "Select statement", but you don't have to be familiar with SQL to write simple filtering conditions. The names of the record fields mentioned in <Filter> should be their names in the database, which match the names of the corresponding leaves of the output data structures(e.g. the record field Family_Name contains the content of the Family Name leaf).

To enable more flexible filtering conditions, additional triggers can be used. For example, you may add a trigger Minimal Age, and change <Filter> to "Age >= ${Minimal Age}". In this case, the action retrieves only records in which the value of the Age field is at least the value received from the Minimal Age trigger at runtime. A more complex filtering condition can be "Age >= ${My Age}-3 AND Age <= ${My Age}+3" (in which case an additional trigger My Age is expected, of course).

<Start From> (non-repetitive trigger [not required]): First record to return.

<Start From> behaves like <From>, but it restricts the returned records rather than the retrieved records. That is, while <From> may reduce the number of matching records (and decrease accordingly the value returned through <Number of Matches>), <Start From> may only reduce the number of records returned.

<Offset> (non-repetitive numeric trigger [not required]): Number of records to skip before the first record that is returned.

This is usually an alternative to <Start From> (although both can be specified together, in which case <Offset> records are skipped, starting from ><Start From>). If none of them is provided, no record is skipped (equivalent to <Offset>=0).

<Backward> (non-repetitive Boolean trigger [not required]): Indication whether to return the last matching records.

Relevant only when <Number of Records> is specified. If <Backward> exists and a Yes value is received, the last <Number of Records> records are returned (rather the the default of returning the first <Number of Records> records).

<Order By> (non-repetitive trigger [not required]): Optional sorting criteria specifying the order of the returned data structures.

The sorting criteria is a comma separated list of record field names (e.g. "Age" specifies sorting the returned records in increasing order of age). If sorting in decreasing order is required, the field name should be followed by "DESC" (e.g. "Age DESC" specifies sorting in descending order of age; "Age DESC, Family_Name, Name" specifies sorting in descending order of age, and for each age, sorting alphabetically according to the family name and then according to the first name.

Technically, <Order By> is the "ORDER BY clause" of an SQL "Select statement", but you don't have to be familiar with SQL to do simple sorting. As with <Filter>, the names of the record fields mentioned in <Order By> should be their names in the database (e.g. Family_Name, corresponding to the Family Name leaf of each output data structure).

<Number of Records> (non-repetitive numeric trigger [not required]): Maximal number of records to return.

<Optimization> (repetitive trigger [not required]): A set of directives overriding the default behavior (to improve performance), each a strings:

• "No Number of Matches" - Avoids executing the additional SELECT statement required to calculate the value for the <Number of Matches> exit.
• "No Position" - Avoids executing the additional SELECT statement required to calculate the value for the <Position> exit.
• "No Records" - Avoids executing the main SELECT statement (so no output is sent through the <Records> exit).
• "CTE" - Converts the statement into a common table expression. Applies only to SQL Server.
• "option..." - Adds the string to the SELECT statement(s). Applies only to SQL Server, and used to add an OPTION clause (query hint) to the SELECT statement(s).

<Data Source> (non-repetitive trigger [not required]): Name of data source to use (if different from the default data source; see Connecting to an External Database).

<Table> (non-repetitive trigger [not required]): Name of table/view to use (if different from the tableName property of the data type of <Records>, or when <Records> is of type Map).

Parameterized tables are also supported (e.g. an SQL Server function returning a table).

<Columns> (non-repetitive trigger [not required]): The table columns to include in <Records>.

<Columns> is a comma separated list of record field names (relevant when <Records> is of type Map).

<Auto Commit> (non-repetitive Boolean trigger [not required]): Indication whether to interact with the database in auto-commit mode.

If <Auto Commit> exists and a Yes value is received, the database action is performed in auto-commit mode, regardless of any active transaction (see Database Transaction Support in Tersus).

Exits

<Records> (repetitive exit [required]): The retrieved records (each output item is a data structure containing the content of one of the retrieved records).

The data type of the exit should be Database Record having a tableName property specifying the name of the database table/view from which records are retrieved.

Alternatively, the data type of the exit can be Map, and then the <Table> trigger is mandatory.

<None> (non-repetitive empty exit [not required]): Indication that no matching record has been found.

<Number of Matches> (non-repetitive numeric exit [not required]): Number of records matching the filtering conditions.

Calculating this number requires an additional query to be performed. The additional query is only performed if the exit exists, and the "No Number of Matches" optimization directive is not specified.

<More> (non-repetitive empty exit [not required]): Indication whether there are additional matching records (beyond those returned).

This is useful when doing paging, to know whether we have reached the end of the data (e.g. to dynamically enable or disable accordingly a Next or More button).

<Position> (non-repetitive numeric exit [not required]): The index of the first record returned as a result.

The index is calculated based on the specified filtering and sorting criteria, taking into account <Start From> and <Offset> (e.g. if <Start From> is not specified, and <Offset> is 20, <Position> is expected to be 20).

Calculating this value requires an additional query to be performed. The additional query is only performed if the exit exists, and the "No Position" optimization directive is not specified.

<Output Columns> (repetitive exit [not required]): The name and type of the columns included in <Records>.

Each returned value is a Column Descriptor structure with the name and type of one of the returned fields (relevant when <Records> is of type Map).

Notice: <Records> and <None> represent two mutually exclusive possibilities: The repetitive exit <Records> is activated only if at least one record has been retrieved; the empty exit <None> is activated only if no record has been retrieved.

Usage Example

"Common/Templates/Modules/Rich Table/Template/Populate Table/Query and Parse":