Actually its odd... The create temp table statement seems to succeed. The subsequent inserts and selects seems to work fine as well. The problem is when we try to delete the rows. The part that is odd is that the temp table is GLOBAL so technically the connections should not matter, but the same connection is used for the inserts and selects as for the delete.
Also, could you clarify wrt GLOBAL temp tables... Do they maintain which session/connection inserted rows? We are using these temporary tables to temporarily store IDs that match some criteria. The issue is that these ID values need to be scoped to a Hibernate session. In the ideal world you'd always use local temporary tables since these would be scoped. The problem is that we create these tables as we need them. And we cannot do this "in line" with the main JDBC connection because there may already be uncommitted work done on it and as we discussed above creating this temp table would cause those changes to be committed. This is why we open up the new connection, but as you point out that means we need to use a global temporary table. Any suggestions? The only one I see is to mimic what Oracle does for its temporary table support in Hibernate itself for databases such as H2 (and HSQLDB) which do not support creating local temp tables within a transaction. What Oracle does is to essentially create a global temporary table but it automatically manages a column that indicates which session (connection) the data belongs to. You see any other options here? -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
