pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Haddon <tom@betterhealthfoundation.org>
To: pgsql-php@postgresql.org
Subject: FW: Nextval Problems
Date: Thu, 22 Aug 2002 09:14:37 -0700
Message-ID: <NEBBIHDGCLBEJMPFAMLAEEEFCIAA.tom@betterhealthfoundation.org> (raw)

Hi Keary,

Thanks for the comments. Could you help me out with a few questions:

Why is "building it all the insertion pieces into one query separated by
";", of course" a bad idea?

Could you explain why "The semicolon in the query is at least superfluous if
not dangerous." - sorry, it's just I thought that was a standard way to
close out a SQL query, and I wonder if you could set me straight there. And
I am definitely not calling setval anywhere else in the code, unless it is
called implicitly by an "INSERT" query.

If it would be helpful and you are interested in spending the time to look
over it, I could send you the file in full to review - I'm relatively new to
php-postgres, and would definitely appreciate another set of eyes - this
applies to anyone else as well.

Thanks, Tom

-----Original Message-----
From: pgsql-php-owner@postgresql.org
[mailto:pgsql-php-owner@postgresql.org]On Behalf Of Keary Suska
Sent: Wednesday, August 21, 2002 10:07 PM
To: Postgres-PHP
Subject: Re: [PHP] Nextval Problems


on 8/21/02 6:33 PM, tom@betterhealthfoundation.org purportedly said:

> Problem is, I have to add 1 onto the nextval to make this work, and when I
> look at the ids that are being added into the tables, they are not
> consecutive, one is being missed each time, so for instance I have 1000,
> 1002, 1004, etc.
>
> Can anyone help? Is this something to do with the way I am connecting to
the
> database? I build it all the insertion pieces into one query separated by
> ";", of course.

That is a really bad idea. Don't do that.

> Originally I thought that was the problem, and split them
> into separate queries and then ran them one after the other, but no dice.
Am
> I misunderstanding nextval?

Hard to say. The code below is not all of the code, and insufficient to
determine what is wrong. In any case, nextval will increment the sequence
and return the incremented value. It's good to keep in mind that sequence
manipulations are not effected by transactions, just in case.

Now let's see what we have here.

> // Need to get value of next input id for agency_contact_info field
> $strnextval="SELECT nextval('agency_contact_info_id_key');";

The semicolon in the query is at least superfluous if not dangerous. Remove
it. Are you *really* sure you never call this SQL again? *Anywhere*? Are you
*really* sure you are not calling setval, *anywhere*?

> // Open Recordset
> $q_nextval=pg_exec($dbconn, $strnextval)
> or die("Couldn't open the database --> " . pg_errormessage($q_nextval));
>
> // Need to work out why this has to be '+1'
> $nextid=pg_result($q_nextval,0)+1;

Surprised you're not getting an error here. There is a 3rd missing parameter
to the pg_result command. And this command is deprecated as of 4.2. In any
case, adding 1 to this value should be pointless and dangerous.
>
> // Breast Cancer Resources
> $strinsert_breast="INSERT INTO breast_cancer_resources (id) VALUES
> ($nextid);";

Looks OK, except you should remove the semicolon.

So--I don't see much wrong. If you make the changes I recommend, and you are
still experiencing the problem, there is still something wrong with your
code. Probably in the parts you don't show us.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster




view thread (7+ messages)  latest in thread

Message-ID: <NEBBIHDGCLBEJMPFAMLAEEEFCIAA.tom@betterhealthfoundation.org>
Permalink:  ../NEBBIHDGCLBEJMPFAMLAEEEFCIAA.tom@betterhealthfoundation.org/
Also on:    postgresql.org/message-id/NEBBIHDGCLBEJMPFAMLAEEEFCIAA.tom@betterhealthfoundation.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: tom@betterhealthfoundation.org
  Subject: Re: FW: Nextval Problems
  In-Reply-To: <NEBBIHDGCLBEJMPFAMLAEEEFCIAA.tom@betterhealthfoundation.org>
  References: <NEBBIHDGCLBEJMPFAMLAEEEFCIAA.tom@betterhealthfoundation.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox