This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes ResultSet closed error (URGENT !!) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ResultSet closed error (URGENT !!)" Watch "ResultSet closed error (URGENT !!)" New topic
Author

ResultSet closed error (URGENT !!)

Nair Anoop
Ranch Hand

Joined: May 09, 2001
Posts: 68
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
Ranch Hand

Joined: Apr 02, 2001
Posts: 1157
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.

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).]


<b>Sandeep</b> <br /> <br /><b>Sun Certified Programmer for Java 2 Platform</b><br /> <br /><b>Oracle Certified Solution Developer - JDeveloper</b><br /><b>-- Oracle JDeveloper Rel. 3.0 - Develop Database Applications with Java </b><br /><b>-- Object-Oriented Analysis and Design with UML</b><br /> <br /><b>Oracle Certified Enterprise Developer - Oracle Internet Platform</b><br /><b>-- Enterprise Connectivity with J2EE </b><br /><b>-- Enterprise Development on the Oracle Internet Platform </b>
Desai Sandeep
Ranch Hand

Joined: Apr 02, 2001
Posts: 1157
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.

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

Joined: May 09, 2001
Posts: 68
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
Ranch Hand

Joined: Apr 02, 2001
Posts: 1157
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" :

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
Ranch Hand

Joined: May 09, 2001
Posts: 68
Hi Sandeep,
Thanks a lot for explaining the intricacies of the statement object. It really very helpful.
Thanks,
Anoop
tim lok
Greenhorn

Joined: Mar 19, 2001
Posts: 4
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ResultSet closed error (URGENT !!)
 
Similar Threads
login servlet = login successful doesn't show
JSP what's wrong with following code?
JDBC Connection (or "I Am At My Wits End")
Newbie problem. JDBC Error
ResultSet is closed??