As mentioned in the preceding section, in Oracle you can explicitly either commit a transaction or roll it back, using the COMMIT or ROLLBACK statements respectively. To perform these statements from PHP code, you don't need to use the oci_parse and oci_execute functions as you do it when it comes to performing other SQL statements, such as SELECT or INSERT. Instead, you use the oci_commit and oci_rollback OCI8 functions.
The following PHP script demonstrates how to explicitly commit or rollback a transaction from PHP when dealing with DML operations. What this script does is attempt to update those records in the employees table that represent employees whose job ID is ST_MAN (Stock Manager), increasing their salaries by 10 percent. If it fails to update one or more of these rows, then the entire transaction is rolled back, setting the updated salary fields back to their original values. The following steps summarize the process:
Step 1: Issues a query against the employees table to obtain the number of rows representing stock managers.
Step 2: Opens a transaction and performs the UPDATE operation against the employees table, attempting to increase stock managers' salaries by 10 percent.
• •
UPDATE operation is less than the number of all the records representing stock managers. Otherwise, it commits the transaction.
Now, let's look at the code for the script to see how the above steps can be implemented in PHP, using the OCI8 functions.
<?php
//File: trans.php
if(!$dbConn = oci_connect('hr', 'hr', '//localhost/orcl')) { $err = oci_error();
trigger_error('Could not establish a connection: ' . $err['message'], E_USER_ERROR); };
$query = "SELECT count(*) num_rows FROM employees WHERE job_id='ST_MAN'"; $stmt = oci_parse($dbConn,$query);
if (!oci_execute($stmt)) {
$err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR); };
oci_fetch($stmt);
$numrows = oci_result($stmt, 'NUM_ROWS'); oci_free_statement($stmt);
$query = "UPDATE employees e SET salary = salary*1.1 WHERE e.job_id='ST_MAN' AND salary*1.1 BETWEEN (SELECT min_salary FROM jobs j WHERE j.job_id=e.job_id) AND (SELECT max_salary FROM jobs j WHERE j.job_id=e.job_id)";
$stmt = oci_parse($dbConn,$query);
if (!oci_execute($stmt, OCI_DEFAULT)) { $err = oci_error($stmt);
trigger_error('Update failed: ' . $err['message'], E_USER_ERROR); }
$updrows = oci_num_rows($stmt);
print "Tried to update ".$numrows." rows.<br />"; print "Managed to update ".$updrows." rows.<br />";
if ($updrows<$numrows) { if (!oci_rollback($dbConn)) {
$err = oci_error($dbConn); trigger_error('Failed to rollback transaction:
'.$err['message'], E_USER_ERROR); }
} else {
if (!oci_commit($dbConn)) {
$err = oci_error($dbConn);
trigger_error('Failed to commit transaction: '.$err['message'], E_USER_ERROR); }
print "Transaction is committed"; }
?>
In the above script, you define the query that will return the number of records representing stock managers. In the select list of the query, you use the count function to obtain the number of rows matching the criteria specified in the WHERE clause of the query. In this particular example, count(*) will return the number of records representing the employees whose job_id is ST_MAN.
In this example, you obtain the number of records representing stock managers from the result buffer, using the oci_fetch/oci_result function combination. You don't need to use a loop here because the query returns a single row containing only one field, namely num_rows.
Next, you perform the query that updates the salary column in the employees table, increasing salaries of stock managers by 10%. It updates the salary only if the value of the new salary is still between the minimum and maximum salary specified for the stock manager in the jobs table.
In this example, you execute the UPDATE statement in the OCI_DEFAULT execution mode. Doing so opens a transaction, which will allow you to explicitly commit or roll back the changes made by the UPDATE operation later in the script. It is interesting to note that the default execution mode is OCI_COMMIT_ON_SUCCESS in which the statement is committed automatically upon successful execution.
Oracle documentation states that applications should always explicitly commit or roll back transactions before program termination. However, when using PHP OCI8 extension, you don't have to do so if you execute SQL statements in the OCI_COMMIT_ON_SUCCESS mode. In that mode, an SQL statement is committed automatically upon successful execution (just as if you explicitly committed immediately after executing the statement). If a severe error prevents the successful execution of an SQL statement, Oracle automatically rolls back all the changes made by that statement. In the above script, you call the oci_num_rows function to get the number of rows affected by the UPDATE operation. Once you know the number of records representing stock managers and how many of them were actually updated, you can
is less than the total number of records representing stock managers. This makes sense given that you don't want to have some stock managers' records updated and others not.
Having the changes rolled back in this situation is crucial—this makes it possible for you to make use of another script that will be able to update each stock manager record in a proper way. For example, in a real-world situation, you would probably want to set the salary of a stock manager to the maximum allowed value if a 10 percent raise exceeds that value.
If the UPDATE operation has affected all of the records representing stock managers, you commit the transaction with the help of the oci_commit function, making the changes made permanent.
Another thing to note here is the error handling mechanism used. If an error occurs during the execution of oci_rollback or oci_commit, you pass the connection identifier as the parameter to the oci_error function, which returns the error message describing the error that has occurred.