{"record":{"id":"bccf4989b10aa780","repo":"pentaho/pentaho-kettle","slug":"dynamicsqlrow-exception-incorrectnrtemplatefields","errorCode":null,"errorMessage":"DynamicSQLRow.Exception.IncorrectNrTemplateFields","messagePattern":"DynamicSQLRow\\.Exception\\.IncorrectNrTemplateFields","errorType":"exception","errorClass":"KettleException","httpStatus":null,"severity":"error","filePath":"engine/src/main/java/org/pentaho/di/trans/steps/dynamicsqlrow/DynamicSQLRow.java","lineNumber":122,"sourceCode":"      }\n    } else {\n      if ( meta.isQueryOnlyOnChange() ) {\n        data.previousrowbuffer.clear();\n      }\n\n      // Set the values on the prepared statement (for faster exec.)\n      ResultSet rs = data.db.openQuery( sql );\n\n      // Get a row from the database...\n      Object[] add = data.db.getRow( rs );\n      RowMetaInterface addMeta = data.db.getReturnRowMeta();\n\n      // Also validate the data types to make sure we've not place an incorrect template in the dialog...\n      //\n      if ( add != null ) {\n        int nrTemplateFields = data.outputRowMeta.size() - getInputRowMeta().size();\n        if ( addMeta.size() != nrTemplateFields ) {\n          throw new KettleException( BaseMessages.getString(\n            PKG, \"DynamicSQLRow.Exception.IncorrectNrTemplateFields\", nrTemplateFields, addMeta.size(), sql ) );\n        }\n        StringBuilder typeErrors = new StringBuilder();\n        for ( int i = 0; i < addMeta.size(); i++ ) {\n          ValueMetaInterface templateValueMeta = addMeta.getValueMeta( i );\n          ValueMetaInterface outputValueMeta = data.outputRowMeta.getValueMeta( getInputRowMeta().size() + i );\n\n          if ( templateValueMeta.getType() != outputValueMeta.getType() ) {\n            if ( typeErrors.length() > 0 ) {\n              typeErrors.append( Const.CR );\n            }\n            typeErrors.append( BaseMessages.getString(\n              PKG, \"DynamicSQLRow.Exception.TemplateReturnDataTypeError\", templateValueMeta.toString(),\n              outputValueMeta.toString() ) );\n          }\n        }\n        if ( typeErrors.length() > 0 ) {\n          throw new KettleException( typeErrors.toString() );","sourceCodeStart":104,"sourceCodeEnd":140,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/engine/src/main/java/org/pentaho/di/trans/steps/dynamicsqlrow/DynamicSQLRow.java#L104-L140","documentation":"DynamicSQLRow.lookupValues validates that the number of template fields (addMeta, the fields the SQL is expected to return) matches the number of columns the step actually appends to the output row (outputRowMeta size minus input size). If these counts differ, it throws this KettleException naming both counts and the SQL.","triggerScenarios":"At runtime in processRow→lookupValues when the template row metadata (addMeta from getQueryFields) has a different field count than the declared output row metadata — e.g. the SQL was edited after the step metadata was generated, or 'replace variables' changed the query.","commonSituations":"Editing the dynamic SQL template without re-running 'SQL' detection in the step dialog; environment variable substitution producing a different column count per run; cached QueryFields from an older SQL.","solutions":["Reopen the Dynamic SQL Row step, re-detect the query fields so output fields match the SQL","Verify the SQL template column count is stable across variable substitution","Clear any cached query-field metadata and re-save the transformation","Align the declared output fields in the dialog with the actual SELECT list"],"exampleFix":"// before\nSELECT id, name FROM customers WHERE region = ?   // dialog still declares 3 return fields\n// after\nSELECT id, name, created_at FROM customers WHERE region = ?   // now matches the 3 declared fields","handlingStrategy":"validation","validationCode":"// In the step dialog, click 'SQL' to re-detect fields after any SQL change,\n// then verify counts match:\nint declaredOutputFields = outputRowMeta.size() - inputRowMeta.size();\nif (declaredOutputFields != templateFields.size()) {\n  throw new IllegalStateException(\"Template/output field count mismatch: \" + declaredOutputFields + \" vs \" + templateFields.size());\n}","typeGuard":null,"tryCatchPattern":"try {\n  return lookupValues(inputRowMeta, row);\n} catch (KettleException e) {\n  logError(\"Template field count mismatch — re-detect SQL fields in the dialog\", e);\n  throw e;\n}","preventionTips":["Re-run field detection in the step dialog after every SQL edit","Avoid SQL templates whose column count varies with variable values","Clear cached query fields when switching databases/drivers","Preview the step after changes to catch mismatches at design time"],"tags":["kettle","sql","metadata"],"backgroundTag":"schema-validation-failed","analyzedSha":"f3058517a153da500bf4551f46d79b91bf8ec552","analyzedAt":"2026-09-13T14:04:16.340Z","contentChangedAt":"2026-09-13T14:04:16.340Z","schemaVersion":2},"datasetVersion":"2026-09-20T23:17:15.980Z"}