SQL To Parameters

RdbmsToParameters32x32

Description

Publishes the result of a SQL query to a parameter.

Parameters

Name Mandatory Default Description

SQL Transaction Name

No

T1

SQL transaction name used for the command.

SQL Transaction Type

No

AUTOCOMMIT

Type of transaction. AUTOCOMMIT: an autocommit connection is picked up from the connection pool, COMMIT: a commit is issued after the SQL command, NOCOMMIT: no commit is issued after the SQL command.

An AUTONOMOUS SQL transaction type was available in older versions of Semarchy xDI. It was removed in version 2024.1.0 LTS.

If you import older projects that are still configured to use the AUTONOMOUS transaction type, xDI will interpret those transactions as AUTOCOMMIT instead.

In the action code, specify the parameter and the SQL query using the syntax below:

<sqlToParameters>
<parameter name="PARAMETER_PATH" type="PARAMETER_TYPE" failsIfNoRecord="FAILS_BOOLEAN" defaultValue="DEFAULT_VALUE" dateTimeFormat="DATETIME_FORMAT">
SQL_QUERY
</parameter>
</sqlToParameters>

Where

  • PARAMETER_PATH is a path and a name of the parameter to publish.

  • PARAMETER_TYPE is the type of parameter (String, Integer, Boolean, Long or Float).

  • FAILS_BOOLEAN is a boolean (true or false). The default is true. That means the action will fail if the Sql Order doesn’t return a value.

  • DEFAULT_VALUE is the default value in case the returned value is null. This can happen if no record is returned or if a null value is returned.

  • SQL_QUERY is the Sql query to be executed. This query should return a single cell (one row and one column). In other cases, the first column of the first row will be used.

  • DATETIME_FORMAT is the date format to use when converting a date or datetime to string. The syntax should comply to the specification of the Java SimpleDateFormat class. Eg. yyyyMMdd

The following example sets the SQL_NB_ROWS integer parameter to the value returned by the SELECT COUNT statement:

<sqlToParameters>
<parameter name="./SQL_NB_ROWS" type="Integer">
Select count(*) from %x{md:physicalPath($REF,'workName')}x%
</parameter>
</sqlToParameters>

The following example sets the SQL_NB_ROWS integer parameter to the value returned by the SELECT COUNT statement. The parameter is published on the father process:

<sqlToParameters>
<parameter name="../SQL_NB_ROWS" type="Integer">
Select count(*) from %x{md:physicalPath($REF,'workName')}x%
</parameter>
</sqlToParameters>
It is possible to publish several parameters at the same time by using multiple <parameter>…</parameter> blocs.