Re: update after select doesn't update... - Mailing list pgsql-general

when I do an echo of the update statement, the ip address displayed in the
update(from the value of primipaddr retrieved from the select) is correct and
matches what is in the table when checking with a shell account.  If I cut and
paste the update statement into the terminal window...  it works like a charm,
just not when used in the script after the select.

The selected/updated line is moved to the bottom of the stack as if a change had
taken place, but the value was not changed.  and no errors are generated (or a
rollback would take place).  Perplexing.

Dave

>-----Original Message-----
>From: Darren Ferguson [mailto:darren@crystalballinc.com]
>Sent: Thursday, January 03, 2002 10:35 AM
>To: Dave VanAuken
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] update after select doesn't update...
>
>
>If it is not updating that is leading me to believe that the
>vlaue of primipaddr does not exist in the table. This would give an update
>of 0
>
>dev=> create table test9 (test integer not null);
>CREATE
>dev=> insert into test9 values (1);
>INSERT 339522 1
>dev=> insert into test9 values (2);
>INSERT 339523 1
>dev=> insert into test9 values (3);
>INSERT 339524 1
>dev=> insert into test9 values (4);
>INSERT 339525 1
>dev=> insert into test9 values (5);
>INSERT 339526 1
>
>dev=> \d test9
>        Table "test9"
> Column |  Type   | Modifiers
>--------+---------+-----------
> test   | integer | not null
>
>dev=> UPDATE test9 set test = 6 where test = 7;
>UPDATE 0
>
>
>This is my two cents worth
>
>Hope it helps
>
>Darren
>Darren Ferguson
>
>
>On Thu, 3 Jan 2002, Dave VanAuken wrote:
>
>> Am retrieveing an "available" ipaddress from a table, then later trying to
>> perform an update to "assign" the address to an id.  The update produces no
>> error, but doesn't update either.  Must be overlooking something but
>can't place
>> a finger on it.
>>
>>     Table "host_ipaddress"
>>  Attribute |  Type   | Modifier
>> -----------+---------+----------
>>  server    | text    | not null
>>  type      | text    |
>>  hostnum   | integer | not null
>>  address   | text    |
>> Index: host_ipaddress_pkey
>>
>>
>> <?PHP
>> # Variables
>> $database = ***pg_connect statement***
>> $hostnum=2; # client reference number
>> $primipaddr=AssignPrivateIP($hostnum,'web'); #function returns IP address
>>
>> # Function
>> function AssignPrivateIP($hostnumber,$server) {
>>     global $database;
>>     $query = "SELECT address FROM host_ipaddress WHERE server='$server' AND
>> type='private' AND hostnum=0 LIMIT 1";
>>     $result = pg_exec($database,$query);
>>     if(pg_numrows($result)==0) {
>>         $return=0;
>>     } else {
>>         $row = pg_fetch_array($result,0);
>>         $return=$row[0];
>>     }
>>     return $return;
>> }
>>
>> # Queries
>>
>> # following does not work, the hostnum in the database table stays at 0
>> $query="UPDATE host_ipaddress SET hostnum=$hostnum WHERE
>address='$primipaddr'";
>>
>> # the following updates all records for the server/of type priv to 2
>> # **EXCEPT** the IP address we retrieved from the function (which we want to
>> update)
>> $query="UPDATE host_ipaddress SET hostnum=2 WHERE server='web' AND
>type='priv'";
>>
>> ...appropriate exec etc...
>> ?>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>
>
>