[jira] [Updated] (NIFI-14346) PutDatabaseRecord fails to do upsert on MySQL due to too few parameters bound

["Tomasz Korniszuk (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/NIFI-14346?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Tomasz Korniszuk updated NIFI-14346:
------------------------------------
    Attachment: 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.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)