I'm getting really ambiguous exception connection exception : connection does not exist and took me a lot of time to find it's source. I've been searching through HSQLDB docs and their mailing list, stackoverflow etc. Recently I found the actual cause of the exception.
In my jdbc project I want to get the auto generated invoiceID from HSQLDB but when I attempt to do so it turns out to be ok but after that when I run another query that reads or updates something in database I get this exception. I've been observing this issue for three weeks. Now I figured out getInvoiceID() method was causing it. When I comment this method the project runs well and good but call to this function at any point prevents further calls to jdbc.
I tried to use alternative ways to retrieve auto generated field from the invoice table but the problem remains same.
public static int getInvoiceID() {
int invoiceID = -1;
try ( Statement stmt = DatabaseManager.getInstance().getConnection()
.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet res = stmt.executeQuery("SELECT COUNT(*) FROM INFORMATION_SCHEMA.SYSTEM_SESSIONS");) {
if(res.next())
invoiceID = res.getInt(1);
else
invoiceID = 1;
} catch (SQLException e) {
System.err.println("getInvoiceID Exception: " + e.getMessage());
}
return invoiceID;
}
the above solution is referenced from Here
public static int getInvoiceID() {
int invoiceID = -1;
try ( Statement stmt = DatabaseManager.getInstance().getConnection()
.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);) {
stmt.executeQuery("INSERT INTO Invoice VALUES (NULL, NULL, NOW(), NULL);");
try (ResultSet result = stmt.executeQuery("CALL IDENTITY();")) {
if(result.next())
invoiceID = result.getInt(1);
} catch( SQLException e){
e.printStackTrace();
}
} catch (SQLException e) {
System.err.println("getInvoiceID Exception: " + e.getMessage());
}
return invoiceID;
}
and I also tried the solution with generated keys
Yesterday when I was about to post this question I figured out that the exception was thrown because I used ResultSet.TYPE_FORWARD_ONLY and I changed to ResultSet.TYPE_SCROLL_INSENSITIVE which eventually solved the issue. But now I'm pretty much sure that this exception is caused by the method getInvoiceID() and is thrown by the next function call that tries to access the database.
Here is my table
CREATE TABLE IF NOT EXISTS Invoice(
InvoiceID INT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY,
ClientID VARCHAR(4),
Entry DATETIME NOT NULL,
TotalAmount DECIMAL(10,2) DEFAULT 0,
FOREIGN KEY (ClientID) REFERENCES Client(ClientID)
);
Exception :
java.sql.SQLNonTransientConnectionException: connection exception: connection does not exist
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.connectionClosedException(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.checkClosed(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at database.ProductManager.getProductByID(ProductManager.java:203)
at gui.InvoicePanel$4.actionPerformed(InvoicePanel.java:423)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: org.hsqldb.HsqlException: connection exception: connection does not exist
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
... 42 more
The problem was with the Connection I amended my code and closed connection after performing any SQL. That solved my problem as follows :
// getConnection() returns new connection if null
DatabaseManager.getInstance().getConnection();
/*
* SQL
*/
DatabaseManager.getInstance().close();