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, AUTONOMOUS: a separate autocommit connection is created for this command.

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.