I'm having a transaction problem when executing prepared statements using the PHP PDO-PGSQL module.
What happens is this:
On the first $subItem, $checkSubscription goes well, but $insertReminderEntry fails with error
"Duplicate key violates unique constraint"
This error should just be logged and the code should continue with the second $subItem.
And it does, but the second $checkSubscription also fails with error
"Current transaction is aborted, commands ignored until end of transaction block"
And that was not what I meant to happen.
Mark that I've only included part of the script. In the foreach loop several other updates and inserts happen.
Basically one of those updates and inserts failing should just be logged and the code should continue.
When the loop has finished all commands that executed without failure should be commited.
How should I change my code to make that happen?
Thanks for any help.
----------------------------------------------
$dbh = new PDO('pgsql:host='.$dbIP.';dbname='.$dbName.';port='.$dbPort, $dbUser, $dbPwd, array(PDO::ATTR_PERSISTENT => true));
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$checkSubscription = $dbh->prepare("select uid from subscriptions where uid=:uid and aboname=:subItem");
$insertReminderEntry = $dbh->prepare("insert into reminders (uid,nl) values (:uid,:aboname)");
$subscription=array('uktapsps','uktapsem');
$uidArray['uid'] = 'W200705084162';
$dbh->beginTransaction();
foreach ($subscription as $subItem) {
$checkSubscription->bindParam(':uid',$uidArray['uid']);
$checkSubscription->bindParam(':subItem',$subItem);
try {
$checkSubscription->execute();
}
catch (Exception $e) {
$msg.="ERROR: Subscription could not be checked! aboname: $subItem\n";
}
$insertReminderEntry->bindParam(':uid',$uidArray['uid']);
$insertReminderEntry->bindParam(':aboname',$subItem);
try {
$insertReminderEntry->execute();
}
catch (Exception $e) {
$msg.="ERROR: Entry could not be inserted into the reminder table! aboname: $subItem\n";
}
}