rdbms reader

RDBMS Reader

Icon
Use When	Data needs read from a relational database
Samples	Relational Database to Flat File, Normalized Table to Denormalized File, Normalized Table to Denormalized File v2
Description	The RDBMS Reader is used to read data from a sql compliant relational database by specifying a SQL query. The RDBMS Reader can take one or more input messages. The reader will be executed (i.e. the sql query run) based on the 'Run When' option. One time, once for every input message or once for every entity record within an input message that it receives. Any entity/attribute within an input message can be used as a parameter in the SQL parameter by specifying the input model entity and attribute name in the query prefaced with a colon. See Example 1 below. By default, the sql results will be mapped directly to the output model based on the table and column name of the selected fields. When aggregate or other functions are used within the sql statement the table and column name will not be available for mapping to the output model, so hints may be used to map the field directly to an output model entity and attribute. See Example 2 below.
Inbound Message Type	Model Based Message
Output Message Type	Model Based Message
Control Message Handling	Input: When a control message is received and 'Run When' is 'PER UNIT OF WORK' start the processing of this component or when any content message is received and 'Run When' is 'PER ENTITY' or else when any message other than a control is received and 'Run When' is 'PER MESSAGE' or this component is a start step (ie. no input links). Output: A control message will be forwarded to downstream components as determined by the 'Unit of Work' property defined below.

Properties

Name	Description
Resource	The SQL Database Resource on which the SQL query should be run to read data.
Input Model
Output Model
Error Suspense Step	Whether to forward failed messages and continue processing. This is the name of a linked component to forward the failed messages to.
Enabled
Get SQL From Message	Whether to use the input text message (from an input link) as the SQL to submit.
Sql	The sql query or script that will be executed for this reader. May have one or more statements to execute.
Run When	This component also has the option of PER ENTITY. This means that the component will execute SQL for each entity.
Unit of Work	When the 'Run When' option is PER UNIT OF WORK this defines when an 'end of transaction' (control message) is forwarded to downstream components. COMPONENT LIFETIME - Send one final control message after the entire SQL content has been completed SQL SCRIPT - Send a control message once per SQL script completed SQL STATEMENT - Send a control message after each SQL statement completed
Query Timeout (seconds)	Sets the query timeout for statements that this component executes. Default is -1, indicating to use the JDBC driver’s default. Note: Any timeout specified here will be overridden by the remaining transaction timeout when executing within a transaction that has a timeout specified at the transaction level.
Rows Per Message
Trim Columns	Whether leading and trailing spaces should be trimmed from character fields after being selected from the database and before they are placed in the output model format
Match On Column Name	Whether when matching SQL results to the output model, you match on table and column or column name only
Pass Input Rows Through	Whether to forward input messages along with the output SQL results
Log Input
Log Output
Inbound Queue Capacity

Example 1. Using Input Message Entity/Attribute as a parameter

select
   field1,
   field2
from
   mytable
where
   field3=:INPUT_MODEL_ENTITY_NAME.INPUT_MODEL_ATTRIBUTE_NAME

In the example above, the SQL Reader query will be run for each input record within each input message. Each time the query is run, field3 will be restricted by the incoming row’s INPUT_MODEL_ENTITY_NAME.INPUT_MODEL_ATTRIBUTE field.

Tip

All parameters in the sql statement should be prefixed with a colon. Parameters prefixed with a colon will use a prepared statement parameter to execute the query with the parameter. Using the deprecated $(parameter_name) will use string substitution versus a prepared statement parameter and is not recommended.

Example 2. Using Hints to Map to the Output Model

select
   field1,
   field2,
   count(*) /* OUTPUTMODEL_ENTITY_NAME.OUTPUT_MODEL_ATTRIBUTE_NAME */
from
   mytable

In the example above, the results from the query execution will be mapped to:

1. field1 ⇒ MYTABLE.FIELD1

2. field2 ⇒ MYTABLE.FIELD2

3. count(*) ⇒ OUTPUTMODEL_ENTITY_NAME.OUTPUT_MODEL_ATTRIBUTE_NAME

Tip

If a single result set / query results from several queries using the union operator, only place the sql column hints on the first query’s columns, not each set of columns in every sql. Also, hints need to directly follow the field they correspond to prior to any comma if other fields follow the hint.