Craig,
After an error raised from the server the connection will be unusable
until a rollback() is called on the connection. Your logic is just
returning the connection to the pool in the error case and is not
calling rollback before doing that.
thanks,
--Barry
Craig Moon wrote:
System: RedHat 7.1
Java: 1.4.0
I am running Tomcat 4.0.4 and using Poolman 2.0.4 to do connection
pooling with the JDBC 2.0 driver. When I try to delete something from
a particular table through a web application, the following
SQLException is thrown:
java.sql.SQLException: ERROR: <unnamed> referential integrity
violation - key in user_products still referenced from cp_product
This was an expected result, as I want to stop a product from being
deleted if it is being used else where. The problem comes that after
the exception is thrown, any query the web application tries to
execute on any connection from the connection pool results in the
following exception:
No results were returned by the query.
at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58)
at
org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement
.java:99)
The only way to be able to execute queries is to reload the
application, which reinitializes the connection pool.
Any help on this would be appreciated. The code where the referential
integrity violation occurs is below.
try {
//get connection from the connection pool
conn = connManager.getConnection();
if (conn == null) {
throw new RepositoryException("deleteMerchantProducts
:connection was null");
}
pstmt =
conn.prepareStatement(this.getDeleteMerchantProductSQL());
for (Iterator iterator = keys.iterator();
iterator.hasNext();) {
Integer key= (Integer) iterator.next();
pstmt.setInt(1, merchant.getKey());
pstmt.setInt(2, key.intValue() );
//delete product from database
pstmt.executeUpdate();
pstmt.clearParameters();
}
} catch (Exception e) {
e.printStackTrace();
throw new RepositoryException(e.toString());
} finally {
try { pstmt.close();} catch (Exception ignored) {}
// return connection to pool
try { connManager.returnConnection(conn);} catch (Exception
ignored) {}
}
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
