You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by "Sean Xiong (Created) (JIRA)" <ji...@apache.org> on 2011/10/27 01:59:32 UTC
[jira] [Created] (DDLUTILS-271) ERROR: current transaction is
aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
--------------------------------------------------------------------------------------
Key: DDLUTILS-271
URL: https://issues.apache.org/jira/browse/DDLUTILS-271
Project: DdlUtils
Issue Type: Bug
Components: Core (No specific database)
Affects Versions: 1.0
Reporter: Sean Xiong
Assignee: Thomas Dudziak
After a violation of unique index inside the database an Exception is thrown and DDLUTILS keeps throwing "ERROR: current transaction is aborted, commands ignored until end of transaction block" stopping the application to do any database activity through it.
User case: trying to insert two data rows with the same primary key to table.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DDLUTILS-271) ERROR: current transaction is
aborted, commands ignored until end of transaction block
Posted by "Sean Xiong (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DDLUTILS-271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13136629#comment-13136629 ]
Sean Xiong commented on DDLUTILS-271:
-------------------------------------
My Solution:
1) Create a sql savepoint before call statement.executeUpdate() or statement.executeBatch().
2) Throw sql exception if the execute in step 1 is rejected by DB serve.
3) Catch the sql exception and call connection.rollback to roll the status of connection back to the savepoint we created in step 1.
Affected files:
PlatformImplBase.java of DDLUtils
modifications:
1) Method public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException;
2) Method public void update(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException;
3) Method private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException;
All modified block is putted between //<Modification done By Sean Xiong> //</Modification done By Sean Xiong>
Method (1)
public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
{
SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
SqlDynaProperty[] properties = getPropertiesForInsertion(model, dynaClass, dynaBean);
Column[] autoIncrColumns = getRelevantIdentityColumns(model, dynaClass, dynaBean);
if ((properties.length == 0) && (autoIncrColumns.length == 0))
{
_log.warn("Cannot insert instances of type " + dynaClass + " because it has no usable properties");
return;
}
String insertSql = createInsertSql(model, dynaClass, properties, null);
String queryIdentitySql = null;
if (_log.isDebugEnabled())
{
_log.debug("About to execute SQL: " + insertSql);
}
if (autoIncrColumns.length > 0)
{
if (!getPlatformInfo().isLastIdentityValueReadable())
{
_log.warn("The database does not support querying for auto-generated column values");
}
else
{
queryIdentitySql = createSelectLastInsertIdSql(model, dynaClass);
}
}
boolean autoCommitMode = false;
PreparedStatement statement = null;
try
{
if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
}
beforeInsert(connection, dynaClass.getTable());
statement = connection.prepareStatement(insertSql);
//System.out.println("insetSql " + insertSql);
for (int idx = 0; idx < properties.length; idx++ )
{
setObject(statement, idx + 1, dynaBean, properties[idx]);
}
//System.out.println("statement" + statement.toString());
//<Modification done By Sean Xiong>
int count = -1;
try {
count = statement.executeUpdate();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
// reset the autoCommit of connection to true
connection.setAutoCommit(autoCommitMode);
throw sqle;
}
//</Modification done By Sean Xiong>
afterInsert(connection, dynaClass.getTable());
if (count != 1)
{
_log.warn("Attempted to insert a single row " + dynaBean +
" in table " + dynaClass.getTableName() +
" but changed " + count + " row(s)");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database: " + ex.getMessage(), ex);
}
finally
{
closeStatement(statement);
}
if (queryIdentitySql != null)
{
Statement queryStmt = null;
ResultSet lastInsertedIds = null;
try
{
if (getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
// we'll commit the statement(s) if no auto-commit is enabled because
// otherwise it is possible that the auto increment hasn't happened yet
// (the db didn't actually perform the insert yet so no triggering of
// sequences did occur)
if (!connection.getAutoCommit())
{
connection.commit();
}
}
queryStmt = connection.createStatement();
lastInsertedIds = queryStmt.executeQuery(queryIdentitySql);
lastInsertedIds.next();
for (int idx = 0; idx < autoIncrColumns.length; idx++)
{
// we're using the index rather than the name because we cannot know how
// the SQL statement looks like; rather we assume that we get the values
// back in the same order as the auto increment columns
Object value = getObjectFromResultSet(lastInsertedIds, autoIncrColumns[idx], idx + 1);
PropertyUtils.setProperty(dynaBean, autoIncrColumns[idx].getName(), value);
}
}
catch (NoSuchMethodException ex)
{
// Can't happen because we're using dyna beans
}
catch (IllegalAccessException ex)
{
// Can't happen because we're using dyna beans
}
catch (InvocationTargetException ex)
{
// Can't happen because we're using dyna beans
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while retrieving the identity column value(s) from the database", ex);
}
finally
{
if (lastInsertedIds != null)
{
try
{
lastInsertedIds.close();
}
catch (SQLException ex)
{
// we ignore this one
}
}
closeStatement(statement);
}
}
if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
try
{
// we need to do a manual commit now
connection.commit();
connection.setAutoCommit(autoCommitMode);
}
catch (SQLException ex)
{
throw new DatabaseOperationException(ex);
}
}
}
Method (2)
private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException
{
if (statement != null)
{
try
{
Connection connection = statement.getConnection();
beforeInsert(connection, table);
//<Modification done By Sean Xiong>
boolean autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
int[] results = null;
try {
results = statement.executeBatch();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
throw sqle;
} finally {
// reset the autoCommit of connection to its original value
connection.setAutoCommit(autoCommitMode);
}
//</Modification done By Sean Xiong>
closeStatement(statement);
afterInsert(connection, table);
boolean hasSum = true;
int sum = 0;
for (int idx = 0; (results != null) && (idx < results.length); idx++)
{
if (results[idx] < 0)
{
hasSum = false;
if (Jdbc3Utils.supportsJava14BatchResultCodes())
{
String msg = Jdbc3Utils.getBatchResultMessage(table.getName(), idx, results[idx]);
if (msg != null)
{
_log.warn(msg);
}
}
}
else
{
sum += results[idx];
}
}
if (hasSum && (sum != numRows))
{
_log.warn("Attempted to insert " + numRows + " rows into table " + table.getName() + " but changed " + sum + " rows");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database", ex);
}
}
}
Method (3)
private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException
{
if (statement != null)
{
try
{
Connection connection = statement.getConnection();
beforeInsert(connection, table);
//<Modification done By Sean Xiong>
boolean autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
int[] results = null;
try {
results = statement.executeBatch();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
throw sqle;
} finally {
// reset the autoCommit of connection to its original value
connection.setAutoCommit(autoCommitMode);
}
//</Modification done By Sean Xiong>
closeStatement(statement);
afterInsert(connection, table);
boolean hasSum = true;
int sum = 0;
for (int idx = 0; (results != null) && (idx < results.length); idx++)
{
if (results[idx] < 0)
{
hasSum = false;
if (Jdbc3Utils.supportsJava14BatchResultCodes())
{
String msg = Jdbc3Utils.getBatchResultMessage(table.getName(), idx, results[idx]);
if (msg != null)
{
_log.warn(msg);
}
}
}
else
{
sum += results[idx];
}
}
if (hasSum && (sum != numRows))
{
_log.warn("Attempted to insert " + numRows + " rows into table " + table.getName() + " but changed " + sum + " rows");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database", ex);
}
}
}
> ERROR: current transaction is aborted, commands ignored until end of transaction block
> --------------------------------------------------------------------------------------
>
> Key: DDLUTILS-271
> URL: https://issues.apache.org/jira/browse/DDLUTILS-271
> Project: DdlUtils
> Issue Type: Bug
> Components: Core (No specific database)
> Affects Versions: 1.0
> Reporter: Sean Xiong
> Assignee: Thomas Dudziak
> Labels: violation,unique,index
>
> After a violation of unique index inside the database an Exception is thrown and DDLUTILS keeps throwing "ERROR: current transaction is aborted, commands ignored until end of transaction block" stopping the application to do any database activity through it.
> User case: trying to insert two data rows with the same primary key to table.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Issue Comment Edited] (DDLUTILS-271) ERROR: current
transaction is aborted, commands ignored until end of transaction block
Posted by "Sean Xiong (Issue Comment Edited) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DDLUTILS-271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13136629#comment-13136629 ]
Sean Xiong edited comment on DDLUTILS-271 at 10/27/11 12:26 AM:
----------------------------------------------------------------
My Solution:
Step 1) store the value of connection.getAutoCommit and change connection.setAutoCommit to false before call statement.executeUpdate() or statement.executeBatch().
2) Throw sql exception if the execute in step 1 is rejected by DB serve.
3) Catch the sql exception and call rollback() to Undoes all changes made in the current transaction.
Affected files:
PlatformImplBase.java of DDLUtils
modifications:
1) Method public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException;
2) Method public void update(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException;
3) Method private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException;
All modified block is putted between //<Modification done By Sean Xiong> //</Modification done By Sean Xiong>
Method (1)
public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
{
SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
SqlDynaProperty[] properties = getPropertiesForInsertion(model, dynaClass, dynaBean);
Column[] autoIncrColumns = getRelevantIdentityColumns(model, dynaClass, dynaBean);
if ((properties.length == 0) && (autoIncrColumns.length == 0))
{
_log.warn("Cannot insert instances of type " + dynaClass + " because it has no usable properties");
return;
}
String insertSql = createInsertSql(model, dynaClass, properties, null);
String queryIdentitySql = null;
if (_log.isDebugEnabled())
{
_log.debug("About to execute SQL: " + insertSql);
}
if (autoIncrColumns.length > 0)
{
if (!getPlatformInfo().isLastIdentityValueReadable())
{
_log.warn("The database does not support querying for auto-generated column values");
}
else
{
queryIdentitySql = createSelectLastInsertIdSql(model, dynaClass);
}
}
boolean autoCommitMode = false;
PreparedStatement statement = null;
try
{
if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
}
beforeInsert(connection, dynaClass.getTable());
statement = connection.prepareStatement(insertSql);
for (int idx = 0; idx < properties.length; idx++ )
{
setObject(statement, idx + 1, dynaBean, properties[idx]);
}
//<Modification done By Sean Xiong>
int count = -1;
try {
count = statement.executeUpdate();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
// reset the autoCommit of connection to true
connection.setAutoCommit(autoCommitMode);
throw sqle;
}
//</Modification done By Sean Xiong>
afterInsert(connection, dynaClass.getTable());
if (count != 1)
{
_log.warn("Attempted to insert a single row " + dynaBean +
" in table " + dynaClass.getTableName() +
" but changed " + count + " row(s)");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database: " + ex.getMessage(), ex);
}
finally
{
closeStatement(statement);
}
if (queryIdentitySql != null)
{
Statement queryStmt = null;
ResultSet lastInsertedIds = null;
try
{
if (getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
// we'll commit the statement(s) if no auto-commit is enabled because
// otherwise it is possible that the auto increment hasn't happened yet
// (the db didn't actually perform the insert yet so no triggering of
// sequences did occur)
if (!connection.getAutoCommit())
{
connection.commit();
}
}
queryStmt = connection.createStatement();
lastInsertedIds = queryStmt.executeQuery(queryIdentitySql);
lastInsertedIds.next();
for (int idx = 0; idx < autoIncrColumns.length; idx++)
{
// we're using the index rather than the name because we cannot know how
// the SQL statement looks like; rather we assume that we get the values
// back in the same order as the auto increment columns
Object value = getObjectFromResultSet(lastInsertedIds, autoIncrColumns[idx], idx + 1);
PropertyUtils.setProperty(dynaBean, autoIncrColumns[idx].getName(), value);
}
}
catch (NoSuchMethodException ex)
{
// Can't happen because we're using dyna beans
}
catch (IllegalAccessException ex)
{
// Can't happen because we're using dyna beans
}
catch (InvocationTargetException ex)
{
// Can't happen because we're using dyna beans
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while retrieving the identity column value(s) from the database", ex);
}
finally
{
if (lastInsertedIds != null)
{
try
{
lastInsertedIds.close();
}
catch (SQLException ex)
{
// we ignore this one
}
}
closeStatement(statement);
}
}
if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
try
{
// we need to do a manual commit now
connection.commit();
connection.setAutoCommit(autoCommitMode);
}
catch (SQLException ex)
{
throw new DatabaseOperationException(ex);
}
}
}
Method (2)
public void update(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
{
SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
SqlDynaProperty[] primaryKeys = dynaClass.getPrimaryKeyProperties();
if (primaryKeys.length == 0)
{
_log.info("Cannot update instances of type " + dynaClass + " because it has no primary keys");
return;
}
SqlDynaProperty[] properties = dynaClass.getNonPrimaryKeyProperties();
String sql = createUpdateSql(model, dynaClass, primaryKeys, properties, null);
PreparedStatement statement = null;
if (_log.isDebugEnabled())
{
_log.debug("About to execute SQL: " + sql);
}
try
{
beforeUpdate(connection, dynaClass.getTable());
statement = connection.prepareStatement(sql);
int sqlIndex = 1;
for (int idx = 0; idx < properties.length; idx++)
{
setObject(statement, sqlIndex++, dynaBean, properties[idx]);
}
for (int idx = 0; idx < primaryKeys.length; idx++)
{
setObject(statement, sqlIndex++, dynaBean, primaryKeys[idx]);
}
//<Modification done By Sean Xiong>
boolean autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
int count = -1;
try {
count = statement.executeUpdate();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
throw sqle;
} finally {
// reset the autoCommit of connection to its original value
connection.setAutoCommit(autoCommitMode);
}
//</Modification done By Sean Xiong>
afterUpdate(connection, dynaClass.getTable());
if (count != 1)
{
_log.warn("Attempted to insert a single row " + dynaBean +
" into table " + dynaClass.getTableName() +
" but changed " + count + " row(s)");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while updating in the database", ex);
}
finally
{
closeStatement(statement);
}
}
/**
* {@inheritDoc}
*/
public void update(Database model, DynaBean dynaBean) throws DatabaseOperationException
{
Connection connection = borrowConnection();
try
{
update(connection, model, dynaBean);
}
finally
{
returnConnection(connection);
}
}
Method (3)
private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException
{
if (statement != null)
{
try
{
Connection connection = statement.getConnection();
beforeInsert(connection, table);
//<Modification done By Sean Xiong>
boolean autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
int[] results = null;
try {
results = statement.executeBatch();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
throw sqle;
} finally {
// reset the autoCommit of connection to its original value
connection.setAutoCommit(autoCommitMode);
}
//</Modification done By Sean Xiong>
closeStatement(statement);
afterInsert(connection, table);
boolean hasSum = true;
int sum = 0;
for (int idx = 0; (results != null) && (idx < results.length); idx++)
{
if (results[idx] < 0)
{
hasSum = false;
if (Jdbc3Utils.supportsJava14BatchResultCodes())
{
String msg = Jdbc3Utils.getBatchResultMessage(table.getName(), idx, results[idx]);
if (msg != null)
{
_log.warn(msg);
}
}
}
else
{
sum += results[idx];
}
}
if (hasSum && (sum != numRows))
{
_log.warn("Attempted to insert " + numRows + " rows into table " + table.getName() + " but changed " + sum + " rows");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database", ex);
}
}
}
was (Author: seanxiong):
My Solution:
1) Create a sql savepoint before call statement.executeUpdate() or statement.executeBatch().
2) Throw sql exception if the execute in step 1 is rejected by DB serve.
3) Catch the sql exception and call connection.rollback to roll the status of connection back to the savepoint we created in step 1.
Affected files:
PlatformImplBase.java of DDLUtils
modifications:
1) Method public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException;
2) Method public void update(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException;
3) Method private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException;
All modified block is putted between //<Modification done By Sean Xiong> //</Modification done By Sean Xiong>
Method (1)
public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
{
SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
SqlDynaProperty[] properties = getPropertiesForInsertion(model, dynaClass, dynaBean);
Column[] autoIncrColumns = getRelevantIdentityColumns(model, dynaClass, dynaBean);
if ((properties.length == 0) && (autoIncrColumns.length == 0))
{
_log.warn("Cannot insert instances of type " + dynaClass + " because it has no usable properties");
return;
}
String insertSql = createInsertSql(model, dynaClass, properties, null);
String queryIdentitySql = null;
if (_log.isDebugEnabled())
{
_log.debug("About to execute SQL: " + insertSql);
}
if (autoIncrColumns.length > 0)
{
if (!getPlatformInfo().isLastIdentityValueReadable())
{
_log.warn("The database does not support querying for auto-generated column values");
}
else
{
queryIdentitySql = createSelectLastInsertIdSql(model, dynaClass);
}
}
boolean autoCommitMode = false;
PreparedStatement statement = null;
try
{
if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
}
beforeInsert(connection, dynaClass.getTable());
statement = connection.prepareStatement(insertSql);
for (int idx = 0; idx < properties.length; idx++ )
{
setObject(statement, idx + 1, dynaBean, properties[idx]);
}
//<Modification done By Sean Xiong>
int count = -1;
try {
count = statement.executeUpdate();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
// reset the autoCommit of connection to true
connection.setAutoCommit(autoCommitMode);
throw sqle;
}
//</Modification done By Sean Xiong>
afterInsert(connection, dynaClass.getTable());
if (count != 1)
{
_log.warn("Attempted to insert a single row " + dynaBean +
" in table " + dynaClass.getTableName() +
" but changed " + count + " row(s)");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database: " + ex.getMessage(), ex);
}
finally
{
closeStatement(statement);
}
if (queryIdentitySql != null)
{
Statement queryStmt = null;
ResultSet lastInsertedIds = null;
try
{
if (getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
// we'll commit the statement(s) if no auto-commit is enabled because
// otherwise it is possible that the auto increment hasn't happened yet
// (the db didn't actually perform the insert yet so no triggering of
// sequences did occur)
if (!connection.getAutoCommit())
{
connection.commit();
}
}
queryStmt = connection.createStatement();
lastInsertedIds = queryStmt.executeQuery(queryIdentitySql);
lastInsertedIds.next();
for (int idx = 0; idx < autoIncrColumns.length; idx++)
{
// we're using the index rather than the name because we cannot know how
// the SQL statement looks like; rather we assume that we get the values
// back in the same order as the auto increment columns
Object value = getObjectFromResultSet(lastInsertedIds, autoIncrColumns[idx], idx + 1);
PropertyUtils.setProperty(dynaBean, autoIncrColumns[idx].getName(), value);
}
}
catch (NoSuchMethodException ex)
{
// Can't happen because we're using dyna beans
}
catch (IllegalAccessException ex)
{
// Can't happen because we're using dyna beans
}
catch (InvocationTargetException ex)
{
// Can't happen because we're using dyna beans
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while retrieving the identity column value(s) from the database", ex);
}
finally
{
if (lastInsertedIds != null)
{
try
{
lastInsertedIds.close();
}
catch (SQLException ex)
{
// we ignore this one
}
}
closeStatement(statement);
}
}
if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
try
{
// we need to do a manual commit now
connection.commit();
connection.setAutoCommit(autoCommitMode);
}
catch (SQLException ex)
{
throw new DatabaseOperationException(ex);
}
}
}
Method (2)
public void update(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
{
SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
SqlDynaProperty[] primaryKeys = dynaClass.getPrimaryKeyProperties();
if (primaryKeys.length == 0)
{
_log.info("Cannot update instances of type " + dynaClass + " because it has no primary keys");
return;
}
SqlDynaProperty[] properties = dynaClass.getNonPrimaryKeyProperties();
String sql = createUpdateSql(model, dynaClass, primaryKeys, properties, null);
PreparedStatement statement = null;
if (_log.isDebugEnabled())
{
_log.debug("About to execute SQL: " + sql);
}
try
{
beforeUpdate(connection, dynaClass.getTable());
statement = connection.prepareStatement(sql);
int sqlIndex = 1;
for (int idx = 0; idx < properties.length; idx++)
{
setObject(statement, sqlIndex++, dynaBean, properties[idx]);
}
for (int idx = 0; idx < primaryKeys.length; idx++)
{
setObject(statement, sqlIndex++, dynaBean, primaryKeys[idx]);
}
//<Modification done By Sean Xiong>
boolean autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
int count = -1;
try {
count = statement.executeUpdate();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
throw sqle;
} finally {
// reset the autoCommit of connection to its original value
connection.setAutoCommit(autoCommitMode);
}
//</Modification done By Sean Xiong>
afterUpdate(connection, dynaClass.getTable());
if (count != 1)
{
_log.warn("Attempted to insert a single row " + dynaBean +
" into table " + dynaClass.getTableName() +
" but changed " + count + " row(s)");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while updating in the database", ex);
}
finally
{
closeStatement(statement);
}
}
/**
* {@inheritDoc}
*/
public void update(Database model, DynaBean dynaBean) throws DatabaseOperationException
{
Connection connection = borrowConnection();
try
{
update(connection, model, dynaBean);
}
finally
{
returnConnection(connection);
}
}
Method (3)
private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException
{
if (statement != null)
{
try
{
Connection connection = statement.getConnection();
beforeInsert(connection, table);
//<Modification done By Sean Xiong>
boolean autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
int[] results = null;
try {
results = statement.executeBatch();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
throw sqle;
} finally {
// reset the autoCommit of connection to its original value
connection.setAutoCommit(autoCommitMode);
}
//</Modification done By Sean Xiong>
closeStatement(statement);
afterInsert(connection, table);
boolean hasSum = true;
int sum = 0;
for (int idx = 0; (results != null) && (idx < results.length); idx++)
{
if (results[idx] < 0)
{
hasSum = false;
if (Jdbc3Utils.supportsJava14BatchResultCodes())
{
String msg = Jdbc3Utils.getBatchResultMessage(table.getName(), idx, results[idx]);
if (msg != null)
{
_log.warn(msg);
}
}
}
else
{
sum += results[idx];
}
}
if (hasSum && (sum != numRows))
{
_log.warn("Attempted to insert " + numRows + " rows into table " + table.getName() + " but changed " + sum + " rows");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database", ex);
}
}
}
> ERROR: current transaction is aborted, commands ignored until end of transaction block
> --------------------------------------------------------------------------------------
>
> Key: DDLUTILS-271
> URL: https://issues.apache.org/jira/browse/DDLUTILS-271
> Project: DdlUtils
> Issue Type: Bug
> Components: Core (No specific database)
> Affects Versions: 1.0
> Reporter: Sean Xiong
> Assignee: Thomas Dudziak
> Labels: violation,unique,index
>
> After a violation of unique index inside the database an Exception is thrown and DDLUTILS keeps throwing "ERROR: current transaction is aborted, commands ignored until end of transaction block" stopping the application to do any database activity through it.
> User case: trying to insert two data rows with the same primary key to table.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Issue Comment Edited] (DDLUTILS-271) ERROR: current
transaction is aborted, commands ignored until end of transaction block
Posted by "Sean Xiong (Issue Comment Edited) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DDLUTILS-271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13136629#comment-13136629 ]
Sean Xiong edited comment on DDLUTILS-271 at 10/27/11 12:20 AM:
----------------------------------------------------------------
My Solution:
1) Create a sql savepoint before call statement.executeUpdate() or statement.executeBatch().
2) Throw sql exception if the execute in step 1 is rejected by DB serve.
3) Catch the sql exception and call connection.rollback to roll the status of connection back to the savepoint we created in step 1.
Affected files:
PlatformImplBase.java of DDLUtils
modifications:
1) Method public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException;
2) Method public void update(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException;
3) Method private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException;
All modified block is putted between //<Modification done By Sean Xiong> //</Modification done By Sean Xiong>
Method (1)
public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
{
SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
SqlDynaProperty[] properties = getPropertiesForInsertion(model, dynaClass, dynaBean);
Column[] autoIncrColumns = getRelevantIdentityColumns(model, dynaClass, dynaBean);
if ((properties.length == 0) && (autoIncrColumns.length == 0))
{
_log.warn("Cannot insert instances of type " + dynaClass + " because it has no usable properties");
return;
}
String insertSql = createInsertSql(model, dynaClass, properties, null);
String queryIdentitySql = null;
if (_log.isDebugEnabled())
{
_log.debug("About to execute SQL: " + insertSql);
}
if (autoIncrColumns.length > 0)
{
if (!getPlatformInfo().isLastIdentityValueReadable())
{
_log.warn("The database does not support querying for auto-generated column values");
}
else
{
queryIdentitySql = createSelectLastInsertIdSql(model, dynaClass);
}
}
boolean autoCommitMode = false;
PreparedStatement statement = null;
try
{
if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
}
beforeInsert(connection, dynaClass.getTable());
statement = connection.prepareStatement(insertSql);
for (int idx = 0; idx < properties.length; idx++ )
{
setObject(statement, idx + 1, dynaBean, properties[idx]);
}
//<Modification done By Sean Xiong>
int count = -1;
try {
count = statement.executeUpdate();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
// reset the autoCommit of connection to true
connection.setAutoCommit(autoCommitMode);
throw sqle;
}
//</Modification done By Sean Xiong>
afterInsert(connection, dynaClass.getTable());
if (count != 1)
{
_log.warn("Attempted to insert a single row " + dynaBean +
" in table " + dynaClass.getTableName() +
" but changed " + count + " row(s)");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database: " + ex.getMessage(), ex);
}
finally
{
closeStatement(statement);
}
if (queryIdentitySql != null)
{
Statement queryStmt = null;
ResultSet lastInsertedIds = null;
try
{
if (getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
// we'll commit the statement(s) if no auto-commit is enabled because
// otherwise it is possible that the auto increment hasn't happened yet
// (the db didn't actually perform the insert yet so no triggering of
// sequences did occur)
if (!connection.getAutoCommit())
{
connection.commit();
}
}
queryStmt = connection.createStatement();
lastInsertedIds = queryStmt.executeQuery(queryIdentitySql);
lastInsertedIds.next();
for (int idx = 0; idx < autoIncrColumns.length; idx++)
{
// we're using the index rather than the name because we cannot know how
// the SQL statement looks like; rather we assume that we get the values
// back in the same order as the auto increment columns
Object value = getObjectFromResultSet(lastInsertedIds, autoIncrColumns[idx], idx + 1);
PropertyUtils.setProperty(dynaBean, autoIncrColumns[idx].getName(), value);
}
}
catch (NoSuchMethodException ex)
{
// Can't happen because we're using dyna beans
}
catch (IllegalAccessException ex)
{
// Can't happen because we're using dyna beans
}
catch (InvocationTargetException ex)
{
// Can't happen because we're using dyna beans
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while retrieving the identity column value(s) from the database", ex);
}
finally
{
if (lastInsertedIds != null)
{
try
{
lastInsertedIds.close();
}
catch (SQLException ex)
{
// we ignore this one
}
}
closeStatement(statement);
}
}
if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
try
{
// we need to do a manual commit now
connection.commit();
connection.setAutoCommit(autoCommitMode);
}
catch (SQLException ex)
{
throw new DatabaseOperationException(ex);
}
}
}
Method (2)
public void update(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
{
SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
SqlDynaProperty[] primaryKeys = dynaClass.getPrimaryKeyProperties();
if (primaryKeys.length == 0)
{
_log.info("Cannot update instances of type " + dynaClass + " because it has no primary keys");
return;
}
SqlDynaProperty[] properties = dynaClass.getNonPrimaryKeyProperties();
String sql = createUpdateSql(model, dynaClass, primaryKeys, properties, null);
PreparedStatement statement = null;
if (_log.isDebugEnabled())
{
_log.debug("About to execute SQL: " + sql);
}
try
{
beforeUpdate(connection, dynaClass.getTable());
statement = connection.prepareStatement(sql);
int sqlIndex = 1;
for (int idx = 0; idx < properties.length; idx++)
{
setObject(statement, sqlIndex++, dynaBean, properties[idx]);
}
for (int idx = 0; idx < primaryKeys.length; idx++)
{
setObject(statement, sqlIndex++, dynaBean, primaryKeys[idx]);
}
//<Modification done By Sean Xiong>
boolean autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
int count = -1;
try {
count = statement.executeUpdate();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
throw sqle;
} finally {
// reset the autoCommit of connection to its original value
connection.setAutoCommit(autoCommitMode);
}
//</Modification done By Sean Xiong>
afterUpdate(connection, dynaClass.getTable());
if (count != 1)
{
_log.warn("Attempted to insert a single row " + dynaBean +
" into table " + dynaClass.getTableName() +
" but changed " + count + " row(s)");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while updating in the database", ex);
}
finally
{
closeStatement(statement);
}
}
/**
* {@inheritDoc}
*/
public void update(Database model, DynaBean dynaBean) throws DatabaseOperationException
{
Connection connection = borrowConnection();
try
{
update(connection, model, dynaBean);
}
finally
{
returnConnection(connection);
}
}
Method (3)
private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException
{
if (statement != null)
{
try
{
Connection connection = statement.getConnection();
beforeInsert(connection, table);
//<Modification done By Sean Xiong>
boolean autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
int[] results = null;
try {
results = statement.executeBatch();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
throw sqle;
} finally {
// reset the autoCommit of connection to its original value
connection.setAutoCommit(autoCommitMode);
}
//</Modification done By Sean Xiong>
closeStatement(statement);
afterInsert(connection, table);
boolean hasSum = true;
int sum = 0;
for (int idx = 0; (results != null) && (idx < results.length); idx++)
{
if (results[idx] < 0)
{
hasSum = false;
if (Jdbc3Utils.supportsJava14BatchResultCodes())
{
String msg = Jdbc3Utils.getBatchResultMessage(table.getName(), idx, results[idx]);
if (msg != null)
{
_log.warn(msg);
}
}
}
else
{
sum += results[idx];
}
}
if (hasSum && (sum != numRows))
{
_log.warn("Attempted to insert " + numRows + " rows into table " + table.getName() + " but changed " + sum + " rows");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database", ex);
}
}
}
was (Author: seanxiong):
My Solution:
1) Create a sql savepoint before call statement.executeUpdate() or statement.executeBatch().
2) Throw sql exception if the execute in step 1 is rejected by DB serve.
3) Catch the sql exception and call connection.rollback to roll the status of connection back to the savepoint we created in step 1.
Affected files:
PlatformImplBase.java of DDLUtils
modifications:
1) Method public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException;
2) Method public void update(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException;
3) Method private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException;
All modified block is putted between //<Modification done By Sean Xiong> //</Modification done By Sean Xiong>
Method (1)
public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException
{
SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
SqlDynaProperty[] properties = getPropertiesForInsertion(model, dynaClass, dynaBean);
Column[] autoIncrColumns = getRelevantIdentityColumns(model, dynaClass, dynaBean);
if ((properties.length == 0) && (autoIncrColumns.length == 0))
{
_log.warn("Cannot insert instances of type " + dynaClass + " because it has no usable properties");
return;
}
String insertSql = createInsertSql(model, dynaClass, properties, null);
String queryIdentitySql = null;
if (_log.isDebugEnabled())
{
_log.debug("About to execute SQL: " + insertSql);
}
if (autoIncrColumns.length > 0)
{
if (!getPlatformInfo().isLastIdentityValueReadable())
{
_log.warn("The database does not support querying for auto-generated column values");
}
else
{
queryIdentitySql = createSelectLastInsertIdSql(model, dynaClass);
}
}
boolean autoCommitMode = false;
PreparedStatement statement = null;
try
{
if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
}
beforeInsert(connection, dynaClass.getTable());
statement = connection.prepareStatement(insertSql);
//System.out.println("insetSql " + insertSql);
for (int idx = 0; idx < properties.length; idx++ )
{
setObject(statement, idx + 1, dynaBean, properties[idx]);
}
//System.out.println("statement" + statement.toString());
//<Modification done By Sean Xiong>
int count = -1;
try {
count = statement.executeUpdate();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
// reset the autoCommit of connection to true
connection.setAutoCommit(autoCommitMode);
throw sqle;
}
//</Modification done By Sean Xiong>
afterInsert(connection, dynaClass.getTable());
if (count != 1)
{
_log.warn("Attempted to insert a single row " + dynaBean +
" in table " + dynaClass.getTableName() +
" but changed " + count + " row(s)");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database: " + ex.getMessage(), ex);
}
finally
{
closeStatement(statement);
}
if (queryIdentitySql != null)
{
Statement queryStmt = null;
ResultSet lastInsertedIds = null;
try
{
if (getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
// we'll commit the statement(s) if no auto-commit is enabled because
// otherwise it is possible that the auto increment hasn't happened yet
// (the db didn't actually perform the insert yet so no triggering of
// sequences did occur)
if (!connection.getAutoCommit())
{
connection.commit();
}
}
queryStmt = connection.createStatement();
lastInsertedIds = queryStmt.executeQuery(queryIdentitySql);
lastInsertedIds.next();
for (int idx = 0; idx < autoIncrColumns.length; idx++)
{
// we're using the index rather than the name because we cannot know how
// the SQL statement looks like; rather we assume that we get the values
// back in the same order as the auto increment columns
Object value = getObjectFromResultSet(lastInsertedIds, autoIncrColumns[idx], idx + 1);
PropertyUtils.setProperty(dynaBean, autoIncrColumns[idx].getName(), value);
}
}
catch (NoSuchMethodException ex)
{
// Can't happen because we're using dyna beans
}
catch (IllegalAccessException ex)
{
// Can't happen because we're using dyna beans
}
catch (InvocationTargetException ex)
{
// Can't happen because we're using dyna beans
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while retrieving the identity column value(s) from the database", ex);
}
finally
{
if (lastInsertedIds != null)
{
try
{
lastInsertedIds.close();
}
catch (SQLException ex)
{
// we ignore this one
}
}
closeStatement(statement);
}
}
if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading())
{
try
{
// we need to do a manual commit now
connection.commit();
connection.setAutoCommit(autoCommitMode);
}
catch (SQLException ex)
{
throw new DatabaseOperationException(ex);
}
}
}
Method (2)
private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException
{
if (statement != null)
{
try
{
Connection connection = statement.getConnection();
beforeInsert(connection, table);
//<Modification done By Sean Xiong>
boolean autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
int[] results = null;
try {
results = statement.executeBatch();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
throw sqle;
} finally {
// reset the autoCommit of connection to its original value
connection.setAutoCommit(autoCommitMode);
}
//</Modification done By Sean Xiong>
closeStatement(statement);
afterInsert(connection, table);
boolean hasSum = true;
int sum = 0;
for (int idx = 0; (results != null) && (idx < results.length); idx++)
{
if (results[idx] < 0)
{
hasSum = false;
if (Jdbc3Utils.supportsJava14BatchResultCodes())
{
String msg = Jdbc3Utils.getBatchResultMessage(table.getName(), idx, results[idx]);
if (msg != null)
{
_log.warn(msg);
}
}
}
else
{
sum += results[idx];
}
}
if (hasSum && (sum != numRows))
{
_log.warn("Attempted to insert " + numRows + " rows into table " + table.getName() + " but changed " + sum + " rows");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database", ex);
}
}
}
Method (3)
private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException
{
if (statement != null)
{
try
{
Connection connection = statement.getConnection();
beforeInsert(connection, table);
//<Modification done By Sean Xiong>
boolean autoCommitMode = connection.getAutoCommit();
connection.setAutoCommit(false);
int[] results = null;
try {
results = statement.executeBatch();
connection.commit();
} catch (SQLException sqle) {
connection.rollback();
throw sqle;
} finally {
// reset the autoCommit of connection to its original value
connection.setAutoCommit(autoCommitMode);
}
//</Modification done By Sean Xiong>
closeStatement(statement);
afterInsert(connection, table);
boolean hasSum = true;
int sum = 0;
for (int idx = 0; (results != null) && (idx < results.length); idx++)
{
if (results[idx] < 0)
{
hasSum = false;
if (Jdbc3Utils.supportsJava14BatchResultCodes())
{
String msg = Jdbc3Utils.getBatchResultMessage(table.getName(), idx, results[idx]);
if (msg != null)
{
_log.warn(msg);
}
}
}
else
{
sum += results[idx];
}
}
if (hasSum && (sum != numRows))
{
_log.warn("Attempted to insert " + numRows + " rows into table " + table.getName() + " but changed " + sum + " rows");
}
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while inserting into the database", ex);
}
}
}
> ERROR: current transaction is aborted, commands ignored until end of transaction block
> --------------------------------------------------------------------------------------
>
> Key: DDLUTILS-271
> URL: https://issues.apache.org/jira/browse/DDLUTILS-271
> Project: DdlUtils
> Issue Type: Bug
> Components: Core (No specific database)
> Affects Versions: 1.0
> Reporter: Sean Xiong
> Assignee: Thomas Dudziak
> Labels: violation,unique,index
>
> After a violation of unique index inside the database an Exception is thrown and DDLUTILS keeps throwing "ERROR: current transaction is aborted, commands ignored until end of transaction block" stopping the application to do any database activity through it.
> User case: trying to insert two data rows with the same primary key to table.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira