my dog learned polymorphism
The moose likes JDBC and Relational Databases and the fly likes error on statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "error on statement" Watch "error on statement" New topic

error on statement

Tom Joiner
Ranch Hand

Joined: Sep 19, 2006
Posts: 47
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.

Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1082

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.


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Nidhi Singhal
Ranch Hand

Joined: Sep 19, 2004
Posts: 89

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

Joined: Sep 19, 2006
Posts: 47
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?
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

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();

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: error on statement
It's not a secret anymore!