Pg Transactional Function

From: Gary Chambers <gwchamb(at)gwcmail(dot)com>
To: PostgreSQL PHP Users List <pgsql-php(at)postgresql(dot)org>
Subject: Pg Transactional Function
Date: 2012-08-23 14:56:54
Message-ID: alpine.DEB.2.00.1208231048040.10347@booby.gogca.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-php

All,

I've written a procedural function to [hopefully] create less cluttered code
when working with PostgreSQL transactions and I am soliciting criticism and
suggestions on how to make it better (or to scrap it).

function db_exec_transaction($dbh, $query_and_params, &$results_array, &$error_str)
{
if (!is_array($query_and_params))
die('Queries and parameters variable must be an array type');

pg_query($dbh, 'begin');
foreach ($query_and_params as $i => $qp)
{
if (empty($qp['params']))
$stmt = pg_query($dbh, $qp['query']);
else
$stmt = pg_query_params($dbh, $qp['query'], $qp['params']);

if ($stmt === false)
{
$error_str = pg_last_error($dbh);
pg_query($dbh, 'rollback');
return false;
}
else
$results_array[] = $stmt;
}
pg_query($dbh, 'commit');
$error_str = null;
return true;
}

A lame and mostly useless example of how I intend to call it is as follows:

function foo_bar_handler($dbh)
{
$query_resources = array();
$queries = array();
$queries[] = array('query' => 'select * from foo for update',
'params' => array());
$queries[] = array('query' => 'delete from foo',
'params' => array());
$queries[] = array('query' => 'select * from bar where fizz = $1 and buzz = $2',
'params' => array(42, 15));

$stmt = db_exec_transaction($dbh, $queries, $query_resources, $error_str);
if ($stmt === false)
{
echo $error_str . "\n";
return false;
}

/* Fetch and do something with the results */
$rows = pg_fetch_all($query_resources[0]);
echo 'Rows retrieved: ' . pg_num_rows($query_resources[0]) . "\n";
$the_answers = pg_fetch_all($query_resources[2]);

/* Release the resources */
pg_free_result($query_resources[0]);
pg_free_result($query_resources[1]);
pg_free_result($query_resources[2]);

return true;
}

Any comments, criticisms, or suggestions are very much appreciated.

Thank you,
Gary Chambers

Browse pgsql-php by date

  From Date Subject
Next Message Archana K N 2012-09-05 07:18:50
Previous Message Jorge Alberto Aquino Andrade 2012-08-23 13:50:47 Fwd: Ayuda de restauracion en phppgadmin