Re: NullPointer error returned from ResultSet.java

Barry, Jef,

I stand corrected!

Dave
On Thu, 2002-10-10 at 20:45, Barry Lind wrote:
> Dave,
>
> The driver does support having multiple result sets open at the same
> time, and I do it frequently in my code.
>
> Having two result sets open lets you nest queries as follows so that you
> can use the results for one as input into the second:
>
> PreparedStatement stmt1 = conn.prepareStatement("select bar1 from bar");
> PreparedStatement stmt2 = conn.prepareStatement("select foo1 from foo "+
>                                                  "where bar1 = ?");
> ResultSet rset1 = stmt1.executeQuery();
> while (rset1.next()) {
>    //for each row in bar, update foo
>    stmt2.setInt(1, rset1.getInt(1));
>    ResultSet rset2 = stmt2.executeQuery();
>    while (rset2.next()) {
>      //do something useful with the results
>    }
>    rset2.close();
> }
> rset1.close();
> stmt1.close();
> stmt2.close();
>
> thanks,
> --Barry
> Dave Cramer wrote:
> > Jeff,
> >
> > You can't have two result sets open on the same connection.
> >
> > Dave
> > On Mon, 2002-10-07 at 11:57, Jeff Duffy wrote:
> >
> >>Greetings,
> >>
> >> I have a JSP page with a rather large scriptlet that uses multiple nested
> >>statements and ResultSets. Under very specific conditions I am encountering
> >>the following error:
> >>
> >>java.lang.NullPointerException
> >> at org.postgresql.jdbc2.ResultSet.next(ResultSet.java:113)
> >>
> >>
> >>Here's one pertinent snippet of my code:
> >>( This code begins inside a while(rs.next()) loop for ResultSet rs,
> >>using Statement stmt)
> >>
> >>
> >>sql.append("AN SQL QUERY");
> >>rs2 = stmt2.executeQuery(sql.toString());
> >>
> >>while(rs2.next()){
> >>     String courseId = rs2.getString(1);
> >>     String roleName = rs2.getString(2);
> >>
> >>     rs3 = stmt3.executeQuery("SELECT nextval('assignment_id')");
> >>     rs3.next();
> >>     assignmentId = rs3.getString(1);
> >>
> >>    sql.delete(0, sql.length());
> >>    sql.append("AN SQL QUERY");
> >>    stmt3.executeUpdate(sql.toString());
> >>}
> >>
> >> Through debugging it appears that rs2 is throwing the error; the message
> >>states the error is in method next(), but if I have only zero records or
> >>only one record in rs2, there is no error. It's only when I have multiple
> >>records that the error is thrown the second time round the while loop, so I
> >>suspect it may be thrown at the assignment of courseId.
> >>
> >> This error is reproduced given the same conditions (nested ResultSets with
> >>the innermost ResultSet executing an Update inside a while(ResultSet.next())
> >>loop.  It almost seems that the call to rs3.executeUpdate() is closing rs2,
> >>but I cannot see why that would happen. This is occurring in at least six
> >>different source files.
> >>
> >> If I create a new Vector and stuff the contents of rs2 into it, then
> >>iterate over the vector and use rs3 as shown above, no error is thrown.
> >>
> >> Updating to the 7.3beta1 JDBC driver changes the error message to:
> >>
> >>javax.servlet.ServletException: postgresql.con.closed
> >>
> >> which is even more puzzling.
> >>
> >>
> >> Notes about my coding style that may be relevant:
> >>- I use one StringBuffer for all sql queries and clear it for each new query
> >>for efficiency. This hasn't ever caused problems elsewhere.
> >>
> >>- I predeclare all Statement and ResultSet objects at the outermost block of
> >>code (the 'top') like so:
> >>
> >>Statement stmt   = conn.createStatement();
> >>Statement stmt2  = conn.createStatement();
> >>Statement stmt3  = conn.createStatement();
> >>ResultSet rs, rs2, rs3;
> >>
> >>Other data:
> >>
> >>- The connections are obtained from a custom class that uses the jdbcpool
> >>pool manager.
> >>- I'm using the Sun JDK v1.4.0 for Linux (it happens on Win32 as well).
> >>- The JDBC library is v7.2 (the pgjdbc2.jar binary download).
> >>- PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
> >>
> >> Any flashes of insight?
> >>
> >>Thanks
> >>
> >>Jeff Duffy
> >>jeff@alanne.com
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 6: Have you searched our list archives?
> >>
> >>http://archives.postgresql.org
> >>
> >>
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>