Examples with Savepoint java.sql.Savepoint used on opensource projects

Example 41 with Savepoint

use of java.sql.Savepoint in project derby by apache.

the class ConstraintCharacteristicsTest method testDerby6670_a.

/**
 * DERBY-6670 test cases. The violation information would be released when
 * we dropped a constraint. Unfortunately, an undo in the form of a
 * rollback to save point would not redo the row operations (so as to
 * regenerate the violation information), but just undo the conglomerate
 * delete (which still isn't physically deleted. So, we'd need the
 * violation information back too. After this fix, we do not release the
 * violation information until commit/rollback, just make it robust to
 * disappearance of constraints and their associated tables/schemas.
 *
 * @throws SQLException
 */
public void testDerby6670_a() throws SQLException {
    final Connection c = getConnection();
    Statement s = createStatement();
    String[] types = new String[] { "pk", "fk", "check" };
    for (String type : types) {
        String expectedErr = null;
        try {
            if (type.equals("pk")) {
                s.execute("create table derby6670_1(x int primary key " + "    initially deferred)");
                s.execute("insert into derby6670_1 values 1,1,1,1");
                expectedErr = LANG_DEFERRED_DUP_VIOLATION_T;
            } else if (type.equals("fk")) {
                s.execute("create table derby6670_11(x int primary key)");
                s.execute("create table derby6670_1(x int " + "  references derby6670_11 initially deferred)");
                s.execute("insert into derby6670_1 values 1");
                expectedErr = LANG_DEFERRED_FK_VIOLATION_T;
            } else if (type.equals("check")) {
                s.execute("create table derby6670_1(x int check (x < 0) " + "    initially deferred)");
                s.execute("insert into derby6670_1 values 1");
                expectedErr = LANG_DEFERRED_CHECK_VIOLATION_T;
            }
            Savepoint sp = c.setSavepoint();
            s.execute("drop table derby6670_1");
            c.rollback(sp);
            // did not prior to DERBY-6670.
            try {
                commit();
                fail();
            } catch (SQLException e) {
                assertSQLState(expectedErr, e);
            }
            // In savepoint, create table and make a violation, then roll
            // back.  Commit should work since no violation exists.
            sp = c.setSavepoint();
            if (type.equals("pk")) {
                s.execute("create table derby6670_2(x int primary key " + "    initially deferred)");
                s.execute("insert into derby6670_2 values 1,1,1,1");
            } else if (type.equals("fk")) {
                s.execute("create table derby6670_22(x int primary key)");
                s.execute("create table derby6670_2(x int " + "  references derby6670_22 initially deferred)");
                s.execute("insert into derby6670_2 values 1");
            } else if (type.equals("check")) {
                s.execute("create table derby6670_2(x int)");
                s.execute("alter table derby6670_2 add constraint c " + "  check(x > 0) deferrable initially deferred");
                s.execute("insert into derby6670_2 values -1");
            }
            c.rollback(sp);
            commit();
            // In a savepoint, add constraint with offending rows. After
            // rollback, the commit should work since no violations exist.
            s.execute("create table derby6670_3(x int not null)");
            commit();
            sp = c.setSavepoint();
            if (type.equals("pk")) {
                s.execute("alter table derby6670_3 add constraint c " + "  primary key(x) deferrable " + "  initially deferred");
                s.execute("insert into derby6670_3 values 1,1");
            } else if (type.equals("fk")) {
                s.execute("create table derby6670_33(x int primary key)");
                s.execute("alter table derby6670_3 add constraint c " + "   foreign key(x) references derby6670_33 " + "   deferrable initially deferred");
                s.execute("insert into derby6670_3 values -1");
            } else if (type.equals("check")) {
                s.execute("alter table derby6670_3 add constraint c " + "   check(x > 0) deferrable initially deferred");
                s.execute("insert into derby6670_3 values -1");
            }
            c.rollback(sp);
            commit();
            // In a savepoint, drop a constraint, then rollback. We should
            // still see violation at commit.
            s.execute("create table derby6670_4(x int not null)");
            c.commit();
            if (type.equals("pk")) {
                s.execute("alter table derby6670_4 add constraint c " + "  primary key(x) deferrable " + "  initially deferred");
                s.execute("insert into derby6670_4 values 1,1");
            } else if (type.equals("fk")) {
                s.execute("create table derby6670_44(x int primary key)");
                s.execute("alter table derby6670_4 add constraint c " + "   foreign key(x) references derby6670_44 " + "   deferrable initially deferred");
                s.execute("insert into derby6670_4 values -1");
            } else if (type.equals("check")) {
                s.execute("alter table derby6670_4 add constraint c " + "   check(x > 0) deferrable initially deferred");
                s.execute("insert into derby6670_4 values -1");
            }
            sp = c.setSavepoint();
            s.execute("alter table derby6670_4 drop constraint c");
            c.rollback(sp);
            try {
                c.commit();
                fail();
            } catch (SQLException e) {
                assertSQLState(expectedErr, e);
            }
        } finally {
            for (int i = 1; i <= 4; i++) {
                dropTable("derby6670_" + i);
            }
            c.commit();
        }
    }
}

Example 42 with Savepoint

use of java.sql.Savepoint in project derby by apache.

the class ConstraintCharacteristicsTest method testDerby6670_b.

/**
 * Similarly to what happened for dropping of constraints, when we revert
 * from deferred constraint mode to immediate, and no violations are seen,
 * we used to drop the violation information, if any. Again, this is not
 * safe iff a rollback to a savepoint re-introduces the violations. This
 * test would fail prior to DERBY-6670.
 * @throws SQLException test error
 */
public void testDerby6670_b() throws SQLException {
    final Connection c = getConnection();
    final Statement s = createStatement();
    String[] forms = new String[] { "c", "all" };
    for (String form : forms) {
        s.execute("create table t1(x int primary key, " + "    constraint c check(x > 0) initially deferred)");
        s.execute("insert into t1 values -1");
        Savepoint sp = c.setSavepoint();
        s.execute("delete from t1");
        s.execute("set constraints " + form + " immediate");
        c.rollback(sp);
        try {
            // Used to succeed because we released violation information of
            // the successful constraint when moving to immediate mode
            c.commit();
            fail();
        } catch (SQLException e) {
            assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
        }
    }
}

Example 43 with Savepoint

use of java.sql.Savepoint in project derby by apache.

the class TriggerWhenClauseTest method testDependencies.

/**
 * Test that dropping objects referenced from the WHEN clause will
 * detect that the trigger depends on the object.
 */
public void testDependencies() throws SQLException {
    Statement s = createStatement();
    s.execute("create table t1(x int, y int, z int)");
    s.execute("create table t2(x int, y int, z int)");
    Savepoint sp = getConnection().setSavepoint();
    // Dropping columns referenced via the NEW transition variable in
    // a WHEN clause should fail.
    s.execute("create trigger tr after insert on t1 " + "referencing new as new for each row " + "when (new.x < new.y) values 1");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column x restrict");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column y restrict");
    s.execute("alter table t1 drop column z restrict");
    getConnection().rollback(sp);
    // Dropping columns referenced via the OLD transition variable in
    // a WHEN clause should fail.
    s.execute("create trigger tr no cascade before delete on t1 " + "referencing old as old for each row " + "when (old.x < old.y) values 1");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column x restrict");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column y restrict");
    s.execute("alter table t1 drop column z restrict");
    getConnection().rollback(sp);
    // Dropping columns referenced via either the OLD or the NEW
    // transition variable referenced in the WHEN clause should fail.
    s.execute("create trigger tr no cascade before update on t1 " + "referencing old as old new as new for each row " + "when (old.x < new.y) values 1");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column x restrict");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column y restrict");
    s.execute("alter table t1 drop column z restrict");
    getConnection().rollback(sp);
    // Dropping columns referenced either in the WHEN clause or in the
    // triggered SQL statement should fail.
    s.execute("create trigger tr no cascade before insert on t1 " + "referencing new as new for each row " + "when (new.x < 5) values new.y");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column x restrict");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column y restrict");
    s.execute("alter table t1 drop column z restrict");
    getConnection().rollback(sp);
    // Dropping any column in a statement trigger with a NEW transition
    // table fails, even if the column is not referenced in the WHEN clause
    // or in the triggered SQL text.
    s.execute("create trigger tr after update of x on t1 " + "referencing new table as new " + "when (exists (select 1 from new where x < y)) values 1");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column x restrict");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column y restrict");
    // Z is not referenced, but the transition table depends on all columns.
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column z restrict");
    getConnection().rollback(sp);
    // Dropping any column in a statement trigger with an OLD transition
    // table fails, even if the column is not referenced in the WHEN clause
    // or in the triggered SQL text.
    s.execute("create trigger tr after delete on t1 " + "referencing old table as old " + "when (exists (select 1 from old where x < y)) values 1");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column x restrict");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column y restrict");
    // Z is not referenced, but the transition table depends on all columns.
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column z restrict");
    getConnection().rollback(sp);
    // References to columns in other ways than via transition variables
    // or transition tables should also be detected.
    s.execute("create trigger tr after delete on t1 " + "referencing old table as old " + "when (exists (select 1 from t1 where x < y)) values 1");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column x restrict");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t1 drop column y restrict");
    s.execute("alter table t1 drop column z restrict");
    getConnection().rollback(sp);
    // References to columns in another table than the trigger table
    // should prevent them from being dropped.
    s.execute("create trigger tr after insert on t1 " + "when (exists (select * from t2 where x < y)) " + "values 1");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t2 drop column x restrict");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t2 drop column y restrict");
    s.execute("alter table t2 drop column z restrict");
    // Dropping a table referenced in a WHEN clause should fail and leave
    // the trigger intact. Before DERBY-2041, DROP TABLE would succeed
    // and leave the trigger in an invalid state so that subsequent
    // INSERT statements would fail when trying to fire the trigger.
    assertStatementError(HAS_DEPENDENTS, s, "drop table t2");
    JDBC.assertSingleValueResultSet(s.executeQuery("select triggername from sys.systriggers"), "TR");
    s.executeUpdate("insert into t1 values (1, 2, 3)");
    getConnection().rollback(sp);
    // Test references to columns in both the WHEN clause and the
    // triggered SQL statement.
    s.execute("create trigger tr after update on t1 " + "when (exists (select * from t2 where x < 5)) " + "select y from t2");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t2 drop column x restrict");
    assertStatementError(HAS_DEPENDENTS, s, "alter table t2 drop column y restrict");
    s.execute("alter table t2 drop column z restrict");
    // DROP TABLE should fail because of the dependencies (didn't before
    // DERBY-2041).
    assertStatementError(HAS_DEPENDENTS, s, "drop table t2");
    JDBC.assertSingleValueResultSet(s.executeQuery("select triggername from sys.systriggers"), "TR");
    getConnection().rollback(sp);
}

Example 44 with Savepoint

use of java.sql.Savepoint in project derby by apache.

the class AutoGenJDBC30Test method testGetKeyAfterSavepointRollback.

/**
 * Inserts one row into a table with an auto-generated column while inside
 * a savepoint unit, does a rollback, then gets keys after an insert
 * into a table without an auto-generated column.
 * Old master Test 13.
 * Expected result: ResultSet has one row with a non-NULL key, and the
 * key value should be the same before and after the rollback.
 * @throws SQLException
 */
public void testGetKeyAfterSavepointRollback() throws SQLException {
    Connection conn = getConnection();
    Statement s = createStatement();
    Savepoint savepoint1 = conn.setSavepoint();
    int expected = 1;
    s.execute("insert into t11_AutoGen(c11) values(99)", Statement.RETURN_GENERATED_KEYS);
    int keyval = getKeyValue(s.getGeneratedKeys());
    assertEquals("Key value before rollback", expected, keyval);
    conn.rollback(savepoint1);
    s.execute("insert into t21_noAutoGen values(39, 'true')", Statement.RETURN_GENERATED_KEYS);
    keyval = getKeyValue(s.getGeneratedKeys());
    assertEquals("Key value after rollback", expected, keyval);
    s.close();
}

Example 45 with Savepoint

use of java.sql.Savepoint in project derby by apache.

the class DeclareGlobalTempTableJavaJDBC30Test method testSavepointRollbackbehaviour13.

/**
 *  Savepoint and Rollback behavior - 13 - 3Q
 *  tests the rollback , savepoint behaviour with prepartedStatement
 *
 *  @throws SQLException
 */
public void testSavepointRollbackbehaviour13() throws SQLException {
    Statement s = createStatement();
    s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(" + "c21 int, c22 int) not logged on commit preserve rows");
    PreparedStatement pStmtInsert = prepareStatement("insert into SESSION.t2 values (?, ?)");
    pStmtInsert.setInt(1, 21);
    pStmtInsert.setInt(2, 1);
    pStmtInsert.execute();
    pStmtInsert.setInt(1, 22);
    pStmtInsert.setInt(2, 2);
    pStmtInsert.execute();
    pStmtInsert.setInt(1, 23);
    pStmtInsert.setInt(2, 2);
    pStmtInsert.execute();
    PreparedStatement pStmtUpdate = prepareStatement("UPDATE SESSION.t2 SET c22 = 3 where c21=?");
    pStmtUpdate.setInt(1, 23);
    pStmtUpdate.execute();
    PreparedStatement pStmtDelete = prepareStatement("DELETE FROM SESSION.t2 where c21 = ?");
    pStmtDelete.setInt(1, 23);
    pStmtDelete.execute();
    // committing point
    commit();
    JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t2"), "2");
    // set the first savepoint
    Savepoint savepoint1 = getConnection().setSavepoint();
    pStmtInsert.setInt(1, 23);
    pStmtInsert.setInt(2, 2);
    pStmtInsert.execute();
    JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t2"), "3");
    // set the second savepoint
    Savepoint savepoint2 = getConnection().setSavepoint();
    pStmtUpdate.setInt(1, 23);
    pStmtUpdate.execute();
    JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t2"), "3");
    // rollback savepoint2 and should loose all the data from t2");
    getConnection().rollback(savepoint2);
    JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t2"), "0");
    // Create savepoint3, insert some rows into t2 using prepared statement
    Savepoint savepoint3 = getConnection().setSavepoint();
    pStmtInsert.setInt(1, 21);
    pStmtInsert.setInt(2, 1);
    pStmtInsert.execute();
    pStmtInsert.setInt(1, 22);
    pStmtInsert.setInt(2, 2);
    pStmtInsert.execute();
    pStmtInsert.setInt(1, 23);
    pStmtInsert.setInt(2, 333);
    pStmtInsert.execute();
    JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t2"), "3");
    // Create savepoint4 and update row inserted in savepoint3 using prepared
    // statement and inspect the data in t2
    Savepoint savepoint4 = getConnection().setSavepoint();
    pStmtUpdate.setInt(1, 23);
    pStmtUpdate.execute();
    JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t2"), "3");
    // release savepoint4
    getConnection().releaseSavepoint(savepoint4);
    JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t2"), "3");
    pStmtDelete.setInt(1, 23);
    pStmtDelete.execute();
    // Commit transaction and should see data in t2
    commit();
    JDBC.assertSingleValueResultSet(s.executeQuery("select count(*) from SESSION.t2"), "2");
    s.executeUpdate("drop table SESSION.t2");
}