This step reads some data from a database using a SQL command that may be parameterized with some data from the input row. For every entry of the SQL command's result set it creates a copy of the processed input row with data from the result set row added according to the given mappings.

In another words, if the SQL command returns n rows in the result set, then there are n copies of the input row in the output, with data from the respective result set row added.

If there is an input row for which the SQL command does not return any entry in the result set, there is no entry for such row in the output data flow as well. This means that such input row is "dropped" from the data flow. This behavior may be overridden by setting includeEmpty to true (default value). This will cause "empty" input rows to be written to the output as well (result set mapped columns will be empty then).

Usage notes

• This step is suitable for read-like operations only (must return a result set). For write-like operations use the SQL Execute step.
• When there is no result set as the result of the query execution, an error is thrown. This is considered a configuration problem that needs to be fixed.
• Because it is not possible now to analyze a SQL command without being run, some problems are reported after the first result set is obtained. Those are validations of mapped result set columns, whether the query returns a result set, etc. Note that when error is thrown at this moment, the state is as follows: 1) possible beforeScript is executed and commited 2) statement for the first row is not commited 3) afterScript is not executed

Processing parameters

For a detailed description of how to define input and output parameters of the SQL query see the description of the SQL Execute step. This step uses parameters the same way.

NOTE: Oracle's stored procedures return multiple values (result set) in it's own (non JDBC standard) way. This is why the step is not currently capable of processing result sets returned from Oracle's stored procedures (as in Oracle's CURSOR data type).

Mapping result set values

To map values from the result set to the data rows, mappings must be provided. To determine SQL output column names reliably, it is strongly recommended to name output columns in the query using SQL's AS operator. In some cases this is necessary, such as when, for example, the SQL query returns an unnamed column, then it is not possible to access it via mappings unless it is named with the AS operator to something nonempty (because the mapping's sqlColumn value is required and it cannot be empty).

Note: Some database drivers (for example Oracle, ApacheDerby and possibly some others) return names of the result set columns in uppercase (even for column names defined with the AS operator). If this is the case, then uppercase names must also be used in result set column mappings, since name matching is case sensitive there. The same problem may occur with diacritics. Again, the same form must be used either in the SQL query and result set mapping.

To convert SQL values to data flow values the JDBC data conversion is used.

<step disabled="false" mode="normal" className="cz.adastra.cif.tasks.jdbc.execute.SQLSelect" id="algorithm_2">
  <properties dataSourceName="ds" 
              query="select names.name AS SQLNAME, addresses.address AS SQLADDRESS from names, addresses 
                     where names.id = ${id} and addresses.id = ${id}" includeEmpty="true">
    <mappings>
      <columnMapping sqlColumn="SQLNAME" dataColumn="outName"/>
      <columnMapping sqlColumn="SQLADDRESS" dataColumn="outAddress"/>
    </mappings>
  </properties>
</step>