rik,
as others have said, the code looks fine, although not all the code is there
so we cannot see what entry.getFileNumber() returns on the second pass. if
you are on a tight deadline what about trying something like :
String sql1 = "select c.Name, pvp.Project_Title, "
+ "pvp.Customer, "
+ "pvp.Department_Code, pvp.Project_Code "
+ "from ProtemisProject pvp, "
+ " Customer c "
+ "where pvp.Project_Ref = '";
String sql2 = "' and pvp.Customer = c.No_ ");
.............
Statement stmt = m_connection.createStatement();
String sqlString = sql1+entry.getFileNumber()+sql2;
debug("executing .... "+sqlString);
ResultSet rs = stmt.executeQuery(sqlString);
..............
stmt.close();
rs.close();
..............
this is obviously not as efficient as the prepared statement and may be
slightly slower depending on what you are doing, but if there is a bug in
the ps then it might work as the db will parse the statement as a whole
without alteration from the driver.
good luck,
ray.
> -----Original Message-----
> From: A mailing list for discussion about Sun Microsystem's Java Servlet
> API Technology. [mailto:[EMAIL PROTECTED]]On Behalf Of Rik
> Gammack
> Sent: 16 June 1999 2:30
> To: [EMAIL PROTECTED]
> Subject: Invalid state for getResultSet
>
>
> Does anyone know a work-around for this when I run a servlet
> under ServletExec.... I've contacted ServletExec themselves, but
> no reply so far and I'm on a tight schedule.
>
> I'm using JDBC to access a database. I have a PreparedStatement
> of "select x from y where ?" and I'm using the setString method
> to fill in the parameter as required. It all works okay the first
> time I call the executeQuery method. I close the resultSet, go
> off and do some other queries (once each) and finally come back
> to execute this query again. I set it up with the new parameter,
> execute the query, and it throws an SQLException "Invalid state
> for getResultSet".
>
> It doesn't seem to matter which of the PreparedStatements in the
> servlet get executed twice, the second time throws an Exception.
>
> Everything works perfectly when I run the same servlet under the
> Java Web Server. (Same machine, same ODBC connection etc). Short
> of actually switching to the JWS (our clients have just gone out
> and bought ServletExec, so this would not be a politically sound
> suggestion) what can I do?
>
> Many thanks,
> Rik Gammack.
>
> The code snippet looks like ...
> pGetProjectDetails = db.prepareStatement(
> "select c.Name, pvp.Project_Title, "
> + "pvp.Customer,
> pvp.Department_Code, pvp.Project_Code "
> + "from ProtemisProject pvp, "
> + " Customer c "
> + "where pvp.Project_Ref = ? "
> + "and pvp.Customer = c.No_ ");
>
>
> //--------------------------------------------------------------------
>
> protected void getProjectDetails(TimeSheetEntry entry)
> throws TimeSheetException
> {
> debug("getProjectDetails() - entry");
>
> entry.setFileName("");
>
> ResultSet results = null;
>
> try
> {
> pGetProjectDetails.setString(1,
> entry.getFileNumber());
>
> results =
> pGetProjectDetails.executeQuery(); <<<<<<<<<<<<<<<<<<<< ERROR POINT
>
> if (results.next())
> {
> entry.setFileName(results.getString(1));
>
> /*
> ** Only insert the description if
> it hasn't already
> ** been entered.
> */
> if (entry.getFileDescription().equals(""))
>
> entry.setFileDescription(results.getString(2));
>
> entry.customer = results.getString(3);
> entry.deptCode = results.getString(4);
> if (entry.deptCode == null)
> entry.deptCode = "";
> entry.projectCode = results.getString(5);
> if (entry.projectCode == null)
> entry.projectCode = "";
> }
> else
> {
> throw new
> TimeSheetException("File Number '" + entry.getFileNumber()
> + "' is not recognised.");
> }
> }
> catch (SQLException e)
> {
> debug("Error validating File Number '"
> + entry.getFileNumber() +
> "'. because '" + e.getMessage() + "'");
> throw new TimeSheetException("Error
> validating File Number '"
> + entry.getFileNumber() + "'.");
> }
> finally
> {
> if (results != null)
> {
> try
> {
> results.close();
> }
> catch (Exception e)
> {
> debug("Error closing
> results because '" + e.getMessage() + "'");
> }
> }
> }
> }
>
> //--------------------------------------------------------------------
>
> __________________________________________________________________
> _________
> To unsubscribe, send email to [EMAIL PROTECTED] and include
> in the body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources: http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
>
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
