Package java.sql

Examples of java.sql.Statement


        deleteDb("multiConn");
        Connection c1 = getConnection("multiConn");
        Connection c2 = getConnection("multiConn");
        c1.setAutoCommit(false);
        c2.setAutoCommit(false);
        Statement s1 = c1.createStatement();
        s1.execute("DROP TABLE IF EXISTS MULTI_A");
        s1.execute("CREATE TABLE MULTI_A(ID INT, NAME VARCHAR(255))");
        s1.execute("INSERT INTO MULTI_A VALUES(0, '0-insert-A')");
        Statement s2 = c2.createStatement();
        s1.execute("DROP TABLE IF EXISTS MULTI_B");
        s1.execute("CREATE TABLE MULTI_B(ID INT, NAME VARCHAR(255))");
        s2.execute("INSERT INTO MULTI_B VALUES(0, '1-insert-B')");
        c1.commit();
        c2.rollback();
        s1.execute("INSERT INTO MULTI_A VALUES(1, '0-insert-C')");
        s2.execute("INSERT INTO MULTI_B VALUES(1, '1-insert-D')");
        c1.rollback();
        c2.commit();
        c1.close();
        c2.close();
View Full Code Here


    assertTrue(rs.next());
    assertEquals(0, rs.getInt(1)); //no rows updated
  }
 
  @Test(expected=TeiidSQLException.class) public void testSystemMatViewsWithRowRefreshNoPk() throws Exception {
    Statement s = conn.createStatement();
    s.executeQuery("select * from (call refreshMatView('TEST.MATVIEW', false)) p");
    //prior to load refresh of a single row returns -1
    s.executeQuery("select * from (call refreshMatViewRow('TEST.MATVIEW', 0)) p");
  }
View Full Code Here

    private void testOneConnectionPrepare() throws SQLException {
        deleteDb("manyObjects");
        SysProperties.runFinalize = false;
        Connection conn = getConnection("manyObjects");
        PreparedStatement prep;
        Statement stat;
        int size = getSize(10, 1000);
        for (int i = 0; i < size; i++) {
            conn.getMetaData();
        }
        for (int i = 0; i < size; i++) {
            conn.createStatement();
        }
        stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
        stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
        for (int i = 0; i < size; i++) {
            stat.executeQuery("SELECT * FROM TEST WHERE 1=0");
        }
        for (int i = 0; i < size; i++) {
            stat.executeQuery("SELECT * FROM TEST");
        }
        for (int i = 0; i < size; i++) {
            conn.prepareStatement("SELECT * FROM TEST");
        }
        prep = conn.prepareStatement("SELECT * FROM TEST WHERE 1=0");
View Full Code Here

            assertEquals(3, call.getInt(1));
        }
    }

    private void testPrepare(Connection conn) throws SQLException {
        Statement stat = conn.createStatement();
        CallableStatement call;
        ResultSet rs;
        stat.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR)");
        call = conn.prepareCall("INSERT INTO TEST VALUES(?, ?)");
        call.setInt(1, 1);
        call.setString(2, "Hello");
        call.execute();
        call = conn.prepareCall("SELECT * FROM TEST",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        rs = call.executeQuery();
        rs.next();
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());
        call = conn.prepareCall("SELECT * FROM TEST",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY,
                ResultSet.HOLD_CURSORS_OVER_COMMIT);
        rs = call.executeQuery();
        rs.next();
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());
        stat.execute("CREATE ALIAS testCall FOR \"" + getClass().getName() + ".testCall\"");
        call = conn.prepareCall("{CALL testCall(?,?,?)}");
        call.setInt("A", 100);
        call.setString(2, "abc");
        long t = System.currentTimeMillis();
        call.setTimestamp("C", new Timestamp(t));
View Full Code Here

        testLogMode(2);
    }

    private void testLogMode(int logMode) throws SQLException {
        Connection conn;
        Statement stat;
        ResultSet rs;
        conn = getConnection("transaction");
        stat = conn.createStatement();
        stat.execute("create table test(id int primary key) as select 1");
        stat.execute("set write_delay 0");
        stat.execute("set log " + logMode);
        rs = stat.executeQuery("select value from information_schema.settings where name = 'LOG'");
        rs.next();
        assertEquals(logMode, rs.getInt(1));
        stat.execute("insert into test values(2)");
        stat.execute("shutdown immediately");
        try {
            conn.close();
        } catch (SQLException e) {
            // expected
        }
        conn = getConnection("transaction");
        stat = conn.createStatement();
        rs = stat.executeQuery("select * from test order by id");
        assertTrue(rs.next());
        if (logMode != 0) {
            assertTrue(rs.next());
        }
        assertFalse(rs.next());
        stat.execute("drop table test");
        conn.close();
    }
View Full Code Here

    private void testConcurrentSelectForUpdate() throws SQLException {
        deleteDb("transaction");
        Connection conn = getConnection("transaction");
        conn.setAutoCommit(false);
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int primary key, name varchar)");
        stat.execute("insert into test values(1, 'Hello'), (2, 'World')");
        conn.commit();
        PreparedStatement prep = conn.prepareStatement("select * from test for update");
        prep.execute();
        Connection conn2 = getConnection("transaction");
        conn2.setAutoCommit(false);
View Full Code Here

    private void testForUpdate() throws SQLException {
        deleteDb("transaction");
        Connection conn = getConnection("transaction");
        conn.setAutoCommit(false);
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int primary key, name varchar)");
        stat.execute("insert into test values(1, 'Hello'), (2, 'World')");
        conn.commit();
        PreparedStatement prep = conn.prepareStatement("select * from test where id = 1 for update");
        prep.execute();
        // releases the lock
        conn.commit();
        prep.execute();
        Connection conn2 = getConnection("transaction");
        conn2.setAutoCommit(false);
        Statement stat2 = conn2.createStatement();
        if (config.mvcc && Constants.VERSION_MINOR >= 3) {
            stat2.execute("update test set name = 'Welt' where id = 2");
        }
        assertThrows(ErrorCode.LOCK_TIMEOUT_1, stat2).
                execute("update test set name = 'Hallo' where id = 1");
        conn2.close();
        conn.close();
View Full Code Here

    }

    private void testRollback() throws SQLException {
        deleteDb("transaction");
        Connection conn = getConnection("transaction");
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int)");
        stat.execute("create index idx_id on test(id)");
        stat.execute("insert into test values(1), (1), (1)");
        if (!config.memory) {
            conn.close();
            conn = getConnection("transaction");
            stat = conn.createStatement();
        }
        conn.setAutoCommit(false);
        stat.execute("delete from test");
        conn.rollback();
        ResultSet rs;
        rs = stat.executeQuery("select * from test");
        assertResultRowCount(3, rs);
        rs = stat.executeQuery("select * from test where id = 1");
        assertResultRowCount(3, rs);
        conn.close();

        conn = getConnection("transaction");
        stat = conn.createStatement();
        stat.execute("create table master(id int) as select 1");
        stat.execute("create table child1(id int references master(id) on delete cascade)");
        stat.execute("insert into child1 values(1), (1), (1)");
        stat.execute("create table child2(id int references master(id)) as select 1");
        if (!config.memory) {
            conn.close();
            conn = getConnection("transaction");
        }
        stat = conn.createStatement();
        assertThrows(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_CHILD_EXISTS_1, stat).
                execute("delete from master");
        conn.rollback();
        rs = stat.executeQuery("select * from master where id=1");
        assertResultRowCount(1, rs);
        rs = stat.executeQuery("select * from child1");
        assertResultRowCount(3, rs);
        rs = stat.executeQuery("select * from child1 where id=1");
        assertResultRowCount(3, rs);
        conn.close();
    }
View Full Code Here

    }

    private void testRollback2() throws SQLException {
        deleteDb("transaction");
        Connection conn = getConnection("transaction");
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int)");
        stat.execute("create index idx_id on test(id)");
        stat.execute("insert into test values(1), (1)");
        if (!config.memory) {
            conn.close();
            conn = getConnection("transaction");
            stat = conn.createStatement();
        }
        conn.setAutoCommit(false);
        stat.execute("delete from test");
        conn.rollback();
        ResultSet rs;
        rs = stat.executeQuery("select * from test where id = 1");
        assertResultRowCount(2, rs);
        conn.close();

        conn = getConnection("transaction");
        stat = conn.createStatement();
        stat.execute("create table master(id int) as select 1");
        stat.execute("create table child1(id int references master(id) on delete cascade)");
        stat.execute("insert into child1 values(1), (1)");
        stat.execute("create table child2(id int references master(id)) as select 1");
        if (!config.memory) {
            conn.close();
            conn = getConnection("transaction");
        }
        stat = conn.createStatement();
        assertThrows(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_CHILD_EXISTS_1, stat).
                execute("delete from master");
        rs = stat.executeQuery("select * from master where id=1");
        assertResultRowCount(1, rs);
        rs = stat.executeQuery("select * from child1 where id=1");
        assertResultRowCount(2, rs);
        conn.close();
    }
View Full Code Here

    private void testSetTransaction() throws SQLException {
        deleteDb("transaction");
        Connection conn = getConnection("transaction");
        conn.setAutoCommit(false);
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int)");
        stat.execute("insert into test values(1)");
        stat.execute("set @x = 1");
        conn.commit();
        assertSingleValue(stat, "select id from test", 1);
        assertSingleValue(stat, "call @x", 1);

        stat.execute("update test set id=2");
        stat.execute("set @x = 2");
        conn.rollback();
        assertSingleValue(stat, "select id from test", 1);
        assertSingleValue(stat, "call @x", 2);

        conn.close();
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.