title: How to SELECT data from in-memory Derby database table?
body: <p>I have created an in-memory database table using JavaDB/Derby. I can <code>INSERT</code> data into the table. But it doesn't work when I try to <code>SELECT</code> data from the table.</p>
  
  <p>I <strong>create</strong> the table with:</p>
  
  <pre><code>DECLARE GLOBAL TEMPORARY TABLE SESSION.memtable (id int, name varchar(10))
  NOT LOGGED
  </code></pre>
  
  <p><strong>insert</strong> data with:</p>
  
  <pre><code>INSERT INTO SESSION.memtable (id, name) VALUES (?,?)
  </code></pre>
  
  <p>and it returns <code>1</code> for <code>rows affected</code>. I <strong>select</strong> data with:</p>
  
  <pre><code>SELECT name FROM SESSION.memtable WHERE id = ?
  </code></pre>
  
  <p>but it return an <strong>empty</strong> <code>ResultSet</code> (<code>rs.next()</code> is false).</p>
  
  <p>What am I doing wrong when using <code>SELECT</code>?</p>
  
  <p>I use the <code>derby.jar</code> that is included in JDK7.</p>
  
  <p>Here is my code:</p>
  
  <pre><code>public class DBTest {
      public static void main(String[] args) {
          final int userId = 4;
          final String sql = "DECLARE GLOBAL TEMPORARY TABLE SESSION.memtable "+
                              "(id int, name varchar(10)) NOT LOGGED";
          final String inSQL = "INSERT INTO SESSION.memtable (id, name) "+
                               "VALUES (?,?)";
          final String selSQL = "SELECT name FROM SESSION.memtable WHERE id = ?"
          final String connURL = "jdbc:derby:memory:memdatabase;create=true";
          try(Connection conn = DriverManager.getConnection(connURL);) {
              try (PreparedStatement ps = conn.prepareStatement(sql);) {
                  ps.execute();
              }
              try (PreparedStatement ps = conn.prepareStatement(inSQL);) {
                  ps.setInt(1, userId);
                  ps.setString(2, "Jonas");
                  int rows = ps.executeUpdate();
                  System.out.println(rows + " rows inserted.");
              }
              try (PreparedStatement ps = conn.prepareStatement(selSQL);) {
                  ps.setInt(1, userId);
                  try (ResultSet rs = ps.executeQuery();) {
                      String name;
                      if(rs.next()) {
                          name = rs.getString("name");
                      } else {
                          name = null;
                      }
                      System.out.println("Name: " + name);
                  }
              }
          } catch (SQLException e) {e.printStackTrace();}}}
  </code></pre>
  
  <p><strong>UPDATE</strong> </p>
  
  <p>If I change the SELECT-query to:</p>
  
  <pre><code>SELECT t1.name AS name FROM SESSION.memtable t1 WHERE id = ?
  </code></pre>
  
  <p>I get this error message:</p>
  
  <pre><code>java.sql.SQLException: Operation 'DROP TABLE' cannot be performed on object 'MEMTABLE(ID, NAME)' because there is an open ResultSet dependent on that object.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.commitIfAutoCommit(Unknown Source)
      at org.apache.derby.impl.jdbc.ConnectionChild.commitIfAutoCommit(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedStatement.resultSetClosing(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
      at DBTest.main(DBTest.java:29)
  Caused by: java.sql.SQLException: Operation 'DROP TABLE' cannot be performed on object 'MEMTABLE(ID, NAME)' because there is an open ResultSet dependent on that object.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
      ... 11 more
  Caused by: ERROR X0X95: Operation 'DROP TABLE' cannot be performed on object 'MEMTABLE(ID, NAME)' because there is an open ResultSet dependent on that object.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.verifyNoOpenResultSets(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.prepareToInvalidate(Unknown Source)
      at org.apache.derby.impl.sql.depend.BasicDependencyManager.coreInvalidateFor(Unknown Source)
      at org.apache.derby.impl.sql.depend.BasicDependencyManager.invalidateFor(Unknown Source)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.tempTablesAndCommit(Unknown Source)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.doCommit(Unknown Source)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.userCommit(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.commit(Unknown Source)
      ... 6 more
  </code></pre>
  
  <p>But <code>Name:</code> is not printed, so it still doesn't work.</p>