我得到这个错误使用Java和postgresql在表上执行插入。我将说明如何重现这个错误:
org.postgresql.util.PSQLException: ERROR:
current transaction is aborted, commands ignored until end of transaction block
概要:
得到此错误的原因是因为您输入了一个事务,并且您的一个SQL查询失败,并且您忽略了该失败并忽略它。但这还不够,所以你使用同样的连接,使用SAME TRANSACTION来运行另一个查询。第二个正确形成的查询会抛出异常,因为您正在使用损坏的事务来执行其他工作。 PostgreSQL默认阻止你这样做。
我使用:PostgreSQL 9.1.6 on x86_64-redhat-linux-gnu,由gcc(GCC)编译4.7.2 20120921(Red Hat 4.7.2-2),64位“。
我的postgresql驱动是:postgresql-9.2-1000.jdbc4.jar
使用java版本:Java 1.7
这里是表的create语句来说明异常:
CREATE TABLE moobar
(
myval INT
);
Java程序导致错误:
public void postgresql_insert()
{
try
{
connection.setAutoCommit(false); //start of transaction.
Statement statement = connection.createStatement();
System.out.println("start doing statement.execute");
statement.execute(
"insert into moobar values(" +
"'this sql statement fails, and it " +
"is gobbled up by the catch, okfine'); ");
//The above line throws an exception because we try to cram
//A string into an Int. I Expect this, what happens is we gobble
//the Exception and ignore it like nothing is wrong.
//But remember, we are in a TRANSACTION! so keep reading.
System.out.println("statement.execute done");
statement.close();
}
catch (SQLException sqle)
{
System.out.println("keep on truckin, keep using " +
"the last connection because what could go wrong?");
}
try{
Statement statement = connection.createStatement();
statement.executeQuery("select * from moobar");
//This SQL is correctly formed, yet it throws the
//'transaction is aborted' SQL Exception, why? Because:
//A. you were in a transaction.
//B. You ran a sql statement that failed.
//C. You didn't do a rollback or commit on the affected connection.
}
catch (SQLException sqle)
{
sqle.printStackTrace();
}
}
上面的代码为我生成这个输出:
start doing statement.execute
keep on truckin, keep using the last connection because what could go wrong?
org.postgresql.util.PSQLException:
ERROR: current transaction is aborted, commands ignored until
end of transaction block
解决方法:
您有几个选项:
>最简单的解决方案:不要在事务中。设置connection.setAutoCommit(false);到connection.setAutoCommit(true);.它工作原因,因为失败的SQL只是被忽略作为失败的sql语句。你欢迎失败sql语句所有你想要和postgresql不会阻止你。
>保持在一个事务中,但是当您检测到第一个sql失败时,回滚/重新启动或提交/重新启动事务。然后,您可以根据需要在该数据库连接上继续失败尽可能多的SQL查询。
>不捕获并忽略在sql语句失败时抛出的异常。然后程序将停止在格式不正确的查询。
>获取Oracle,Oracle不会在事务中的连接上的查询失败并继续使用该连接时抛出异常。
在保护postgresql的决定做这样的事情…甲骨文让你软中间让你做蠢的东西,俯瞰它。