Can't use prepared statement via transaction from php?
I am working on a LAPP environment (linux apache postgresql php) and I am just trying to figure out how to use a prepared statement in a transaction (if possible).
Hopefully the code explains the words better:
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 above example, if I downgrade a transaction, the only effect on the database would be to update last_activity ... ye?
If I try to use this transaction in php (with both PDO methods and pg_), the code should look like this (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 it works great. Maybe ugly to see, but seems to work (suggestion is always welcome)
Anyway, my problem comes up when I try to include example 2 with prepared statements (I know that in example 2, using prepared statements is not very useful)
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 example 3 just doesn't work, the prepared statement will be efficient if the transaction is rolled back.
So, prepared statements cannot be used in a transaction, or am I wrong?
EDIT:
After some try with PDO, I came to 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();
?>
This code doesn't work with this output:
array (3) {[0] => string (5) "00000" [1] => int (7) [2] => string (62) "ERROR: duplicate key violates unique" id_h_orders "constraint}
array (3) {[0] => string (5) "25P02" [1] => int (7) [2] => string (87) "ERROR: the current transaction is aborted, commands are ignored until the end of the transaction block"}
Fatal error: Calling member function fetch () for non-object in / srv / www / test -db / test-db-pgsql-08.php on line 23
So it looks like when the first execution fails (the one with ID 293), the transaction is automatically aborted ... does the PDO auto-rollback or something?
My goal is to end the first while loop, and at the end, using the bool var as flag, decide whether to rollback or commit the transaction.
a source to share
With PostgreSQL, if any statement generates a server error during a transaction, that transaction is marked aborted. That doesn't mean it actually rolled back - it's just that you can hardly do anything other than to roll it back. My guess is that PDO will not automatically rollback, it waits for you to call the "rollback" method.
To achieve what I think you want, you can use a savepoint. Instead of rolling back the entire transaction, you can simply rollback to the savepoint and continue with the transaction. I'll give an example of using this method 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 strings in t with id 9 and get the unique constraint. I can't just rewrite the insert with the correct values, because now any statement will get a "current transaction aborted ..." error. But I can do a "rollback to the savepoint", which brings me back to the state I was in when I did the "savepoint" ("xyzzy" is the name of the savepoint). I can then issue the correct insert command and finally commit the transaction (which commits both inserts).
So, in your case, I suspect you need to create a savepoint before the UPDATE statement: if it gives an error, do a "rollback to savepoint" and set your flag. You will need to generate unique names for savepoints: for example using a counter.
I'm not entirely sure I understand why you are doing all this. Surely you want to stop processing as soon as you know that you are going to cancel the transaction? Or is there some other processing in the loop that needs to happen too?
a source to share
You must use
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 have not made any transactions and need to manually configure the material, use a different transaction. Preparing your request (if I remember) is part of the transaction, because it can fail. You can't just manually take the SQL statement and include it in your queries. The PDO interface has abstractions for a reason. :)
http://uk3.php.net/pdo <- Solid PHP / Postgre Examples Using PDO
luck
a source to share