Win a copy of Java Database Connections & Transactions (e-book only) this week in the JDBC forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

select and update using the same statement object  RSS feed

 
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
 
Ranch Hand
Posts: 494
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..
 
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 ?
 
Bartender
Posts: 2653
18
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: 494
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
 
Curse your sudden but inevitable betrayal! And this tiny ad too!
how do I do my own kindle-like thing - without amazon
https://coderanch.com/t/711421/engineering/kindle-amazon
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!