6.9.1.4.9.1.1 Turning Off PGQL Auto Commit

When an INSERT, UPDATE, or DELETE operation is executed, a commit is performed automatically at the end of the PGQL execution so that changes are persisted on the RDBMS side.

The flag AUTO_COMMIT=F can be added to the options argument of execute or the flag Doracle.pg.rdbms.pgql.autoCommit=false can be set in the Java command line to turn off auto commit. Notice that when auto commit is off, you must perform any necessary commits or rollbacks on the JDBC connection in order to persist or cancel graph modifications.

Example 6-28 Turn Off Auto Commit and Roll Back Changes

PgqlExample21.java turns off auto commit and performs a rollback of the changes.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to modify a PGQL graph
 * with auto commit off.
 */
public class PgqlExample21
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Delete all the edges in the graph
      String pgql = 
        "DELETE e "+
        "  FROM MATCH () -[e]-> ()";
      ps.execute(pgql, /*  query string  */ 
                 "",   /* query options  */
                 "AUTO_COMMIT=F"  /* modify options */);

      // Execute a query to verify deletion
      pgql = 
          "SELECT COUNT(e) "+
          "  FROM MATCH () -[e]-> ()";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      System.out.println("Number of edges after deletion:");
      rs.print();
      rs.close();

      // Rollback the changes. This is possible because
      // AUTO_COMMIT=F flag was used in execute
      conn.rollback();

      // Execute a query to verify rollback
      pgql = 
          "SELECT COUNT(e) "+
          "  FROM MATCH () -[e]-> ()";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      System.out.println("Number of edges after rollback:");
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample21.java gives the following output for a graph with one edge:

Number of edges after deletion:
+----------+
| COUNT(e) |
+----------+
| 0        |
+----------+
Number of edges after rollback:
+----------+
| COUNT(e) |
+----------+
| 1        |
+----------+