Using pg_result_error with transaction

From: Sylvain Racine <sylvain(dot)racine(at)citenet(dot)net>
To: pgsql-php(at)postgresql(dot)org
Subject: Using pg_result_error with transaction
Date: 2005-08-19 08:21:39
Message-ID: 3.0.6.32.20050819042139.00ad57d0@pop.citenet.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Browse pgsql-php by date

  From Date Subject
Next Message Postgres Admin 2005-08-20 17:17:55 Data insert
Previous Message Hui Chen 2005-08-18 20:39:46 Re: Problem about connecting PostgreSQL through TCP/IP