RecordSets

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.jav
a:135)
and on and on
**
If a full error dump would help let me know.

Any suggestions ???

Thanks in anticipation


Craig A Golby