private void testException() throws SQLException {
deleteDb("batchUpdates");
conn = getConnection("batchUpdates");
stat = conn.createStatement();
stat.execute("create table test(id int primary key)");
prep = conn.prepareStatement("insert into test values(?)");
for (int i = 0; i < 700; i++) {
prep.setString(1, "x");
prep.addBatch();
}
try {
prep.executeBatch();
} catch (BatchUpdateException e) {
PrintStream temp = System.err;
try {
ByteArrayOutputStream buff = new ByteArrayOutputStream();
PrintStream p = new PrintStream(buff);
System.setErr(p);
e.printStackTrace();
} finally {
System.setErr(temp);
}
}
conn.close();
}
/** Test batched callable statements where the call has no parameters. */
public void testCallStmtNoParams() throws Exception {
dropProcedure("jTDS_PROC");
try {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id numeric(10) identity, data varchar(255))");
stmt.execute(
"create proc jTDS_PROC as " + "INSERT INTO #testbatch (data) VALUES ('same each time')");
CallableStatement cstmt = con.prepareCall("{call jTDS_PROC}");
for (int i = 0; i < 5; i++) {
cstmt.addBatch();
}
int x[];
try {
x = cstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
} finally {
dropProcedure("jTDS_PROC");
}
}
/**
* Test batched callable statements where the call includes literal parameters which prevent the
* use of RPC calls.
*/
public void testCallStmtBatch2() throws Exception {
dropProcedure("jTDS_PROC");
try {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
stmt.execute(
"create proc jTDS_PROC @p1 varchar(10), @p2 varchar(255) as "
+ "INSERT INTO #testbatch VALUES (convert(int, @p1), @p2)");
CallableStatement cstmt = con.prepareCall("{call jTDS_PROC (?, 'literal parameter')}");
for (int i = 0; i < 5; i++) {
if (i == 2) {
cstmt.setString(1, "XXX");
} else {
cstmt.setString(1, Integer.toString(i));
}
cstmt.addBatch();
}
int x[];
try {
x = cstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")) {
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
} else {
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
cstmt.setString(1, Integer.toString(i));
cstmt.addBatch();
}
try {
x = cstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
} finally {
dropProcedure("jTDS_PROC");
}
}
private void testContinueBatch01() throws SQLException {
trace("testContinueBatch01");
int[] batchUpdates = {0, 0, 0};
int buCountLen = 0;
try {
String sPrepStmt = COFFEE_UPDATE_SET;
trace("Prepared Statement String:" + sPrepStmt);
prep = conn.prepareStatement(sPrepStmt);
// Now add a legal update to the batch
prep.setInt(1, 1);
prep.setString(2, "Continue-1");
prep.setString(3, "COFFEE-1");
prep.addBatch();
// Now add an illegal update to the batch by
// forcing a unique constraint violation
// Try changing the key_id of row 3 to 1.
prep.setInt(1, 1);
prep.setString(2, "Invalid");
prep.setString(3, "COFFEE-3");
prep.addBatch();
// Now add a second legal update to the batch
// which will be processed ONLY if the driver supports
// continued batch processing according to 6.2.2.3
// of the J2EE platform spec.
prep.setInt(1, 2);
prep.setString(2, "Continue-2");
prep.setString(3, "COFFEE-2");
prep.addBatch();
// The executeBatch() method will result in a
// BatchUpdateException
prep.executeBatch();
} catch (BatchUpdateException b) {
trace("expected BatchUpdateException");
batchUpdates = b.getUpdateCounts();
buCountLen = batchUpdates.length;
}
if (buCountLen == 1) {
trace("no continued updates - OK");
return;
} else if (buCountLen == 3) {
trace("Driver supports continued updates.");
// Check to see if the third row from the batch was added
String query = COFFEE_SELECT_CONTINUED;
trace("Query is: " + query);
ResultSet rs = stat.executeQuery(query);
rs.next();
int count = rs.getInt(1);
rs.close();
stat.close();
trace("Count val is: " + count);
// make sure that we have the correct error code for
// the failed update.
if (!(batchUpdates[1] == -3 && count == 1)) {
fail("insert failed");
}
}
}
private Object runInTx(Task task, Object... input) {
HibernateCallback action = new RunInHibernate(task, input);
TransactionTemplate txTemplate = new TransactionTemplate(this.txMgr);
boolean rollbackOnly = task instanceof DefaultRollback && !isTxRunning();
RunInTx tx = new RunInTx(action, rollbackOnly);
if (txLogger.isInfoEnabled()) {
if (isTxRunning()) {
txLogger.info("tx is running executing \"" + task.getName() + "\" in current tx");
} else {
txLogger.info("no tx running, wrapping execution of \"" + task.getName() + "\" in tx");
if (rollbackOnly) {
txLogger.info("rollback enabled for \"" + task.getName() + "\"");
}
}
}
Object rtn = null;
try {
rtn = txTemplate.execute(tx);
} catch (Throwable ex) {
// The following logic intends to display a sensible message for the user when a column
// contains a value whose length
// exceeds the maximum length allowed in the database. The logic has been tested on MySQL,
// Postgres, Oracle and
// SQLServer so far.
if (ex.getCause() instanceof java.sql.BatchUpdateException) { // Oracle
String msg =
((java.sql.BatchUpdateException) ex.getCause()).getNextException().getMessage();
if (msg != null) {
ex.printStackTrace();
throw new WMRuntimeException(msg);
}
} else if (ex.getCause().getCause() instanceof java.sql.BatchUpdateException) { // Postgres
java.sql.BatchUpdateException e = (java.sql.BatchUpdateException) ex.getCause().getCause();
if (e != null && e.getMessage() != null) {
ex.printStackTrace();
throw new WMRuntimeException(e.getNextException().getMessage());
}
} else if (ex.getCause().getCause() != null) { // MySQL, SQLServer
String msg = ex.getCause().getCause().getMessage();
if (msg != null) {
ex.printStackTrace();
throw new WMRuntimeException(msg);
}
} else {
throw new WMRuntimeException(ex);
}
}
if (txLogger.isInfoEnabled()) {
if (isTxRunning()) {
txLogger.info("tx is running after execution of \"" + task.getName() + "\"");
} else {
txLogger.info("tx is not running after execution of \"" + task.getName() + "\"");
}
}
return rtn;
}
/** Test batched prepared statements. */
public void testPrepStmtBatch() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
for (int i = 0; i < 5; i++) {
if (i == 2) {
pstmt.setString(1, "xxx");
} else {
pstmt.setInt(1, i);
}
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")) {
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
} else {
// Sybase - Entire batch fails due to data conversion error
// detected in statement 3
assertEquals(5, x.length);
assertEquals(EXECUTE_FAILED, x[0]);
assertEquals(EXECUTE_FAILED, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
}
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
x = pstmt.executeBatch();
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
/** Test batched statements. */
public void testBatch() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
for (int i = 0; i < 5; i++) {
if (i == 2) {
// This statement will generate an error
stmt.addBatch("INSERT INTO #testbatch VALUES ('xx', 'This is line " + i + "')");
} else {
stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
}
}
int x[];
try {
x = stmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")
&& ((JtdsDatabaseMetaData) con.getMetaData()).getDatabaseMajorVersion() > 6) {
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
} else {
// Sybase or SQL Server 6.5 - Entire batch fails due to data conversion error
// detected in statement 3
assertEquals(5, x.length);
assertEquals(EXECUTE_FAILED, x[0]);
assertEquals(EXECUTE_FAILED, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
}
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
}
x = stmt.executeBatch();
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
/**
* This test should generate an error as the second statement in the batch returns a result set.
*/
public void testResultSetError() throws Exception {
Statement stmt = con.createStatement();
stmt.addBatch("create table #testbatch (id int, data varchar(255))");
stmt.addBatch("insert into #testbatch VALUES(1, 'Test line')");
stmt.addBatch("SELECT 'This is an error'");
int x[];
try {
x = stmt.executeBatch();
fail("Expecting BatchUpdateException");
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(3, x.length);
assertEquals(SUCCESS_NO_INFO, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
}
private void extractDetails(BatchUpdateException ex) {
String msg = ex.getMessage();
Matcher m = FOREIGN_KEY_CONSTRAINT.matcher(msg);
if (m.matches()) {
this.child = m.group(2);
this.parent = m.group(1);
} else {
logger.error("matcher did not match, ex='" + root.getStackTrace() + "' msg was:" + msg);
}
}
public void run() {
try {
PreparedStatement pstmt =
con.prepareStatement(
"insert into #testConcurrentBatch (v1, v2, v3, v4, v5, v6) values (?, ?, ?, ?, ?, ?)");
for (int i = 0; i < 64; ++i) {
// Make sure we end up with 64 different prepares, use the binary representation of i to
// set each
// of the 6 parameters to either an int or a string.
int mask = i;
for (int j = 1; j <= 6; ++j, mask >>= 1) {
if ((mask & 1) != 0) {
pstmt.setInt(j, i);
} else {
pstmt.setString(j, String.valueOf(i));
}
}
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
e.printStackTrace();
x = e.getUpdateCounts();
}
if (x.length != 64) {
throw new SQLException("Expected 64 update counts, got " + x.length);
}
for (int i = 0; i < x.length; ++i) {
if (x[i] != 1) {
throw new SQLException("Error at position " + i + ", got " + x[i] + " instead of 1");
}
}
// Rollback the transaction, exposing any race conditions.
con.rollback();
pstmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
exceptions.add(ex);
}
}
@Override
public synchronized void write(final Record record) {
try {
final RecordDefinition recordDefinition = record.getRecordDefinition();
final RecordStore recordStore = recordDefinition.getRecordStore();
final RecordState state = record.getState();
if (recordStore != this.recordStore) {
if (state != RecordState.DELETED) {
insert(record);
}
} else {
switch (state) {
case NEW:
insert(record);
break;
case MODIFIED:
update(record);
break;
case PERSISTED:
// No action required
break;
case DELETED:
delete(record);
break;
default:
throw new IllegalStateException("State not known");
}
}
} catch (final RuntimeException e) {
throw e;
} catch (final Error e) {
throw e;
} catch (final BatchUpdateException e) {
for (SQLException e1 = e.getNextException(); e1 != null; e1 = e1.getNextException()) {
LOG.error("Unable to write", e1);
}
throw new RuntimeException("Unable to write", e);
} catch (final Exception e) {
throw new RuntimeException("Unable to write", e);
}
}
/** Test for bug [1371295] SQL Server continues after duplicate key error. */
public void testPrepStmtBatchDupKey() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255), PRIMARY KEY (id))");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
for (int i = 0; i < 5; i++) {
if (i == 2) {
pstmt.setInt(1, 1); // Will cause duplicate key batch will continue
} else {
pstmt.setInt(1, i);
}
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
x = pstmt.executeBatch();
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
public int[] executeBatch() throws BatchUpdateException {
if (batches == null) return new int[0];
final int[] result = new int[batches.size()];
BatchUpdateException failed = null;
for (int i = 0; i < result.length; i++) {
try {
result[i] = executeUpdate((String) batches.get(i));
} catch (SQLException ex) {
result[i] = EXECUTE_FAILED;
if (failed == null) {
failed =
new BatchUpdateException(
ex.getMessage(), ex.getSQLState(), ex.getErrorCode(), result);
failed.initCause(ex);
}
failed.setNextException(ex);
}
}
batches.clear();
if (failed != null) throw failed;
return result;
}
/** Test for PreparedStatement batch with no parameters. */
public void testPrepStmtNoParams() throws Exception {
Statement stmt = con.createStatement();
stmt.execute(
"create table #testbatch (id numeric(10) identity, data varchar(255), PRIMARY KEY (id))");
PreparedStatement pstmt =
con.prepareStatement("INSERT INTO #testbatch (data) VALUES ('Same each time')");
for (int i = 0; i < 5; i++) {
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
private void testExecuteBatch06() throws SQLException {
trace("testExecuteBatch06");
boolean batchExceptionFlag = false;
// Insert a row which is already Present
String sInsCoffee = COFFEE_INSERT1;
String sDelCoffee = COFFEE_DELETE1;
stat.addBatch(sInsCoffee);
stat.addBatch(sInsCoffee);
stat.addBatch(sDelCoffee);
try {
stat.executeBatch();
} catch (BatchUpdateException b) {
batchExceptionFlag = true;
for (int uc : b.getUpdateCounts()) {
trace("Update counts:" + uc);
}
}
if (batchExceptionFlag) {
trace("executeBatch insert duplicate; correct");
} else {
fail("executeBatch");
}
}
/** Test for bug [1371295] SQL Server continues after duplicate key error. */
public void testBatchDupKey() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255), PRIMARY KEY (id))");
for (int i = 0; i < 5; i++) {
if (i == 2) {
// This statement will generate an duplicate key error
stmt.addBatch("INSERT INTO #testbatch VALUES (1, 'This is line " + i + "')");
} else {
stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
}
}
int x[];
try {
x = stmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
}
x = stmt.executeBatch();
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
/** Test for PreparedStatement batch with variable parameter types. */
public void testPrepStmtVariableParams() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data int, PRIMARY KEY (id))");
PreparedStatement pstmt =
con.prepareStatement("INSERT INTO #testbatch VALUES (?, convert(int, ?))");
for (int i = 0; i < 5; i++) {
pstmt.setInt(1, i);
if (i == 2) {
// This statement will require a string param instead of an int
pstmt.setString(2, "123");
} else {
pstmt.setInt(2, 123);
}
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
ResultSet rs = stmt.executeQuery("SELECT * FROM #testbatch");
assertNotNull(rs);
int i = 0;
while (rs.next()) {
assertEquals(123, rs.getInt(2));
i++;
}
assertEquals(5, i);
}
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;
}