Menu
I am using apache derby for my database. I am able to perform inserts into the database. The following is the excerpt from the code that attempts to display the contents of my only table 'MAINTAB'. The instance of java.sql.Connection is 'dbconn'.
ResultSet word;
Statement query;
String getData="SELECT THEWORD FROM MAINTAB";
try{
System.out.println(dbconn.getAutoCommit());
query = dbconn.createStatement();
word = query.executeQuery(getData);
query.close();
dbconn.setAutoCommit(false);
System.out.println(dbconn.getAutoCommit());
for(;word.next();)
System.out.println(word.getString(1));
}catch(Throwable e){
System.out.println("Table fetch failed or result data failed");}
And the following is the output.
org.apache.derby.jdbc.EmbeddedDriver loaded.
Database testDB connected
true
false
Table fetch failed or result data failed
---SQLException Caught---
SQLState: XCL16
Severity: 20000
Message: ResultSet not open. Operation 'getString' not permitted. Verify that autocommit is OFF.
java.sql.SQLException: ResultSet not open. Operation 'getString' not permitted. Verify that autocommit is OFF.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.checkIfClosed(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.getString(Unknown Source)
Closed connection
at test.ShowData.main(ShowData.java:30)
Caused by: java.sql.SQLException: ResultSet not open. Operation 'getString' not permitted. Verify that autocommit is OFF.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(
Unknown Source)
... 9 more
Database shut down normally
When, it first asked to verify if AUTOCOMMIT is OFF, I have found from the Derby Documentation that AUTOCOMMIT is turned ON by default to any connection. So, I've turned it off using dbconn.setAutoCommit(false). Still, the error is thrown.
The output before the error explains that the result set was fetched without any error. Also, please note that the same error is thrown even if I do not set the AutoCommit to false. Between, I am running derby on eclipse.
206
This occurs because the IBM Data Server Driver for JDBC and SQLJ automatically closes the cursor when all rows have been retrieved from a ResultSet. When ResultSet. next is executed after the cursor is closed the SQLException is thrown.
In your code you closed the ResultSet and the Connection , after which the ResultSet is no longer usable. If you want it to be usable you must leave it (and the Connection ) open. However, if you return the ResultSet , you should refactor your code so the calling method provides the Connection .
The field ResultSet. CONCUR_UPDATABLE creates a ResultSet object that can be updated while CONCUR_READ_ONLY creates a ResultSet that cannot be updated but only it can be read. The problem that you cannot see any changes that you made in the database table is because you are using read only type ResultSet.
for me, it was the Connection object that got closed. so next time think about using your existing Connection object.
instead, I Use this everytime I make a new Query.
private Connection getConnect() {
try {
return DriverManager.getConnection(Utils.getDatabaseConnection(), null);
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
and then do whatever with null check for ex.
getConnect().createStatement();
answered Oct 06 '22 01:10
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With