on 2010 Jan 14 1:04 PM
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?
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
@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.
@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!
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 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.