Thread: "nested transaction" - encosing statement gets closed when the enclosed statement is closed

I have experienced problems using the JDBC type 2 and type 3
drivers for 8.1 (postgresql-8.1-408.jdbc3.jar,
postgresql-8.1-408.jdbc2.jar) against PostgreSQL 8.1.5.

I am running through the ResultSet of a select query that
involves a join, and for each result I do a lookup on one
of the tables in the join.

   Statement stmt = conn.createStatement();
   Statement stmtchk = conn.createStatement();
     ....
   ResultSet rs = stmt.executeQuery(searchquery);
     ....
   while (rs.next()) {
       .....

For the lookup, I do

     ResultSet rsch = stmtchk.executeQuery(checkquery);
     while (rsch.next()) {
       maxrefid = rsch.getInt("max");
     }
     rsch.close();
     stmtchk.close(); // leads to the closing of stmt!!!

     ...
   } // while


For some reason, the ResultSet that I am looping through is
closed by the lookup, and I receive the following exception:
"SQL Exception (working on resultset)! (selecttempmaps):
  org.postgresql.util.PSQLException: This statement has been closed."

In PostgreSQL 7.4.1 I did not experience this behaviour.

As you can see, I am using a new statement for the lookup.
I have also tried to create a new connection for the lookup,
but the problem persists.

If I comment out stmtchk.close(), the application runs as
it did in 7.4.1.


Is this expected behaviour?


--
Håvard Tveite
Department of Mathematical Sciences and Technology, UMB
Drøbakveien 14, POBox 5003, N-1432 Ås, NORWAY
Phone: +47 64965483 Fax: +47 64965401 http://www.umb.no/imt

On Mon, 15 Jan 2007, Havard Tveite wrote:

> I have experienced problems using the JDBC type 2 and type 3
> drivers for 8.1 (postgresql-8.1-408.jdbc3.jar,
> postgresql-8.1-408.jdbc2.jar) against PostgreSQL 8.1.5.
>

Are you sure it's complaining that stmt is closed.  This coding clearly
looks like the problem will be with stmtchk as you close it in the loop,
but then continue using it.

>  Statement stmtchk = conn.createStatement();
>  while (rs.next()) {
>    ResultSet rsch = stmtchk.executeQuery(checkquery);
>    stmtchk.close(); // leads to the closing of stmt!!!
>  }
>

Kris Jurka

Kris Jurka wrote:
> On Mon, 15 Jan 2007, Havard Tveite wrote:
>
>> I have experienced problems using the JDBC type 2 and type 3
>> drivers for 8.1 (postgresql-8.1-408.jdbc3.jar,
>> postgresql-8.1-408.jdbc2.jar) against PostgreSQL 8.1.5.
>>
>
> Are you sure it's complaining that stmt is closed.  This coding clearly
> looks like the problem will be with stmtchk as you close it in the loop,
> but then continue using it.

You are right.  I was so hung up in that it worked for
7.4.1 that I did not check things properly.  Sorry!
Thank you for the help!

--
Håvard Tveite
Department of Mathematical Sciences and Technology, UMB
Drøbakveien 14, POBox 5003, N-1432 Ås, NORWAY
Phone: +47 64965483 Fax: +47 64965401 http://www.umb.no/imt