Обсуждение: 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?

Mike

Personally, if you are trying to do what I think you are trying to do,
I accomplish it with a 'trick' (aka kludge).

When users open my Access 2000 app I add a record to an Access table
called 'sessions' and keep track of the session_id which is created.
The postgres records I need id's from have the session id stored in them
at creation with the rest of the data, as well as a timestamp.

so I do the following to get the key value of the record I just
created:

MyNow = Now()

rsData.AddNew
....
rsData!creation_date = MyNow
rsData!session_id = MySession
....
rsData.Update

then I run a query which asks for the key value of the record with
session_id = MySession and creation_date = MyNow.

Works every time
Lots of luck

Fred


>>> Mike Fahey <mfahey@enter.net> 11/12/2004 9:36:23 AM >>>
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@postgresql.org)

Hello,

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

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

The problem comes when we start talking about Access and subforms. That's pretty useful to use subforms in Access.
Exceptif you use an "add" button along with your subform, you won't be able to used a stored procedure for insert data
intothe "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
@@IDENTITYvariable. So until the PGSQL Driver supports @@IDENTITY as well - what would be great - requery all the lines
inthe subform after each insertion! 

I hope this helps,

Philippe Lang

-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.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@postgresql.org)