Transaction Statements in Oracle and PHP

In this second part of a two-part series on how to properly handle transactions with Oracle databases, you’ll learn how to use rollback statments, the savepoint statement, and how to complete transactions with PHP. This article is excerpted from chapter 33 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Implicit COMMIT Statement

A common mistake you can make when building a transaction is to perform an implicit COMMIT —in other words, a SQL statement that automatically commits your changes, usually when you do not want to. All DDL statements perform an implicit COMMIT , such as CREATE TABLE or ALTER INDEX . As a general rule, then, do not perform DDL within your transactions. In a typical OLTP (online transaction processing) environment, this should not be a problem; a well-designed end user application will only have DML statements. Implicit COMMIT statements are more of a concern when you are issuing statements interactively. Then you must take care and pay attention to what you are doing. If you must perform any DDL for a user application, do it before or after a transaction, not during.

Explicit ROLLBACK Statement

Up to now, we have not used ROLLBACK explicitly; implicit ROLLBACK statements usually occur because of some kind of database, network, or client failure. To elaborate further, an explicit ROLLBACK is a great way to change your mind if you make a change to a database table and you do not want to make the change permanent. Running an explicit ROLLBACK statement applies to all DML activity since the last COMMIT or ROLLBACK or since the session began. It can roll back 1 or 1,000 DML statements, limited only by the size of your UNDO tablespace (we cover the UNDO tablespace in Chapter 29). You can also perform a partial ROLLBACK ; we tell you more about this in the next section on the SAVEPOINT statement.

For example, your last task for the day is to delete employee number 106 from the database; you run this DELETE statement in SQL*Plus:

SQL> delete from employees;

107 rows deleted.

Oops, you forgot the WHERE clause, so all of the employees are deleted. This is not the desired result. However, since you are using Oracle Database XE, you can roll back the deletions using ROLLBACK (and likely save your job in the process):

SQL> rollback;

Rollback complete.

SQL>

The EMPLOYEES table is now back to its original state. Any other DML statements executed before the DELETE without an intervening ROLLBACK or COMMIT will also be rolled back. This time you remember the WHERE clause:

SQL> delete from employees where employee_id = 106;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

During your lapse of judgment, probably due to not enough coffee, your users are none the wiser; during the entire session until the COMMIT , Oracle provided a read-consistent view of the EMPLOYEES table. In other words, your users were not aware that the EMPLOYEES table was empty and would not see your changes, unless you were to perform a COMMIT after the first DELETE .

{mospagebreak title=The SAVEPOINT Statement}

Using the SAVEPOINT statement, you can mark a spot in the middle of a transaction to which you might wish to roll back. A SAVEPOINT statement allows you to undo part of a transaction rather than all of it. Following is the syntax to use in creating a savepoint:

SAVEPOINT savepoint_name;

You uniquely name the savepoint and then may subsequently reference it in a ROLLBACK statement as follows:

ROLLBACK TO SAVEPOINT savepoint_name;

Regardless of how many savepoints exist within a transaction, a ROLLBACK statement without a SAVEPOINT reference will automatically roll back the entire transaction. The following SQL*Plus example uses a savepoint to conditionally undo the DML statements since the SAVEPOINT statement was issued. First, the example creates a new region in the REGIONS table because you are opening a branch office in Antarctica:

SQL> insert into regions (region_id, region_name)
 
2  values (5, ‘Antarctica’);

1 row created.

SQL> savepoint new_region;

Savepoint created.

The savepoint NEW_REGION may come in handy later if you need to roll back the insertion of the new countries you will be adding in the next few statements:

SQL> insert into countries (country_id, country_name, region_id )
  2 values (‘NA’, ‘Northern Antarctica’, 5);

1 row created.

SQL> savepoint new_country_1;

Savepoint created.

SQL> insert into countries (country_id, country_name, region_id)
  2  values (‘SA’, ‘Southern Antarctica’, 5);

1 row created.

SQL> savepoint new_country_2;

Savepoint created.

After performing these two inserts, you realize that you have a geographic anomaly on your hands, and that for now you are just going to add one country to the COUNTRIES table but keep the new region in the REGIONS table:

SQL> rollback to new_region;

Rollback complete.

SQL> insert into countries (country_id, country_name, region_id)
  2  values (‘IA’, ‘Inner Circle of Antarctica’, 5);

1 row created.

Just to double-check your work before you run the COMMIT statement, you query the REGIONS and COUNTRIES tables:

SQL> select * from regions;

 REGION_ID REGION_NAME
———- ————————-
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Antarctica

SQL> select * from countries;

CO COUNTRY_NAME                    REGION_ID
– —————————— ———-

AR Argentina                               2 AU Australia                               3 BE Belgium                                 1 BR Brazil                                  2 CA Canada                                  2 CH Switzerland                             1 CN China                                   3 DE Germany                                 1 DK Denmark                                 1 EG Egypt                                   4 FR France                                  1

CO COUNTRY_NAME                    REGION_ID
– —————————— ———-HK HongKong                                3 IA Inner Circle of Antarctica              5 IL Israel                                  4 IN India                                   3 IT Italy                                   1 JP Japan                                   3 KW Kuwait                                  4 MX Mexico                                  2 NG Nigeria                                 4 NL Netherlands                             1 SG Singapore                               3

CO COUNTRY_NAME                    REGION_ID
– —————————— ———-
UK United Kingdom                          1 US United States of America                2 ZM Zambia                                  4 ZW Zimbabwe                                4

26 rows selected.

SQL>

Now that everything looks good, you commit the single-row change to the REGIONS table, and the single-row change to the COUNTRIES table:

SQL> commit;

Commit complete.

{mospagebreak title=Performing Transactions Using PHP}

Using the transactional features in OCI8 is fairly straightforward, relying on a small handful of function calls: oci_connect() , oci_close() , oci_execute() , oci_commit() , and oci_rollback() . You have seen three of these function calls before; in this section, we revisit them from a transactional perspective.

When you connect to the database using oci_connect() , you are implicitly starting a transaction. None of the parameters of oci_connect() , however, dictate how your transactions are processed; the oci_execute() function call is where all the transactional fun begins. As you may remember from Chapter 32, the syntax of oci_execute() is as follows:

bool oci_execute ( resource statement [, int mode] )

The mode parameter is typically one of two values:

  1. OCI_DEFAULT : The DML statement does not automatically execute a COMMIT statement upon successful execution.
  2. OCI_COMMIT_ON_SUCCESS : The DML statement will automatically perform a COMMIT upon successful execution of the statement.

The default value for mode is OCI_COMMIT_ON_SUCCESS , which is a bit counterintuitive; you might expect that the default would be OCI_DEFAULT . This is one of the things about oci_execute() that you just have to remember: the default is not OCI_DEFAULT . In all of the examples up to this point, you used oci_execute() without the second parameter, and therefore automatically committed any changes to the database by the statement specified in the first parameter of oci_execute() .

When you are done with the transaction, you can either commit the transaction with oci_commit() or roll it back with oci_rollback() . Both functions use the connection handle from oci_connect() ; as a result, you can have several ongoing transactions within the same PHP script—one active transaction per connection handle.

Revisiting the example earlier in the chapter, we use a PHP script to update two more salaries in the database as a single transaction. In the example in Listing 33-1, you increase the monthly salary by $1,000 for employee number 100 and decrease the monthly salary by the same amount for employee number 102.

Listing 33-1. PHP Code to Perform Two Salary Updates As a Single Transaction (db_update_salary.php)

<?php

  // Update salaries: increase one salary and decrease
  // another as a single transaction.

  // Connect to the server and select the database
  $c = @oci_connect(‘hr’, ‘hr’, ‘//localhost/xe’)
            or die("Could not connect to Oracle server");

  // Show salaries before update.
  echo "Previous salaries: <br />";
  $s = oci_parse($c, ‘select employee_id, last_name, salary from employees’
                      . ‘ where employee_id in (100,102)’);

  oci_execute($s, OCI_DEFAULT);
  while ($res = oci_fetch_array($s)) {
     echo $res['EMPLOYEE_ID'] . ‘ — ‘ . $res['LAST_NAME']
           . ‘: ‘ . $res['SALARY'] . "<br />";
  }

  // add $1000 to first employee’s salary

  $s = oci_parse($c, "update employees
                       set salary = salary + 1000
                       where employee_id = 100");                       

  $result = oci_execute($s, OCI_DEFAULT);

  // subtract $1000 from second employee’s salary

  $s = oci_parse($c, "update employees
                       set salary = salary – 1000
                       where employee_id = 102");

  $result = oci_execute($s, OCI_DEFAULT);

  // end of transaction
  oci_commit($c);

  // Show salaries after update.

  echo "<br /><br /><br />New salaries: <br />";
  $s = oci_parse($c, ‘select employee_id, last_name, salary from employees’
                      . ‘ where employee_id in (100,102)’);

  oci_execute($s, OCI_DEFAULT);
  while ($res = oci_fetch_array($s)) {
     echo $res['EMPLOYEE_ID'] . ‘ — ‘ . $res['LAST_NAME']
           . ‘: ‘ . $res['SALARY'] . "<br />";
  }

  // done. If there are any uncommitted transactions, oci_close()
  // will roll back.

  oci_close($c);
?>

Any script or database failure up until the oci_commit() statement will force the execution of a ROLLBACK statement for both UPDATE statements. The output of this script is in Figure 33-3. Notice that you must use OCI_DEFAULT even if you are only displaying rows, otherwise the commit will occur prematurely. In the example in Listing 33-1, you run a SELECT before the transaction begins, and you do not run another SELECT until after the transaction is complete. Using OCI_DEFAULT is not required for the SELECT statements in this example, but it is good practice to use OCI_DEFAULT every time you run oci_execute() in a transaction-based application to ensure you do not accidentally perform a COMMIT statement when you do not want to.


Note  There is no explicit SAVEPOINT in OCI8 or PDO. It is, however, fully supported in PEAR/MDB2 starting with version 1.0.0.


Finally, oci_close() or the termination of the PHP script will automatically roll back any uncommitted transactions; as a result, it is good programming practice to explicitly commit or roll back your transactions, otherwise oci_close() will roll them back when you might not want to.


Figure 33-3.  Results of salary update transaction processing

Summary

Now that you’ve read this chapter, you should possess a thorough understanding of transactions, how they’re implemented in Oracle Database XE, and how to incorporate them into your PHP applications. Using transactions in Oracle boils down to three statements: COMMIT , ROLLBACK , and SAVEPOINT . Some statements, such as Oracle DDL statements, force the execution of a COMMIT statement; these should be avoided when you’re in the middle of a transaction. From a PHP perspective, the OCI8 transaction-related function calls give you all the tools you need to create robust Web applications. 

Database transactions are of immense use when modeling your business processes because they help to ensure the integrity of your organization’s most valuable asset: its information and the relationships between different categories and hierarchies of information in your database such as orders with order items and employees within departments. If you use database transactions prudently, they are a great asset when building database-driven applications.

In the next chapter, we delve more deeply into the SELECT statement and how to craft a query for any requirement, including, of course, how to use the results of these queries in a PHP application. 

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan