Examples with Call org.h2.command.dml.Call used on opensource projects

Example 66 with Call

use of org.h2.command.dml.Call in project h2database by h2database.

the class TestTriggersConstraints method testTriggers.

private void testTriggers() throws SQLException {
    mustNotCallTrigger = false;
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST");
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
    // CREATE TRIGGER trigger {BEFORE|AFTER}
    // {INSERT|UPDATE|DELETE|ROLLBACK} ON table
    // [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL triggeredClass
    stat.execute("CREATE TRIGGER IF NOT EXISTS INS_BEFORE " + "BEFORE INSERT ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER IF NOT EXISTS INS_BEFORE " + "BEFORE INSERT ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER INS_AFTER " + "" + "AFTER INSERT ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER UPD_BEFORE " + "BEFORE UPDATE ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER INS_AFTER_ROLLBACK " + "AFTER INSERT, ROLLBACK ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
    ResultSet rs;
    rs = stat.executeQuery("SCRIPT");
    checkRows(rs, new String[] { "CREATE FORCE TRIGGER PUBLIC.INS_BEFORE " + "BEFORE INSERT ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";", "CREATE FORCE TRIGGER PUBLIC.INS_AFTER " + "AFTER INSERT ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";", "CREATE FORCE TRIGGER PUBLIC.UPD_BEFORE " + "BEFORE UPDATE ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";", "CREATE FORCE TRIGGER PUBLIC.INS_AFTER_ROLLBACK " + "AFTER INSERT, ROLLBACK ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";" });
    while (rs.next()) {
        String sql = rs.getString(1);
        if (sql.startsWith("CREATE TRIGGER")) {
            System.out.println(sql);
        }
    }
    rs = stat.executeQuery("SELECT * FROM TEST");
    rs.next();
    assertEquals("Hello-updated", rs.getString(2));
    assertFalse(rs.next());
    stat.execute("UPDATE TEST SET NAME=NAME||'-upd'");
    rs = stat.executeQuery("SELECT * FROM TEST");
    rs.next();
    assertEquals("Hello-updated-upd-updated2", rs.getString(2));
    assertFalse(rs.next());
    mustNotCallTrigger = true;
    stat.execute("DROP TRIGGER IF EXISTS INS_BEFORE");
    stat.execute("DROP TRIGGER IF EXISTS INS_BEFORE");
    stat.execute("DROP TRIGGER IF EXISTS INS_AFTER_ROLLBACK");
    assertThrows(ErrorCode.TRIGGER_NOT_FOUND_1, stat).execute("DROP TRIGGER INS_BEFORE");
    stat.execute("DROP TRIGGER  INS_AFTER");
    stat.execute("DROP TRIGGER  UPD_BEFORE");
    stat.execute("UPDATE TEST SET NAME=NAME||'-upd-no_trigger'");
    stat.execute("INSERT INTO TEST VALUES(100, 'Insert-no_trigger')");
    conn.close();
    conn = getConnection("trigger");
    mustNotCallTrigger = false;
    conn.close();
}

Example 67 with Call

use of org.h2.command.dml.Call in project h2database by h2database.

the class TestTriggersConstraints method testDeleteInTrigger.

private void testDeleteInTrigger() throws SQLException {
    if (config.mvcc || config.mvStore) {
        return;
    }
    Connection conn;
    Statement stat;
    conn = getConnection("trigger");
    stat = conn.createStatement();
    stat.execute("create table test(id int) as select 1");
    stat.execute("create trigger test_u before update on test " + "for each row call \"" + DeleteTrigger.class.getName() + "\"");
    // this threw a NullPointerException
    assertThrows(ErrorCode.ROW_NOT_FOUND_WHEN_DELETING_1, stat).execute("update test set id = 2");
    stat.execute("drop table test");
    conn.close();
}

Example 68 with Call

use of org.h2.command.dml.Call in project h2database by h2database.

the class TestTriggersConstraints method testViewTriggerGeneratedKeys.

private void testViewTriggerGeneratedKeys() throws SQLException {
    Connection conn;
    Statement stat;
    conn = getConnection("trigger");
    stat = conn.createStatement();
    stat.execute("drop table if exists test");
    stat.execute("create table test(id int identity)");
    stat.execute("create view test_view as select * from test");
    stat.execute("create trigger test_view_insert " + "instead of insert on test_view for each row call \"" + TestViewGeneratedKeys.class.getName() + "\"");
    if (!config.memory) {
        conn.close();
        conn = getConnection("trigger");
        stat = conn.createStatement();
    }
    PreparedStatement pstat;
    pstat = conn.prepareStatement("insert into test_view values()", Statement.RETURN_GENERATED_KEYS);
    int count = pstat.executeUpdate();
    assertEquals(1, count);
    ResultSet gkRs;
    gkRs = stat.executeQuery("select scope_identity()");
    assertTrue(gkRs.next());
    assertEquals(1, gkRs.getInt(1));
    assertFalse(gkRs.next());
    ResultSet rs;
    rs = stat.executeQuery("select * from test");
    assertTrue(rs.next());
    assertFalse(rs.next());
    stat.execute("drop view test_view");
    stat.execute("drop table test");
    conn.close();
}

Example 69 with Call

use of org.h2.command.dml.Call in project h2database by h2database.

the class TestTriggersConstraints method testTriggerSelectEachRow.

private void testTriggerSelectEachRow() throws SQLException {
    Connection conn;
    Statement stat;
    conn = getConnection("trigger");
    stat = conn.createStatement();
    stat.execute("drop table if exists test");
    stat.execute("create table test(id int)");
    assertThrows(ErrorCode.TRIGGER_SELECT_AND_ROW_BASED_NOT_SUPPORTED, stat).execute("create trigger test_insert before select on test " + "for each row call \"" + TestTriggerAdapter.class.getName() + "\"");
    conn.close();
}

Example 70 with Call

use of org.h2.command.dml.Call in project h2database by h2database.

the class TestFunctions method assertCallResult.

private void assertCallResult(String expected, Statement stat, String sql) throws SQLException {
    ResultSet rs = stat.executeQuery("CALL " + sql);
    rs.next();
    String s = rs.getString(1);
    assertEquals(expected, s);
}