PDO Transaction rolling back when returning LastInsertID

Question

I am having a strange problem which I don't understand. I have the following function which is supposed to return $asset_ID:

function commit_purchase($asset_type_ID, $org_ID, $asset_desc, $asset_cost, $date, $org_to_member_ID, $asset_ID, $purchaser_cur_invest, $purchaser_cred_deb, $purchaser_balance) {
global $db;
$db->beginTransaction();
$query = "INSERT INTO assets
                       (asset_type_ID, org_ID, asset_desc, asset_cost, asset_value, purchase_date, is_approved)
                    VALUES
                       (:asset_type_ID, :org_ID, :asset_desc, :asset_cost, :asset_cost, :date, 1)";
$statement = $db->prepare($query);
$statement->bindValue(':asset_type_ID', $asset_type_ID);
$statement->bindValue(':org_ID', $org_ID);
$statement->bindValue(':asset_desc', $asset_desc);
$statement->bindValue(':asset_cost', $asset_cost);
$statement->bindValue(':date', $date);
$statement->execute();
$asset_ID = $db->lastInsertId();
//return $asset_ID;
$db->commit(); 
}

I am calling the function like so:

$asset_ID = commit_purchase($asset_type_ID, $org_ID..etc, etc.);

If I uncomment the return $asset_ID, the transaction rolls back and does not commit. If I leave it commented, the variable is not passed. If I comment out the beginTransaction and commit lines, I can uncomment the return $asset_ID and everything works.

I want it to stay as a transaction and I want to return the $asset_ID. What am I doing wrong?

score 1 · Accepted Answer · answered Mar 24 '12 at 00:04

1

You need to move the return $asset_ID; line to after the commit as the execution of the function stops when you return. Without the commit being called you get an implicit rollback.

answered Mar 24 '12 at 00:04

user1191247

10,808
2
22
32

That did the trick. I plan on throwing in a few more queries before the commit. Will I be able to use the $asset_ID variable in the queries which follow? – Progger Mar 24 '12 at 00:11
Yes. Once you have assigned the variable it is available to use within the current scope unless you overwrite or unset it. You must not return the value until after you have executed all your queries and committed them. – user1191247 Mar 24 '12 at 00:22

PDO Transaction rolling back when returning LastInsertID

1 Answers1