{"record":{"id":"fadc1ee2c8390a6d","repo":"pentaho/pentaho-kettle","slug":"error-inserting-updating-row","errorCode":null,"errorMessage":"Error inserting/updating row","messagePattern":"Error inserting/updating row","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"error","filePath":"core/src/main/java/org/pentaho/di/core/database/Database.java","lineNumber":1620,"sourceCode":"          ps.executeBatch();\n          commit();\n          ps.clearBatch();\n        } else {\n          debug = \"insertRow normal commit\";\n          commit();\n        }\n        written = 0;\n        rowsAreSafe = true;\n      }\n\n      return rowsAreSafe;\n    } catch ( BatchUpdateException ex ) {\n      throw createKettleDatabaseBatchException( \"Error updating batch\", ex );\n    } catch ( SQLException ex ) {\n      if ( isBatchUpdate ) {\n        throw createKettleDatabaseBatchException( \"Error updating batch\", ex );\n      } else {\n        throw new KettleDatabaseException( \"Error inserting/updating row\", ex );\n      }\n    } catch ( Exception e ) {\n      throw new KettleDatabaseException( \"Unexpected error inserting/updating row in part [\" + debug + \"]\", e );\n    }\n  }\n\n  /**\n   * Clears batch of insert prepared statement\n   *\n   * @throws KettleDatabaseException\n   * @deprecated\n   */\n  @Deprecated\n  public void clearInsertBatch() throws KettleDatabaseException {\n    clearBatch( prepStatementInsert );\n  }\n\n  public void clearBatch( PreparedStatement preparedStatement ) throws KettleDatabaseException {","sourceCodeStart":1602,"sourceCodeEnd":1638,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/core/src/main/java/org/pentaho/di/core/database/Database.java#L1602-L1638","documentation":"In the shared execute/insert path (execUpdate/insertRow with prepared statements), a non-batch SQLException during statement execution is wrapped as KettleDatabaseException('Error inserting/updating row'). This is the generic JDBC execution failure for a single-row insert/update: constraint violations, syntax errors, deadlocks, connection loss, etc. The cause contains the driver's real message.","triggerScenarios":"ps.executeUpdate()/execute() throwing SQLException with isBatchUpdate=false — e.g. duplicate key, NOT NULL violation, foreign key violation, table missing, connection broken, SQL syntax error.","commonSituations":"Duplicate primary keys from re-runs; truncation of strings longer than column size; table dropped/renamed; connection idle timeout killed the session; wrong schema/table name in the target table setting.","solutions":["Read the chained SQLException cause for the exact DB error (ORA-xxxx, MySQL error code)","Fix the data causing constraint violations (deduplicate keys, handle nulls)","Verify the target table exists and column types match the row metadata","Check connection health / increase pool and network timeouts if connection loss","Enable debug field (debug Db part label) to identify which insert/update part failed"],"exampleFix":null,"handlingStrategy":"try-catch","validationCode":"// pre-check for duplicate keys before insert\n// e.g. SELECT COUNT(*) FROM target WHERE key = ?","typeGuard":null,"tryCatchPattern":"try {\n  database.insertRow(tableName, rowMeta, data);\n} catch (KettleDatabaseException e) {\n  SQLException root = getRootSqlException(e);\n  if (root != null && isConstraintViolation(root)) {\n    // route row to error stream / alternate table\n  } else {\n    throw e;\n  }\n}","preventionTips":["Use the step's error handling to divert rejected rows","Keep string lengths within column sizes","Deduplicate keys upstream (Sort + Unique rows)","Verify target table exists in every environment"],"tags":["jdbc","sql","insert-update"],"backgroundTag":"database-write-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"}