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 Data Platform / Orchestration server via the specified Alias, and returns the result rows as a list of client objects.

Tooltip

• Usage: As long as the Lobster Data Platform / Orchestration server has database access for the parameterized Alias, the SQL Query is executed – taking into account the configured parameters for inputting data from the call context, if applicable. Result rows appear as entries of a list, where each entry is a client object whose field names correspond to the projections (or 'columns') of the query.

• Parameter:

  • The Alias parameter addresses the database via an alias name configured for the Lobster Data Platform / Orchestration server. The name can be determined either by static direct entry in the configuration or via value resolvers at runtime

  • The SQL Query parameter defines an SQL SELECT statement in which parameters whose values are determined at runtime can be referred to instead of values (e.g. in criteria of the WHERE condition).

  • For each parameter used in the SQL Query, a 'variable' must be configured with an integer index value as the Variable name value resolver that returns values in a matching data type at runtime.

• Note: If a query returns no rows, the result variable contains an empty list that satisfies a check by  Is empty, but not a comparison like 'Equals to no value'.

The Value from SQL query value resolver passes a single native SQL SELECT statement to a database that must be accessible to the Lobster Data Platform / Orchestration 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 Platform / Integration 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 Platform / Integration 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 Platform / Integration 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):

PostGreSQL-example

SELECT username FROM base_user_history WHERE id=@1:i@ ORDER BY entry_valid_from DESC LIMIT 1