> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org