title: HSQLDB: I'm trying to populate a table with some data from temp table, but the data is getting erased
body: <p>I'm using HSQLDB. Inside a java function I'm creating a temporary table and populating it with some data. Right after I'm updating a regular table with the data from the temporary table. </p>
  
  <p>If I debug the code inside the function and perform <code>SELECT</code> I can see that the data was indeed updated in both tables, but after committing the changes the table is no longer updated! (the regular table, not the temporary one).</p>
  
  <p>When I created the temp table I used: <code>ON COMMIT PRESERVE ROWS</code> but it doesn't help.</p>
  
  <p>Does anyone know why is this happening?</p>
  
  <p>Thanks!</p>
  
  <p>Here is the code:</p>
  
  <pre><code>public synchronized void foo(String[] objectsToMark) throws Exception {
  
          String createTempTableQuery = "CREATE TEMP TABLE "+TEMP_TABLE_NAME+"(OBJECTID VARCHAR) " +
                  "ON COMMIT PRESERVE ROWS";
          String dropTempTableQuery = "DROP TABLE "+TEMP_TABLE_NAME;
  
          String updateQueryString = "update " + TABlE_A + " set " +TABlE_A+".X = 'N' where "+TABlE_A+".RESULT_ID in (select "+TABlE_A+".RESULT_ID FROM "+TABlE_A
                          +" inner join "+TABLE_B+" on "+TABLE_B+".OBJECTID = "+TABlE_A+".OBJECTID"
                          +" inner join TEMP_TABLE on "+TABLE_B+".OBJECTID = TEMP_TABLE.OBJECTID"
                          +" where "+TABlE_A+".IS_X = 'Y')";
  
          String insertObjectIDs = "INSERT INTO " + TEMP_TABLE_NAME + " (OBJECTID) VALUES (?)";
          Connection conn = null;
          Statement stmt = null;
          PreparedStatement psInsertObjectIDs = null;
          try {
              conn = getConnection(getClass().getName() + "");
              stmt = conn.createStatement();
  
  
              stmt.execute(createTempTableQuery);
  
              // insert object's IDs into table
              psInsertObjectIDs = conn.prepareStatement(insertObjectIDs);
  
              for (String id : objectsToMark) {
                  int i = 0;
                  psInsertObjectIDs.setString(++i, id);
                  psInsertObjectIDs.addBatch();
              }
              psInsertObjectIDs.executeBatch();
  
              stmt.execute(updateQueryString);
  
          } catch (Exception e) {
          } finally {
              // close resources
              close(stmt);
              stmt = null;
              // drop the temporary tables
              if (conn != null) {
                  try {
                      stmt = conn.createStatement();
                      stmt.execute(dropTempTableQuery);
                  } catch (SQLException e) {
                  }
                  close(stmt);
              }
  
              close(psInsertObjectIDs);
              closeConnection(conn);
          }
      }
  </code></pre>
  
  <p>Notice that there is no commit here but the connection is configured to perform auto commit.</p>