Re: RecordSets

Craig,

closing a connection causes the result set to be closed as well, you
will have to copy it into another object if you want to access it this
way.

Dave
On Wed, 2003-12-03 at 18:38, CRAIG GOLBY wrote:
> I have been trying to get a piece of code working for some days now
> and am struggling to establish my problem, my investigations have
> however led me back to the database.
>
> I am creating a set of forms, some/all of which will contain at least
> one DropDown (Select) box populated from a Static Data table in my
> PostgreSQL database. In the example I will provide here the data that
> I am selecting is simply Title data such as Mr, Mrs Miss etc.
>
> Environmentally, I am running Tomcat 5, PostgreSQL 732 both sitting on
> Red Hat 9. Db Connectivity is handled using Context Lookups through
> Tomcat. I have pg73jdbc3.jar in my tomcat/common/lib directory.
>
> So I have a database table of simple construct, and I then have a bean
> that contains the connectivity to the database and the select
> statement.
>
> I predefine a variable stmtString as
>
> String stmtString = "SELECT ";
>
> and I then add to it depending on certain parameters, in this
> example :-
>
> stmtString = stmtString + "id, title FROM public.title ORDER BY
> useagecount";
>
> I then connect to the database. I know this element works as tomcat
> tests the connection first by selecting from a sequence, this is
> incrementing, so connectivity is fine. I create a prepared statement
> and execute it.
>
> if(conn != null)
> { PreparedStatement pStmt =
> conn.prepareStatement(stmtString);
> rs = pStmt.executeQuery();
> conn.close();
> .............
>
> Later in my code I then return the value of rs as the output of the
> bean.
>
> The JSP that calls the bean receives the ResultSet, and then in theory
> would work through it, populating the DropDown. This always failed
> despite repeated attempts. On tracking back however, it would appear
> that the ResultSet was empty, as both .next and.first failed with
> nullpointer exceptions.
>
> I modified the JSP to output MetaData information on the ResultSet and
> it confirmed 2 columns and their names perfectly, I have also included
> this code in the Bean, and unsuprisingly it too outputs the correct
> information to the log files when asked to.
>
> If I output the final content of stmtString to the log file, then cut
> and paste that into a pgAdmin SQL window, it works fine.
>
> So it would seem that my SQL is okay, it connects to the database as
> the sequence increments, it runs because the MetaData clearly
> identifies the data headings that are coming back, but it has no
> content.
>
> If I add a .next or a .first within the Bean in order to attempt to
> get at the ResultSet then the following errors are generated :-
>
> **
> Connection is closed. Operation is not permitted.
> at
> org.postgresql.jdbc1.AbstractJdbc1ResultSet.next(AbstractJdbc1ResultSet.java:63)
> at
> org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:135)
> and on and on
> **
> If a full error dump would help let me know.
>
> Any suggestions ???
> Thanks in anticipation
>
>
> Craig A Golby