Using pg_result_error with transaction - Mailing list pgsql-php

From Sylvain Racine
Subject Using pg_result_error with transaction
Date
Msg-id 3.0.6.32.20050819042139.00ad57d0@pop.citenet.net
Whole thread Raw
List pgsql-php
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.


pgsql-php by date:

Previous
From: Matthew Terenzio
Date:
Subject: Re: Problem about connecting PostgreSQL through TCP/IP
Next
From: Postgres Admin
Date:
Subject: Data insert