Обсуждение: Last ID Problem
i'm setting up a data entry entry form. once the data
is entered in a pgsql, i want to have it redisplay the
blank form with the text just entered displayed.
I have it so it enters data. i'm having a problem
with permissions so i have to use the database creator
and owner to access the db.
i'm using adodb and the following code to interact
with my db...
-----------------------
$cust = $_POST['cust']; // data entered
$cust = addslashes($cust); // take care of slashes
$db = &ADONewConnection('postgres');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);
$sql = "INSERT INTO customer (customer_name) VALUES
('$cust')"; // query to insert data - works fine.
$id = "SELECT currval('cust_id')"; // used in an
attempt to get last id (colum 'cust id')entered into
db.
$result = $db->Execute($sql); // works fine.
$id_result = $db->execute($id); // $id_result has no
value.
------------------------------
does anyone know how i can structure this so that i
get the last 'cust_id' entered so that i can then use
that id to display the data just entered?
tia...
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Hi,
> $id = "SELECT currval('cust_id')"; // used in an
> attempt to get last id (colum 'cust id')entered into
> db.
>
> $result = $db->Execute($sql); // works fine.
>
> $id_result = $db->execute($id); // $id_result has no
> value.
This is because currval fetched data during a transaction process and
not after the process is complete.
the best way I suggest is
$id = "SELECT cust_id from customer where customer_name ='$cust'
order by cust_id desc limit1" ; // used in an
--
With Best Regards,
Vishal Kashyap.
Lead Software Developer,
http://saihertz.com,
http://vishalkashyap.tk
Vishal,
will your suggestion protect my db if I have two
customers with the same name? does it always return
the highest cust_id and is that *always* the last
cust_id entered?
tia...
--- "Vishal Kashyap @ [SaiHertz]"
<vishalonlist@gmail.com> wrote:
> Hi,
>
> > $id = "SELECT currval('cust_id')"; // used in an
> > attempt to get last id (colum 'cust id')entered
> into
> > db.
> >
> > $result = $db->Execute($sql); // works fine.
> >
> > $id_result = $db->execute($id); // $id_result has
> no
> > value.
>
> This is because currval fetched data during a
> transaction process and
> not after the process is complete.
>
> the best way I suggest is
>
> $id = "SELECT cust_id from customer where
> customer_name ='$cust'
> order by cust_id desc limit1" ; // used in an
>
> --
> With Best Regards,
> Vishal Kashyap.
> Lead Software Developer,
> http://saihertz.com,
> http://vishalkashyap.tk
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
On Mon, 31 Jan 2005 11:13:58 -0800 (PST),
operationsengineer1@yahoo.com <operationsengineer1@yahoo.com> wrote:
> -----------------------
>
> $cust = $_POST['cust']; // data entered
> $cust = addslashes($cust); // take care of slashes
>
> $db = &ADONewConnection('postgres');
> $db -> Connect($db_string,$db_owner,$db_pw,$db_name);
>
> $sql = "INSERT INTO customer (customer_name) VALUES
> ('$cust')"; // query to insert data - works fine.
>
> $id = "SELECT currval('cust_id')"; // used in an
> attempt to get last id (colum 'cust id')entered into
> db.
>
> $result = $db->Execute($sql); // works fine.
>
> $id_result = $db->execute($id); // $id_result has no
> value.
>
> ------------------------------
Why not first get the current value from the sequence, use it for your
INSERT statement, and then have it handy for the rest of the script?
http://phplens.com/lens/adodb/docs-adodb.htm#inserted_id
-- Mitch
Hi , > will your suggestion protect my db if I have two > customers with the same name? Yes , it would with the consideration that the sequence used to create the cust_id is increasing by some number , customer may have same name but in all means the customer name which is having the MAXIMUM cust_id is ur need. > does it always return > the highest cust_id and is that *always* the last > cust_id entered? > did i said I gave you a query that would run faster you may alternatively use $id = "SELECT max(cust_id) from customer where customer_name ='$cust' " ; Use explain in psql and u will know why I suggested the previous query. -- With Best Regards, Vishal Kashyap. Lead Software Developer, http://saihertz.com, http://vishalkashyap.tk
mitch, i tried insert_id(), however, the following...
print "Query Success! The new row has an id of: " .
$db->Insert_Id();
produced...
"Query Success! The new row has an id of: 0"
every time.
reading your suggestion, though, leads me to believe
that geting insert_id() BEFORE running the the query
may impact the results.
can you point me to a simple code example of the whole
process?
also, i recall reading something about insert_id() not
working if the db connection wasn't persistent.
--- Mitch Pirtle <mitch.pirtle@gmail.com> wrote:
> On Mon, 31 Jan 2005 11:13:58 -0800 (PST),
> operationsengineer1@yahoo.com
> <operationsengineer1@yahoo.com> wrote:
> > -----------------------
> >
> > $cust = $_POST['cust']; // data entered
> > $cust = addslashes($cust); // take care of slashes
> >
> > $db = &ADONewConnection('postgres');
> > $db ->
> Connect($db_string,$db_owner,$db_pw,$db_name);
> >
> > $sql = "INSERT INTO customer (customer_name)
> VALUES
> > ('$cust')"; // query to insert data - works fine.
> >
> > $id = "SELECT currval('cust_id')"; // used in an
> > attempt to get last id (colum 'cust id')entered
> into
> > db.
> >
> > $result = $db->Execute($sql); // works fine.
> >
> > $id_result = $db->execute($id); // $id_result has
> no
> > value.
> >
> > ------------------------------
>
> Why not first get the current value from the
> sequence, use it for your
> INSERT statement, and then have it handy for the
> rest of the script?
>
>
>
http://phplens.com/lens/adodb/docs-adodb.htm#inserted_id
>
> -- Mitch
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
__________________________________
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250
This is the easiest way to do it: http://ask.slashdot.org/article.pl?sid=05/01/31/1441200&from=rss This is using plain old SQL the PostgreSQL way ;-) Basically you: 1) get the next number from the sequence 2) do the update 3) use that number for related table insterts For an ADOdb example, this thread: http://www.phparch.com/discuss/index.php/t/372/0/ Says to use this syntax: $insert_id = $db->getone("select currval('sequence_name')"); -- Mitch
thanks mitch...
i ahve the following code...
$cust = $_POST['cust'];
$cust = addslashes($cust);
$db = &ADONewConnection('postgres');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);
$sql = "INSERT INTO customer (customer_name) VALUES
('$cust')";
$result = $db->Execute($sql);
$insert_id = $db->getone("select currval('cust_id')");
if ($result === false)
{
print $db->ErrorMsg();
exit();
}
else
{
$dbreturn = 'Passed';
print $dbreturn;
print $insert_id;
exit();
}
it prints $dbreturn as "Passed", but it does not print
any value for insert_id. i'm trying to see this value
and verify it is working correctly before trying
anything more complex.
--- Mitch Pirtle <mitch.pirtle@gmail.com> wrote:
> This is the easiest way to do it:
>
>
>
http://ask.slashdot.org/article.pl?sid=05/01/31/1441200&from=rss
>
> This is using plain old SQL the PostgreSQL way ;-)
>
> Basically you:
>
> 1) get the next number from the sequence
> 2) do the update
> 3) use that number for related table insterts
>
> For an ADOdb example, this thread:
>
>
> http://www.phparch.com/discuss/index.php/t/372/0/
>
> Says to use this syntax:
>
> $insert_id = $db->getone("select
> currval('sequence_name')");
>
> -- Mitch
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
> joining column's datatypes do not match
>
__________________________________
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250
On Mon, 31 Jan 2005 15:33:02 -0800 (PST),
operationsengineer1@yahoo.com <operationsengineer1@yahoo.com> wrote:
> thanks mitch...
>
> i ahve the following code...
>
> $cust = $_POST['cust'];
> $cust = addslashes($cust);
> $db = &ADONewConnection('postgres');
> $db -> Connect($db_string,$db_owner,$db_pw,$db_name);
> $sql = "INSERT INTO customer (customer_name) VALUES
> ('$cust')";
> $result = $db->Execute($sql);
> $insert_id = $db->getone("select currval('cust_id')");
>
> if ($result === false)
> {
> print $db->ErrorMsg();
> exit();
> }
> else
> {
> $dbreturn = 'Passed';
> print $dbreturn;
> print $insert_id;
> exit();
> }
>
> it prints $dbreturn as "Passed", but it does not print
> any value for insert_id. i'm trying to see this value
> and verify it is working correctly before trying
> anything more complex.
That is because you are doing it out of order. First, you get the
sequence id, and THEN you use that number for your INSERT statement:
$cust = $_POST['cust'];
$cust = addslashes($cust);
$db = &ADONewConnection('postgres');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);
// get the insert id FIRST
$insert_id = $db->getone("select currval('cust_id')");
// THEN issue the INSERT statement
$sql = 'INSERT INTO customer (id, customer_name) VALUES
(' . $id . ', ' . $db->qstr( $cust ) . ')';
if ( $db->Execute( $sql ) === false ){
print $db->ErrorMsg();
} else {
$dbreturn = 'Passed';
print $dbreturn;
print $insert_id;
}
I also changed around the format of your SQL statement, as it makes
sense to quote your $cust before adding to the database. So so you see
the difference? You need to get the sequence number first, and then
use it in your queries. The exit() statements were not needed, and I
wanted to show a different way of nesting your IF statement.
Note that an INSERT statement doesn't return a resultset, just a
success or fail. John's way of doing it (at least for the
documentation) are found here:
http://phplens.com/lens/adodb/docs-adodb.htm#ex3
It is a good example, as it quotes strings and uses time() as well.
-- Mitch
On Mon, Jan 31, 2005 at 03:33:02PM -0800, operationsengineer1@yahoo.com wrote:
> $cust = $_POST['cust'];
> $cust = addslashes($cust);
> $db = &ADONewConnection('postgres');
> $db -> Connect($db_string,$db_owner,$db_pw,$db_name);
> $sql = "INSERT INTO customer (customer_name) VALUES
> ('$cust')";
> $result = $db->Execute($sql);
> $insert_id = $db->getone("select currval('cust_id')");
If cust_id was defined as a serial type then you should be calling
currval() with the sequence name, not the column name. Look at the
table definition (e.g., run "\d customer" in psql) and see what the
sequence name is. It's probably customer_cust_id_seq; if so, then
following should work:
$insert_id = $db->getone("select currval('customer_cust_id_seq')");
Contrary to what another message in this thread says, it is indeed
common practice to do the insert first and call currval() afterwards
to find out what value you got from the sequence. And no, this
doesn't introduce a race condition -- currval() returns the last
value obtained from the sequence in the current session.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Mon, Jan 31, 2005 at 07:58:42PM -0500, Mitch Pirtle wrote: > That is because you are doing it out of order. First, you get the > sequence id, and THEN you use that number for your INSERT statement: Common practice when using a sequence in PostgreSQL is to do the INSERT first, then call currval() to find out what value you got. If you want to obtain the sequence value first then use nextval(), not currval() as your code showed. Calling currval() before any calls to nextval() should fail with an error like the following: currval of sequence "customer_cust_id_seq" is not yet defined in this session -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Mon, 31 Jan 2005 18:38:55 -0700, Michael Fuhr <mike@fuhr.org> wrote:
>
> Contrary to what another message in this thread says, it is indeed
> common practice to do the insert first and call currval() afterwards
> to find out what value you got from the sequence. And no, this
> doesn't introduce a race condition -- currval() returns the last
> value obtained from the sequence in the current session.
Tell that to the maintainers of PEAR's DB, which is installed by
default with all recent versions of PHP (that would be all of them). I
felt the exact same way as you did, and spent an afternoon
rediscovering the joys of sequence values until one of the maintainers
pointed out that behavior. I even tried to convince them that this was
a bug ('inappropriate behavior' was the term IIRC)...
'Common', unfortunately, is relative; and in this matter might only
apply to ADOdb ;-)
-- Mitch, getting his PHP database classes all mixed up *gasp*
On Mon, Jan 31, 2005 at 08:55:22PM -0500, Mitch Pirtle wrote:
> On Mon, 31 Jan 2005 18:38:55 -0700, Michael Fuhr <mike@fuhr.org> wrote:
> >
> > Contrary to what another message in this thread says, it is indeed
> > common practice to do the insert first and call currval() afterwards
> > to find out what value you got from the sequence. And no, this
> > doesn't introduce a race condition -- currval() returns the last
> > value obtained from the sequence in the current session.
>
> Tell that to the maintainers of PEAR's DB, which is installed by
> default with all recent versions of PHP (that would be all of them). I
> felt the exact same way as you did, and spent an afternoon
> rediscovering the joys of sequence values until one of the maintainers
> pointed out that behavior. I even tried to convince them that this was
> a bug ('inappropriate behavior' was the term IIRC)...
I don't use DB so I can't comment on what its maintainers should
or shouldn't be doing. Abstraction layers sometimes do things in
ways that are easy to implement across multiple systems, so the
maintainers might have portability concerns.
I'm not saying that doing the INSERT first and then calling currval()
is the "right" way, just that it's a common way, one that's often
suggested on the PostgreSQL mailing lists. One argument in its
favor is that you can use the same INSERT statement regardless of
whether you need the sequence number or not, so that's one less
thing to maintain if your needs change in that respect.
> 'Common', unfortunately, is relative; and in this matter might only
> apply to ADOdb ;-)
The world's bigger than a couple of PHP modules :-) Calling currval()
after an INSERT is a common way to get the sequence value when using
PostgreSQL, regardless of the programming language or API being
used.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
> On Mon, Jan 31, 2005 at 08:55:22PM -0500, Mitch Pirtle wrote:
>> 'Common', unfortunately, is relative; and in this matter might only
>> apply to ADOdb ;-)
> The world's bigger than a couple of PHP modules :-) Calling currval()
> after an INSERT is a common way to get the sequence value when using
> PostgreSQL, regardless of the programming language or API being
> used.
His point stands though: if you are accessing Postgres through some kind
of connection-pooling software, currval() cannot be trusted across
transaction boundaries, since the pool code might give your connection
to someone else. In this situation the nextval-before-insert paradigm
is the only way.
(But in most of the applications I can think of, your uses of currval
subsequent to an INSERT ought to be in the same transaction as the
insert, so are perfectly safe. If your connection pooler takes control
away from you within a transaction block, you need a less broken
pooler...)
regards, tom lane
On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote: > > His point stands though: if you are accessing Postgres through some kind > of connection-pooling software, currval() cannot be trusted across > transaction boundaries, since the pool code might give your connection > to someone else. In this situation the nextval-before-insert paradigm > is the only way. I don't disagree with that; if the thread mentioned connection pooling then I must have overlooked it. > (But in most of the applications I can think of, your uses of currval > subsequent to an INSERT ought to be in the same transaction as the > insert, so are perfectly safe. If your connection pooler takes control > away from you within a transaction block, you need a less broken > pooler...) That's the common situation I was talking about: doing an INSERT and immediately calling currval(), presumably in the same transaction. I should have been more clear about that and warned what could happen in other situations. Thanks. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
> On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote:
>> His point stands though: if you are accessing Postgres through some kind
>> of connection-pooling software, currval() cannot be trusted across
>> transaction boundaries, since the pool code might give your connection
>> to someone else. In this situation the nextval-before-insert paradigm
>> is the only way.
> I don't disagree with that; if the thread mentioned connection
> pooling then I must have overlooked it.
>> (But in most of the applications I can think of, your uses of currval
>> subsequent to an INSERT ought to be in the same transaction as the
>> insert, so are perfectly safe. If your connection pooler takes control
>> away from you within a transaction block, you need a less broken
>> pooler...)
> That's the common situation I was talking about: doing an INSERT
> and immediately calling currval(), presumably in the same transaction.
> I should have been more clear about that and warned what could
> happen in other situations. Thanks.
Apropos to all this: Tatsuo recently proposed a RESET CONNECTION command
that could be used to reset a connection between pooling assignments, so
as to be sure that different pooled threads wouldn't see state that
changes depending on what some other thread did. It seems like RESET
CONNECTION ought to reset all currval() states to the "error, currval
not called yet" condition. Comments?
regards, tom lane
Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote:
> >> His point stands though: if you are accessing Postgres through some kind
> >> of connection-pooling software, currval() cannot be trusted across
> >> transaction boundaries, since the pool code might give your connection
> >> to someone else. In this situation the nextval-before-insert paradigm
> >> is the only way.
>
> > I don't disagree with that; if the thread mentioned connection
> > pooling then I must have overlooked it.
>
> >> (But in most of the applications I can think of, your uses of currval
> >> subsequent to an INSERT ought to be in the same transaction as the
> >> insert, so are perfectly safe. If your connection pooler takes control
> >> away from you within a transaction block, you need a less broken
> >> pooler...)
>
> > That's the common situation I was talking about: doing an INSERT
> > and immediately calling currval(), presumably in the same transaction.
> > I should have been more clear about that and warned what could
> > happen in other situations. Thanks.
>
> Apropos to all this: Tatsuo recently proposed a RESET CONNECTION command
> that could be used to reset a connection between pooling assignments, so
> as to be sure that different pooled threads wouldn't see state that
> changes depending on what some other thread did. It seems like RESET
> CONNECTION ought to reset all currval() states to the "error, currval
> not called yet" condition. Comments?
TODO update:
* Add RESET CONNECTION command to reset all session state
This would include resetting of all variables (RESET ALL), dropping of
all temporary tables, removal of any NOTIFYs, cursors, prepared
queries(?), currval()s, etc. This could be used for connection pooling.
We could also change RESET ALL to have this functionality.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
> That is because you are doing it out of order.
> First, you get the
> sequence id, and THEN you use that number for your
> INSERT statement:
>
> $cust = $_POST['cust'];
> $cust = addslashes($cust);
> $db = &ADONewConnection('postgres');
> $db ->
> Connect($db_string,$db_owner,$db_pw,$db_name);
> // get the insert id FIRST
> $insert_id = $db->getone("select
> currval('cust_id')");
> // THEN issue the INSERT statement
> $sql = 'INSERT INTO customer (id, customer_name)
> VALUES
> (' . $id . ', ' . $db->qstr( $cust ) . ')';
>
> if ( $db->Execute( $sql ) === false ){
> print $db->ErrorMsg();
> } else {
> $dbreturn = 'Passed';
> print $dbreturn;
> print $insert_id;
> }
>
> I also changed around the format of your SQL
> statement, as it makes
> sense to quote your $cust before adding to the
> database. So so you see
> the difference? You need to get the sequence number
> first, and then
> use it in your queries. The exit() statements were
> not needed, and I
> wanted to show a different way of nesting your IF
> statement.
>
> Note that an INSERT statement doesn't return a
> resultset, just a
> success or fail. John's way of doing it (at least
> for the
> documentation) are found here:
>
> http://phplens.com/lens/adodb/docs-adodb.htm#ex3
>
> It is a good example, as it quotes strings and uses
> time() as well.
>
> -- Mitch
>
mitch and all, i've developed a simple little script
in order to test the "last id" methodology mitch
suggested.
it looks like this... php and adodb include excluded
for brevity...
-----
$db = &ADONewConnection('postgres7');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);
$insert_id = $db->getone("select
nextval('public.customer_cust_id_seq')");
print 'The ID is ' . $insert_id;
-----
my sequence name is 'public.customer_cust_id_seq'
(found this in pgadmin3).
the last id number in my table is 65. when i use
nextval(), i get a result of 66 for $insert_id - which
is the value that i would want to then perform and
insert.
however, when i use currval(), as recommended, i get
no result. i probably get an error, but i haven't
checked for that yet.
is it OK to use nextval() to get the next id value in
the sequence before doing an insert? how come
currval() doesn't work.
thanks to all for any guidance here.
__________________________________
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250
> mitch and all, i've developed a simple little script
> in order to test the "last id" methodology mitch
> suggested.
>
> it looks like this... php and adodb include
> excluded
> for brevity...
>
> -----
> $db = &ADONewConnection('postgres7');
> $db ->
> Connect($db_string,$db_owner,$db_pw,$db_name);
> $insert_id = $db->getone("select
> nextval('public.customer_cust_id_seq')");
>
> print 'The ID is ' . $insert_id;
> -----
>
> my sequence name is 'public.customer_cust_id_seq'
> (found this in pgadmin3).
>
> the last id number in my table is 65. when i use
> nextval(), i get a result of 66 for $insert_id -
> which
> is the value that i would want to then perform and
> insert.
>
> however, when i use currval(), as recommended, i get
> no result. i probably get an error, but i haven't
> checked for that yet.
>
> is it OK to use nextval() to get the next id value
> in
> the sequence before doing an insert? how come
> currval() doesn't work.
>
> thanks to all for any guidance here.
mitch and all, i noticed that if i keep refreshing my
page that the $insert_id keeps growing... 66 then 67
then 68 then 69.
i guess this makes sense, after all, the "next value"
is always and icnrement higher. this makes me a
little nervous, though.
i only want one value... the next id i should use to
perform an insert.
i'm hoping currval() does the trick, however, i'm
getting no result.
do i need to instruct the sequence to go to it last
value before calling currval()?
tia...
__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail
> mitch and all, i noticed that if i keep refreshing > my > page that the $insert_id keeps growing... 66 then > 67 > then 68 then 69. > > i guess this makes sense, after all, the "next > value" > is always and icnrement higher. this makes me a > little nervous, though. > > i only want one value... the next id i should use > to > perform an insert. > > i'm hoping currval() does the trick, however, i'm > getting no result. > > do i need to instruct the sequence to go to it last > value before calling currval()? > > tia... another point of interest. now that i've been refreshing my nextval() statement, my highest cust_id value is 65, but nextval() keeps incrementing from where it was before. reading through the manual, this is designed in behavior. it looks like i could reset the value using setval(). i also noticed that currval() returns the value of the last nextval(). this infers that i must call nextval() prior to being able to get currval(), yet nextval() was not included in the original suggestion. am i missing something here? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Wed, Feb 09, 2005 at 08:10:57AM -0800, operationsengineer1@yahoo.com wrote:
> however, when i use currval(), as recommended, i get
> no result. i probably get an error, but i haven't
> checked for that yet.
Error checking is A Good Thing.
When I suggested using currval() I wasn't necessarily recommending
it over nextval() (although I usually prefer it); I was pointing
out that nextval() isn't the only method and that currval() is
common practice. Not that common practice makes it a good idea,
but rather that it's common practice because it works if used
properly and it can be convenient. Depending on what you're doing,
you can use currval() in a subsequent INSERT or UPDATE without
having to fetch the ID into the client code at all:
INSERT INTO foo (name) VALUES ('some name');
INSERT INTO log (fooid) VALUES (currval('foo_fooid_seq'));
> is it OK to use nextval() to get the next id value in
> the sequence before doing an insert?
Yes. If you defined a SERIAL column, that's what the column's
default expression does.
> how come currval() doesn't work.
You didn't show your currval() code so we can't say for sure why
it doesn't work. Did you call currval() *after* the INSERT? Did
you call currval() in the same connection as the INSERT? As has
been pointed out, if you use connection pooling and your call to
currval() ends up in a different connection than the INSERT, then
it won't work.
The following works for me:
$db = ADONewConnection($driver);
# $db->debug = true;
$db->Connect($connectstr);
$db->Execute("INSERT INTO foo (name) VALUES ('test')");
$id = $db->getone("SELECT currval('foo_id_seq')");
print "last insert id = $id<br>\n";
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
> another point of interest. now that i've been > refreshing my nextval() statement, my highest > cust_id > value is 65, but nextval() keeps incrementing from > where it was before. reading through the manual, > this > is designed in behavior. > > it looks like i could reset the value using > setval(). > > i also noticed that currval() returns the value of > the > last nextval(). this infers that i must call > nextval() prior to being able to get currval(), yet > nextval() was not included in the original > suggestion. > > am i missing something here? > > tia... > actually, michael fuhr addressed this issue in this group on 1/31/05. not sure why it didn't register. so, i should use nextval() if i use mitch's methodology of calling the id first then performing the insert. giving that nextval increments on a refresh, is there anything i need to worry about? or is the worst case scenario a gap in unique ids? this shouldn't matter as far as i can tell - as long as the numbers are unique. tia... __________________________________ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250
On Wed, Feb 09, 2005 at 08:53:18AM -0800, operationsengineer1@yahoo.com wrote: > so, i should use nextval() if i use mitch's > methodology of calling the id first then performing > the insert. Right. You can call nextval() first and then explicitly insert the value you obtained, or you can do the insert first and let the serial column's default expression call nextval() automatically, and you can then find out the value it used with a subsequent call to currval(). > giving that nextval increments on a refresh, is there > anything i need to worry about? nextval() increments the sequence each time it's called. If you're using the sequence values as keys, then that's what you need. Presumably you'd only call nextval() when you're going to insert a new record, so what's the concern? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
> Why not first get the current value from the > sequence, use it for your > INSERT statement, and then have it handy for the > rest of the script? i hate to revisit this old topic, however, i just want to make sure i'm avoiding future problems. if i pull nextval then insert it into the id column where i pulled it from, will the counter ever get "off track" since i'm doing manual inserts? can nextval ever become a value that's unexpected? in practice, this method is working very well, but i'm paranoid. ;-) __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Wed, May 04, 2005 at 09:21:52 -0700, operationsengineer1@yahoo.com wrote: > > Why not first get the current value from the > > sequence, use it for your > > INSERT statement, and then have it handy for the > > rest of the script? > > i hate to revisit this old topic, however, i just want > to make sure i'm avoiding future problems. > > if i pull nextval then insert it into the id column > where i pulled it from, will the counter ever get "off > track" since i'm doing manual inserts? What do you mean by off track? nextval will return a unique value each time it is called. If you want to refer to the last value returned by nextval in the same session, you should use currval. > can nextval ever become a value that's unexpected? in > practice, this method is working very well, but i'm > paranoid. ;-) When you reach the maximum value for the sequence then you will get an error. The default maximum value for 8.0 appears to be 9223372036854775807. If you store the value in a 4 byte integer, then you will have problems sooner.