01: /*
02: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
03: * (license2)
04: * Initial Developer: H2 Group
05: */
06: package org.h2.test.db;
07:
08: import java.sql.Connection;
09: import java.sql.ResultSet;
10: import java.sql.SQLException;
11: import java.sql.Statement;
12:
13: import org.h2.test.TestBase;
14:
15: /**
16: * Temporary table tests.
17: */
18: public class TestTempTables extends TestBase {
19:
20: public void test() throws Exception {
21: deleteDb("tempTables");
22: Connection c1 = getConnection("tempTables");
23: Connection c2 = getConnection("tempTables");
24: Statement s1 = c1.createStatement();
25: Statement s2 = c2.createStatement();
26: s1.execute("CREATE LOCAL TEMPORARY TABLE LT(A INT)");
27: s1.execute("CREATE GLOBAL TEMPORARY TABLE GT1(ID INT)");
28: s2.execute("CREATE GLOBAL TEMPORARY TABLE GT2(ID INT)");
29: s2.execute("CREATE LOCAL TEMPORARY TABLE LT(B INT)");
30: s2.execute("SELECT B FROM LT");
31: s1.execute("SELECT A FROM LT");
32: s1.execute("SELECT * FROM GT1");
33: s2.execute("SELECT * FROM GT1");
34: s1.execute("SELECT * FROM GT2");
35: s2.execute("SELECT * FROM GT2");
36: s2.execute("DROP TABLE GT1");
37: s2.execute("DROP TABLE GT2");
38: s2.execute("DROP TABLE LT");
39: s1.execute("DROP TABLE LT");
40:
41: // temp tables: 'on commit' syntax is currently not documented, because
42: // not tested well
43: // and hopefully nobody is using it, as it looks like functional sugar
44: // (this features are here for compatibility only)
45: ResultSet rs;
46: c1.setAutoCommit(false);
47: s1
48: .execute("create local temporary table test_temp(id int) on commit delete rows");
49: s1.execute("insert into test_temp values(1)");
50: rs = s1.executeQuery("select * from test_temp");
51: checkResultRowCount(rs, 1);
52: c1.commit();
53: rs = s1.executeQuery("select * from test_temp");
54: checkResultRowCount(rs, 0);
55: s1.execute("drop table test_temp");
56:
57: s1
58: .execute("create local temporary table test_temp(id int) on commit drop");
59: s1.execute("insert into test_temp values(1)");
60: rs = s1.executeQuery("select * from test_temp");
61: checkResultRowCount(rs, 1);
62: c1.commit();
63: try {
64: rs = s1.executeQuery("select * from test_temp");
65: error("test_temp should have been dropped automatically");
66: } catch (SQLException e) {
67: checkNotGeneralException(e);
68: }
69:
70: c1.close();
71: c2.close();
72: }
73:
74: }
|