private ArrayList<Object[]> writeToTable(RowMetaInterface rowMeta, ArrayList<Object[]> rows) throws KettleException { if (rows.isEmpty()) // Stop: last line or error encountered { if (log.isDetailed()) logDetailed("Last line inserted: stop"); return null; } PreparedStatement insertStatement = null; ArrayList<Object[]> insertRowsData = new ArrayList<Object[]>(); ArrayList<Object[]> outputRowsData = rows; String tableName = null; boolean sendToErrorRow = false; String errorMessage = null; boolean rowIsSafe = false; int[] updateCounts = null; List<Exception> exceptionsList = null; boolean batchProblem = false; for (Object[] row : rows) { if (meta.isTableNameInField()) { // Cache the position of the table name field if (data.indexOfTableNameField < 0) { String realTablename = environmentSubstitute(meta.getTableNameField()); data.indexOfTableNameField = rowMeta.indexOfValue(realTablename); if (data.indexOfTableNameField < 0) { String message = "Unable to find table name field [" + realTablename + "] in input row"; logError(message); throw new KettleStepException(message); } if (!meta.isTableNameInTable()) { data.insertRowMeta.removeValueMeta(data.indexOfTableNameField); } } tableName = rowMeta.getString(rows.get(0), data.indexOfTableNameField); if (!meta.isTableNameInTable()) { // If the name of the table should not be inserted itself, // remove the table name // from the input row data as well. This forcibly creates a // copy of r insertRowsData.add( RowDataUtil.removeItem(rowMeta.cloneRow(row), data.indexOfTableNameField)); } else { insertRowsData.add(row); } } else if (meta.isPartitioningEnabled() && (meta.isPartitioningDaily() || meta.isPartitioningMonthly()) && (meta.getPartitioningField() != null && meta.getPartitioningField().length() > 0)) { // Initialize some stuff! if (data.indexOfPartitioningField < 0) { data.indexOfPartitioningField = rowMeta.indexOfValue(environmentSubstitute(meta.getPartitioningField())); if (data.indexOfPartitioningField < 0) { throw new KettleStepException( "Unable to find field [" + meta.getPartitioningField() + "] in the input row!"); } if (meta.isPartitioningDaily()) { data.dateFormater = new SimpleDateFormat("yyyyMMdd"); } else { data.dateFormater = new SimpleDateFormat("yyyyMM"); } } ValueMetaInterface partitioningValue = rowMeta.getValueMeta(data.indexOfPartitioningField); if (!partitioningValue.isDate() || row[data.indexOfPartitioningField] == null) { throw new KettleStepException( "Sorry, the partitioning field needs to contain a data value and can't be empty!"); } Object partitioningValueData = rowMeta.getDate(row, data.indexOfPartitioningField); tableName = environmentSubstitute(meta.getTablename()) + "_" + data.dateFormater.format((Date) partitioningValueData); insertRowsData.add(row); } else { tableName = data.tableName; insertRowsData.add(row); } if (Const.isEmpty(tableName)) { throw new KettleStepException("The tablename is not defined (empty)"); } } if (!data.preparedStatements.containsKey(tableName)) { data.preparedStatements.put(tableName, new Hashtable<Integer, PreparedStatement>()); } insertStatement = (PreparedStatement) data.preparedStatements.get(tableName).get(rows.size()); if (insertStatement == null) { String sql = getInsertStatement( environmentSubstitute(meta.getSchemaName()), tableName, data.insertRowMeta, rows.size()); if (log.isDetailed()) logDetailed("Prepared statement : " + sql); insertStatement = data.db.prepareSQL(sql); if (!data.preparedStatements.containsKey(tableName)) { data.preparedStatements.put(tableName, new Hashtable<Integer, PreparedStatement>()); } data.preparedStatements.get(tableName).put(rows.size(), insertStatement); } try { // For PG & GP, we add a savepoint before the row. // Then revert to the savepoint afterwards... (not a transaction, so // hopefully still fast) // if (data.specialErrorHandling) { data.savepoint = data.db.setSavepoint(); } RowMeta insertRowMeta = new RowMeta(); for (int i = 0; i < rows.size(); i++) { for (int j = 0; j < data.valuenrs.length; j++) { insertRowMeta.addValueMeta(data.insertRowMeta.getValueMeta(j)); } } data.db.setValues(insertRowMeta, toArray(insertRowsData), insertStatement); data.db.insertRow(insertStatement, data.batchMode, false); // false: // no // commit, // it is // handled // in // this // step // different // Get a commit counter per prepared statement to keep track of // separate tables, etc. // Integer commitCounter = data.commitCounterMap.get(tableName); if (commitCounter == null) { commitCounter = Integer.valueOf(1); } else { commitCounter++; } data.commitCounterMap.put(tableName, Integer.valueOf(commitCounter.intValue())); // Release the savepoint if needed // if (data.specialErrorHandling) { if (data.releaseSavepoint) { data.db.releaseSavepoint(data.savepoint); } } // Perform a commit if needed // if ((data.commitSize > 0) && ((commitCounter % data.commitSize) == 0)) { if (data.batchMode) { try { insertStatement.executeBatch(); data.db.commit(); insertStatement.clearBatch(); } catch (BatchUpdateException ex) { KettleDatabaseBatchException kdbe = new KettleDatabaseBatchException("Error updating batch", ex); kdbe.setUpdateCounts(ex.getUpdateCounts()); List<Exception> exceptions = new ArrayList<Exception>(); // 'seed' the loop with the root exception SQLException nextException = ex; do { exceptions.add(nextException); // while current exception has next exception, add // to list } while ((nextException = nextException.getNextException()) != null); kdbe.setExceptionsList(exceptions); throw kdbe; } catch (SQLException ex) { throw new KettleDatabaseException("Error inserting row", ex); } catch (Exception ex) { throw new KettleDatabaseException("Unexpected error inserting row", ex); } } else { // insertRow normal commit data.db.commit(); } // Clear the batch/commit counter... // data.commitCounterMap.put(tableName, Integer.valueOf(0)); rowIsSafe = true; } else { rowIsSafe = false; } } catch (KettleDatabaseBatchException be) { errorMessage = be.toString(); batchProblem = true; sendToErrorRow = true; updateCounts = be.getUpdateCounts(); exceptionsList = be.getExceptionsList(); if (getStepMeta().isDoingErrorHandling()) { data.db.clearBatch(insertStatement); data.db.commit(true); } else { data.db.clearBatch(insertStatement); data.db.rollback(); StringBuffer msg = new StringBuffer("Error batch inserting rows into table [" + tableName + "]."); msg.append(Const.CR); msg.append("Errors encountered (first 10):").append(Const.CR); for (int x = 0; x < be.getExceptionsList().size() && x < 10; x++) { Exception exception = be.getExceptionsList().get(x); if (exception.getMessage() != null) msg.append(exception.getMessage()).append(Const.CR); } throw new KettleException(msg.toString(), be); } } catch (KettleDatabaseException dbe) { if (getStepMeta().isDoingErrorHandling()) { if (data.specialErrorHandling) { data.db.rollback(data.savepoint); if (data.releaseSavepoint) { data.db.releaseSavepoint(data.savepoint); } // data.db.commit(true); // force a commit on the connection // too. } sendToErrorRow = true; errorMessage = dbe.toString(); } else { if (meta.ignoreErrors()) { if (data.warnings < 20) { if (log.isBasic()) logBasic("WARNING: Couldn't insert row into table." + Const.CR + dbe.getMessage()); } else if (data.warnings == 20) { if (log.isBasic()) logBasic( "FINAL WARNING (no more then 20 displayed): Couldn't insert row into table: " + Const.CR + dbe.getMessage()); } data.warnings++; } else { setErrors(getErrors() + 1); data.db.rollback(); throw new KettleException("Error inserting row into table [" + tableName + "]", dbe); } } } if (data.batchMode) { if (sendToErrorRow) { if (batchProblem) { for (Object[] row : outputRowsData) { data.batchBuffer.add(row); } outputRowsData = null; processBatchException(errorMessage, updateCounts, exceptionsList); } else { // Simply add this row to the error row for (Object[] row : outputRowsData) { putError(rowMeta, row, 1L, errorMessage, null, "TOP001"); } outputRowsData = null; } } else { for (Object[] row : outputRowsData) { data.batchBuffer.add(row); } outputRowsData = null; if (rowIsSafe) // A commit was done and the rows are all safe // (no error) { for (int i = 0; i < data.batchBuffer.size(); i++) { Object[] row = (Object[]) data.batchBuffer.get(i); putRow(data.outputRowMeta, row); incrementLinesOutput(); } // Clear the buffer data.batchBuffer.clear(); } } } else { if (sendToErrorRow) { for (Object[] row : outputRowsData) { putError(rowMeta, row, 1L, errorMessage, null, "TOP001"); } outputRowsData = null; } } return outputRowsData; }
public void dispose(StepMetaInterface smi, StepDataInterface sdi) { meta = (MySQLTableOutputMeta) smi; data = (MySQLTableOutputData) sdi; if (data.db != null) { try { for (String schemaTable : data.preparedStatements.keySet()) { // Get a commit counter per prepared statement to keep track // of separate tables, etc. // Integer batchCounter = data.commitCounterMap.get(schemaTable); if (batchCounter == null) { batchCounter = 0; } for (PreparedStatement insertStatement : data.preparedStatements.get(schemaTable).values()) { data.db.emptyAndCommit(insertStatement, data.batchMode, batchCounter); } } for (int i = 0; i < data.batchBuffer.size(); i++) { Object[] row = (Object[]) data.batchBuffer.get(i); putRow(data.outputRowMeta, row); incrementLinesOutput(); } // Clear the buffer data.batchBuffer.clear(); } catch (KettleDatabaseBatchException be) { if (getStepMeta().isDoingErrorHandling()) { // Right at the back we are experiencing a batch commit // problem... // OK, we have the numbers... try { processBatchException(be.toString(), be.getUpdateCounts(), be.getExceptionsList()); } catch (KettleException e) { logError("Unexpected error processing batch error", e); setErrors(1); stopAll(); } } else { logError("Unexpected batch update error committing the database connection.", be); setErrors(1); stopAll(); } } catch (Exception dbe) { logError("Unexpected error committing the database connection.", dbe); logError(Const.getStackTracker(dbe)); setErrors(1); stopAll(); } finally { setOutputDone(); if (getErrors() > 0) { try { data.db.rollback(); } catch (KettleDatabaseException e) { logError("Unexpected error rolling back the database connection.", e); } } data.db.disconnect(); } super.dispose(smi, sdi); } }
protected Object[] writeToTable(RowMetaInterface rowMeta, Object[] r) throws KettleException { if (r == null) { // Stop: last line or error encountered if (log.isDetailed()) { logDetailed("Last line inserted: stop"); } return null; } PreparedStatement insertStatement = null; Object[] insertRowData; Object[] outputRowData = r; String tableName = null; boolean sendToErrorRow = false; String errorMessage = null; boolean rowIsSafe = false; int[] updateCounts = null; List<Exception> exceptionsList = null; boolean batchProblem = false; Object generatedKey = null; if (meta.isTableNameInField()) { // Cache the position of the table name field if (data.indexOfTableNameField < 0) { String realTablename = environmentSubstitute(meta.getTableNameField()); data.indexOfTableNameField = rowMeta.indexOfValue(realTablename); if (data.indexOfTableNameField < 0) { String message = "Unable to find table name field [" + realTablename + "] in input row"; logError(message); throw new KettleStepException(message); } if (!meta.isTableNameInTable() && !meta.specifyFields()) { data.insertRowMeta.removeValueMeta(data.indexOfTableNameField); } } tableName = rowMeta.getString(r, data.indexOfTableNameField); if (!meta.isTableNameInTable() && !meta.specifyFields()) { // If the name of the table should not be inserted itself, remove the table name // from the input row data as well. This forcibly creates a copy of r // insertRowData = RowDataUtil.removeItem(rowMeta.cloneRow(r), data.indexOfTableNameField); } else { insertRowData = r; } } else if (meta.isPartitioningEnabled() && (meta.isPartitioningDaily() || meta.isPartitioningMonthly()) && (meta.getPartitioningField() != null && meta.getPartitioningField().length() > 0)) { // Initialize some stuff! if (data.indexOfPartitioningField < 0) { data.indexOfPartitioningField = rowMeta.indexOfValue(environmentSubstitute(meta.getPartitioningField())); if (data.indexOfPartitioningField < 0) { throw new KettleStepException( "Unable to find field [" + meta.getPartitioningField() + "] in the input row!"); } if (meta.isPartitioningDaily()) { data.dateFormater = new SimpleDateFormat("yyyyMMdd"); } else { data.dateFormater = new SimpleDateFormat("yyyyMM"); } } ValueMetaInterface partitioningValue = rowMeta.getValueMeta(data.indexOfPartitioningField); if (!partitioningValue.isDate() || r[data.indexOfPartitioningField] == null) { throw new KettleStepException( "Sorry, the partitioning field needs to contain a data value and can't be empty!"); } Object partitioningValueData = rowMeta.getDate(r, data.indexOfPartitioningField); tableName = environmentSubstitute(meta.getTableName()) + "_" + data.dateFormater.format((Date) partitioningValueData); insertRowData = r; } else { tableName = data.tableName; insertRowData = r; } if (meta.specifyFields()) { // // The values to insert are those in the fields sections // insertRowData = new Object[data.valuenrs.length]; for (int idx = 0; idx < data.valuenrs.length; idx++) { insertRowData[idx] = r[data.valuenrs[idx]]; } } if (Const.isEmpty(tableName)) { throw new KettleStepException("The tablename is not defined (empty)"); } insertStatement = data.preparedStatements.get(tableName); if (insertStatement == null) { String sql = data.db.getInsertStatement( environmentSubstitute(meta.getSchemaName()), tableName, data.insertRowMeta); if (log.isDetailed()) { logDetailed("Prepared statement : " + sql); } insertStatement = data.db.prepareSQL(sql, meta.isReturningGeneratedKeys()); data.preparedStatements.put(tableName, insertStatement); } try { // For PG & GP, we add a savepoint before the row. // Then revert to the savepoint afterwards... (not a transaction, so hopefully still fast) // if (data.useSafePoints) { data.savepoint = data.db.setSavepoint(); } data.db.setValues(data.insertRowMeta, insertRowData, insertStatement); data.db.insertRow( insertStatement, data.batchMode, false); // false: no commit, it is handled in this step // different if (isRowLevel()) { logRowlevel("Written row: " + data.insertRowMeta.getString(insertRowData)); } // Get a commit counter per prepared statement to keep track of separate tables, etc. // Integer commitCounter = data.commitCounterMap.get(tableName); if (commitCounter == null) { commitCounter = Integer.valueOf(1); } else { commitCounter++; } data.commitCounterMap.put(tableName, Integer.valueOf(commitCounter.intValue())); // Release the savepoint if needed // if (data.useSafePoints) { if (data.releaseSavepoint) { data.db.releaseSavepoint(data.savepoint); } } // Perform a commit if needed // if ((data.commitSize > 0) && ((commitCounter % data.commitSize) == 0)) { if (data.db.getUseBatchInsert(data.batchMode)) { try { insertStatement.executeBatch(); data.db.commit(); insertStatement.clearBatch(); } catch (SQLException ex) { throw Database.createKettleDatabaseBatchException("Error updating batch", ex); } catch (Exception ex) { throw new KettleDatabaseException("Unexpected error inserting row", ex); } } else { // insertRow normal commit data.db.commit(); } // Clear the batch/commit counter... // data.commitCounterMap.put(tableName, Integer.valueOf(0)); rowIsSafe = true; } else { rowIsSafe = false; } // See if we need to get back the keys as well... if (meta.isReturningGeneratedKeys()) { RowMetaAndData extraKeys = data.db.getGeneratedKeys(insertStatement); if (extraKeys.getRowMeta().size() > 0) { // Send out the good word! // Only 1 key at the moment. (should be enough for now :-) generatedKey = extraKeys.getRowMeta().getInteger(extraKeys.getData(), 0); } else { // we have to throw something here, else we don't know what the // type is of the returned key(s) and we would violate our own rule // that a hop should always contain rows of the same type. throw new KettleStepException( "No generated keys while \"return generated keys\" is active!"); } } } catch (KettleDatabaseBatchException be) { errorMessage = be.toString(); batchProblem = true; sendToErrorRow = true; updateCounts = be.getUpdateCounts(); exceptionsList = be.getExceptionsList(); if (getStepMeta().isDoingErrorHandling()) { data.db.clearBatch(insertStatement); data.db.commit(true); } else { data.db.clearBatch(insertStatement); data.db.rollback(); StringBuilder msg = new StringBuilder("Error batch inserting rows into table [" + tableName + "]."); msg.append(Const.CR); msg.append("Errors encountered (first 10):").append(Const.CR); for (int x = 0; x < be.getExceptionsList().size() && x < 10; x++) { Exception exception = be.getExceptionsList().get(x); if (exception.getMessage() != null) { msg.append(exception.getMessage()).append(Const.CR); } } throw new KettleException(msg.toString(), be); } } catch (KettleDatabaseException dbe) { if (getStepMeta().isDoingErrorHandling()) { if (isRowLevel()) { logRowlevel("Written row to error handling : " + getInputRowMeta().getString(r)); } if (data.useSafePoints) { data.db.rollback(data.savepoint); if (data.releaseSavepoint) { data.db.releaseSavepoint(data.savepoint); } // data.db.commit(true); // force a commit on the connection too. } sendToErrorRow = true; errorMessage = dbe.toString(); } else { if (meta.ignoreErrors()) { if (data.warnings < 20) { if (log.isBasic()) { logBasic( "WARNING: Couldn't insert row into table: " + rowMeta.getString(r) + Const.CR + dbe.getMessage()); } } else if (data.warnings == 20) { if (log.isBasic()) { logBasic( "FINAL WARNING (no more then 20 displayed): Couldn't insert row into table: " + rowMeta.getString(r) + Const.CR + dbe.getMessage()); } } data.warnings++; } else { setErrors(getErrors() + 1); data.db.rollback(); throw new KettleException( "Error inserting row into table [" + tableName + "] with values: " + rowMeta.getString(r), dbe); } } } // We need to add a key if (generatedKey != null) { outputRowData = RowDataUtil.addValueData(outputRowData, rowMeta.size(), generatedKey); } if (data.batchMode) { if (sendToErrorRow) { if (batchProblem) { data.batchBuffer.add(outputRowData); outputRowData = null; processBatchException(errorMessage, updateCounts, exceptionsList); } else { // Simply add this row to the error row putError(rowMeta, r, 1L, errorMessage, null, "TOP001"); outputRowData = null; } } else { data.batchBuffer.add(outputRowData); outputRowData = null; if (rowIsSafe) { // A commit was done and the rows are all safe (no error) for (int i = 0; i < data.batchBuffer.size(); i++) { Object[] row = data.batchBuffer.get(i); putRow(data.outputRowMeta, row); incrementLinesOutput(); } // Clear the buffer data.batchBuffer.clear(); } } } else { if (sendToErrorRow) { putError(rowMeta, r, 1, errorMessage, null, "TOP001"); outputRowData = null; } } return outputRowData; }