Pg Transactional Function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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


--
Sent via pgsql-php mailing list (pgsql-php@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php


[Index of Archives]     [Postgresql General]     [Postgresql Admin]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Backpacking]     [Postgresql Jobs]

  Powered by Linux