cancel
Showing results for 
Search instead for 
Did you mean: 

How to get the last inserted value of a GUID field

MCMartin
Participant
5,316

If you use a GUID column as a primary key, how to get the latest inserted value? Does a mechanism like @@identity for autoincrements exists?

Accepted Solutions (2)

Accepted Solutions (2)

Former Member

While I do not know the answer to this question, you don't need it.

If you need to know the GUID primary key of a records after insertion, you should just obtain the GUID yourself (you can use NewID() on the database, or use a feature in the programming language you use to write the software to access the data in the database, the beauty of GUIDs is that it doesn't matter), then insert the record into the database with that GUID. That way you know the primary key of a record even before you add that record to the database.

If you choose to use NewID(), you are still making the same amount of round trips to the database as if you had used @@Identity.

VolkerBarth
Contributor

In that respect, calling NewID() beforehand instead of letting the engine supply it as a default value is comparable with the use of get_identity() for default (global) autoincrement.

VolkerBarth
Contributor

@RobertDD: The fact that one can get the "next" GUID value beforehand with NewID() or with a facility of the DB access framework isn't "more beautiful" than with DEFAULT AUTOINCREMENT, as you can do the same with the latter. In that respect, they are both "beautiful", methinks.

Former Member
0 Kudos

@Volker: Not entirely true. When I use GUIDs as primary keys in my code, I can create a valid structure of objects in code that refer to one another by primary/foreign key before I ever save anything to the database, which has lots of neat advantages.

VolkerBarth
Contributor
0 Kudos

@RobertDD: I guess you can do exactly the same with PK values generated beforehand with get_identity(). If you don't think so, I would like to know the reason:)

Former Member
0 Kudos

@Volker: You are correct. I was not aware that you could do that. There is a tiny issue with having more than one autoincrementing column (but why would you have that anyway?) on a table, but other than that you'd get the same advantages. Get_Identity() is actually pretty cool, I will have to add that to my box of tricks!

Breck_Carter
Participant

I don't think there is anything like a @@guid (@@uuid?) global variable, but IMO there should be... sometimes it is a LOT more convenient to ask SQL Anywhere after-the-fact about what it just generated for you.

In particular, if you have written some event-driven code where there is no parameter passing between different units of code, it can be hard to code the alternative: calculate the guid ahead of time and pass it to all the bits of code that need it.

One kludgy alternative might be to use your own connection-level variable to capture the value from a freshly-inserted row and then SELECT that connection-level variable wherever you need the latest value... kode available on request :)... CREATE VARIABLE is at the heart of this technique.

BTW the Help calls @@identity a "global variable" but it really isn't... it is a pre-defined connection-level variable. The value of a true global would be available across all connections.

SQL Anywhere does have true globals, they are called "tables" <g>

Yet another aside: CREATE VARIABLE is truly magical. The following post does NOT address your question, but it does include code that shows how CREATE VARIABLE can create a different kind of cross-process communication path where none existed previously http://sqlanywhere.blogspot.com/2008/02/tip-triggering-audit-trail.html ...of course, all of this is heresy, it's not politically correct to discuss globals in the Brave New World of OOPS 🙂

Answers (0)