Inserts data to a database table.

Properties

Option	Type	Default	Description

Option	Type	Default	Description
`dataSource`	string	`dataSource`	Defines a Data Source name.
`table`	string		Defines a database table.
`mapper`	string		Defines a data mapper.
`batchSize`	integer	5000	Used for fetching from the database.
`dialect`	string	MYSQL	Defines an SQL dialect. Possible values are: MYSQL ORACLE DERBY POSTGRESQL SNOWFLAKE
`businessKeys`	string		Defines business keys.
`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

These examples of SQL insert using the pfx-sql component are quite straightforward. You only need to set a correct body and mapper.

Example of inserting rows from a CSV file to a POSTGRESQL database table:

Mapper:

<pfx:loadMapper id="orderReasonMapper" includeUnmappedProperties="false">
        <pfx:groovy expression="body.ORDER_REASON == null || body.ORDER_REASON.length() == 0 ? '-' : body.ORDER_REASON"     out="ORDER_REASON" />
        <pfx:groovy expression="body.SALES_ORG == null || body.SALES_ORG.length() == 0 ? '-' : body.SALES_ORG"              out="SALES_ORG" />
        <pfx:body in="FLAG_TO_RELEVANT"/>
        <pfx:body in="FLAG_QTY_RELEVANT" />
        <pfx:body in="FLAG_IPT_RELEVANT"/>
        <pfx:body in="FLAG_COGS_RELEVANT"/>
</pfx:loadMapper>

Route:

<split streaming="true" stopOnException="true">
      <tokenize token="\n" group="5000"/>

      <log loggingLevel="INFO" message="Going to unmarshall ${property[CamelSplitIndex]}. batch of ORDER REASONS from CVS file" logName="pig.orderReasonCsvLoad"/>
      <to uri="pfx-csv:unmarshal?header={{orderReasonData-header}}&amp;skipHeaderRecord=false&amp;quoteDisabled=true&amp;delimiter=;"/>
      <log loggingLevel="INFO" message="Unmarshalled ${property[CamelSplitIndex]}. batch of ORDER REASONS from CVS file successfuly" logName="pig.orderReasonCsvLoad"/>

      <log loggingLevel="INFO" message="Going to load ${property[CamelSplitIndex]}. batch of ORDER REASONS to DB" logName="pig.orderReasonCsvLoad"/>
      <to uri="pfx-sql:insert?mapper=orderReasonMapper&amp;businessKeys=ORDER_REASON,SALES_ORG&amp;table=ORDER_REASON&amp;dialect=POSTGRESQL"/>
      <log loggingLevel="INFO" message="ORDER REASON data ${property[CamelSplitIndex]}. batch load to DB processed successfully" logName="pig.billingTransactionsCsvLoad"/>
</split>