• 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
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

error on statement

 
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This code generates are error, having something to do with using the "Statement" twice in a row. If I utilized "Statement2" it has no error and works correctly. Do I need to close out the recordset or the statement to utilize the statement again in another query?

The error is posted at the bottom.




 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Reusing/Closing a Statement will cause any resultset associated with to be released, so you can not use ResultSet once you close the statement or you execute any other query on same statement object.

you are getting the error because you are using resultset (roleSet), after you have executed another query on statement.

Shailesh
 
Ranch Hand
Posts: 89
Oracle Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The error is coming because of using executeQuery() on the same statement object two times.

try if this works..

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database01", "SMART", "password");

String str1 = "SELECT * FROM account WHERE username = '" + userName + "'";

String str2 = "select * from role";

Statement statement1 = connection.createStatement();
ResultSet roleSet = statement1.executeQuery(str2);

Statement statement2 = connection.createStatement();
ResultSet rs = statement2.executeQuery(str1);
 
Tom Joiner
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So you are supposed to create a new statement for every SQL execution? That seems a bit out of control.. is there a way to release it and use it again?
 
author & internet detective
Posts: 39957
804
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes. You can reuse a prepared statement. However the SQL needs to be the same, so it wouldn't help you here.

String sql = "SELECT * FROM account WHERE username = ?";
PreparedStatement statement1 = connection.prepareStatement(sql);
statement1.setString(1, userName)l
ResultSet roleSet = statement1.executeQuery();
 
Evildoers! Eat my justice! And this tiny ad's justice too!
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic