0

Execute Query

  • updated 1 yr ago

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:
  • applicationresources (will run the query against the application package*)
  • KnowledgeCenter (will run the query against the KC)
  • [YourPublishedPackage] (will run the query against the published data under this name  - which is NOT the same as the data in the Designer table with the same name)
  • session (will run the query against a table set up as a session table  - meaning a new, separate table each time a session is created, specific to the user and specific instance)

* 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.
  • json converts the json string into an object.
  • date expects either a number in the format yyyymmdd.hhmmss or a string in that same format or in the format 'yyyy-mm-dd hh:mm:ss' (separators are irrelevant, e.g. 'yyyy/mm ddThh-mm_ss' also converts correctly; timezone details are ignored).
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.

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like Follow
  • 1 yr agoLast active
  • 85Views
  • 5 Following

Home