KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > h2 > test > db > TestTempTables


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