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?
asked 14 Jan '10, 13:04
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.
answered 14 Jan '10, 14:08
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 :)
answered 15 Jan '10, 08:38