Re: [PHP-DB] oci_commit always returns true even when the transaction fails.
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 0 ) deferrable initially immediate primary key, 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($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 = oci_parse($c, "insert into t_tab values ( 1,1 )"); $r = oci_execute($s, OCI_DEFAULT); if (!$r) { $m = oci_error($c); print('Could not execute: '. $m['message']); } $r = oci_commit($c); if (!$r) { $m = oci_error($c); print('Could not commit: '. $m['message']); } 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($c); print('Could not execute: '. $m['message']); } $r = oci_commit($c); if (!$r) { $m = oci_error($c); print('Could not commit: '. $m['message']); } $s = oci_parse($c, "drop table t_tab"); oci_execute($s); ?> 2010/3/17 Christopher Jones : > >> 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.jo...@oracle.com > Tel: +1 650 506 8630 > Blog: http://blogs.oracle.com/opal/ > Free PHP Book: http://tinyurl.com/ugpomhome > -- ZeYuan Zhang Mail: 51ajax@gmail.com Blog: http://51ajax.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] oci_commit always returns true even when the transaction fails.
> 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.jo...@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: [PHP-DB] oci_commit always returns true even when the transaction fails.
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.jo...@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: [PHP-DB] oci_commit always returns true even when the transaction fails.
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: [PHP-DB] oci_commit always returns true even when the transaction fails.
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.jo...@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: [PHP-DB] oci_commit always returns true even when the transaction fails.
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 - 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.jo...@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: [PHP-DB] oci_commit always returns true even when the transaction fails.
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: 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