pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sylvain Racine <sylvain.racine@citenet.net>
To: pgsql-php@postgresql.org
Subject: Using pg_result_error with transaction
Date: Fri, 19 Aug 2005 04:21:39 -0400
Message-ID: <3.0.6.32.20050819042139.00ad57d0@pop.citenet.net> (raw)

I want to send a transaction block to my PostgreSQL database throught a PHP
Web page.

I use the pg_send_query command combine with pg_get_result and
pg_result_error commands to trap any error. The problem is that commands
abort the transaction block. 

I use Postgresql 7.4 running on Cygwin on Windows XP Pro and PHP 4.3.8.
Here is my PHP script. Notice: This script worked fine while I use pg_exec
command...



if (@$arpenteur && @$minute && @$date && @$travaux[0]) { //check for not
null data
  $query = "BEGIN;";
  $query .= "INSERT INTO minutes(numero_arpenteur, numero_minute, date,
numero_mandat, bloc) VALUES ($arpenteur, $minute, $date, '$mandat',
'$bloc');";
  foreach ($lots as $no_lot) {
    $query .= "INSERT INTO minutes_parcelles (id_minute, id_parcelle)
SELECT currval('seq_minute'), $no_lot;";
  }
  foreach ($travaux as $travail) {
    $query .= "INSERT INTO minutes_travaux (id_minute, id_travail) SELECT
currval('seq_minute'), $travail;";
  }
  foreach ($clients as $client) {
    $query .= "INSERT INTO minutes_clients (id_minute, id_client) SELECT
currval ('seq_minute'), $client;";
  }
  $query .= "UPDATE arpenteurs SET no_minute = (SELECT (no_minute)+1 FROM
arpenteurs WHERE no_arpenteur = $arpenteur) WHERE no_arpenteur = $arpenteur;";
  $query .= "COMMIT;";

  if ($GLOBALS['phpag']->db->query($query)) {
    echo 'Minute '.$minute.' recorded';
  }
}


and here is the class db function:


function query($query) 
{
	if (!$this->link_ID)
	{
		echo "You are not link with the database!";
		return FALSE;
	}
  unset($this->record);
	pg_send_query($this->link_ID, $query);

do { 
  $this->query_ID = pg_get_result($this->link_ID);
  $this->error = pg_result_error($this->query_ID);
  if ($this->error !== '') {
    echo $this->error;
    echo "SQL query ('$query') is not valid.";
    return $this->error;
  }
  else
  {
    for ($i = 0; $i < pg_numrows($this->query_ID); $i++) {
      $this->record[$i] = pg_fetch_row($this->query_ID,$i);
    }
  }
} while (pg_connection_busy($this->link_ID));
return $this->error;
}



Here is what appears in my Web browser while I try to execute this PHP script:

  ERROR: current transaction is aborted, commands ignored until end of
transaction block 

  SQL query ('SELECT nom_arpenteur, no_arpenteur FROM arpenteurs ORDER BY
nom_arpenteur') is not valid.

  ERROR: current transaction is aborted, commands ignored until end of
transaction block 

  SQL query ('SELECT prefixe_minute, no_minute FROM arpenteurs WHERE
no_arpenteur = ''') is not valid.

  ERROR: current transaction is aborted, commands ignored until end of
transaction block 

  SQL query ('SELECT numero_mandat FROM mandats ORDER BY numero_mandat') is
not valid.

  ERROR: current transaction is aborted, commands ignored until end of
transaction block 

  SQL query ('SELECT id_client, (COALESCE(nom,'') || (CASE WHEN prenom IS
NULL THEN '' ELSE ', ' END) || COALESCE(prenom,'')) AS client FROM clients
WHERE nom <> '' UNION SELECT id_client, compagnie FROM clients WHERE nom IS
NULL OR nom = '' ORDER BY client') is not valid.

  ERROR: current transaction is aborted, commands ignored until end of
transaction block 

  SQL query ('SELECT id_travail, nom_travail FROM travaux ORDER BY
nom_travail')



Thank you.




Message-ID: <3.0.6.32.20050819042139.00ad57d0@pop.citenet.net>
Permalink:  ../3.0.6.32.20050819042139.00ad57d0@pop.citenet.net/
Also on:    postgresql.org/message-id/3.0.6.32.20050819042139.00ad57d0@pop.citenet.net

 · 

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: sylvain.racine@citenet.net
  Subject: Re: Using pg_result_error with transaction
  In-Reply-To: <3.0.6.32.20050819042139.00ad57d0@pop.citenet.net>
  References: <3.0.6.32.20050819042139.00ad57d0@pop.citenet.net>

* 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