Retrieves data from a database table.

Properties

Option	Type	Default	Description

Option	Type	Default	Description
`dataSource`	string	`dataSource`	Defines a Data Source name.
`table`	string		Defines a DB table.
`mapper`	string		Defines a data mapper.
`batchMode`	Boolean	false	Defines a batch mode for select.
`batchSize`	integer	5000	Used for fetching from the database.
`dialect`	string	MYSQL	Defines an SQL dialect. Possible values are: MYSQL ORACLE DERBY POSTGRESQL SNOWFLAKE
`sql`	string		Defines an SQL query.
`outputTarget`	string		Defines an output target for data in exchange. It can be stored in header, property or body.
`outputTargetName`	string		Defines an output target name for the target type header and property. Name of the exchange header, name of the exchange property.

Examples

Below is an example of route which uses select in the batch mode together with selectIterator. Note that you have to specify a correct ORDER BY clause when using the batch mode. This ORDER BY has to provide clear deterministic ordering of each loaded row, otherwise there could be unexpected problems with rows mixing.

<route id="loadOrderReasonToIpt">
            <from uri="direct:loadOrderReasonToIpt"/>

            <log loggingLevel="INFO" message="Going to load ORDER_REASON data to IPT" logName="pig.orderReasonIPTLoad" />

            <to uri="pfx-sql:select?sql=select * from V_ORDER_REASON ORDER BY key1,key2&amp;batchMode=true&amp;batchSize=5000&amp;dialect=POSTGRESQL"/>

            <split stopOnException="true" parallelProcessing="false" streaming="true">
                <simple>${body}</simple>
                <to uri="pfx-sql:selectIterator"/>

                <log message="Found ${body.size()} entries" loggingLevel="INFO" logName="pig.orderReasonIPTLoad"/>

                <to uri="pfx-api:loaddata?mapper=orderReasonToPPMapper&amp;objectType=MLTV2&amp;pricingParameterName=GLOBAL.OrderReasonFlag"/>
                <log message="Loaded ${property[CamelSplitIndex]}. batch of ORDER REASON data to IPT" loggingLevel="INFO" logName="pig.orderReasonIPTLoad"/>
            </split>

            <log message="All ORDER REASON data loaded to IPT" loggingLevel="INFO" logName="pig.orderReasonIPTLoad"/>
</route>

Without using the batch mode, the example above would look like this. But it is recommended to use the batch mode whenever possible to avoid performance issues.

<route id="loadOrderReasonToIpt">
            <from uri="direct:loadOrderReasonToIpt"/>

            <log loggingLevel="INFO" message="Going to load ORDER_REASON data to IPT" logName="pig.orderReasonIPTLoad" />

            <to uri="pfx-sql:select?sql=select * from V_ORDER_REASON ORDER BY key1,key2&amp;batchMode=false&amp;dialect=POSTGRESQL"/>

            <to uri="pfx-api:loaddata?mapper=orderReasonToPPMapper&amp;objectType=MLTV2&amp;pricingParameterName=GLOBAL.OrderReasonFlag"/>

            <log message="All ORDER REASON data loaded to IPT" loggingLevel="INFO" logName="pig.orderReasonIPTLoad"/>

</route>