使用嵌入式 Derby，我可以插入临时表，但插入后不能查询
我有一个到嵌入式德比数据库的连接。
我想创建一个临时表，并了解到我必须使用 DECLARE GLOBAL TEMPORARY TABLE 并在 INSERT 语句中将 SESSION 前缀添加到表名中。
我尝试过在 DECLARE 和 SELECT 语句中使用和不使用 SESSION 前缀，但没有任何区别。
用于创建临时表的 DECLARE 语句和两个 INSERT 语句一样成功（每个语句返回计数 1）。
SELECT 语句无法返回任何结果。我也尝试过“SELECT * FROM...”但没有成功。
任何建议表示赞赏。
（注意：这里的conn是一个包装类，所以conn.executeQuery、conn.executeUpdate实际上得到一个Statement并使用Statement的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();
上面代码的结果是
tt 1a
tt 1b
tt 2, count 1
tt 3, count 1
tt 4, query result count = 0
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果自动提交设置为 true，则必须使用“ON COMMIT PRESERVE ROWS”创建临时表，就像我的情况一样。
如果将 autocommit 设置为 false，原始代码将起作用。
在我的例子中，它被设置为 true，现在设置为 false。
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.
创建表时使用“ON COMMIT PRESERVE ROWS”：
Use "ON COMMIT PRESERVE ROWS" when you create a table: