error during a transaction gets a php script to abort

[wizard]

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',
'f**@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);

[Captain Paralytic]

On 28 Jun, 11:27, wizard <[e-mail]:

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',
'[e-mail]')";
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

[rf]

"Captain Paralytic" <pa**********@yahoo.comwrote in message
news:11*********************@k79g2000hse.googlegro ups.com...

On 28 Jun, 11:27, wizard <[e-mail]:

>Hello dear friends,

Do not multi-post. Cross-post if you must but don't multi post

and snip the portions of the post you are replying to that are not relevant
so others to not have to search through pages of stuff just to find your one
line reply.

--
Richard.

[Toby A Inkster]

wizard wrote:

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.

From the code posted, that is to be expected.

The pg_query issues a warning, but continues. If you want to suppress this
warning, then use the @-operator. The $result===FALSE condition evaluates
to TRUE, so the interpreter executes the conditional block, which does the
following:

* prints out "Failed to execute command2"
* rolls back the transaction
* executes a "return" instruction

At a guess, "return" doesn't mean what you think it means. "return" means
"I'm finished with this function/script. Stop processing any more of it".
That is why the script finishes here.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 7 days, 16:33.]

Long-Awaited Zeldman Article
http://tobyinkster.co.uk/blog/2007/0...ldman-in-time/

[Captain Paralytic]

On 28 Jun, 13:39, "rf" <[e-mail]:

"Captain Paralytic" <[e-mail] in message

news:11*********************@k79g2000hse.googlegro ups.com...

On 28 Jun, 11:27, wizard <[e-mail]:

Hello dear friends,

Do not multi-post. Cross-post if you must but don't multi post

and snip the portions of the post you are replying to that are not relevant
so others to not have to search through pages of stuff just to find your one
line reply.

--
Richard.

Good point. I guess I'm spoilt by the Google reader hiding the quoted
text automagically.