Package java.sql

Examples of java.sql.Statement


        conn.close();
    }

    private void testSource() throws SQLException {
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        ResultSet rs;
        stat.execute("create force alias sayHi as 'String test(String name) {\n" +
                "return \"Hello \" + name;\n}'");
        rs = stat.executeQuery("SELECT ALIAS_NAME FROM INFORMATION_SCHEMA.FUNCTION_ALIASES");
        rs.next();
        assertEquals("SAY" + "HI", rs.getString(1));
        rs = stat.executeQuery("call sayHi('Joe')");
        rs.next();
        assertEquals("Hello Joe", rs.getString(1));
        if (!config.memory) {
            conn.close();
            conn = getConnection("functions");
            stat = conn.createStatement();
            rs = stat.executeQuery("call sayHi('Joe')");
            rs.next();
            assertEquals("Hello Joe", rs.getString(1));
        }
        stat.execute("drop alias sayHi");
        conn.close();
    }
View Full Code Here


        }
        deleteDb("openClose");
        String url = getURL("openClose", true);
        org.h2.Driver.load();
        Connection conn = DriverManager.getConnection(url, "sa", "abc def");
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST(C CLOB)");
        stat.execute("INSERT INTO TEST VALUES(SPACE(10000))");
        stat.execute("BACKUP TO '" + getBaseDir() + "/test.zip'");
        conn.close();
        deleteDb("openClose");
        Restore.execute(getBaseDir() + "/test.zip", getBaseDir(), null, true);
        conn = DriverManager.getConnection(url, "sa", "abc def");
        stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
        rs.next();
        assertEquals(10000, rs.getString(1).length());
        assertFalse(rs.next());
        conn.close();
        IOUtils.delete(getBaseDir() + "/test.zip");
View Full Code Here

        deleteDb("openClose");
        String user = getUser(), password = getPassword();
        String url = getURL("openClose;DATABASE_EVENT_LISTENER='" + TestOpenClose.class.getName()
                + "'", true);
        Connection conn = DriverManager.getConnection(url, user, password);
        Statement stat = conn.createStatement();
        try {
            stat.execute("CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR)");
            stat.execute("SET MAX_MEMORY_UNDO 100000");
            stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
            stat.execute("INSERT INTO TEST SELECT X, X || ' Data' FROM SYSTEM_RANGE(1, 1000)");
        } catch (SQLException e) {
            // ok
        }
        stat.close();
        conn.close();
        conn = DriverManager.getConnection(url, user, password);
        stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("SELECT * FROM DUAL");
        if (rs.next()) {
            rs.getString(1);
        }
        rs.close();
        stat.close();
        conn.close();
        conn = DriverManager.getConnection(url, user, password);
        stat = conn.createStatement();
        // stat.execute("SET DB_CLOSE_DELAY 0");
        stat.executeUpdate("SHUTDOWN");
        stat.close();
        conn.close();
    }
View Full Code Here

        deleteDb("compatibility");
    }

    private void testCaseSensitiveIdentifiers() throws SQLException {
        Connection c = getConnection("compatibility;DATABASE_TO_UPPER=FALSE");
        Statement stat = c.createStatement();
        stat.execute("create table test(id int primary key, name varchar) as select 1, 'hello'");
        ResultSet rs;
        rs = stat.executeQuery("select * from test");
        assertEquals("id", rs.getMetaData().getColumnLabel(1));
        assertEquals("name", rs.getMetaData().getColumnLabel(2));
        assertThrows(ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1, stat).
                execute("select * from TEST");
        stat.execute("select COUNT(*), count(*), Count(*), Sum(id) from test");
        stat.execute("select LENGTH(name), length(name), Length(name) from test");
        stat.execute("drop table test");
        c.close();
    }
View Full Code Here

    private void testDomain() throws SQLException {
        if (config.memory) {
            return;
        }
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int primary key) as select 1");
        assertThrows(ErrorCode.USER_DATA_TYPE_ALREADY_EXISTS_1, stat).
                execute("create domain int as varchar");
        conn.close();
        conn = getConnection("compatibility");
        stat = conn.createStatement();
        stat.execute("insert into test values(2)");
        stat.execute("drop table test");
    }
View Full Code Here

        stat.execute("insert into test values(2)");
        stat.execute("drop table test");
    }

    private void testColumnAlias() throws SQLException {
        Statement stat = conn.createStatement();
        String[] modes = { "PostgreSQL", "MySQL", "HSQLDB", "MSSQLServer", "Derby", "Oracle", "Regular" };
        String columnAlias;
        columnAlias = "MySQL,Regular";
        stat.execute("CREATE TABLE TEST(ID INT)");
        for (String mode : modes) {
            stat.execute("SET MODE " + mode);
            ResultSet rs = stat.executeQuery("SELECT ID I FROM TEST");
            ResultSetMetaData meta = rs.getMetaData();
            String columnName = meta.getColumnName(1);
            String tableName = meta.getTableName(1);
            if ("ID".equals(columnName) && "TEST".equals(tableName)) {
                assertTrue(mode + " mode should not support columnAlias", columnAlias.indexOf(mode) >= 0);
            } else if ("I".equals(columnName) && tableName == null) {
                assertTrue(mode + " mode should support columnAlias", columnAlias.indexOf(mode) < 0);
            } else {
                fail();
            }
        }
        stat.execute("DROP TABLE TEST");
    }
View Full Code Here

        // getConnection("speed;ASSERT=0;MAX_MEMORY_ROWS=1000000;MAX_LOG_SIZE=1000");

        // Class.forName("org.hsqldb.jdbcDriver");
        // conn = DriverManager.getConnection("jdbc:hsqldb:speed");

        Statement stat = conn.createStatement();
        stat.execute("DROP TABLE IF EXISTS TEST");
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
        int len = getSize(1, 10000);
        for (int i = 0; i < len; i++) {
            stat.execute("SELECT ID, NAME FROM TEST ORDER BY ID");
        }

        // drop table if exists test;
        // CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
        // @LOOP 100000 INSERT INTO TEST VALUES(?, 'Hello');
        // @LOOP 100000 SELECT * FROM TEST WHERE ID = ?;

        // stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME
        // VARCHAR(255))");
        // for(int i=0; i<1000; i++) {
        // stat.execute("INSERT INTO TEST VALUES("+i+", 'Hello')");
        // }
        // stat.execute("CREATE TABLE TEST_A(ID INT PRIMARY KEY, NAME
        // VARCHAR(255))");
        // stat.execute("INSERT INTO TEST_A VALUES(0, 'Hello')");
        long time = System.currentTimeMillis();
        // for(int i=1; i<8000; i*=2) {
        // stat.execute("INSERT INTO TEST_A SELECT ID+"+i+", NAME FROM TEST_A");
        //
        // // stat.execute("INSERT INTO TEST_A VALUES("+i+", 'Hello')");
        // }
        // for(int i=0; i<4; i++) {
        // ResultSet rs = stat.executeQuery("SELECT * FROM TEST_A");
        // while(rs.next()) {
        // rs.getInt(1);
        // rs.getString(2);
        // }
        // }
        // System.out.println(System.currentTimeMillis()-time);

        //
        // stat.execute("CREATE TABLE TEST_B(ID INT PRIMARY KEY, NAME
        // VARCHAR(255))");
        // for(int i=0; i<80000; i++) {
        // stat.execute("INSERT INTO TEST_B VALUES("+i+", 'Hello')");
        // }

        // conn.close();
        // System.exit(0);
        // int testParser;
        // java -Xrunhprof:cpu=samples,depth=8 -cp . org.h2.test.TestAll
        //
        // stat.execute("CREATE TABLE TEST(ID INT)");
        // stat.execute("INSERT INTO TEST VALUES(1)");
        // ResultSet rs = stat.executeQuery("SELECT ID OTHER_ID FROM TEST");
        // rs.next();
        // rs.getString("ID");
        // stat.execute("DROP TABLE TEST");

        // long time = System.currentTimeMillis();

        stat.execute("DROP TABLE IF EXISTS TEST");
        stat.execute("CREATE CACHED TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
        PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");

        int max = getSize(1, 10000);
        for (int i = 0; i < max; i++) {
            prep.setInt(1, i);
View Full Code Here

    @AfterClass public static void tearDown() throws SQLException {
      connection.close();
    }

    @Test public void testSetTableStats() throws Exception {
      Statement s = connection.createStatement();
      ResultSet rs = s.executeQuery("select cardinality from tables where name = 'PARTSSUPPLIER.PARTS'");
      rs.next();
      assertEquals(16, rs.getInt(1));
      s.execute("call setTableStats(tableName=>'partssupplier.partssupplier.parts', cardinality=>32)");
      rs = s.executeQuery("select cardinality from tables where name = 'PARTSSUPPLIER.PARTS'");
      rs.next();
      assertEquals(32, rs.getInt(1));
    }
View Full Code Here

      rs.next();
      assertEquals(32, rs.getInt(1));
    }
   
    @Test public void testSetColumnStats() throws Exception {
      Statement s = connection.createStatement();
      ResultSet rs = s.executeQuery("select MinRange, MaxRange, DistinctCount, NullCount from columns where name = 'PART_ID'");
      rs.next();
      assertEquals(null, rs.getString(1));
      assertEquals(null, rs.getString(2));
      assertEquals(-1, rs.getInt(3));
      assertEquals(-1, rs.getInt(4));
      s.execute("call setColumnStats(tableName=>'partssupplier.partssupplier.parts', columnName=>'PART_ID', max=>32, nullcount=>0)");
      rs = s.executeQuery("select MinRange, MaxRange, DistinctCount, NullCount from columns where name = 'PART_ID'");
      rs.next();
      assertEquals(null, rs.getString(1));
      assertEquals("32", rs.getString(2));
      assertEquals(-1, rs.getInt(3));
      assertEquals(0, rs.getInt(4));
View Full Code Here

      assertEquals(-1, rs.getInt(3));
      assertEquals(0, rs.getInt(4));
    }
   
    @Test(expected=SQLException.class) public void testSetColumnStatsInvalidColumn() throws Exception {
      Statement s = connection.createStatement();
      s.execute("call setColumnStats(tableName=>'partssupplier.partssupplier.parts', columnName=>'foo', max=>32, nullcount=>0)");
    }
View Full Code Here

TOP

Related Classes of java.sql.Statement

Copyright © 2015 www.massapicom. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.