JDBC Producer throws "JDBC_23 - Can't coerce 'some value' of type 'STRING' to column 'someColumn'" when 'multi-row' is enabled

Product: StreamSets Data Collector

Issue:

When executing a pipeline writing to a database via the JDBC Producer destination, the pipeline fails and the following exception is seen in the logs:

Failed executing stage 'JDBCProducer_01': com.streamsets.pipeline.api.base.OnRecordErrorException: JDBC_23 - Can't coerce 'some value' of type 'STRING' to column 'someColumn'
...
The column index is out of range: 1, number of columns: 0

The JDBC Producer stage has the 'multi-row' configuration enabled for it.

Solution:

This is the result of a misconfiguration for the Statement Parameter Limit property that appears as a configurable option when 'multi-row' is enabled for the JDBC Producer stage.

The Statement Parameter Limit is a configured limit for the total number of parameters a given query is allowed to have. If this limit is exceeded, the query will fail with the exception noted above - it expects values to be supplied for the query, but the parameter limit restricts the JDBC Producer stage from passing them through to the query.

By default the limit is set -1, meaning there is no limit enforced. Please verify that the configuration is either set to -1, or that the limit is increased sufficiently high enough to encompass the number of parameters used by any query being sent through the JDBC Producer stage.