Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9482A475FB0 for ; Tue, 6 Aug 2002 12:42:01 -0400 (EDT) Received: from wilson.acpub.duke.edu (wilson.acpub.duke.edu [152.3.233.69]) by postgresql.org (Postfix) with ESMTP id 7193B475993 for ; Tue, 6 Aug 2002 12:41:55 -0400 (EDT) Received: from [152.3.11.193] (bglab2.botany.duke.edu [152.3.11.193]) by wilson.acpub.duke.edu (8.12.4/8.12.4/Duke-5.0.0) with ESMTP id g76GfwEc015812; Tue, 6 Aug 2002 12:41:59 -0400 (EDT) Mime-Version: 1.0 X-Sender: chauser@mail-ch.acpub.duke.edu Message-Id: In-Reply-To: <20533.1028650590@sss.pgh.pa.us> References: <20533.1028650590@sss.pgh.pa.us> Date: Tue, 6 Aug 2002 12:43:32 -0400 To: Tom Lane From: Charles Hauser Subject: Re: problem fetching currval of sequence Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary="============_-1183470269==_ma============" X-Virus-Scanned: by amavisd-milter, Duke University (http://amavis.org/) X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/106 X-Sequence-Number: 8952 --============_-1183470269==_ma============ Content-Type: text/plain; charset="us-ascii" ; format="flowed" Tom, Quite right, I did mean to do an INSERT. Changing the script to do so though I still get the error: ERROR: gb_gb_id_seq.currval is not yet defined in this session. foreach $id(sort keys %ACCN) { $conn->exec( "INSERT INTO gb (clone_id,accn,gi) VALUES ( '$ACCN{$id}{clone_id}','$ACCN{$id}{gb}','$ACCN{$id}{gi}')"; ); $result = $conn->exec("SELECT currval('gb_gb_id_seq');"); die $conn->errorMessage unless PGRES_TUPLES_OK eq $result->resultStatus; $gb_id = $result->fetchrow; $ACCN{$id}{gb_id} = $gb_id; } >Charles Hauser writes: >> foreach $id(sort keys %ACCN) { >> $conn->exec( >> "UPDATE gb SET accn_no = '$ACCN{$id}{gb}',gi_no = >> '$ACCN{$id}{gi}' WHERE clone_id = '$ACCN{$id}{clone_id}'" >> ); > >> $result = $conn->exec("SELECT currval('gb_gb_id_seq');"); > >This isn't inserting any new row, so nextval() isn't called, so there's >nothing for currval() to work on. Are you sure you did not mean to do >an INSERT? > >If you did want an UPDATE, seems like you could do > > SELECT gb_id FROM gb WHERE clone_id = '$ACCN{$id}{clone_id}' > > regards, tom lane -- Regards, Chuck --============_-1183470269==_ma============ Content-Type: text/html; charset="us-ascii" Re: [SQL] problem fetching currval of sequence
Tom,

Quite right, I did mean to do an INSERT.  Changing the script to do so though I still get the error:
                ERROR: gb_gb_id_seq.currval is not yet defined in this session.


    foreach $id(sort keys %ACCN) {
      $conn->exec(
                                "INSERT INTO gb (clone_id,accn,gi) VALUES ( '$ACCN{$id}{clone_id}','$ACCN{$id}{gb}','$ACCN{$id}{gi}')";
                                );
        $result = $conn->exec("SELECT currval('gb_gb_id_seq');");
        die $conn->errorMessage unless PGRES_TUPLES_OK eq $result->resultStatus;
        $gb_id = $result->fetchrow;
        $ACCN{$id}{gb_id} = $gb_id;
    }


Charles Hauser <chauser@duke.edu> writes:
>      foreach $id(sort keys %ACCN) {
> $conn->exec(
>                     "UPDATE gb SET accn_no = '$ACCN{$id}{gb}',gi_no =
> '$ACCN{$id}{gi}' WHERE clone_id = '$ACCN{$id}{clone_id}'"
>                     );

> $result = $conn->exec("SELECT currval('gb_gb_id_seq');");

This isn't inserting any new row, so nextval() isn't called, so there's
nothing for currval() to work on.  Are you sure you did not mean to do
an INSERT?

If you did want an UPDATE, seems like you could do

      SELECT gb_id FROM gb WHERE clone_id = '$ACCN{$id}{clone_id}'

                    regards, tom lane


--
Regards,

        Chuck
--============_-1183470269==_ma============--