aspose file tools *
The moose likes JDBC and the fly likes Update inside Select statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Update inside Select statement" Watch "Update inside Select statement" New topic
Author

Update inside Select statement

carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

Hi i am trying to select rows from table and copy it in another table and update the first table rows status but i am facing issues in this



but here i am getting error

java.sql.SQLException: ResultSet is closed
at sun.jdbc.odbc.JdbcOdbcResultSet.checkOpen(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.next(Unknown Source)

Can anybody suggets me whats wrong here?
ujjwal soni
Ranch Hand

Joined: Mar 28, 2007
Posts: 403
Hi,

It simply means that your resultset has been closed. Your code looks ok. Can you check if you have written rs.close() or ars.close() any where in your code ?


Cheers!!!
Ujjwal B Soni <baroda, gujarat, india> <+919909981973>
"Helping hands are better than praying lips......"
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3860

ruquia tabassum wrote:


First, Make sure where exactly you are getting the error. There are two ResultSets involved here (rs & ars). May be you have closed the "ars" already?


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Seems an odd way to do this. Why issue individual update statements as you iterate through your result set rather then one after you have iterated through your result set? What you are doing has a lot of unecessary database round trips. e.g.





JavaRanch FAQ HowToAskQuestionsOnJavaRanch
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

i have only one result set object and that is rs and i did not specify rs.close() anywhere in my code my complete program is



error:
id 7 name survey

java.sql.SQLException: ResultSet is closed
at sun.jdbc.odbc.JdbcOdbcResultSet.checkOpen(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.next(Unknown Source)


If there are unnecessary round trips then the output should be slow but here i am getting an odd message saying ResultSet closed and the while loop is not iterating through all the records fetched it just shows one record from the System.out.println ("id"+id+"name"+name).
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Updating a result set as you iterate through it would usually be done with an updatable results set, which is not somthing Access supports. I wouldn't be surprised if your update is also causing the result set to be closed.


i did not specify rs.close() anywhere in my code my complete program is
]
This is very bad practice. You should always release JDBC resources in a finally block.
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

i could solve it by using different statement for insert , select and update

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Update inside Select statement