Using Derby embedded, I can insert into a temporary table but not query after the insert

Question

I have a connection to an embedded derby database.

I want to create a temporary table and learned that I have to use DECLARE GLOBAL TEMPORARY TABLE and prefix SESSION to the table name in the INSERT statement.

I have tried using and not using the SESSION prefix in the DECLARE and SELECT statements but it has made no difference.

The DECLARE statement to create the temporary table is successful as is the two INSERT statements (each returns a count of 1).

The SELECT statement fails to return any results. I have tried "SELECT * FROM..." without success as well.

Any advice is appreciated.

(NOTE: the conn here is a wrapper class, so the conn.executeQuery, conn.executeUpdate actually get a Statement and use the Statement's executeQuery/Update)

    System.out.println("tt 1a" );
    conn.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.tempstats ( t1 int, t2 int, t3 int) NOT LOGGED "); 
    System.out.println("tt 1b" );
    //
    PreparedStatement pStmt = conn.prepareStatement( "INSERT INTO SESSION.tempstats VALUES ( ?, ?, ? )" );
    pStmt.setInt( 1, 1 );
    pStmt.setInt( 2, 1 );
    pStmt.setInt( 3, 1 );
    int count = pStmt.executeUpdate();
    System.out.println("tt 2, count " + count );
    pStmt.setInt( 1, 2 );
    pStmt.setInt( 2, 2 );
    pStmt.setInt( 3, 2 );
    count = pStmt.executeUpdate();
    System.out.println("tt 3, count " + count );
    ResultSet testRs = conn.executeQuery( "SELECT t1, t2 from SESSION.tempstats" );
    count = 0;
    while ( testRs.next() ) {
        log2 ("result of tempstats table is " + testRs.getInt("t1") + " " + testRs.getString("t2") );
        count++;
    }
    System.out.println("tt 4, query result count = " + count );
    testRs.close();    
    pStmt.close();

The result of the above code is

tt 1a
tt 1b
tt 2, count 1
tt 3, count 1
tt 4, query result count = 0

Jim In NH Jim In NH · Accepted Answer · 2010-11-14T06:04:02

The temporary table has to be created with "ON COMMIT PRESERVE ROWS" if autocommit is set to true, as was my case.

If you set autocommit to false the original code would work.

In my case is was set to true, it is now set to false.

Using Derby embedded, I can insert into a temporary table but not query after the insert

2 Answers