Bruno Dumon created NIFI-14346:
----------------------------------
Summary: 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
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)
