Обсуждение: update after select doesn't update...

Поиск
Список
Период
Сортировка

update after select doesn't update...

От
"Dave VanAuken"
Дата:
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...
?>

Re: update after select doesn't update...

От
Darren Ferguson
Дата:
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
>

Re: update after select doesn't update...

От
dave@hawk-systems.com (Dave)
Дата:
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
>>
>
>
>

Re: update after select doesn't update...

От
Darren Ferguson
Дата:
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
> >>
> >
> >
> >
>

Re: update after select doesn't update...

От
dave@hawk-systems.com (Dave)
Дата:
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
>> >>
>> >
>> >
>> >
>>
>
>
>