Execute Query
Description
The Execute Query part can be used to execute a query or fetch data from a database.
Use
The Execute Query part is a powerful one that can be used to query any database within the installation (including different projects) without having to use the LogicNets interface like 'add records' or 'change records'. It can handle more complex SELECT statements including joins, interpolated variables, etc. and has a variety of options in terms of outputting data. It also provides error handling that is helpful in determining any test failures.
Tips and Tricks
Execute query is very useful when using more complex selections, or when using $(..) constructions in the select or where statement. There are several choices of output format for the resulting data too.
Using the execute query part is more likely to give errors as the syntax is not validated by the LogicNets system. Any errors during design are can be seen in the error object or by testing in debug mode. Only use this part if you are familiar with SQLite syntax such that you can write the query within non-validated text editor.
Testing
During the design process you may want to test this node. But if your source is the published package name, and you have not yet published a test version, the part will not return any data. To rectify this, publish the unfinished project to at least establish some data for the Execute Query to work with. But remember the data in the published application is only updated by the runtime (and the data in the Designer table is only updated by running in TEST mode in the Designer, or from direct editing of the table.
Editor Fields
| Field Name | Description/Use | Type/Options | Optional/Mandatory |
| Source | Specify the exact name of the database on which to execute the queries; for example:
* in the case of complex installations with stacking packages by inheritance, "applicationresources" points to the top package that has the most specific implementation. |
Text | Data Object | Mandatory |
| Query | This is where you specify the complete query in the form SELECT column_list FROM table-name [WHERE Clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause]; |
Text | Data Object | Mandatory |
| Interpolate Query | This is where you specify whether the query needs to be interpolated. Check this in case the query has any variables that are set by the application. e.g. if the WHERE clause is WHERE temperature >= $(user_input_temp) |
Checkbox | Optional |
| Mode | Execute: Use this option to execute a query without return values. Fetch: Use this option if the query involves retrieving data from the database. Multi query: Use this option if the query will return multiple result sets. The total result will be a collection of each individual result set. |
Execute | Fetch | Multi-query | Mandatory |
| Multi-record | This is only required when mode = Fetch or Multi query. | Fetch | Multi-query | Mandatory |
| Result path | The result of the query is stored in this data object. This parameter is optional but if you expect values to be returned this must be specified. | Data Object | Optional |
| Error path | This is the data object in which with error message is stored if the query fails, which is useful to see in the DATA tab when testing and the model returns no results (or different to expected results). This field will disclose any syntax errors with the SQL SELECT statement such as column does not exist. | Data Object | Optional |
| Error code path | This is the data object in which the error code is stored if the query fails. | Data Object | Optional |
| Override result path | If this checkbox is checked and the data object for the result is not empty the system will override the result. | Checkbox | Optional |
| Column mapping | This is a key-value collection with the column names as keys and object paths as values. For example, the column BIRTH_DATE can be mapped to object Patient.Info.BirthDate. | Data Object | Optional |
| Column data types | Key-value collection with the column names as keys and the data types as values. Supported data-types: text, date, number, json. The specified data type overrides the actual data type read from the query result. If the conversion fails, no value is written.
|
Data Object | Optional |
| Lower case col name? | When this is selected the system converts the column name to lowercase letters. For example, if the queried table has uppercase column names these name will be converted to lower case and stored into result path. | Checkbox | Optional |
| Result as collection | When this is selected the system converts the query result to a simple collection with the values of the column specified by Value column name. The checkbox is not visible when Result as key-value is checked. | Checkbox | Optional |
| Value column name | This field is only available when Result as collection is checked. Specify the name of the column to be used to get the values from to be stored as simple collection items. A simple collection is for example: { "one", "two", "three" }. | Data Object | Optional |
| Result as key-value | Select this field to allow the query result to be easily converted to a key-value collection. The checkbox is not visible when Result as collection is checked. | Checkbox | Optional |
| Key column name | This is only available when Result as key-value is checked. Specify the name of the column to be used as key in the resulting collection. The actual value of the column of that record will be the key. For example, if the query returns two rows with values { 'ABC'}, {123} for the specified column, then the result set will be indexed on keys ['ABC'] and [123]. NOTE: If there are multiple records with the same column value, then the last read/processed records overwrite the previous records. |
Data Object | Optional |
| Value column name | This is only available when Result as key-value is checked. Specify the name of the column to be used as the single value result in the resulting collection. This simplifies the result rows into a single value. NOTE: This only usable in combination with Key column name. |
Data Object | Optional |
Examples
Download and import one of the following example projects in your workspace.