Posted by Captain Paralytic on 06/28/07 12:36

On 28 Jun, 11:27, wizard <vivek.j.jo...@gmail.com> wrote:
> Hello 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',
> '...@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);

Do not multi-post. Cross-post if you must but don't multi post
http://www.blakjak.demon.co.uk/mul_crss.htm

[Back to original message]