ResultSet closed error (URGENT !!)

[Nair Anoop]

Hi,
I am performing the following operations:
1. Open a connection and execute a query statement
2. I get a resultSet which I wish to update using the same statement and conn. object. It gives me "ResultSet " closed error.
3. If I use a second statement object, it tells me that the conn. is busy with results of another statement.
Please help me solve the above problem.
----------------------------------------------------------
Connection myConn = DriverManager.getConnection(url, "Administrator", "ashwin");
Statement stmt = myConn.createStatement();
ResultSet myResultSet;
myResultSet = stmt.executeQuery(query);
while (myResultSet.next())
{
counter++;
strIndex = "txt" + counter;
String ResId = myResultSet.getString("ResId").trim();
String RevId = myResultSet.getString("RevId").trim();
if (!request.getParameter(strIndex).equals(""))
{
query = "update TransactEJBTable set comments = '" + request.getParameter(strIndex) + "' where ResId = '" + ResId + "' and RevId = '" + RevId + "'";
out.println(query);
out.println("
");
stmt.execute(query); /// GIVING "resultSet closed" error
}
}
myResultSet.close();
stmt.close();
myConn.close();
}
-------------------------------------------------------------
Thanks in advance,
Anoop

[Desai Sandeep]

Hi Anoop,
I am assuming that your DBMS is JDBC 2.0 complaint.
This is the possible solution to update the records in the ResultSet.

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@sandeep:1521:oracle8i","sandeep","sandeep");
PreparedStatement stmt = conn.prepareStatement("select dept.* from DEPT dept where deptno = ?",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
stmt.setInt(1,30);
ResultSet rs  = stmt.executeQuery();
while (rs.next()) {
    System.out.println(rs.getString(3));
    rs.updateString(3,"CHICAGO");
    rs.updateRow();
    System.out.println(rs.getString(3));
}

Note that you have to set the concurrency type to CONCUR_UPDATABLE.You can check if the concurrency of your DBMS is set to this value by using ResultSet.getConcurrency() method.Also it eliminates the need to write another UPDATE statement.
In case you DBMS doesn't support JDBC 2.0 feature, do let me know about the RDBMS you are using.
Hope this helps,
Sandeep

• Sun Certified Programmer for Java 2 Platform Scored 93 per cent
  
• Oracle JDeveloper Rel. 3.0 - Develop Database Applications with Java Scored 56 out of 59
  
• IBM Enterprise Connectivity with J2EE Scored 72 per cent
  
• Enterprise Development on the Oracle Internet Platform Scored 44 out of 56
  

[This message has been edited by Desai Sandeep (edited June 01, 2001).]

[Desai Sandeep]

Anoop,
The code that you have pasted is to be used only for RDBMS which are not JDBC 2.0 complaint.The obvious disadvantage is you have to write an UPDATE statement to update to the new values.
In your code, I saw you are trying to execute the UPDATE statement using the same Statement object.You would need to use a different Statement object, if you want to do it in this way.

Statement stmt1 = myConn.createStatement();
query = "update TransactEJBTable set comments = '" + request.getParameter(strIndex) + "' where ResId = '" + ResId + "' and RevId = '" + RevId + "'";
out.println(query);
stmt1.execute(query);

Let me know if you are still getting errors.
- Sandeep
[This message has been edited by Desai Sandeep (edited June 01, 2001).]

[Nair Anoop]

Hi Sandeep,
Thanks a lot for your help. The code now works perfectly. It indeed was the problem of the same statement object which I was using. By the way ... what is the reason for this ?
Thanks once again,
Anoop

[Desai Sandeep]

Hi Anoop,
The method you used for updating the records in the ResultSet is called "Positional Update".
This method requires you to lock a record in a ResultSet before updating a record.The first Statement object through which you derive a ResultSet object maintains a cursor on the record and locks the record that needs to be updated.The second Statement object locates for that cursor name and releases the lock after updating the record.
I think your DBMS is setting and finding the cursor internally like Oracle8i.The Oracle DB uses the concept of ROWID to lock and update the record.It doesn't support cursors.
Normally, this is the procedure for "Positional Update" :

stmt.setCursorName("anoop");
ResultSet rs = stmt.executeQuery("SELECT .... FROM .... WHERE ... FOR UPDATE...");
String cursorName = rs.getCursorName();
int updateCount = stmt1.executeUpdate("UPDATE ....  WHERE CURRENT OF " + cursorName);

Also note that, all the methods executing Statements close the calling Statement object's result set if there is one open.This means that any processing of the current ResultSet object needs to be completed before the Statement object is re-executed.Hence, if you want to process on the records in the result set, you have no option but to create a new Statement Object.
Hope this helps,
Sandeep
[This message has been edited by Desai Sandeep (edited June 03, 2001).]

[Nair Anoop]

Hi Sandeep,
Thanks a lot for explaining the intricacies of the statement object. It really very helpful.
Thanks,
Anoop

[tim lok]

Originally posted by Nair Anoop:
Hi,
I am performing the following operations:
1. Open a connection and execute a query statement
2. I get a resultSet which I wish to update using the same statement and conn. object. It gives me "ResultSet " closed error.
3. If I use a second statement object, it tells me that the conn. is busy with results of another statement.
Please help me solve the above problem.

The myConn object should be closed outside the while loop.