Executing SQL Statements With OCI8 Transactions

Executing SQL Statements With OCI8 Transactions : Oracle : Databases PHP Source Code

PHP Source Code » Databases » Oracle »

Executing SQL Statements With OCI8 Transactions

Using transactions to protect the integrity of data is as important in PHP as any other
relational database application. Except in special cases, you want either all your changes to be
committed, or none of them.
OCI8’s default commit behavior is like other PHP extensions and different from Oracle’s
standard. The default mode of oci_execute() is OCI_COMMIT_ON_SUCCESS to commit
changes. At the end of a script any uncommitted changes are automatically rolled back.

In the following example the changes are committed immediately when the
oci_execute() call is called:
$s = oci_parse($c, "insert into testtable values ('my data')");
oci_execute($s); // automatically committed
This can be handy for single INSERTs and UPDATEs, but transactional and performance
requirements should be thought about before using the default mode everywhere.
Unnecessarily committing or rolling back impacts database performance as it causes
unnecessary network traffic (“round trips”) between PHP and the database.

It is also causes extra processing and more IO to the database files. To maximize efficiency,
use transactions where appropriate.
You specify not to auto-commit and begin a transaction with:
$s = oci_parse($c, "insert into testtest values ('my data 2')");
oci_execute($s, OCI_DEFAULT); // not committed
The PHP parameter name OCI_DEFAULT is borrowed from Oracle’s Call Interface, where the
value of the C macro with the same name is actually the default value when nothing else is
specified. In PHP a better name would have been NO_AUTO_COMMIT, but we are stuck with
the awkward usage.
To commit any un-committed transactions for your connection, do:
oci_commit($c);
To rollback, do:
oci_rollback($c);
Transactions are automatically rolled back when you close the connection or at the end of the
script.
Be careful mixing and matching oci_execute() calls with both commit modes in one
script, since you may end up auto-committing and later also unnecessarily explicitly
committing, or you may simply commit at incorrect times. Benchmarking and testing your
applications in your environment is wise.

Autonomous Transactions
Oracle’s procedural language for SQL, PL/SQL, allows you to do autonomous transactions,
which are effectively sub-transactions. An autonomous transaction can be committed or
rolled back without affecting the main transaction. This might be useful for logging data
access. An audit record can be inserted even if the user decides to rollback their main change.
An example is:

SQL> create table logtable (name varchar2(30));

SQL> create or replace procedure updatelog(name_p in varchar2)

         as

           pragma autonomous_transaction;

         begin

          insert into logtable (name) values(name_p);

          commit;

end;

/

SQL> insert into logtable values ('Sally');

SQL> execute updatelog('Fred’);

SQL> rollback;

SQL> select name from logtable;

NAME
------------------------------
Fred
You could call the PL/SQL function from PHP to log access.

"; var_dump($res); echo ""; } $c1 = oci_connect("hr", "hrpwd", "//localhost/XE"); 120 $s = oci_parse($c1, "insert into mytable values ('" . date('j:M:y') . "')"); $r = oci_execute($s, OCI_DEFAULT); // No COMMIT occurs do_query($c1, "select * from mytable"); $c2 = oci_connect("hr", "hrpwd", "//localhost/XE"); do_query($c2, "select * from mytable"); ?>


<?php

function do_query($c, $query)

{

$s = oci_parse($c, $query);

           oci_execute($s, OCI_DEFAULT);

           oci_fetch_all($s, $res);

               echo "<pre>";

               var_dump($res);

               echo "</pre>";

}

$c1 = oci_connect("hr", "hrpwd", "//localhost/XE");





$s = oci_parse($c1,

           "insert into mytable values ('" . date('j:M:y') . "')");

$r = oci_execute($s, OCI_DEFAULT); // No COMMIT occurs

do_query($c1, "select * from mytable");

$c2 = oci_connect("hr", "hrpwd", "//localhost/XE");

do_query($c2, "select * from mytable");

?>



Using <em>oci_connect() </em>connection lets you query the newly inserted (but uncommitted) data<br />

because $c1 and $c2 refer to the same Oracle connection. Using <em>oci_pconnect()</em> is the<br />

same as <em>oci_connect()</em>.<br />

Using <em>oci_new_connect()</em> for $c2 gives a new connection which cannot see the<br />

uncommitted data.