HSQL connection exception, connection does not exist

Question

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 is your unnecessary use of methods and parameters without understanding what they mean. The best method is with getGeneratedKeys(). The other methods can be confusing. Do not use TYPE_SCROLL_INSENSITIVE or CONCUR_READ_ONLY, etc. Just use the default createStatement() — fredt, Feb 02 '18 at 09:58
@fredt as I explained, I have tried using that method all three solutions provide same results. And I mentioned in my question that `TYPE_FORWARD_ONLY` also causes this exception, since you are asking me to remove the argument the default would be `TYPE_FORWARD_ONLY`. I tried it as well still the same exception is thrown. — Mirwise Khan, Feb 02 '18 at 15:29
"connection exception: connection does not exist" means that you are trying to use a `Connection` object whose connection to the database has been closed. Check your code to see where that might be happening. I also agree with @fredt that you really don't seem to understand what you are asking HSQLDB to do, e.g., generating an invoiceID from `SELECT COUNT(*) FROM INFORMATION_SCHEMA.SYSTEM_SESSIONS` makes so sense at all. — Gord Thompson, Feb 02 '18 at 16:27
The `try(...)` block is closing the connection and the connection is probably a singleton. Rewrite the code without the try with resources block and do not close the connection. — fredt, Feb 02 '18 at 18:18
@fredt I tried but that didn't work as well. In my singleton class when `close()` is called it outputs "connection closed" at console. Besides if the connection is closed it will be opened again in the `getConnection()`. I get the "connection closed" message only when either the GUI is minimized or closed. — Mirwise Khan, Feb 03 '18 at 08:21

score 0 · Accepted Answer · answered Feb 08 '18 at 19:55

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

HSQL connection exception, connection does not exist

1 Answers1