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

select and update using the same statement object

vidhi sanghavi
Greenhorn

Joined: Mar 28, 2009
Posts: 13
Hi ,



I am trying to access cetain data from the database.
once I retrieve the data I have to process dat data and then update a particular column using the modified data.
I used the following code.

Statement stmt=null;

ResultSet rs=null;
rs = stmt.executeQuery("select data from table where id = 2");
while(rs.next()){
String description= rs.getString("data");
String overview=description.substring(description.indexOf("<Overview>")+10,description.indexOf("</Overview"));

String updateString = "update table " +
"set data= '"+overview+"' " +
"where id = 2 ";

int x= stmt.executeUpdate(updateString);
System.out.println("query updated");
}

I am getting the followng exception.........


java.sql.SQLException: ResultSet is closed


Please help me in solving the problem......


Thank you in advance

Vidhi
Leonardo Carreira
Ranch Hand

Joined: Apr 07, 2009
Posts: 489

Hi...

i think you should use 2 statements for execute your queries..
first statement is used to execute "executeQuery" method...
second statement is used to execute "executeUpdate" method..

or you can use only 1 statement with define it as private...

private Statement stmt;

hope that help...

Correct Me if i'm wrong please..
Thanks..


Sorry, perhaps my english language isn't too good.. Prepare for SCJP 6, Please God help me.. ☼
References : [Java.Boot] [JavaChamp] [JavaPrepare]
Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874



Vidhi,

Executing SQL Statements with same Statemenet object without closing the resultSet opened will yeild uncharacteristic results. The solution should be to store the resultset values to a local objects (variables) , open a new resultset and use it execute the second Statement ( statement object can be same).
vidhi sanghavi
Greenhorn

Joined: Mar 28, 2009
Posts: 13
Hi,


Thank you for the previous reply!!!11

Could you please include the statements that are mssing in the code.
I am nt able to understand what am I supposed to da



Thank you in advance,
Vidhi
Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874

vidhi sanghavi wrote:
Could you please include the statements that are mssing in the code.
I am nt able to understand what am I supposed to da


You understood my reply ?



Why do you require to execute update for each time the loop iterates , your where condition "id = 2" which is always same for all iterations , so you can move that code out. like this.



Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874

Leonardo Carreira wrote:

Or you can use only 1 statement with define it as private...
private Statement stmt;
Correct Me if i'm wrong please..


How "private" would help here ?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

A remark:
Your example:would throw an exception, because stmt is null when you call it's executeQuery() method.



In your initial example, you change the original stmt inside of your loop.
Maybe this corrupts your loop, because the ResultSet you associated with the Statement is closed when you call a new operation (executeOpdate()) on that same statement?

Can you replace


by

, and tell us what happens?

OCUP UML fundamental and ITIL foundation
youtube channel
Leonardo Carreira
Ranch Hand

Joined: Apr 07, 2009
Posts: 489

Balu Sadhasivam wrote:
How "private" would help here ?


previously, i guessed the Statement stmt object is out of scope..
because stmt object accessed in the loop..

yeah i think the problem is stmt object set by null value..

Correct me if i'm wrong..
Thanks
 
 
subject: select and update using the same statement object