Value from SQL query
See also: Execute SQL query (Event action)
Value resolver – Abstract
Purpose: Passes a single native SQL SELECT statement to a database that must be accessible to the Lobster_pro server via the specified Alias, and returns the result rows as a list of client objects .
The Value from SQL query value resolver passes a single native SQL SELECT statement to a database that must be accessible to the Lobster_pro server via the specified Alias, and returns the result rows as a list of client objects.
Unlike the Execute SQL query event action, the Value from SQL query value resolver can only execute SELECT statements. The value resolver returns their return values as a list of client objects. Each client object represents one result row. The 'column names' of the query (projections) appear as fields of each client object in the list. Depending on the database system, the column names appear either uniformly in upper case, uniformly in lower case, or case-sensitive . In any case, the respective notation must match exactly ('case-sensitive') when accessing the data fields in Lobster Data Platform / Orchestration (e.g. via an Object property value resolver).
►NOTE◄ If a query returns no rows, the result variable contains an empty list that satisfies an Is empty check, but not a comparison like "Equals 'no value'".
The SQL statement can include the values of variables from the context of the current event handling via type-safe placeholders according to the Lobster_data query syntax.
Configuration
The Alias parameter can be specified as static text or as the return value of a value resolver. It must point to a database that can be accessed by the Lobster Data Platform / Orchestration server.
The SQL Query parameter defines the SQL statement to be executed in the native syntax of the database addressed by Alias, if necessary taking into account more typical placeholders, in the place of which the Value of one variable each from the event handling context is inserted at runtime.
For the definition of these placeholders the Lobster_data query syntax with the structure @<index>:<typ>@ is used, e.g. @1:s@ for a string value or @2:t@ for a timestamp.
The <index> must be a positive integer, which is also used as Variable name within the configuration. The values used do not have to be assigned without gaps and in ascending order.
As <typ> an identification letter for a data type must be specified ('l' long, 's' string, for a complete list see Lobster_data query syntax), which must match the intended use for the placeholder.
For all placeholders used in the SQL Query, a corresponding Variable name must be explicitly configured with a value resolver whose return value must correspond to the data type of the placeholder or at least be suitably convertible.
The same Variable name can be referenced by several placeholders, which can even specify different data types if needed, as long as any value occurring at runtime can be converted to all target data types.
►NOTE◄ Placeholders can only be used at positions in the SQL statement that affect values and not, for example, to dynamically assign fields, table names or keywords to the syntax. However, the value for the SQL Query parameter as a whole can be built dynamically (e.g. via Concat strings) to achieve corresponding flexibility.
Example
The 'Creator' (creatorId) field of an entity refers via a Long value to the account of the user (see Users) or guest user (see Guest users) who created the entity – i.e. saved it for the first time. A positive creatorId refers to the ID of a user, while a negative value for the creatorId refers to a guest user as creator. However, the possibility that guest users create entities is excluded in the following example, so that only positive values for the creatorId can occur.
Based on a creatorId, the user name of the relevant user is to be identified as the 'creator' of the entity in a notification. This can be easily achieved via the lookup function for entities of the Input object (type safe) value resolver.
However, accessing the user account via the creatorId by Input object (type safe) value resolver fails if the account in question has been deleted since the entity was created. A Search would not return a match either, since a deleted user account actually no longer exists in the relevant table of the Lobster Data Platform / Orchestration database (here: base_user).
However, since Lobster Data Platform / Orchestration keeps information on the change history for Users, the user name of a deleted user account can be looked up in the 'change history'. The Value from SQL query value resolver provides the necessary read access to the relevant database table, in this use case: base_user_history.
Configuration:
The following SELECT statement returns the last used username of a user account for an ID given in the integer parameter 1 (placeholder @1:i):
SELECT username FROM base_user_history WHERE id=@1:i@ ORDER BY entry_valid_from DESC LIMIT 1|
The value resolution chain shown on the right identifies the 'Creator' of the entity present as a reference object either by the 'Username' (userName) field of an existing user account or – if the account has been deleted – by the last relevant entry in the change history:
|
|
