How to get the last inserted value of a GUID field

3	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? foreign-key asked 14 Jan '10, 13:04 Martin 9.0k●130●169●257 accept rate: 14% flat view

2 Answers:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 14 Jan '10, 14:08

RobertDD
489●16●17●19
accept rate: 42%

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.

(14 Jan '10, 15:14) Volker Barth

@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.

(14 Jan '10, 15:19) Volker Barth

@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.

(14 Jan '10, 20:01) RobertDD

@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:)

(14 Jan '10, 21:02) Volker Barth

@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!

(15 Jan '10, 20:47) RobertDD

flat view

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 :)

permanent link

answered 15 Jan '10, 08:38

Breck Carter
32.5k●541●726●1050
accept rate: 20%

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

foreign-key ×31

question asked: 14 Jan '10, 13:04

question was seen: 4,919 times

last updated: 15 Jan '10, 08:38

SAP SQL Anywhere

How to get the last inserted value of a GUID field

Follow this question

Related questions