pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Chambers <gwchamb@gwcmail.com>
To: PostgreSQL PHP Users List <pgsql-php@postgresql.org>
Subject: Pg Transactional Function
Date: Thu, 23 Aug 2012 10:56:54 -0400 (EDT)
Message-ID: <alpine.DEB.2.00.1208231048040.10347@booby.gogca.com> (raw)

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




Message-ID: <alpine.DEB.2.00.1208231048040.10347@booby.gogca.com>
Permalink:  ../alpine.DEB.2.00.1208231048040.10347@booby.gogca.com/
Also on:    postgresql.org/message-id/alpine.DEB.2.00.1208231048040.10347@booby.gogca.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: gwchamb@gwcmail.com
  Subject: Re: Pg Transactional Function
  In-Reply-To: <alpine.DEB.2.00.1208231048040.10347@booby.gogca.com>
  References: <alpine.DEB.2.00.1208231048040.10347@booby.gogca.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox