[PHP] PostgreSQL/PHP: transactions: how-to abstract out?

[Jean-Christian Imbeault]

Hi. I thought I had abstracted out the SQL querying part of my code out, just to find out today that it doesn't work when it comes to transactions. I had come up with this code:

function sql_query($sql) {
$conn = pg_connect("dbname=JC user=postgres");
$res = pg_exec($conn, $sql);
if (!$res) {
echo "CONNECTION: could not execute query ($sql)<br>";
die;
}
else return $res;
}

I had transactions in my code implemented like this:

$sql = "BEGIN";
sql_query($sql);
[some sql that should be in a transaction ...]
$sql = "COMMIT";
sql_query($sql);

This doesn't work. Now that I look at my code I clearly see why. All sql queries are executed using a new Postgres connection, hence the use of BEGIN/COMMIT as I was using them have no effect.

Can someone recommend a way to abstract out my DB layer while still being able to use transactions?

I was thinking of using the same function but if the incoming query contained the word BEGIN, saving that and all future queries in a session var and when the COMMIT comes in executing all the saved queries as one (i.e. "BEGIN;[....];COMMIT"). One drawback is that all queries will be written out to disk (as session vars) and that will slow things down. Another drawback is that I have to abort if not COMMIT comes in. And a few more drawbacks ...

I was also thinking about maybe the $sql a GLOBAL or first building up my query as as long string ("BEGIN;[....];COMMIT") and *then* sending it to my sql_query() function.

The last two seem easier to implement, safer, and more efficient but they don't seem "elegant" because I haven't abstracted out the fact that I want a transaction. Whenever I write an SQL query I have to think "does this need to be in a transaction" and then use a different coding technique depending on the answer. And if the future something that didn't need to be in a transaction now needs to be in a transaction I have to revisit my code and change the code.

I'm sure someone out there must have thought about this and come up with an elegant solution and way of abstracting out the DB layer from PHP.

Can anyone share their solution with me or give me some pointers to reference material?

Thanks,

Jc


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php