Cant the prepared statement be used throught the transaction, from php?

Question

I work on a LAPP environment (linux apache postgresql php), and i'm just triyn to find out how to use the prepared statement within the transaction (if is possible).

I hope code will explain better then words:

Example 1, simple transaction:

BEGIN;
INSERT INTO requests (user_id, description, date) VALUES ('4', 'This dont worth anything', NOW());
UPDATE users SET num_requests = (num_requests + 1) WHERE id = '4';
--something gone wrong, cancel the transaction
ROLLBACK;
UPDATE users SET last_activity = NOW() WHERE id = '4'
COMMIT;

In the example above, if i undestood right the transaction, the only effect in the database will be the last_activity's update... ye?

If i try to use that transaction in php (both with PDO or pg_ methods) the code should look like that (example 2):

/* skip the connection */
pg_query($pgConnection, "BEGIN");
pg_query($pgConnection, "INSERT INTO requests (user_id, description, date) VALUES ('$id_user', 'This dont worth anything', NOW())");
pg_query($pgConnection, "UPDATE users SET num_requests = (num_requests + 1) WHERE id = '$id_user'");
//something gone wrong, cancel the transaction
pg_query($pgConnection, "ROLLBACK");
pg_query($pgConnection, "UPDATE users SET last_activity = NOW() WHERE id = '$id_user'");
pg_query($pgConnection, "COMMIT");

And that works fine. Maybe ugly to see, but seem to work (suggestion are always welcome)

Anyway, my problem come when i try to envolve the example 2 with the prepared statements (i know that in the example 2 the use of prepared statements is not very usefull)

Example 3:

/* skip the connection */
pg_prepare($pgConnection, 'insert_try', "INSERT INTO requests (user_id, description, date) VALUES ('$1', '$2', $3)");
pg_query($pgConnection, "BEGIN");
pg_execute($pgConnection, 'insert_try', array($user_id, 'This dont worth anything', date("Y-m-d")));
/* and so on ...*/

Well, the example 3 simply dont work, the prepared statement will be effective if the transaction due in rollback.

So, the prepared statements can't be used in the transaction, or am i taking the wrong way?

EDIT:

After some try with PDO, i'm arrived at this point:

<?php
$dbh = new PDO('pgsql:host=127.0.0.1;dbname=test', 'myuser', 'xxxxxx');

$rollback = false;

$dbh->beginTransaction();

//create the prepared statements
$insert_order = $dbh->prepare('INSERT INTO h_orders (id, id_customer, date, code) VALUES (?, ?, ?, ?)');
$insert_items = $dbh->prepare('INSERT INTO h_items (id, id_order, descr, price) VALUES (?, ?, ?, ?)');
$delete_order = $dbh->prepare('DELETE FROM p_orders WHERE id = ?');

//move the orders from p_orders to h_orders (history)
$qeOrders = $dbh->query("SELECT id, id_customer, date, code FROM p_orders LIMIT 1");
while($rayOrder = $qeOrders->fetch(PDO::FETCH_ASSOC)){
    //h_orders already contain a row with id 293
    //lets make the query fail
    $insert_order->execute(array('293', $rayOrder['id_customer'], $rayOrder['date'], $rayOrder['code'])) OR var_dump($dbh->errorInfo());
    //this is the real execute
    //$insert_order->execute(array($rayOrder['id'], $rayOrder['id_customer'], $rayOrder['date'], $rayOrder['code'])) OR die(damnIt('insert_order'));
    //for each order, i move the items too
    $qeItems = $dbh->query("SELECT id, id_order, descr, price FROM p_items WHERE id_order = '" . $rayOrder['id'] . "'") OR var_dump($dbh->errorInfo());
    while($rayItem = $qeItems->fetch(PDO::FETCH_ASSOC)){
        $insert_items->execute(array($rayItem['id'], $rayItem['id_order'], $rayItem['descr'], $rayItem['price'])) OR var_dump($dbh->errorInfo());
    }
    //if everything is ok, delete the order from p_orders
    $delete_order->execute(array($rayOrder['id'])) OR var_dump($dbh->errorInfo());
}
//in here i'll use a bool var to see if anythings gone wrong and i need to rollback,
//or all good and commit
$dbh->rollBack();
//$dbh->commit();
?>

The code above fails with this output:

array(3) { [0]=> string(5) "00000" [1]=> int(7) [2]=> string(62) "ERROR: duplicate key violates unique constraint "id_h_orders"" }

array(3) { [0]=> string(5) "25P02" [1]=> int(7) [2]=> string(87) "ERROR: current transaction is aborted, commands ignored until end of transaction block" }

Fatal error: Call to a member function fetch() on a non-object in /srv/www/test-db/test-db-pgsql-08.php on line 23

So, seem like when the first execute fail (the one with id 293) the transaction is automatically aborted... does the PDO auto-rollback, or something else?

My goal is to complete the first big while loop, and at the end, using a bool var as flag, decide if to rollback or commit the transaction.

Answer 1

You should be using

pdo_obj->beginTransaction()
pdo_obj->commit()
pdo_obj->prepare()

Also you have a random commit at the end of your first example.

begin
// do all your stuff
// check for errors through interface
commit OR not

pg_query($pgConnection, "ROLLBACK"); // end of tx(1)
// start new transaction after last rollback = tx(2)
pg_query($pgConnection, "UPDATE users SET last_activity = NOW() WHERE id = '$id_user'");
// commit tx(2) or don't here
// this isn't needed pg_query($pgConnection, "COMMIT");

If you didn't commit, and need to manually adjust stuff, use another transaction. Preparing your query (if I recall) is part of a transaction, because it can fail. You can't really just manually take an SQL statement and turn it into queries. The PDO interface has abstractions for a reason. :)

http://uk3.php.net/pdo <-- Solid examples of PHP/Postgre using PDO

good luck

Answer 2

With PostgreSQL, if any statement produces a server error during a transaction, that transaction is marked as aborted. That doesn't mean it's actually rolled back yet- just that you can hardly do anything except roll it back. I assume PDO doesn't automatically issue a rollback, it waits for you to call the "rollback" method.

To achieve what I think you want, you can use a savepoint. Rather than rolling back the entire transaction, you can just rollback to the savepoint, and continue the transaction. I'll give an example of using this from psql:

srh@srh@[local] =# begin;
BEGIN
srh@srh@[local] *=# insert into t values(9,6,1,true);
INSERT 0 1
srh@srh@[local] *=# savepoint xyzzy;
SAVEPOINT
srh@srh@[local] *=# insert into t values(9,6,2,true);
ERROR:  duplicate key value violates unique constraint "t_pkey"
srh@srh@[local] !=# insert into t values(10,6,2,true);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
srh@srh@[local] !=# rollback to savepoint xyzzy;
ROLLBACK
srh@srh@[local] *=# insert into t values(10,6,2,true);
INSERT 0 1
srh@srh@[local] *=# commit;
COMMIT
srh@srh@[local] =# 

So in this example, the first column of t is the primary key. I tried to insert two rows into t with an id of 9, and the got a uniqueness constraint. I can't just redo the insert with the right values because now any statement will get the "current transaction is aborted..." error. But I can do "rollback to savepoint", which brings me back to the state I was at when I did "savepoint" ("xyzzy" is the name of the savepoint). Then I can issue the correct insert command and finally commit the transaction (which commits both inserts).

So in your case, I suspect what you need to do is create a savepoint before your UPDATE statement: if it gives an error, do a "rollback to savepoint" and set your flag. You'll need to generate unique names for the savepoints: using a counter, for example.

I'm not entirely sure I understand why you're doing all this. Surely you want to stop processing as soon as you know you're going to roll back the transaction? Or is there some other processing going on in the loop that has to happen as well?