Examples of java.sql.Savepoint

java.sql.Savepoint
A Savepoint is an instant during the current transaction that can be utilised by a Rollback from the Connection.rollback method. Rolling back to a particular Savepoint means that all changes that occurred after that Savepoint are removed.


    public void release(Handle handle, String name)
    {
        final Connection conn = handle.getConnection();
        try {
            final Savepoint savepoint = localStuff.get(handle).getCheckpoints().remove(name);
            if (savepoint == null) {
                throw new TransactionException(String.format("Attempt to rollback to non-existant savepoint, '%s'",
                                                             name));
            }
            conn.releaseSavepoint(savepoint);

View Full Code Here

     */
    public void rollback(Handle handle, String name)
    {
        final Connection conn = handle.getConnection();
        try {
            final Savepoint savepoint = localStuff.get(handle).getCheckpoints().remove(name);
            if (savepoint == null) {
                throw new TransactionException(String.format("Attempt to rollback to non-existant savepoint, '%s'",
                                                             name));
            }
            conn.rollback(savepoint);

View Full Code Here

  public void run(DataSource dataSource, ConnCallback callback) {
    Transaction t = Trans.get();
    // 有事务
    if (null != t) {
      Connection conn = null;
      Savepoint sp = null;
      try {
        conn = t.getConnection(dataSource);
        sp = conn.setSavepoint();
        callback.invoke(conn);

View Full Code Here

        //make sure that if it is a named savepoint then supplied name length is not > 128
        if (userSuppliedSavepointName && (name.length() > Limits.MAX_IDENTIFIER_LENGTH))
          throw newSQLException(SQLState.LANG_IDENTIFIER_TOO_LONG, name, String.valueOf(Limits.MAX_IDENTIFIER_LENGTH));
        if (userSuppliedSavepointName && name.startsWith("SYS")) //to enforce DB2 restriction which is savepoint name can't start with SYS
          throw newSQLException(SQLState.INVALID_SCHEMA_SYS, "SYS");
        Savepoint savePt = new EmbedSavepoint30(this, name);
        return savePt;
      } catch (StandardException e) {
        throw handleException(e);
      } finally {
          restoreContextStack();

View Full Code Here

    {
      System.out.println("TEST3A : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and declare temp table t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      PreparedStatement pStmt = con.prepareStatement("insert into SESSION.t1 values (?, ?)");
      pStmt.setInt(1, 11);
      pStmt.setInt(2, 1);
      pStmt.execute();
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  Create savepoint 2, drop temp table t1, rollback savepoint 2");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("drop table SESSION.t1");
      try {
        rs1 = s.executeQuery("select * from SESSION.t1");
      } catch (Throwable e)
      {
        System.out.println("Expected message: "+ e.getMessage());
      }
      con.rollback(savepoint2);


      System.out.println("  select should pass, rollback savepoint 1, select should fail");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      con.rollback(savepoint1);
      rs1 = s.executeQuery("select * from SESSION.t1");


      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3A FAILED");
    } catch (Throwable e)
    {
      System.out.println("Expected message: "+ e.getMessage());
      con.commit();
      System.out.println("TEST3A PASSED");
    }


    try
    {
      System.out.println("TEST3B : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and declare temp table t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");


      System.out.println("  Create savepoint2 and declare temp table t2");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");


      System.out.println("  Release savepoint 1 and select from temp table t1 & t2");
      con.releaseSavepoint(savepoint1);
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Drop temp table t2(explicit drop), rollback transaction(implicit drop of t1)");
      s.executeUpdate("drop table SESSION.t2");
      con.rollback();


      System.out.println("  Select from temp table t1 and t2 will fail");
      try {
        rs1 = s.executeQuery("select * from SESSION.t1");
      } catch (Throwable e)
      {
        System.out.println("Expected message: "+ e.getMessage());
      }
      try {
        rs1 = s.executeQuery("select * from SESSION.t2");
      } catch (Throwable e)
      {
        System.out.println("Expected message: "+ e.getMessage());
      }
      con.commit();
      System.out.println("TEST3B PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3B FAILED");
    }


    try
    {
      System.out.println("TEST3C : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and declare temp table t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");


      System.out.println("  Create savepoint2 and declare temp table t2");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");


      System.out.println("  Release savepoint 1 and select from temp table t1 and t2");
      con.releaseSavepoint(savepoint1);
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint3 and rollback savepoint3(should not touch t1 and t2)");
      Savepoint savepoint3 = con.setSavepoint();
      con.rollback(savepoint3);


      System.out.println("  select from temp tables t1 and t2 should pass");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback transaction and select from temp tables t1 and t2 should fail");
      con.rollback();
      try {
        rs1 = s.executeQuery("select * from SESSION.t1");
      } catch (Throwable e)
      {
        System.out.println("Expected message: "+ e.getMessage());
      }
      try {
        rs1 = s.executeQuery("select * from SESSION.t2");
      } catch (Throwable e)
      {
        System.out.println("Expected message: "+ e.getMessage());
      }


      con.commit();
      System.out.println("TEST3C PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3C FAILED");
    }


    try
    {
      System.out.println("TEST3D : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and declare temp table t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");


      System.out.println("  Create savepoint2 and drop temp table t1");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("drop table SESSION.t1");


      System.out.println("  Rollback savepoint2 and select temp table t1");
      con.rollback(savepoint2);
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println(" Commit transaction and select temp table t1");
      con.commit();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      con.commit();
      System.out.println("TEST3D PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3D FAILED");
    }


    try
    {
      System.out.println("TEST3E : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and declare temp table t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");


      System.out.println("  Create savepoint2 and drop temp table t1");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("drop table SESSION.t1");


      System.out.println("  Rollback savepoint 1 and select from temp table t1 should fail");
      con.rollback(savepoint1);
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");


      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3E FAILED");
    } catch (Throwable e)
    {
      System.out.println("Expected message: "+ e.getMessage());
      con.commit();
      System.out.println("TEST3E PASSED");
    }


    try
    {
      System.out.println("TEST3F : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 and drop temp table t1");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      s.executeUpdate("drop table SESSION.t1");
      System.out.println("  rollback, select on t1 should fail");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");


      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3F FAILED");
    } catch (Throwable e)
    {
      System.out.println("Expected message: "+ e.getMessage());
      con.commit();
      System.out.println("TEST3F PASSED");
    }


    try
    {
      System.out.println("TEST3G : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      con.commit();
      System.out.println(" In the transaction:");
      System.out.println("  drop temp table t1 and rollback, select on t1 should pass");
      s.executeUpdate("drop table SESSION.t1");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      con.commit();
      System.out.println("TEST3G PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3G FAILED");
    }


    try
    {
      System.out.println("TEST3H : Savepoint and commit behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      con.commit();
      System.out.println(" In the transaction:");
      System.out.println("  drop temp table t1 and commit, select on t1 should fail");
      s.executeUpdate("drop table SESSION.t1");
      con.commit();
      rs1 = s.executeQuery("select * from SESSION.t1");


      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3H FAILED");
    } catch (Throwable e)
    {
      System.out.println("Expected message: "+ e.getMessage());
      con.commit();
      System.out.println("TEST3H PASSED");
    }


    try
    {
      System.out.println("TEST3I : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 and rollback");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");


      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3I FAILED");
    } catch (Throwable e)
    {
      System.out.println("Expected message: "+ e.getMessage());
      con.commit();
      System.out.println("TEST3I PASSED");
    }


    try
    {
      System.out.println("TEST3J : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 with 2 columns and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      s.executeUpdate("insert into SESSION.t1 values(11, 11)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      con.commit();
      System.out.println("  Create savepoint1 and drop temp table t1 with 2 columns");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("drop table SESSION.t1");
      System.out.println("  declare temp table t1 but this time with 3 columns");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int, c13 int not null) on commit preserve rows not logged");
      s.executeUpdate("insert into SESSION.t1 values(22, 22, 22)");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      System.out.println("  Create savepoint2 and drop temp table t1 with 3 columns");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("drop table SESSION.t1");
      con.rollback();
      System.out.println("  select from temp table t1 here should have 2 columns");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      s.executeUpdate("drop table SESSION.t1");


      con.commit();
      System.out.println("TEST3J PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3J FAILED");
    }


    try
    {
      System.out.println("TEST3K : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 & t2, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into session.t2 values(21, 1)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      con.commit();


      System.out.println(" In the next transaction, insert couple more rows in t1 & t2 and ");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      s.executeUpdate("insert into SESSION.t2 values(22, 2)");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint1 and update some rows in t1 and inspect the data");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  update t2 with where clause such that no rows get modified in t2 and inspect the data");
      s.executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>2");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback to savepoint1 and we should loose all the rows in t1");
      con.rollback(savepoint1);
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      System.out.println("  temp table t2 should also have no rows because attempt was made to modify it (even though nothing actually got modified in t2 in the savepoint)");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Commit the transaction and should see no data in t1 and t2");
      con.commit();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      s.executeUpdate("drop table SESSION.t2");
      con.commit();
      System.out.println("TEST3K PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3K FAILED");
    }


    try
    {
      System.out.println("TEST3L : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 & t2, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into session.t2 values(21, 1)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      con.commit();


      System.out.println(" In the next transaction, insert couple more rows in t1 & t2 and ");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      s.executeUpdate("insert into session.t2 values(22, 2)");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint1 and update some rows in t1 and inspect the data");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  update t2 with where clause such that no rows get modified in t2 and inspect the data");
      s.executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>3");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback to savepoint1 and we should loose all the rows in t1");
      con.rollback(savepoint1);
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      System.out.println("  temp table t2 should also have no rows because attempt was made to modfiy it (even though nothing actually got modified in t2 in the savepoint)");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback the transaction and should see no data in t1 and t2");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      s.executeUpdate("drop table SESSION.t2");
      con.commit();
      System.out.println("TEST3L PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3L FAILED");
    }


    try
    {
      System.out.println("TEST3M : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 & t2 & t3 & t4, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into SESSION.t2 values(21, 1)");
      s.executeUpdate("insert into SESSION.t3 values(31, 1)");
      s.executeUpdate("insert into SESSION.t4 values(41, 1)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t4");
      dumpRS(rs1);
      con.commit();


      System.out.println(" In the next transaction, insert couple more rows in t1 & t2 & t3 and ");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      s.executeUpdate("insert into session.t2 values(22, 2)");
      s.executeUpdate("insert into session.t3 values(32, 2)");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);


      System.out.println("  Create savepoint1 and delete some rows from t1 and inspect the data in t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  Create savepoint2 and delete some rows from t2 this time and inspect the data in t2");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Release savepoint2 and now savepoint1 should keep track of changes made to t1 and t2, inspect the data in t1 & t2");
      con.releaseSavepoint(savepoint2);
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback savepoint1 and should see no data in t1 and t2, inspect the data");
      con.rollback(savepoint1);
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Should see data in t3 since it was not touched in the savepoint that was rolled back");
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);


      System.out.println("  Rollback the transaction and should see no data in t1 and t2 and t3");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);


      System.out.println("  Should see data in t4 since it was not touched in the transaction that was rolled back");
      rs1 = s.executeQuery("select * from SESSION.t4");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      s.executeUpdate("drop table SESSION.t2");
      s.executeUpdate("drop table SESSION.t3");
      s.executeUpdate("drop table SESSION.t4");
      con.commit();
      System.out.println("TEST3M PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3M FAILED");
    }


    try
    {
      System.out.println("TEST3N : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 & t2 & t3 & t4, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      s.executeUpdate("insert into SESSION.t2 values(21, 1)");
      s.executeUpdate("insert into SESSION.t2 values(22, 2)");
      s.executeUpdate("insert into SESSION.t3 values(31, 1)");
      s.executeUpdate("insert into SESSION.t4 values(41, 1)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t4");
      dumpRS(rs1);
      con.commit();


      System.out.println(" In the next transaction, insert couple more rows in t3 ");
      s.executeUpdate("insert into SESSION.t3 values(31, 2)");
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);


      System.out.println("  Create savepoint1 and delete some rows from t1 and inspect the data in t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  delete from t2 with where clause such that no rows are deleted from t2 and inspect the data in t2");
      s.executeUpdate("DELETE FROM SESSION.t2 where c22>3");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint2 and delete some rows from t2 this time and inspect the data in t2");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback the transaction and should see no data in t1 and t2 and t3");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);


      System.out.println("  Should see data in t4 since it was not touched in the transaction that was rolled back");
      rs1 = s.executeQuery("select * from SESSION.t4");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      s.executeUpdate("drop table SESSION.t2");
      s.executeUpdate("drop table SESSION.t3");
      s.executeUpdate("drop table SESSION.t4");
      con.commit();
      System.out.println("TEST3N PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3N FAILED");
    }


    try
    {
      System.out.println("TEST3O : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 & t2, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into SESSION.t2 values(21, 1)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      con.commit();


      System.out.println(" In the next transaction, insert couple more rows in t1 ");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  Create savepoint1 and insert one row in t2 and inspect the data in t2");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("insert into SESSION.t2 values(22, 2)");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback savepoint1 and should see no data in t2 but t1 should have data, inspect the data");
      con.rollback(savepoint1);
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Commit the transaction and should see no data in t2 but t1 should have data");
      con.commit();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      s.executeUpdate("drop table SESSION.t2");
      con.commit();
      System.out.println("TEST3O PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3O FAILED");
    }


    try
    {
      System.out.println("TEST3P : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      con.commit();


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and insert some rows into t1 and inspect the data in t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("insert into SESSION.t1 values(13, 3)");


      System.out.println("  Release savepoint1 and now transaction should keep track of changes made to t1, inspect the data in t1");
      con.releaseSavepoint(savepoint1);
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  Rollback the transaction and should still see no data in t1");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      con.commit();
      System.out.println("TEST3P PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3P FAILED");
    }


    try
    {
      System.out.println("TEST3Q : Prepared statement test - DML and rollback behavior");
      System.out.println(" In the transaction:");
      System.out.println("  Declare temp table t2, insert / update / delete data using various prepared statements and commit");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
      PreparedStatement pStmtInsert = con.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 = con.prepareStatement("UPDATE SESSION.t2 SET c22 = 3 where c21=?");
      pStmtUpdate.setInt(1, 23);
      pStmtUpdate.execute();
      PreparedStatement pStmtDelete = con.prepareStatement("DELETE FROM SESSION.t2 where c21 = ?");
      pStmtDelete.setInt(1, 23);
      pStmtDelete.execute();


      con.commit();
      ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println(" In the next transaction:");
      System.out.println("  Create savepoint1 and insert some rows into t2 using prepared statement and inspect the data in t2");
      Savepoint savepoint1 = con.setSavepoint();
      pStmtInsert.setInt(1, 23);
      pStmtInsert.setInt(2, 2);
      pStmtInsert.execute();
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint2 and update row inserted in savepoint1 using prepared statement and inspect the data in t2");
      Savepoint savepoint2 = con.setSavepoint();
      pStmtUpdate.setInt(1, 23);
      pStmtUpdate.execute();
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  rollback savepoint2 and should loose all the data from t2");
      con.rollback(savepoint2);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint3 and insert some rows into t2 using prepared statement and inspect the data in t2");
      Savepoint savepoint3 = con.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();
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint4 and update row inserted in savepoint3 using prepared statement and inspect the data in t2");
      Savepoint savepoint4 = con.setSavepoint();
      pStmtUpdate.setInt(1, 23);
      pStmtUpdate.execute();
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);

View Full Code Here

      ps = conn.prepareStatement("insert into t21 values(38, 'true')", Statement.RETURN_GENERATED_KEYS);
      ps.executeUpdate();
      dumpRS(ps.getGeneratedKeys());


      System.out.println("Test 13 - try savepoint rollback and see what happens to auto generated keys resultset");
      Savepoint savepoint1;
      System.out.println("Inside the savepoint unit, issue a one-row insert into a table with auto generated keys");
      savepoint1 = conn.setSavepoint();
      s.execute("insert into t11(c11) values (99)", Statement.RETURN_GENERATED_KEYS);
      System.out.println("We should get a resultset with one row of non-NULL value");
      dumpRS(s.getGeneratedKeys());

View Full Code Here

        String            sql;
        String            msg;
        int               i;
        PreparedStatement ps;
        ResultSet         rs;
        Savepoint         sp1;
        Savepoint         sp2;
        Savepoint         sp3;
        Savepoint         sp4;
        Savepoint         sp5;
        Savepoint         sp6;
        Savepoint         sp7;
        int               rowcount = 0;


        sql = "drop table t if exists";


        stmt.executeUpdate(sql);

View Full Code Here

    /**
     * Test2 - After releasing a savepoint, should be able to reuse it.
     */
    public void testReusingSavepoints() throws SQLException {
        Connection con = getConnection();
        Savepoint savepoint1 = con.setSavepoint("s1");
        con.releaseSavepoint(savepoint1);
        con.setSavepoint("s1");
        con.rollback();
    }

View Full Code Here

     * @throws SQLException
     */
    public void testNamesAndIds() throws SQLException {
        Connection con = getConnection();
        try {
            Savepoint savepoint1 = con.setSavepoint();
            savepoint1.getSavepointId();
            // following should throw exception for unnamed savepoint
            savepoint1.getSavepointName();
            fail("FAIL 4 getSavepointName on id savepoint");
        } catch (SQLException se) {
            // Expected exception.
            assertSQLState("XJ014", se);
        }
        con.rollback();
        try {
            Savepoint savepoint1 = con.setSavepoint("s1");
            savepoint1.getSavepointName();
            // following should throw exception for named savepoint
            savepoint1.getSavepointId();
            fail("FAIL 4 getSavepointId on named savepoint ");
        } catch (SQLException se) {
            // Expected exception.
            assertSQLState("XJ013", se);
        }

View Full Code Here

     * different transactions and release the first one in the subsequent
     * transaction
     */
    public void testBug4465() throws SQLException {
        Connection con = getConnection();
        Savepoint savepoint1 = con.setSavepoint("s1");
        con.commit();
        // The following savepoint was earlier named s1. Changed it to s2 while
        // working on DRDA support
        // for savepoints. The reason for that is as follows
        // The client translates all savepoint jdbc calls to equivalent sql and

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of java.sql.Savepoint

arjdbc.jdbc.RubyJdbcConnection

br.com.objectos.comuns.relational.jdbc.TransactionsGuice

br.com.objectos.way.relational.TransactionsGuice

com.alibaba.druid.bvt.pool.DruidPooledConnectionTest

com.alibaba.druid.bvt.proxy.BasicTypeTest

com.alibaba.druid.bvt.proxy.ConnectionTest

com.alibaba.druid.filter.logging.LogFilter

com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl

com.cloud.utils.db.Merovingian

com.cloud.utils.db.Transaction

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.