Re: [PHP-DB] oci_commit always returns true even when the transaction fails.

2010-03-17 Thread ZeYuan Zhang 
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 
?php

ini_set('display_errors', 'Off');

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

$stmtarray = 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 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 christopher.jo...@oracle.com:

 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.

2010-03-16 Thread Chris 

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


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: [PHP-DB] oci_commit always returns true even when the transaction fails.

2010-03-16 Thread Christopher Jones 

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.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.

2010-03-16 Thread Christopher Jones 



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:
 ?php
 $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.

2010-03-16 Thread Chris 

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:
  ?php
  $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.

2010-03-16 Thread Christopher Jones 



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:
  ?php
  $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.

2010-03-16 Thread 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

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