Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

select and update using the same statement object

 
vidhi sanghavi
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 489
Eclipse IDE Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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..
 
Balu Sadhasivam
Ranch Hand
Posts: 874
Android Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 874
Android Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 874
Android Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Leonardo Carreira
Ranch Hand
Posts: 489
Eclipse IDE Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic