A.10.4.8.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 A-48 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 | +----------+
Parent topic: Additional Options for PGQL Statement Execution