• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

select and update using the same statement object

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Postgres Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Postgres Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Stop it! You're embarassing me! And you are embarrassing this tiny ad!
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic