failure during a transaction aborts php script- help
am 28.06.2007 12:27:15 von vivek.j.joshiHello dear friends,
I am writing a small program to test transactions in php. But when I
try to simulate an error condition during transaction, my php script
aborts. It does
rollback work, but I cannot continue the script. Please help. I am a
newbie to both php and postgresql.
PostgreSQL 7.4.17
PHP 5.1.6
table 1 - users:- username(unique), password, handle(unique).
table 2 - contacts:- username(unique), email(unique), foreign key -
users(username).
When I try to insert into a contacts with value of email which already
exist, it fails due to unique contact. The query should fail and I
should enter into
the $result === FALSE block. But it just displays the following and
ends the script.
PHP Warning: pg_query(): Query failed: ERROR: duplicate key violates
unique constraint "contacts_email_key" in /tmp/transaction.php on line
80
Failed to execute command2.
The code.
$dbConnection = sqlConnect("localhost", "vivek", "postgres");
if ($dbConnection === FALSE) {
echo "Program Failed.\n";
return;
}
echo "Connection id returned is " . $dbConnection . ".\n\n";
// construct the command.
$command1 = "Insert into users (username,passwd,handle) VALUES ('foo',
'foo', iamfoo')";
$command2 = "Insert into contacts (username,email) VALUES ('foo',
'foo@site.com')";
echo "Command 1 is:- " . $command1 . "\n";
echo "Command 2 is:- " . $command2 . "\n";
$result = pg_query($dbConnection, "begin");
if ($result === FALSE) {
echo "Unable to begin a transaction. " . pg_last_error() . ".
\n";
return;
}
pg_free_result($result);
$result = pg_query($dbConnection, $command1);
if ($result === FALSE) {
echo "Failed to execute command1.\n";
$result = pg_query($dbConnection, "rollback");
if ($result === FALSE) {
echo "Failed to rollback\n";
}
return;
}
pg_free_result($result);
$result = pg_query($dbConnection, $command2);
if ($result === FALSE) {
echo "Failed to execute command2.\n";
$result = pg_query($dbConnection, "rollback");
if ($result === FALSE) {
echo "Failed to rollback\n";
}
return;
}
pg_free_result($result);
$result = pg_query($dbConnection, "commit");
if ($result === FALSE) {
echo "Failed to commit.\n";
$result = pg_query($dbConnection, "rollback");
if ($result === FALSE) {
echo "Failed to rollback\n";
}
return;
}
// Free the result set.
pg_free_result($result);
// Closing connection
sqlDisconnect($dbConnection);