Re: oci_commit always returns true even when the transaction fails.

[Christopher Jones <christopher.jones@xxxxxxxxxx>]

ZeYuan Zhang wrote:
> Hi there.
>
> Why oci_commit function always returns true even when the transaction fails.
> I just copied the code in the php manual [Example 1636. oci_commit() example],
> and runned it, the situation is as follows:
>
> * The statements do commit at the moment when oci_commit executes.
> * But some statements are committed to oracle successfully, when some fails.
>   I think it cannot be called a transaction, and I did used
> OCI_DEFAULT in the oci_execute function.
>
> Code:
> <?php
> $conn = oci_connect('scott', 'tiger');
> $stmt = oci_parse($conn, "INSERT INTO employees (name, surname) VALUES
> ('Maxim', 'Maletsky')");
> oci_execute($stmt, OCI_DEFAULT);

Add some error checking for oci_execute() - you might find failure is
happening before you even get to commit.

> $committed = oci_commit($conn);
>
> if (!$committed) {
>     $error = oci_error($conn);
>     echo 'Commit failed. Oracle reports: ' . $error['message'];
> }
> ?>
> $committed is always true, whenever $stmt executes successfully or fails.
> I use PHP5.2.12, apache_2.0.63-win32-x86-openssl-0.9.7m.msi, windows
> xp, Oracle10.1
> and oci8 1.2.5.
>
> thanks.
>
> -- paravoice
>

The following code show oci_commit failing.  It gives me:

  $ php52 commit_fail.php
  First Insert
  Second Insert
  PHP Warning:  oci_commit(): ORA-02091: transaction rolled back
  ORA-02290: check constraint (CJ.CHECK_Y) violated in commit_fail.php on line 57
  PHP Fatal error:  Could not commit:  in commit_fail.php on line 60

Chris

-----------------

<?php

ini_set('display_errors', 'Off');

// Uses deferred constraint example from
// http://www.oracle.com/technology/oramag/oracle/03-nov/o63asktom.html

$c = oci_connect('cj', 'cj', 'localhost/orcl2');
if (!$c) {
    $m = oci_error();
    trigger_error('Could not connect to database: '. $m['message'], E_USER_ERROR);
}

$stmtarray = array(
	"drop table t_tab",
	"create table t_tab
     ( x int constraint check_x check ( x > 0 ) deferrable initially immediate,
       y int constraint check_y check ( y > 0 ) deferrable initially deferred)"
);

foreach ($stmtarray as $stmt) {
	$s = oci_parse($c, $stmt);
	$r = oci_execute($s);
	if (!$r) {
		$m = oci_error($s);
		if (!in_array($m['code'], array(   // ignore expected errors
                    942 // table or view does not exist
                    ,  2289 // sequence does not exist
                    ,  4080 // trigger does not exist
                    , 38802 // edition does not exist
                ))) {
			echo $stmt . PHP_EOL . $m['message'] . PHP_EOL;
		}
	}
}

echo "First Insert\n";
$s = oci_parse($c, "insert into t_tab values ( 1,1 )");
$r = oci_execute($s, OCI_DEFAULT);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not execute: '. $m['message'], E_USER_ERROR);
}
$r = oci_commit($c);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not commit: '. $m['message'], E_USER_ERROR);
}

echo "Second Insert\n";
$s = oci_parse($c, "insert into t_tab values ( 1,-1)");
$r = oci_execute($s, OCI_DEFAULT);  // Explore the difference with and without OCI_DEFAULT
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not execute: '. $m['message'], E_USER_ERROR);
}
$r = oci_commit($c);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not commit: '. $m['message'], E_USER_ERROR);
}

$s = oci_parse($c, "drop table t_tab");
oci_execute($s);

?>

--
Email: christopher.jones@xxxxxxxxxx
Tel:  +1 650 506 8630
Blog:  http://blogs.oracle.com/opal/
Free PHP Book: http://tinyurl.com/ugpomhome

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php