/*

 Derby - Class org.apache.derbyTesting.functionTests.tests.lang.DeclareGlobalTempTableJavaTest

 Licensed to the Apache Software Foundation (ASF) under one or more
 contributor license agreements.  See the NOTICE file distributed with
 this work for additional information regarding copyright ownership.
 The ASF licenses this file to You under the Apache License, Version 2.0
 (the "License"); you may not use this file except in compliance with
 the License.  You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

 Unless required by applicable law or agreed to in writing, software
 distributed under the License is distributed on an "AS IS" BASIS,
 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 See the License for the specific language governing permissions and
 limitations under the License.

 */

package org.apache.derbyTesting.functionTests.tests.lang;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.DatabaseMetaData;
import java.sql.Connection;

import junit.framework.Test;
import org.apache.derbyTesting.functionTests.util.SampleVTI;


import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;


/**
 * Test for declared global temporary tables introduced in Cloudscape 5.2 The
 * temp table tests with holdable cursor and savepoints are in
 * declareGlobalTempTableJavaJDBC30 class. The reason for a different test class
 * is that the holdability and savepoint support is under jdk14 and higher. But
 * we want to be able to run the non-jdk14 specific tests under all the jdks we
 * support and hence splitting the tests into 2 different classes Global
 * Temporary Tables are referenced as GTT to make it easy for Naming.
 */

public class DeclareGlobalTempTableJavaTest extends BaseJDBCTestCase {

    public DeclareGlobalTempTableJavaTest(String name) {
        super(name);
    }

    public static Test suite() {
	return TestConfiguration.defaultSuite(DeclareGlobalTempTableJavaTest.class);
    }
    protected void setUp() throws Exception {
        super.setUp();
        dropSchemaTables();
        getConnection().setAutoCommit(false);
    }

    protected void tearDown() throws Exception {
        super.tearDown();
    }

    /**
     * Test switching to session schema (it doesn't yet exist because no create
     * schema session has been issued yet) and then try to create first persistent
     * object in it. This used to cause null pointer exception (DERBY-1706).
     * 
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */
    public void testDerby1706() throws SQLException {
        Statement s = createStatement();
        // the try/catch block ensures to drop the SESSION schema if exists.
        try {
            s.executeUpdate("drop schema SESSION restrict");
        } catch (SQLException e) {
            assertSQLState("42Y07", e);
        }
        assertUpdateCount(s , 0 , "set schema SESSION");
        // This used to cause NullPointerException before.
        assertUpdateCount(s, 0, "create table DERBY1706(c11 int)");
 	assertUpdateCount(s, 0, "drop table DERBY1706");
        assertUpdateCount(s, 0, "set schema APP");
 	assertUpdateCount(s, 0, "drop schema SESSION restrict");
    }

    /**
     * Test the schema of Golabal Temporary Tables.
     * Global Temporary Tables can only be in SESSION schema. Declaring them in Other schemas Should give an  Error. 
     * Global Temporary Tables always goes into SESSION schema. Even if
     * the current schema is not SESSION.
     * 
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */
    public void testGTTSchemaName() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "set schema APP");
        // Global Temporary Tables can only be created in SESSION schema
        assertStatementError("428EK",s,"DECLARE GLOBAL TEMPORARY TABLE APP.t2(c21 int) on commit delete rows not logged");
        s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t2(c21 int) on commit delete rows not logged");
        // temp table t2 is not in APP schema
        assertStatementError("42X05", s, "insert into APP.t2 values(7)");
        // temp table should be referred as SESSIO.t2
        assertStatementError("42X05", s, "insert into t2 values(7)");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(7)");
        // temp table t2 must be qualified with SESSION
        assertStatementError("42Y55", s, "drop table t2");
        assertUpdateCount(s , 0 , "drop table SESSION.t2");
    }

    /**
     * Some positive Grammar tests for the DECLARE GLOBAL TEMPORARY TABLE
     * 
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */
    public void testPositiveGrammars() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s, 0,
                "DECLARE GLOBAL TEMPORARY TABLE tA(c1 int) not logged");
        assertUpdateCount(s, 0,
                "DECLARE GLOBAL TEMPORARY TABLE tB(c1 int) on commit delete rows not logged");
        assertUpdateCount(s, 0,
                "DECLARE GLOBAL TEMPORARY TABLE tC(c1 int) not logged on commit delete rows");
        assertUpdateCount(s, 0,
                "DECLARE GLOBAL TEMPORARY TABLE tD(c1 int) on commit preserve rows not logged");
        assertUpdateCount(s, 0,
                "DECLARE GLOBAL TEMPORARY TABLE tE(c1 int) not logged on commit preserve rows");
        assertUpdateCount(s, 0,
                "DECLARE GLOBAL TEMPORARY TABLE tF(c1 int) on rollback delete rows not logged");
        assertUpdateCount(s, 0,
                "DECLARE GLOBAL TEMPORARY TABLE tG(c1 int) not logged on rollback delete rows");
        assertUpdateCount(
                s,
                0,
                "DECLARE GLOBAL TEMPORARY TABLE tH(c1 int) on commit preserve rows not logged on rollback delete rows");
        assertUpdateCount(
                s,
                0,
                "DECLARE GLOBAL TEMPORARY TABLE tI(c1 int) not logged on commit preserve rows on rollback delete rows");
        assertUpdateCount(
                s,
                0,
                "DECLARE GLOBAL TEMPORARY TABLE tJ(c1 int) not logged on rollback delete rows on commit preserve rows");
        assertUpdateCount(
                s,
                0,
                "DECLARE GLOBAL TEMPORARY TABLE tK(c1 int) on commit delete rows not logged on rollback delete rows");
        assertUpdateCount(
                s,
                0,
                "DECLARE GLOBAL TEMPORARY TABLE tL(c1 int) not logged on commit delete rows on rollback delete rows");
        assertUpdateCount(
                s,
                0,
                "DECLARE GLOBAL TEMPORARY TABLE tM(c1 int) not logged on rollback delete rows on commit delete rows");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tA");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tB");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tC");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tD");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tE");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tF");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tG");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tH");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tI");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tJ");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tK");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tL");
        assertUpdateCount(s, 0, "DROP TABLE SESSION.tM");
    }

    /**
     * Some Negative Grammar tests for the DECLARE GLOBAL TEMPORARY TABLE
     * 
     * @exception SQLException
     * Thrown if some unexpected error happens
     */
    public void testNegativeGrammars() throws SQLException {
        Statement s = createStatement();
        assertStatementError("42X01", s,
                "DECLARE GLOBAL TEMPORARY TABLE t1(c11 int)");
        assertStatementError("42613", s,
                "DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED NOT LOGGED");
        assertStatementError(
                "42613",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON COMMIT PRESERVE ROWS ON COMMIT DELETE ROWS");
        assertStatementError(
                "42613",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON ROLLBACK DELETE ROWS ON ROLLBACK DELETE ROWS");
        assertStatementError(
                "42X01",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) ON ROLLBACK DELETE ROWS ON COMMIT PRESERVE ROWS");
    }

    /**
     * Test some of the features that are not allowed on temp tables namely
     * ALTER TABLE , LOCK TABLE , RENAME TABLE , CREATE INDEX AND CREATE VIEW
     * CREATE VIEW is some what special in that it can't have reference to the
     * temp tables. The Other two features are generated always as identity and
     * Long datatype.
     * 
     * @throws SQLException
     */
    public void testFeaturesNotAllowedOnGTTs() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged on commit delete rows");
        // Alter Table is not allowed on temp tables.
        assertStatementError("42995", s,"ALTER TABLE SESSION.t2 add column c22 int");
        // Lock Table is not allowed on temp tables.
        assertStatementError("42995", s, "LOCK TABLE SESSION.t2 IN SHARE MODE");
        // Rename Table is not allowed on temp tables.
        assertStatementError("42995", s, "RENAME TABLE SESSION.t2 TO t3");
        // Create Index is not allowed on temp tables.
        assertStatementError("42995", s, "CREATE index t2i1 on SESSION.t2 (c21)");
        // Create view that references temp tables is not allowed
        assertStatementError("XCL51", s, "CREATE VIEW t2v1 as select * from SESSION.t2");
        // generated always as identity not allowed on temp tables.
        assertStatementError("42995",s,"DECLARE GLOBAL TEMPORARY TABLE SESSION.t1(c21 int generated always as identity) on commit delete rows not logged");
        // Long datatypes are not supported.
        assertStatementError(
                "42962",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE SESSION.t1(c21 int, c22 blob(3k)) on commit delete rows not logged");
        assertStatementError(
                "42962",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE SESSION.t1(c21 int, c22 clob(3k)) on commit delete rows not logged");
        assertStatementError(
                "42962",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE SESSION.t1(c21 int, c22 long varchar) on commit delete rows not logged");
        s.executeUpdate("DROP TABLE SESSION.t2");
        assertStatementError("42Y55", s, "DROP TABLE SESSION.t1");
    }

    /**
     * Test some features that allowed on physical tables in SESSION Schema
     * 
     * @throws SQLException
     */
    public void testFeaturesAllowedOnPhysicalTablesOfSESSIONSchema()
            throws SQLException {
        Statement s = createStatement();
        try {
            s.executeUpdate("CREATE schema SESSION");
        } catch (SQLException e) {
            assertSQLState("X0Y68", e);
        }
        assertUpdateCount(s , 0 , "CREATE TABLE SESSION.t2(c21 int)");
        // Alter Table is allowed on physical tables in SESSION schema
        assertUpdateCount(s, 0, "ALTER TABLE SESSION.t2 add column c22 int");
        // Lock Table is allowed on physical tables in SESSION schema
  	assertUpdateCount(s , 0 , "LOCK TABLE SESSION.t2 IN EXCLUSIVE MODE");
        // Rename Table is allowed on physical tables in SESSION schema
        assertUpdateCount(s , 0 , "RENAME TABLE SESSION.t2 TO t3");
        // Lock column is allowed on physical tables in SESSION schema
        assertUpdateCount(s , 0 , "RENAME COLUMN SESSION.t3.c21 TO c23");
        // Create Index is allowed on physical tables in SESSION schema
        assertUpdateCount(s , 0 , "CREATE TABLE SESSION.t2 (c21 int)");
        assertUpdateCount(s , 0 , "CREATE index t2i1 on SESSION.t2 (c21)");
        // Create View referencing physical tables in SESSION schema is not
        // supported
        assertStatementError("XCL51", s,
                "CREATE VIEW t2v1 as select * from SESSION.t2");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t3");
    }
    /**
     * Test the various constraints not allowed on temp tables namely primary
     * key constraints , Unique constraints , check constraints and foreign key
     * constraints.
     * 
     * @throws SQLException
     */
    public void testConstraintsNotAllowedOnGTTs() throws SQLException {
        Statement s = createStatement();
        // primary key constraints are not allowed on temp tables.
        assertStatementError(
                "42995",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int not null, constraint pk primary key (c21)) on commit delete rows not logged");
        // Unique constraints are not allowed on temp tables.
        assertStatementError(
                "42995",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int not null unique) on commit delete rows not logged");
        // foreign key constraints are not allowed on temp tables.
        s.executeUpdate("CREATE TABLE t1(c11 int not null unique)");
        assertStatementError(
                "42995",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int references t1(c11)) on commit delete rows not logged");
        // check constraints are not allowed on temp tables.
        assertStatementError(
                "42995",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int check (c21 > 0)) on commit delete rows not logged");
        s.executeUpdate("DROP TABLE t1");
    }

    /**
     * Test the constraints allowed on physical tables in SESSION schema.
     * 
     * @throws SQLException
     */
    public void testConstraintsAllowedOnSESSIONPhysicalTables()
            throws SQLException {
        Statement s = createStatement();
        try {
            s.executeUpdate("CREATE SCHEMA SESSION");
        } catch (SQLException e) {
            assertSQLState("X0Y68", e);
        }
        // primary key constraints allowed on SESSION physical tables
        s
                .executeUpdate("CREATE TABLE SESSION.t1(c21 int not null, constraint pk primary key (c21))");
        // unique constraints allowed on SESSION physical tables
        s.executeUpdate("CREATE TABLE SESSION.t2(c21 int not null unique)");
        // check constraints allowed on SESSION physical tables
        s.executeUpdate("CREATE TABLE SESSION.t3(c21 int check (c21 > 0))");
        // foreign key constraints allowed on SESSION physical tables
        s.executeUpdate("CREATE TABLE t4(c11 int not null unique)");
        s.executeUpdate("CREATE TABLE SESSION.t5(c21 int references t4(c11))");
        // cleanUp
        s.executeUpdate("DROP TABLE SESSION.t1");
        s.executeUpdate("DROP TABLE SESSION.t2");
        s.executeUpdate("DROP TABLE SESSION.t3");
        s.executeUpdate("DROP TABLE SESSION.t5");
        s.executeUpdate("DROP TABLE t4");
    }

    /**
     * Test declared temporary table with ON COMMIT DELETE ROWS with and without
     * open cursors. Tests with holdable cursor are in a different class since
     * holdability support is only under jdk14 and higher.
     * 
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */
    public void testOnCommiDeleteRowsWithAndWithoutOpenCursors()
            throws SQLException {
        Statement s = createStatement();
        // Temp table t2 with not holdable cursor open on it. Data should get
        // deleted from t2 at commit time
        assertUpdateCount(s, 0, "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(22, 22)");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(23, 23)");
        ResultSet rs2 = s.executeQuery("select count(*) from SESSION.t2");
        // Before commit t2 has 2 columns.
        JDBC.assertSingleValueResultSet(rs2 , "2");
        // eventhough this cursor is open, it is not a hold cursor. Commit
        // should delete the rows
        rs2 = s.executeQuery("select * from SESSION.t2");
        rs2.next();
        // Temp table t3 with no open cursors of any kind on it. Data should get
        // deleted from t3 at commit time
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit delete rows not logged");
        assertUpdateCount(s , 1 , "insert into SESSION.t3 values(32, 32)");
        assertUpdateCount(s , 1 , "insert into SESSION.t3 values(33, 33)");
        ResultSet rs3 = s.executeQuery("select count(*) from SESSION.t3");
        // Before commit t3 has 2 columns.
        JDBC.assertSingleValueResultSet(rs3 , "2");
        // commiting the above statements
	commit();
        // The 2 rows from t2 got deleted
        rs2 = s.executeQuery("select count(*) from SESSION.t2");
        JDBC.assertSingleValueResultSet(rs2 , "0");
        // The 2 rows from t3 got deleted
        rs3 = s.executeQuery("select count(*) from SESSION.t3");
        JDBC.assertSingleValueResultSet(rs3 , "0");
        s.executeUpdate("DROP TABLE SESSION.t2");
        s.executeUpdate("DROP TABLE SESSION.t3");
    }
    /**
     * Declare a temporary table with ON COMMIT PRESERVE ROWS with and without
     * open cursors. Tests with holdable cursor are in a different class since
     * holdability support is only under jdk14 and higher.
     * 
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */
    public void testOnCommitPreserveRowsWithAndWithoutOpenCursors()
            throws SQLException {
        Statement s = createStatement();
        // Temp table t2 with not holdable cursor open on it. Data should be
        // preserved, holdability shouldn't matter
        s
                .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
        s.executeUpdate("insert into SESSION.t2 values(22, 22)");
        s.executeUpdate("insert into SESSION.t2 values(23, 23)");
        ResultSet rs2 = s.executeQuery("select count(*) from SESSION.t2");
        // t2 has 2 rows before commiting.
        JDBC.assertSingleValueResultSet(rs2 , "2");
        // eventhough this cursor is open, it is not a hold cursor.
        rs2 = s.executeQuery("select * from SESSION.t2");
        rs2.next();
        // Temp table t3 with no open cursors of any kind on it. Data should be
        // preserved, holdability shouldn't matter
        s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged");
        s.executeUpdate("insert into SESSION.t3 values(32, 32)");
        s.executeUpdate("insert into SESSION.t3 values(33, 33)");
        ResultSet rs3 = s.executeQuery("select count(*) from SESSION.t3");
        // t3 has 2 rows before commiting.
        JDBC.assertSingleValueResultSet(rs3 , "2");
        // commit point
        commit();
        rs2 = s.executeQuery("select count(*) from SESSION.t2");
        // The rows in t2 got preserved
        JDBC.assertSingleValueResultSet(rs2 , "2");
        rs3 = s.executeQuery("select count(*) from SESSION.t3");
        // The rows in t3 got preserved
        JDBC.assertSingleValueResultSet(rs3 , "2");

        s.executeUpdate("DROP TABLE SESSION.t2");
        s.executeUpdate("DROP TABLE SESSION.t3");
    }

    /**
     * Test that We can't create the temp table twice and we can'd drop a temp
     * table that doesn't wxist.
     * 
     * @throws SQLException
     */
    public void testDuplicateAndNullGTT() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
        // temp table t2 already exists.
        assertStatementError(
                "X0Y32",
                s,
                "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged on commit preserve rows");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        // t2 has already been dropped.
        assertStatementError("42Y55", s, "DROP TABLE SESSION.t2");
    }

    /**
     * Test That Insert command allowed on temp tables in various conditions.
     * 
     * @throws SQLException
     */
    public void testInsertOnGTT() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 char(2)) on commit delete rows not logged");
        // Regular Insertion - Inserting with values.
        assertUpdateCount(s, 1, "insert into SESSION.t2 values (1, 'aa')");
        assertUpdateCount(s, 3, "insert into SESSION.t2 values (2, 'bb'),(3, 'cc'),(4, null)");
        assertUpdateCount(s, 0, "CREATE TABLE t1(c11 int, c22 char(2))");
        assertUpdateCount(s, 3, "insert into t1 values (5, null),(6, null),(7, 'gg')");
        // Insert into a table values selected from the Other table.
        assertUpdateCount(s, 3, "insert into SESSION.t2 (select * from t1 where c11>4)");
        assertUpdateCount(s, 7, "insert into SESSION.t2 select * from SESSION.t2");
        ResultSet rs1 = s.executeQuery("select sum(c21) from SESSION.t2");
	JDBC.assertSingleValueResultSet(rs1 , "56");
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c21 int, c22 char(2) not null) on commit delete rows not logged");
        assertUpdateCount(s , 1 , "insert into SESSION.t3 values (1, 'aa')");
        // test inserting a null value into a non null column
        assertStatementError("23502", s, "insert into SESSION.t3 values (2, null)");
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c21 int, c22 char(2) default 'aa', c23 varchar(20) default user ) on commit delete rows not logged");
        assertUpdateCount(s, 1, "insert into SESSION.t4 values (1, 'aa', null)");
        // Inserting into a table of which some columns have default values.
        assertUpdateCount(s, 1, "insert into SESSION.t4(c21) values (2)");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t4");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t3");
        assertUpdateCount(s , 0 , "DROP TABLE t1");
    }

    /**
     * Test Delete Operation on temp tables.
     * 
     * @throws SQLException
     */
    public void testDeleteOnGTT() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 decimal) not logged");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(1, 1.1)");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(2, 2.2)");
        ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
        // Initially t2 has 2 rows
        JDBC.assertSingleValueResultSet(rs1 , "2");
        assertUpdateCount(s , 2 , "DELETE FROM SESSION.t2 where c21 > 0");
        rs1 = s.executeQuery("select count(*) from SESSION.t2");
        // After deletion t2 has nothing
        JDBC.assertSingleValueResultSet(rs1 , "0");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
    }

    /**
     * Test Update on temp tables in various ways.
     * 
     * @throws SQLException
     */
    public void testUpdateOnGTT() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(1, 1)");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(2, 1)");
        ResultSet rs2 = s.executeQuery("select count(*) from SESSION.t2 where c22 = 1");
        JDBC.assertSingleValueResultSet(rs2 , "2");
        assertUpdateCount(s , 2 , "UPDATE SESSION.t2 SET c22 = 2 where c21>0");
        rs2 = s.executeQuery("select count(*) from SESSION.t2 where c22 = 1");
        JDBC.assertSingleValueResultSet(rs2 , "0");
        rs2 = s.executeQuery("select count(*) from SESSION.t2 where c22 = 2");
        JDBC.assertSingleValueResultSet(rs2 , "2");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
    }

    /**
     * Test CREATE and DROP operations on SESSION schema
     * 
     * @throws SQLException
     */
    public void testSESSIONschema() throws SQLException {
        Statement s = createStatement();
        // SESSION schema can be created like any other schema
        try {

            s.executeUpdate("CREATE SCHEMA SESSION");
        } catch (SQLException e) {
            assertSQLState("X0Y68", e);
        }
        // SESSION schema can be dropped like any Other Schema
        assertUpdateCount(s , 0 , "DROP SCHEMA SESSION restrict");
        // We can't drop the In-Memory SESSION schema
        assertStatementError("42Y07", s, "DROP SCHEMA SESSION restrict");
    }
    /**
     * CREATE VIEW in SESSION schema referencing a table outside of SESSION
     * schema.
     * 
     * @throws SQLException
     */
    public void testCreateView() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "CREATE TABLE t2 (c28 int)");
        assertUpdateCount(s , 2 , "INSERT INTO t2 VALUES (280),(281)");
        // Create a VIEW in SESSION schema referencing a table outside of
        // SESSION schema
        assertUpdateCount(s, 0, "CREATE VIEW SESSION.t2v1 as select * from t2");
        // Drop the view.
        ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2v1");
        // Test the values in View as selected from Table.
        JDBC.assertSingleValueResultSet(rs1 , "2");
        assertUpdateCount(s , 0 , "DROP VIEW SESSION.t2v1");
        assertUpdateCount(s , 0 , "DROP TABLE t2");
    }
    /**
     * Multiple tests to make sure we do not do statement caching for statement
     * referencing SESSION schema tables. CREATE physical table and then DECLARE
     * GLOBAL TEMPORARY TABLE with the same name in session schema.
     * 
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */
    public void testStatementCaching() throws SQLException {
        Statement s = createStatement();
        // Need to do following 3 in autocommit mode otherwise the data
        // dictionary will be in write mode and statements won't get
        // cached. I need to have statement caching enabled here to make sure
        // that tables with same names do not conflict
        getConnection().setAutoCommit(true);
        try {
            s.executeUpdate("CREATE schema SESSION");
        } catch (SQLException e) {
            assertSQLState("X0Y68", e);
        }
        assertUpdateCount(s , 0 , "CREATE TABLE SESSION.t2 (c21 int)");
        assertUpdateCount(s, 1, "INSERT into SESSION.t2 values(21)");
        getConnection().setAutoCommit(false);
        // select will return data from physical table t2
        ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
        JDBC.assertSingleValueResultSet(rs1 , "1");
        // declare temporary table with same name as a physical table in SESSION
        // schema
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
        assertUpdateCount(s , 1 , "INSERT into SESSION.t2 values(22, 22)");
        assertUpdateCount(s , 1 , "INSERT into SESSION.t2 values(23, 23)");
        // select will return data from temp table t2
        rs1 = s.executeQuery("select count(*) from SESSION.t2");
        JDBC.assertSingleValueResultSet(rs1 , "2");
        // drop the temp table t2
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        // select will return data from physical table t2 because temp table has
        // been deleted
        rs1 = s.executeQuery("select count(*) from SESSION.t2");
        JDBC.assertSingleValueResultSet(rs1 , "1");
	// cleanup
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        assertUpdateCount(s , 0 , "drop schema SESSION restrict");
    }

    /**
     * After creating SESSION schema and making it current schema, temporary
     * tables should not require SESSION qualification
     * 
     * @exception SQLException
     */
    public void testSESSIONQualifier() throws SQLException {
        Statement s = createStatement();
        // We have to qualify the temp tables with SESSION qualifier.
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(21, 21)");
        assertStatementError("42X05", s, "insert into t2 values(23, 23)");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(22, 22)");
        ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
        JDBC.assertSingleValueResultSet(rs1 , "2");
        // Create the SESSION Schema Manually
        try {
            s.executeUpdate("CREATE SCHEMA SESSION");
        } catch (SQLException e) {
            assertSQLState("X0Y68", e);
        }
        // Set the SESSION schema as current schema
        s.executeUpdate("SET SCHEMA SESSION");
        // we don't need the SESSION qualifier for referencing temp tables.
        rs1 = s.executeQuery("select count(*) from t2");
        JDBC.assertSingleValueResultSet(rs1 , "2");
        assertUpdateCount(s , 0 , "DROP TABLE t2");
        assertUpdateCount(s , 0 , "SET SCHEMA APP");
        assertUpdateCount(s , 0 , "drop schema SESSION restrict");
    }
    /**
     * Temporary table created in one connection should not be available in
     * another connection.
     * 
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */
    public void testOneGTTInConnection() throws SQLException {
        Statement s1 = createStatement();
        // creating the temp table in connection 1
        assertUpdateCount(s1 , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
        assertUpdateCount(s1, 1, "insert into SESSION.t2 values(22, 22)");
        // Getting the Second Connection
        Connection con2 = openDefaultConnection();
        Statement s2 = con2.createStatement();
        // con2 should not find temp table declared in con1
        assertStatementError("42X05", s2, "select count(*) from SESSION.t2");
        // connection1 will be closed in tearDown() methos automatically.
        s2.close();
        con2.close();
    }

    /**
     * Temp table in one connection should not conflict with temp table with
     * same name in another connection.
     * 
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */
    public void testSameGTTNameForTwoConnections() throws SQLException {
        Statement s1 = createStatement();
        // creating the temp table t2 in First Connection
        assertUpdateCount(s1 , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
        assertUpdateCount(s1, 1, "insert into SESSION.t2 values(22, 22)");
        // Getting the Second Connection.
        Connection con2 = openDefaultConnection();
        Statement s2 = con2.createStatement();
        // creating the temp table with same name (t2) in the Sesond Connection
	assertUpdateCount(s2 , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged");
        assertUpdateCount(s2, 1, "insert into SESSION.t2 values(99)");
        // dropping temp table t2 defined for con1
        assertUpdateCount(s1 , 0 , "DROP TABLE SESSION.t2");
        // dropping temp table t2 defined for con2
        assertUpdateCount(s2 , 0 , "DROP TABLE SESSION.t2");
	s2.close();
        con2.close();
    }
    /**
     * Prepared statement test - drop the temp table underneath
     * 
     * @throws SQLException
     */
    public void testPreparedStatement1() throws SQLException {
        Statement s = createStatement();
        s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
        PreparedStatement pStmt = prepareStatement("insert into SESSION.t2 values (?, ?)");
        pStmt.setInt(1, 21);
        pStmt.setInt(2, 1);
        pStmt.execute();
        ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
        JDBC.assertSingleValueResultSet(rs1 , "1");
        // drop the temp table t2
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        // now try to insert into the table t2 which was dropped
        try {
            pStmt.setInt(1, 22);
            pStmt.setInt(2, 2);
            pStmt.execute();
            fail(" Table/View 'SESSION.T2' does not exist:");
        } catch (SQLException e) {
            assertSQLState("42X05", e);
        }
    }
    /**
     * Prepared statement test - drop and recreate the temp table with different
     * definition underneath
     * 
     * @throws SQLException
     */
    public void testPreparedStatement2() throws SQLException {
        Statement s = createStatement();
        // create the temporary table t2 with 2 columns.
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
        PreparedStatement pStmt = prepareStatement("insert into SESSION.t2 values (?, ?)");
        pStmt.setInt(1, 21);
        pStmt.setInt(2, 1);
        pStmt.execute();
        pStmt.close();
        ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
	rs1.next();
	ResultSetMetaData rsmd = rs1.getMetaData();
	assertEquals(2 , rsmd.getColumnCount());
 	//JDBC.assertSingleValueResultSet(rs1 , "1");
        // drop the temp table t2
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        // recreate the temp table t2 with 3 columns
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int) not logged");
        pStmt = prepareStatement("insert into SESSION.t2 values (?, ? , ?)");
        pStmt.setInt(1, 22);
        pStmt.setInt(2, 2);
        pStmt.setNull(3, java.sql.Types.INTEGER);
        pStmt.execute();
        rs1 = s.executeQuery("select * from SESSION.t2");
        rs1.next();
	rsmd = rs1.getMetaData();
        assertEquals(3 , rsmd.getColumnCount());
        // drop the temp table t2
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        // recreate the temp table t2 with 4 columns.
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int, c24 int not null) not logged");
        pStmt = prepareStatement("insert into SESSION.t2 values (?, ? , ? , ?)");
        // try to insert a null value into a non-null column
        try {
            pStmt.setInt(1, 22);
            pStmt.setInt(2, 2);
            pStmt.setNull(3, java.sql.Types.INTEGER);
            pStmt.setNull(4, java.sql.Types.INTEGER);
            pStmt.execute();
            fail("trying to Insert a null value into non null column:");
        } catch (SQLException e) {
            assertSQLState("23502", e);
        }
    }
    /**
     *  Temporary table create and drop Rollback behaviour
     *  Tests the basic function of temporary table with holdability
     *
     *  @throws SQLException 
     */
    public void testTempTableDDLRollbackbehaviour1() throws SQLException {
        Statement s = createStatement();
        s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows  not logged");
        JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t1") , "0");
        s.executeUpdate("drop table SESSION.t1");
        // we've removed the table, so select should fail
        assertStatementError("42X05" , s , "select * from SESSION.t1");
        rollback();
        // should still fail after rollback; we don't have savepoints and
        // we rolled back entire transaction, incl. creation of temp table
        assertStatementError("42X05" , s , "select * from SESSION.t1");
    }

    /**
     *  Temporary table create and drop Rollback behaviour
     *  Tests drop of temp table and rollback - select should still work
     *
     *  @throws SQLException 
     */
    public void testTempTableDDLRollbackbehaviour2() throws SQLException {
        Statement s = createStatement();
        s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows  not logged");
        commit();
        // drop table and rollback - select should still work
        s.executeUpdate("drop table SESSION.t1");
        assertStatementError("42X05" , s , "select * from SESSION.t1");
        rollback();
        // select should work again
        JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t1") , "0");
    }

    /**
     *  Temporary table create and drop Rollback behaviour
     *  Tests drop of temp table and commit - select should no longer work
     *
     *  @throws SQLException 
     */
    public void testTempTableDDLRollbackbehaviour3() throws SQLException {
        Statement s = createStatement();
        s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows  not logged");
        JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t1") , "0");
        commit();
        // drop table and commit - select should no longer work
        s.executeUpdate("drop table SESSION.t1");
        commit();
        assertStatementError("42X05" , s , "select * from SESSION.t1");
    }

    /**
     *  Temporary table create and drop Rollback behaviour
     *  Tests create of temp table and rollback - select should fail
     *
     *  @throws SQLException 
     */
    public void testTempTableDDLRollbackbehaviour4() throws SQLException {
        Statement s = createStatement();
        s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows  not logged");
        JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t1") , "0");
        rollback();
        assertStatementError("42X05" , s , "select * from SESSION.t1");
    }
    
    /**
     * Rollback behavior - declare temp table, rollback, select should fail.
     * 
     * @throws SQLException
     */
    public void testRollbackBehavior1() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
        PreparedStatement pStmt = prepareStatement("insert into SESSION.t2 values (?, ?)");
        pStmt.setInt(1, 21);
        pStmt.setInt(2, 1);
        pStmt.execute();
        pStmt.close();
        ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
        JDBC.assertSingleValueResultSet(rs1 , "1");
        // RollBack to the last committed point
        rollback();
        // Now select from SESSION.t2 should fail
        assertStatementError("42X05", s, "select * from SESSION.t2");
    }
    /**
     * Rollback behavior - declare temp table, commit, drop temp table,
     * rollback, select should pass
     * 
     * @throws SQLException
     */
    public void testRollbackBehavior2() throws SQLException {
        Statement s = createStatement();
        // create a temp table t2
    	assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
        PreparedStatement pStmt = prepareStatement("insert into SESSION.t2 values (?, ?)");
        pStmt.setInt(1, 21);
        pStmt.setInt(2, 1);
        pStmt.execute();
        pStmt.close();
        // commit the changes
        commit();
        // After commiting drop the temp table t2
        s.executeUpdate("DROP TABLE SESSION.t2");
        // Rollback the last Operation that is the DROP TABBE SESSION.t2 operation
        rollback();
        // now select will pass
        ResultSet rs2 = s.executeQuery("select count(*) from SESSION.t2");
        JDBC.assertSingleValueResultSet(rs2 , "0");	
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        commit();
    }
    /**
     * Rollback behavior - create temp table , commit , drop it and create
     * another temp table with samename , rollback select will select from the
     * first temp table.
     * 
     * @throws SQLException
     */
    public void testRollbackBehavior3() throws SQLException {
        Statement s = createStatement();
        // create temp table t2 with 3 columns.
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int) on commit preserve rows not logged");
        assertUpdateCount(s, 1, "insert into session.t2 values(1,1,1)");
        ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
        rs1.next();
	ResultSetMetaData rsmd = rs1.getMetaData();
        assertEquals(3 , rsmd.getColumnCount());
        // drop the temp table t2 with 3 columns.
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        assertStatementError("42X05", s, "select * from SESSION.t2");
        // create temp table with 2 columns.
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
        assertUpdateCount(s, 1, "insert into session.t2 values(1,1)");
        rs1 = s.executeQuery("select * from SESSION.t2");
        rs1.next();
	rsmd = rs1.getMetaData();
        assertEquals(2 , rsmd.getColumnCount());
        // commit point
        commit();
        // drop the temp table with 2 columns.
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        // create the temp table with 1 column.
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
        assertUpdateCount(s, 1, "insert into session.t2 values(1)");
        rs1 = s.executeQuery("select * from SESSION.t2");
	rs1.next();
	rsmd = rs1.getMetaData();
        assertEquals(1 , rsmd.getColumnCount());
        rs1.close();
        // rollback to the last committed point
        rollback();
        // Now we have the temp table with 2 columns.
        rs1 = s.executeQuery("select * from SESSION.t2");
        rs1.next();
	rsmd = rs1.getMetaData();
        assertEquals(2 , rsmd.getColumnCount());
        rs1.close();
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
    }
    /**
     * Rollback behavior for tables touched with DML
     * 
     * @throws SQLException
     */
    public void testRollbackBehavior4() throws SQLException {
        Statement s = createStatement();
        // Declare temp table t2 & t3 & t4 & t5 with preserve rows, insert data
        // and commit
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) not logged on commit preserve rows on rollback delete rows");
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) not logged on rollback delete rows on commit preserve rows");
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t5(c51 int, c52 int) on commit preserve rows not logged");
        s.executeUpdate("insert into session.t2 values(21,1)");
        s.executeUpdate("insert into session.t2 values(22,2)");
        s.executeUpdate("insert into session.t2 values(23,3)");
        s.executeUpdate("insert into session.t3 values(31,1)");
        s.executeUpdate("insert into session.t3 values(32,2)");
        s.executeUpdate("insert into session.t3 values(33,3)");
        s.executeUpdate("insert into session.t4 values(41,1)");
        s.executeUpdate("insert into session.t4 values(42,2)");
        s.executeUpdate("insert into session.t4 values(43,3)");
        s.executeUpdate("insert into session.t5 values(51,1)");
        s.executeUpdate("insert into session.t5 values(52,2)");
        s.executeUpdate("insert into session.t5 values(53,3)");
        // commit point
        commit();
        // create a temp table t6 with preserve rows , insert data.
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t6(c61 int, c62 int) on commit preserve rows not logged on rollback delete rows");
        s.executeUpdate("insert into session.t6 values(61,1)");
        s.executeUpdate("insert into session.t6 values(62,2)");
        s.executeUpdate("insert into session.t6 values(63,3)");
        // DML Operations.
        s.executeUpdate("DELETE FROM session.t2 WHERE c22> (select c52 from session.t5 where c52=2)");
        s.executeUpdate("DELETE FROM session.t3 WHERE c32>3");
        // rollback to the last commit point
        rollback();
        // After rollback t2 should have nothing.
        ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
	JDBC.assertSingleValueResultSet(rs1 , "0");
        // temp table t3 should have no rows because attempt was made to delete
        // from it even though nothing actually got deleted from it in the transaction
        rs1 = s.executeQuery("select count(*) from SESSION.t3");
	JDBC.assertSingleValueResultSet(rs1 , "0");
        // temp table t4 should have its data intact because it was not touched
        // in the transaction that got rolled back
        rs1 = s.executeQuery("select count(*) from SESSION.t4");
	JDBC.assertSingleValueResultSet(rs1 , "3");
        // temp table t5 should have its data intact because it was only used in
        // where clause and not touched in the transaction that got rolled back
        rs1 = s.executeQuery("select count(*) from SESSION.t5");
	JDBC.assertSingleValueResultSet(rs1 , "3");
        // temp table t6 got dropped as part of rollback of this transaction
        // since it was declared in this same transaction
        assertStatementError("42X05", s, "select * from SESSION.t6");
        // CleanUp
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t3");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t4");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t5");
    }
    /**
     * The Test Verifies that there is no entry in system catalogs for temporary
     * tables. while physical tables from SESSION schema have an entry.
     * 
     * @throws SQLException
     */
    public void testEntryForSESSIONTablesToSysCatalog() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
       ResultSet rs1 = s.executeQuery("select count(*) from sys.systables where CAST(tablename AS VARCHAR(128)) like 'T2'");
	JDBC.assertSingleValueResultSet(rs1 , "0");
        // drop the temp table t2
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        // create a physical table in SESSION schema
        try {
            s.executeUpdate("CREATE SCHEMA SESSION");
        } catch (SQLException e) {
            assertSQLState("X0Y68", e);
        }
        assertUpdateCount(s , 0 , "CREATE TABLE SESSION.t2(c21 int, c22 int)");
        rs1 = s.executeQuery("select count(*) from sys.systables where CAST(tablename AS VARCHAR(128)) like 'T2'");
        // System Catalog contains the physical tables from SESSION schema.
	JDBC.assertSingleValueResultSet(rs1 , "1");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        assertUpdateCount(s , 0 , "drop schema SESSION restrict");
    }
    /**
     * Verify that there is no entry in system catalogs for SESSION schmea after
     * declare table.
     * 
     * @throws SQLException
     */
    public void testEntryForSEESSIONtoSysCatalog() throws SQLException {
        Statement s = createStatement();
        try {
            s.executeUpdate("drop schema SESSION restrict");
        } catch (SQLException e) {
            assertSQLState("42Y07", e);
        }
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
        ResultSet rs1 = s.executeQuery("select count(schemaname) from sys.sysschemas where CAST(schemaname AS VARCHAR(128)) like 'SESSION'");
	JDBC.assertSingleValueResultSet(rs1 , "0");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
    }
    /**
     * DatabaseMetaData.getTables() should not return temporary tables
     * 
     * @throws SQLException
     */
    public void testDatabaseMetadata() throws SQLException {
        Statement s = createStatement();
        int count = 0;
        DatabaseMetaData databaseMetaData;
        databaseMetaData = getConnection().getMetaData();
        try {
            s.executeUpdate("CREATE SCHEMA SESSION");
        } catch (SQLException e) {
            assertSQLState("X0Y68", e);
        }
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
        assertUpdateCount(s , 0 , "CREATE TABLE SESSION.t3(c31 int, c32 int)");
        ResultSet rs1 = databaseMetaData.getTables("", null, "%", null);
        while (rs1.next()) {
            if (("T2".equals(rs1.getString(3))) &&
                    ("SESSION".equals(rs1.getString(2))))
                fail("Temporary table Found");
            count++;
        }
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t3");
        assertUpdateCount(s , 0 , "drop schema SESSION restrict");
    }
    /**
     * Test for delete where current of on temporary tables
     * 
     * @throws SQLException
     */
    public void testDeleteWhereCurrentOfOnGTT() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(21, 1)");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(22, 1)");
        ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
	JDBC.assertSingleValueResultSet(rs1 , "2");
        PreparedStatement pStmt1 = prepareStatement("select c21 from session.t2 for update");
        ResultSet rs2 = pStmt1.executeQuery();
        rs2.next();
        PreparedStatement pStmt2 = prepareStatement("delete from session.t2 where current of "+ rs2.getCursorName());
        pStmt2.executeUpdate();
        rs1 = s.executeQuery("select * from SESSION.t2");
        rs1.next();
        assertEquals(22, rs1.getInt(1));
        assertEquals(1, rs1.getInt(2));
        rs2.next();
        pStmt2.executeUpdate();
        rs1 = s.executeQuery("select count(*) from SESSION.t2");
        rs1.next();
        assertEquals(0, rs1.getInt(1));
        rs2.close();
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
    }
    /**
     * Test for update where current of on temporary tables
     * 
     * @throws SQLException
     */
    public void UpdateWhereCurrentOfOnGTT() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(21, 1)");
        assertUpdateCount(s , 1 , "insert into SESSION.t2 values(22, 1)");
        ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
	JDBC.assertSingleValueResultSet(rs1 , "2");
        PreparedStatement pStmt1 = prepareStatement("select c21 from session.t2 for update");
        ResultSet rs2 = pStmt1.executeQuery();
        rs2.next();
        PreparedStatement pStmt2 = prepareStatement("update session.t2 set c22 = 2 where current of "+ rs2.getCursorName());
        pStmt2.executeUpdate();
        rs1 = s.executeQuery("select * from SESSION.t2");
        rs1.next();
        assertEquals(21, rs1.getInt(1));
        assertEquals(2, rs1.getInt(2));
        rs1.next();
        assertEquals(22, rs1.getInt(1));
        assertEquals(1, rs1.getInt(2));
        rs2.next();
        pStmt2.executeUpdate();
        rs1 = s.executeQuery("select * from SESSION.t2");
        rs1.next();
        assertEquals(21, rs1.getInt(1));
        assertEquals(2, rs1.getInt(2));
        rs1.next();
        assertEquals(22, rs1.getInt(1));
        assertEquals(2, rs1.getInt(2));
        rs2.close();
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
    }
    /**
     * Prepared statement test - DML and rollback behavior
     * 
     * @throws SQLException
     */
    public void testDMLRollback1() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
        PreparedStatement pStmt = prepareStatement("insert into SESSION.t2 values (?, ?)");
        pStmt.setInt(1, 21);
        pStmt.setInt(2, 1);
        pStmt.execute();
        commit();
        ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
        rs1.next();
        assertEquals(21, rs1.getInt(1));
        assertEquals(1, rs1.getInt(2));
        pStmt.setInt(1, 22);
        pStmt.setInt(2, 2);
        pStmt.execute();
        rollback();
        rs1 = s.executeQuery("select count(*) from SESSION.t2");
        rs1.next();
        assertEquals(0, rs1.getInt(1));
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
    }
    /**
     * Prepared statement test - DML and rollback behavior.
     * 
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */
    public void testDMLRollback2() throws SQLException {
        Statement s = createStatement();
        assertUpdateCount(s , 0 , "DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
        assertUpdateCount(s , 1 , "INSERT INTO SESSION.t2 VALUES(21, 1)");
        commit();
        ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
        rs1.next();
        assertEquals(21, rs1.getInt(1));
        assertEquals(1, rs1.getInt(2));
        prepareStatement("insert into SESSION.t2 values (?, ?)");
        rollback();
        rs1 = s.executeQuery("select * from SESSION.t2");
        rs1.next();
        assertEquals(21, rs1.getInt(1));
        assertEquals(1, rs1.getInt(2));
        assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
    }

    /**
     * Tests that you can insert data into a GTT with a VTI as the source.
     * <p>
     * This used to fail because inserting from a VTI would trigger bulk insert,
     * but the bulk insert code path is not supported for GTT as the
     * destination of the insert.
     * <p>
     * See DERBY-5614.
     */
    public void testVtiInsertIntoGTT()
            throws SQLException {
        Statement s = createStatement();
        s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.vtitogtt(" +
                "c1 varchar(10)) not logged on commit preserve rows");
        // Use an empty VTI as the source.
        s.executeUpdate("CREATE FUNCTION emptySampleVTI() " +
                "RETURNS TABLE(v1 varchar(10))" +
                "LANGUAGE JAVA " +
                "PARAMETER STYLE DERBY_JDBC_RESULT_SET " +
                "NO SQL " +
                "EXTERNAL NAME 'org.apache.derbyTesting.functionTests." +
                "util.SampleVTI.emptySampleVTI'");
        s.executeUpdate("insert into session.vtitogtt " +
                "select * from table(emptySampleVTI()) as v");
        JDBC.assertEmpty(s.executeQuery("select * from session.vtitogtt"));
        s.executeUpdate("DROP FUNCTION emptySampleVTI");

        // Now try to actually insert some data.
        s.executeUpdate("CREATE FUNCTION sampleVTI() " +
                "RETURNS TABLE(v1 varchar(10))" +
                "LANGUAGE JAVA " +
                "PARAMETER STYLE DERBY_JDBC_RESULT_SET " +
                "NO SQL " +
                "EXTERNAL NAME 'org.apache.derbyTesting.functionTests." +
                "util.SampleVTI.oneColSampleVTI'");
        s.executeUpdate("insert into session.vtitogtt " +
                "select * from table(sampleVTI()) as v");
        JDBC.assertUnorderedResultSet(
                s.executeQuery("select * from session.vtitogtt"),
                SampleVTI.oneColSampleVTIData());
        s.executeUpdate("DROP FUNCTION sampleVTI");
    }

    /**
     * 
     * A Utility method that deletes all the SESSION schema tables before each fixture.
     *
     * @exception SQLException
     */
    public void dropSchemaTables() throws SQLException {
        Statement s = createStatement();
        // Query the meta data to avoid filling the log with lots of
        // table-not-found error messages.
        ResultSet rs = getConnection().getMetaData().getTables(
                null, "SESSION", "%", null);
        while (rs.next()) {
            try {
                s.executeUpdate("DROP TABLE " + rs.getString(2) + "." +
                        rs.getString(3));
            } catch (SQLException e) {
            }
        }
        rs.close();
    }
}