oci_commit always returns true even when the transaction fails.

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:
$conn = oci_connect('scott', 'tiger');
$stmt = oci_parse($conn, "INSERT INTO employees (name, surname) VALUES
('Maxim', 'Maletsky')");
oci_execute($stmt, OCI_DEFAULT);
$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

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

Re: oci_commit always returns true even when the transactionfails.

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:
> > $conn = oci_connect('scott', 'tiger');
> $stmt = oci_parse($conn, "INSERT INTO employees (name, surname) VALUES
> ('Maxim', 'Maletsky')");
> oci_execute($stmt, OCI_DEFAULT);

Reading the docs (straight from
http://www.php.net/manual/en/function.oci-commit.php).

A transaction begins when the first SQL statement that changes data is
executed with oci_execute() using the OCI_NO_AUTO_COMMIT flag.

You need to

oci_execute($stmt, OCI_NO_AUTO_COMMIT);

--
Postgresql & php tutorials
http://www.designmagick.com/


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

Re: oci_commit always returns true even when the transactionfails.

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:
> > $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

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


ini_set('display_errors', 'Off');

// Uses deferred constraint example from
// http://www.oracle.com/technology/oramag/oracle/03-nov/o63ask tom.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

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

Re: oci_commit always returns true even when the transactionfails.

Chris wrote:
> 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:
>> >> $conn = oci_connect('scott', 'tiger');
>> $stmt = oci_parse($conn, "INSERT INTO employees (name, surname) VALUES
>> ('Maxim', 'Maletsky')");
>> oci_execute($stmt, OCI_DEFAULT);
>
> Reading the docs (straight from
> http://www.php.net/manual/en/function.oci-commit.php).
>
> A transaction begins when the first SQL statement that changes data is
> executed with oci_execute() using the OCI_NO_AUTO_COMMIT flag.
>
> You need to
>
> oci_execute($stmt, OCI_NO_AUTO_COMMIT);
>

OCI_NO_AUTO_COMMIT is a recently introduced alias for OCI_DEFAULT, so
the original code is equivalent. This could be made clearer in the
oci_commit documentation, but is explained on
http://www.php.net/manual/en/function.oci-execute.php, which is where
the flag is actually used.

Chris

--
Email: christopher.jones@oracle.com
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

Re: oci_commit always returns true even when the transactionfails.

Christopher Jones wrote:
>
>
> Chris wrote:
> > 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:
> >> > >> $conn = oci_connect('scott', 'tiger');
> >> $stmt = oci_parse($conn, "INSERT INTO employees (name, surname) VALUES
> >> ('Maxim', 'Maletsky')");
> >> oci_execute($stmt, OCI_DEFAULT);
> >
> > Reading the docs (straight from
> > http://www.php.net/manual/en/function.oci-commit.php).
> >
> > A transaction begins when the first SQL statement that changes data is
> > executed with oci_execute() using the OCI_NO_AUTO_COMMIT flag.
> >
> > You need to
> >
> > oci_execute($stmt, OCI_NO_AUTO_COMMIT);
> >
>
> OCI_NO_AUTO_COMMIT is a recently introduced alias for OCI_DEFAULT, so
> the original code is equivalent. This could be made clearer in the
> oci_commit documentation, but is explained on
> http://www.php.net/manual/en/function.oci-execute.php, which is where
> the flag is actually used.

Fair enough, thanks for the clarification :)

--
Postgresql & php tutorials
http://www.designmagick.com/


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

Re: oci_commit always returns true even when the transactionfails.

Chris wrote:
> Christopher Jones wrote:
>>
>>
>> Chris wrote:
>> > 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:
>> >> >> >> $conn = oci_connect('scott', 'tiger');
>> >> $stmt = oci_parse($conn, "INSERT INTO employees (name, surname)
>> VALUES
>> >> ('Maxim', 'Maletsky')");
>> >> oci_execute($stmt, OCI_DEFAULT);
>> >
>> > Reading the docs (straight from
>> > http://www.php.net/manual/en/function.oci-commit.php).
>> >
>> > A transaction begins when the first SQL statement that changes data is
>> > executed with oci_execute() using the OCI_NO_AUTO_COMMIT flag.
>> >
>> > You need to
>> >
>> > oci_execute($stmt, OCI_NO_AUTO_COMMIT);
>> >
>>
>> OCI_NO_AUTO_COMMIT is a recently introduced alias for OCI_DEFAULT, so
>> the original code is equivalent. This could be made clearer in the
>> oci_commit documentation, but is explained on
>> http://www.php.net/manual/en/function.oci-execute.php, which is where
>> the flag is actually used.
>
> Fair enough, thanks for the clarification :)
>

No problems.

Chris

--
Email: christopher.jones@oracle.com
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

Re: oci_commit always returns true even when the transactionfails.

> 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);

Correction: the two oci_error() calls after oci_commit() should use
the $c connection resource, not $s, e.g.:

$m = oci_error($c);

> 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

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

Re: oci_commit always returns true even when the transaction

Thanks everybody.
To: Christopher Jones
thanks for your code. I have run your code.
For the sample you supplied, the oci_commit really returns false.

But if I add a primary key constraint to t_tab.x, and then run the
script, the oci_commit returns true!strange...

So do I need check all the oci_execute's return value? awful...

******** the code pk added ********

ini_set('display_errors', 'Off');

$c =3D oci_connect('scott', 'tiger');
if (!$c) {
$m =3D oci_error();
print ('Could not connect to database: '. $m['message']);
}

$stmtarray =3D array(
"drop table t_tab",
"create table t_tab
( x int constraint check_x check ( x > 0 ) deferrable initially
immediate primary key,
y int constraint check_y check ( y > 0 ) deferrable initially deferre=
d)"
);

foreach ($stmtarray as $stmt) {
$s =3D oci_parse($c, $stmt);
$r =3D oci_execute($s);
if (!$r) {
$m =3D oci_error($c);
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 =3D oci_parse($c, "insert into t_tab values ( 1,1 )");
$r =3D oci_execute($s, OCI_DEFAULT);
if (!$r) {
$m =3D oci_error($c);
print('Could not execute: '. $m['message']);
}
$r =3D oci_commit($c);
if (!$r) {
$m =3D oci_error($c);
print('Could not commit: '. $m['message']);
}

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

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

?>

2010/3/17 Christopher Jones :
>
>> echo "Second Insert\n";
>> $s =3D oci_parse($c, "insert into t_tab values ( 1,-1)");
>> $r =3D oci_execute($s, OCI_DEFAULT);  // Explore the difference wit=
h and
>> without OCI_DEFAULT
>> if (!$r) {
>>     $m =3D oci_error($s);
>>     trigger_error('Could not execute: '. $m['message'], E_USER=
_ERROR);
>> }
>> $r =3D oci_commit($c);
>> if (!$r) {
>>     $m =3D oci_error($s);
>
> Correction: the two oci_error() calls after oci_commit() should use
> the $c connection resource, not $s, e.g.:
>
>     $m =3D oci_error($c);
>
>>     trigger_error('Could not commit: '. $m['message'], E_USER_=
ERROR);
>> }
>>
>> $s =3D 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
>



--=20
############################
ZeYuan Zhang
Mail: 51ajax.net@gmail.com
Blog: http://51ajax.net/
############################

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