Re: Last ID Problem - Mailing list pgsql-novice

> 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