title: HSQL connection exception, connection does not exist
body: <p>I'm getting really ambiguous exception <code>connection exception : connection does not exist</code> 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.</p>
  
  <p>In my jdbc project I want to get the auto generated <code>invoiceID</code> 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 <code>getInvoiceID()</code> 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.</p>
  
  <p>I tried to use alternative ways to retrieve auto generated field from the invoice table but the problem remains same.</p>
  
  <pre><code>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;
  }
  </code></pre>
  
  <p>the above solution is referenced from <a href="https://stackoverflow.com/questions/29789008/hsql-identify-number-of-open-connection">Here</a></p>
  
  <pre><code>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;
  }
  </code></pre>
  
  <p>and I also tried the solution with <a href="https://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc">generated keys</a></p>
  
  <p>Yesterday when I was about to post this question I figured out that the exception was thrown because I used <code>ResultSet.TYPE_FORWARD_ONLY</code> and I changed to <code>ResultSet.TYPE_SCROLL_INSENSITIVE</code> which eventually solved the issue. But now I'm pretty much sure that this exception is caused by the method <code>getInvoiceID()</code> and is thrown by the next function call that tries to access the database.</p>
  
  <p>Here is my table</p>
  
  <pre><code> 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)
  );
  </code></pre>
  
  <p>Exception :</p>
  
  <pre><code>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
  </code></pre>