Re: referential integrity violation makes connection pool

Craig,

This connection is in transaction mode, so you need to roll the
transaction back, or commit it in order to be able to use it again.

alternatively you can close it and see if poolman checks to see if it is
closed?

Dave

On Thu, 2002-06-20 at 13:38, 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
>
>