Re: oci_commit always returns true even when the transaction fails.
| From: | Christopher Jones | Date: | Tue, 16 Mar 2010 22:44:16 +0000 |
| Subject: | Re: oci_commit always returns true even when the transaction fails. | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-46962@lists.php.net to get a copy of this message | ||
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@oracle.com
Tel: +1 650 506 8630
Blog: http://blogs.oracle.com/opal/
Free PHP Book: http://tinyurl.com/ugpomhome