As you learned from the examples discussed earlier in this chapter, the oci_execute function allows you to execute an SQL statement in one of two modes—OCI_COMMIT_ON_SUCCESS mode and OCI_DEFAULT mode.
While statements are automatically committed when run in the OCI_COMMIT_ON_ SUCCESS mode, you have to explicitly call oci_commit or oci_rollback to commit or roll back the transaction respectively, when specifying the OCI_DEFAULT mode. However, it is interesting to note that a transaction created when a statement is executed in the OCI_DEFAULT mode may still be committed without calling oci_ commit. To accomplish this, all you need to do is to execute a subsequent statement in the OCI_COMMIT_ON_SUCCESS mode.
The above technique can be applied when you're grouping two or more statements into a single transaction. To guarantee that the entire transaction will be rolled back when the execution of one of the statements within the transaction fails or you get results telling you that the transaction must be undone, you may simply stop the script execution by calling, say, the trigger_error function with E_USER_ERROR as the second parameter, thus rolling back the transaction without calling
an Oracle transaction from PHP, rather than explicitly calling oci_commit or oci_rollback. After all, the latter is the recommended method for ending transactions. Well, the main purpose of this discussion is to give you a better understanding of how Oracle transactions work in PHP scripts that interact with the database via the OCI8 extension.
The example discussed in this section uses the data structures that were defined in the An Example of When to Use a Stored Subprogram section in Chapter 3. However, before you proceed with the example, you need to alter these data structures as shown below. You can perform the SQL statements shown in the following listing via SQL*Plus when connected as usr/usr.
ALTER TABLE accounts ADD (num_logons INT); UPDATE accounts SET num_logons = 0; COMMIT; DELETE logons; ALTER TABLE logons ADD CONSTRAINT log_time_day
CHECK (RTRIM(TO_CHAR(log_time, 'Day')) NOT IN ('Saturday', 'Sunday'));
By issuing the ALTER TABLE statement in the above example, you add a
num_logons column of INT to the accounts table. This column will accumulate the number of successful logons for each user account. For that, you will have to increase the number of logons stored in the num_logons field once the user is successfully authenticated.
Of course, you can still do without it, querying the logons table like this:
SELECT count(*) FROM logons WHERE usr_id='bob';
However, as the number of logons grows, the above would be a very expensive operation just to know how many logons a given user has performed.
Once you have added the num_logons column to the accounts table, you have to set the initial value for that column to 0. Alternatively, you might have issued the ALTER TABLE statement, using the DEFAULT clause for the num_logons column as follows:
ALTER TABLE accounts ADD (num_logons INT DEFAULT 0);
In this example, you explicitly commit the transaction to make the changes made by the UPDATE operation permanent.
In the next step you delete all the rows in the logons table. This step is required to guarantee that the check constraint, which will be defined in the next step, is not violated. In this example, you may omit this step if the logons table contains no records created on Saturday or Sunday, and so the check constraint defined in the next step will not be violated. Otherwise, when trying to perform the ALTER TABLE, you will receive the following error message:
ERROR at line 2:
ORA-02293: cannot validate (USR.LOG_TIME_DAY) - check constraint violated
Here, you define the check constraint on the log_time column of the logons table. This constraint prevents inserting new rows into the logons table on Saturday or Sunday, which allows you to modify your authentication system so that it
prevents each and every user from being able to log on on Saturdays and Sundays, thus allowing users to log on only on the working days. Later, you can always drop this constraint by issuing the following statement:
ALTER TABLE logons DROP CONSTRAINT log_time_day;
Turning back to the ALTER TABLE statement shown in the preceding page, note the use of the format 'Day' specified as the second parameter of the TO_CHAR function. This format tells the TO_CHAR function to convert a date stored in the log_time field to a day of the week. Then, you use the NOT IN operator to exclude Saturdays and Sundays from the list of allowed days.
Bear in mind that in this case Oracle uses case-sensitive matching. So, if you have specified 'Day' as the second argument of the TO_CHAR function, then you have to specify the days of the week in the expression list to the right of the NOT IN operator like this: 'Saturday', 'Sunday'. It would be 'SATURDAY', 'SUNDAY' if you have specified 'DAY' as the second parameter of TO_CHAR.
Now that you have modified all the required database structures as needed, you can proceed with the example whose intent is to illustrate how to create a transaction by executing a DML statement in the OCI_DEFAULT mode and then how to implicitly end that transaction by executing the subsequent statement in the OCI_COMMIT_ON_SUCCESS mode.
In reality, of course, you might want to have more than just two statements in a transaction. To accomplish this, you might execute all of the statements (except for the last one) that you want to group into a single transaction in the OCI_DEFAULT mode, and then execute the last statement in the OCI_COMMIT_ON_SUCCESS mode to close the transaction.
Data state 1 Data state 2 oci_execute($stml1, OCI_DEFAULT); oci_execute($stmlM, OCI_DEFAULT); oci_execute($stmlN, OCI_COMMIT_ON_SUCCESS); . . .
The following script demonstrates how the architecture shown in the figure can be implemented in PHP. Note that unlike the login function discussed in the An Example of When to Use a Stored Subprogram section in Chapter 3, the login function shown below stops execution and returns false when it fails to insert an audit record into the logons table. This makes sense, since you now insert a new record into the logons table not only to save the information about a logon, but to check if the inserted data adhere to the business rule, which says that no row in the logons table can contain a date whose day of the week is Saturday or Sunday in the log_time column.
<?php
//File: userLoginTrans.php function login($usr, $pswd) {
if(!$rsConnection = oci_connect('usr', 'usr', '//localhost/orcl')) { $err = oci_error();
trigger_error('Could not establish a connection: ' . $err['message'], E_USER_ERROR); };
$query = "SELECT full_name, num_logons FROM accounts
$stmt = oci_parse($rsConnection,$query);
oci_bind_by_name($stmt, ':userid', $usr); oci_bind_by_name($stmt, ':passwd', $pswd); if (!oci_execute($stmt)) {
$err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR); }
if (!$arr = oci_fetch_array($stmt, OCI_ASSOC)) { print "Wrong user/password combination"; return false;
}
$num_logons=$arr['NUM_LOGONS']+1; oci_free_statement($stmt);
$query = "UPDATE accounts SET num_logons = num_logons + 1"; $stmt = oci_parse($rsConnection,$query); if (!oci_execute($stmt, OCI_DEFAULT)) { $err = oci_error($stmt); trigger_error('Update failed: ' . $err['message'], E_USER_WARNING); return false; } oci_free_statement($stmt);
$query = "INSERT INTO logons VALUES (:userid, SYSDATE)"; $stmt = oci_parse($rsConnection,$query);
oci_bind_by_name($stmt, ':userid', $usr); if (!oci_execute($stmt, OCI_COMMIT_ON_SUCCESS)) { $err = oci_error($stmt);
trigger_error('Insertion failed: ' . $err['message'], E_USER_WARNING);
if ($err['code']=='02290'){ print "You cannot connect on Saturday or Sunday"; } return false; }
print "Hello, ".$arr['FULL_NAME']."<br/>"; print "You have visited us ".$num_logons." time(s)"; session_start(); $_SESSION['user']=$usr; return true; }
number of successful logons for each user account. In the script, you define the UPDATE statement that will increase the value of the num_logons field in the record representing the user whose credentials are being used for authentication.
By executing the statement in the OCI_DEFAULT mode, you create a new transaction. This makes sense, since you may need to roll back the changes made by this UPDATE operation if the subsequent insert into the logons table fails.
If the UPDATE operation fails, you exit the login function, returning false to the calling script. This tells the calling script that the authentication has failed. Next, you define the INSERT statement that is executed once a user has been successfully authenticated and the counter of his or her successful logons has been incremented.
Executing the INSERT statement in the OCI_COMMIT_ON_SUCCESS mode in the script guarantees that the transaction will be committed on success or rolled back on failure, which means that either both the changes made by the INSERT and the effects of the UPDATE statement become permanent or both are undone.
If you recall, the oci_error function returns an associative array of two elements, namely, code, which contains the Oracle error code, and message, which contains the message string describing the error. In this particular example, you check to see if the Oracle error code is equal to 02290. If so, this indicates that a check constraint violation error occurred. Since you have only one check constraint defined on the logons table (the one that prevents inserting new rows into the logons table on Saturdays and Sundays), you may inform the user that he or she cannot connect on Saturday and Sunday.
In this example, if the INSERT fails, you exit the login function with false, thus telling the calling script that the authentication has failed. In the case of successful authentication, you take appropriate actions, such as displaying a welcome message and creating a new session.
Now, to see the newly created login function in action, you might use the following simple script: <?php //File: testLoginTrans.php require_once "userLoginTrans.php"; if (login('bob','pswd')) { if (isset($_SESSION['user'])) {
print '<p>'.'Your account name: '.$_SESSION['user'].'</p>'; } else {
is not set'.'</p>'; }
}else {
print '<p>'.'Authentication failed'.'</p>'; }
?>
If you run the testLoginTrans.php script shown in the above listing on Saturday or Sunday, you should see the following output:
You cannot connect on Saturday or Sunday Authentication failed
On a working day, however, the output should be as follows: Hello, Bob Robinson
You have visited us 1 time(s) Your account name: bob
Each subsequent execution of the testLoginTrans.php script on a working day should increase the number of Bob Robinson's visits. However, if you execute the script on Saturday or Sunday, it will not increase that number. This proves that everything works as expected.