Re: Getting the last inserted id

Hello,

Yes, you are right, @@IDENTITY is Microsoft only. We had a discussion about that one year ago. Search for "@@IDENTITY" in the past discussions.

For inserting values into the database, the best solution I found was to use a stored procedure to do that job, and give me the id back. It gives you the opportunity to do different initialisations when inserting data into the database as well. That's useful.

The problem comes when we start talking about Access and subforms. That's pretty useful to use subforms in Access. Except if you use an "add" button along with your subform, you won't be able to used a stored procedure for insert data into the "subtable". ADP projects are limited to MS SQL Server.
In this case, a weird thing can happen: you add a line through a subform, and Access fetches the wrong id back in the subform. This is all explained in the past discussion I talked about. I guess this is why Microsoft started using their @@IDENTITY variable. So until the PGSQL Driver supports @@IDENTITY as well - what would be great - requery all the lines in the subform after each insertion!

I hope this helps,

Philippe Lang

-----Message d'origine-----
De : pgsql-odbc-owner(at)postgresql(dot)org
[mailto:pgsql-odbc-owner(at)postgresql(dot)org]De la part de Mike Fahey
Envoyé : vendredi, 12. novembre 2004 18:36
À : Postgres list
Objet : [ODBC] Getting the last inserted id

So I guess the question still remains, how do you get the last inserted
id from a sequence?

I'm using access 2003 & postgres odbc 8.0.02

select @@IDENTITY seems to be MS Specific and not work with postgres.

calling currval has to be a pass-through query which makes it high
complicated if at all
possible.

How does everyone else do this ?

How does any database maintain referential integrity if this doesn't work?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)