X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A4C6552CBB for ; Fri, 19 Aug 2005 05:23:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48630-03 for ; Fri, 19 Aug 2005 08:23:46 +0000 (GMT) Received: from cti06.citenet.net (cti06.citenet.net [206.123.38.70]) by svr1.postgresql.org (Postfix) with ESMTP id DF69352C59 for ; Fri, 19 Aug 2005 05:23:43 -0300 (ADT) Received: from sylvain (g60-222.citenet.net [207.183.44.222]) by cti06.citenet.net (8.12.10/8.12.10) with SMTP id j7J8NqgA065866 for ; Fri, 19 Aug 2005 04:23:52 -0400 (EDT) (envelope-from sylvain.racine@citenet.net) Message-Id: <3.0.6.32.20050819042139.00ad57d0@pop.citenet.net> X-Sender: ctmz9129@pop.citenet.net X-Mailer: QUALCOMM Windows Eudora Light Version 3.0.6 (32) Date: Fri, 19 Aug 2005 04:21:39 -0400 To: pgsql-php@postgresql.org From: Sylvain Racine Subject: Using pg_result_error with transaction Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 required=5 tests=[none] X-Spam-Level: X-Archive-Number: 200508/25 X-Sequence-Number: 3237 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.