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

Logical thought...

The transaction involves about 7 seperate inserts and this one update.  All the
inserts are done successfully and commited...  and the update doesn't cause a
failure either, just doesn't update.

# commit or rollback
        if($continue_queries==1){
            # commit this transaction
            query("COMMIT");
            if($acct_privipflag==1){
                UnAssignPrivateIP($acct_primipaddr);
            }

did catch that errant bit of code though when running over it again...  Commits,
then reverses the IP assignment.  At least you got me looking in the right
place.  Thanks.

Dave

>-----Original Message-----
>From: Darren Ferguson [mailto:darren@crystalballinc.com]
>Sent: Thursday, January 03, 2002 11:13 AM
>To: Dave
>Cc: pgsql-general@postgresql.org
>Subject: RE: [GENERAL] update after select doesn't update...
>
>
>I know this may sound silly but i had the problem( i am assuming a
>transaction ) where by it would do everything but the data would not get
>updated. It turned out he code never got the the commit transaction part.
>Did not see this in the snippet you gave but if you are using a
>transaction then this could be a possibility
>
>Darren
>
>Darren Ferguson
>Software Engineer
>Openband
>
>On Thu, 3 Jan 2002, Dave wrote:
>
>> 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
>> >>
>> >
>> >
>> >
>>
>
>
>