[
https://issues.apache.org/jira/browse/NIFI-14346?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Tomasz Korniszuk updated NIFI-14346:
------------------------------------
Attachment: (was:
0002-NIFI-14346-Corrected-parameter-count-division-in-UPSERT-process.patch)
> PutDatabaseRecord fails to do upsert on MySQL due to too few parameters bound
> -----------------------------------------------------------------------------
>
> Key: NIFI-14346
> URL: https://issues.apache.org/jira/browse/NIFI-14346
> Project: Apache NiFi
> Issue Type: Bug
> Affects Versions: 2.2.0
> Reporter: Bruno Dumon
> Assignee: Tomasz Korniszuk
> Priority: Major
> Attachments:
> 0002-NIFI-14346-Corrected-parameter-count-division-in-UPSERT-process-1.patch
>
>
> Using the PutDatabaseRecord processor with 'Statement Type' set to
> {{{}UPSERT{}}}, connected to a MySQL database, it fails with
> {{{}java.sql.SQLException: No value specified for parameter 1{}}}.
> On MySQL, as we can see in {{{}MySQLDatabaseAdapter{}}}, for an upsert
> statement two {{?}} parameters are added for each column: once for the
> insert-case, once for the update-case:
> {code:java}
> StringBuilder statementStringBuilder = new StringBuilder("INSERT INTO ")
> .append(table)
> .append("(").append(columns).append(")")
> .append(" VALUES ")
> .append("(").append(parameterizedInsertValues).append(")")
> .append(" ON DUPLICATE KEY UPDATE ")
> .append(parameterizedUpdateValues);
> {code}
> The {{PutDatabaseRecord}} processor binds these parameters as follows:
> {code:java}
> } else if (UPSERT_TYPE.equalsIgnoreCase(statementType)) {
> // Calculate the number of times to set the parameter based on fields
> divided by parameters
> final int timesToAddObjects = fieldIndexes.size() /
> preparedSqlAndColumns.parameterCount;
> for (int j = 0; j < timesToAddObjects; j++) {
> setParameter(ps, i + (fieldIndexes.size() * j) + 1, currentValue,
> fieldSqlType, sqlType);
> }
> {code}
> Say we have records with 5 fields, then {{fieldIndexes.size()}} will be 5 and
> {{preparedSqlAndColumns.parameterCount}} will be 10 (the parameterCount is a
> count of the number of question marks in the statement, see
> {{{}PutDatabaseRecord.getParameterCount{}}}).
> Thus, it is dividing 5 by 10 (which is 0, so no parameters will be set at
> all), while it should be the other way around.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
