My habit is to use the ID Param at the INSERT stage, which is consistent with Adam's script. I don't have any experience with GUID primary key fields, but in other field types the ID is available as part of the INSERT statement.

 aQry.SQL.Add('INSERT INTO Run (RunID, StartDT, EndDT, Note, EventType)'+
              ' VALUES (:RunID, :StartDT, :EndDT, :Note, :EventType);');
 aQry.Prepare;
///params for all BUT RunID, as it is generated ----
 aQry.ExecSQL;
 iNewRunID := aQry.ParamByName('RunID').AsInteger; // --- now the new value is available

Peter

IDENTITY is only for Autoincrement Integers.

I don't believe there is a way to get the last assigned GUID of a table,
even when it's a primary key.

What I've done (I use GUIDs as PKs in most of my tables) is only insert
a record with a known unique value and then when I need the GUID, search
for that unique value.  The tables I have where I need the GUID
immediately after inserting a record also happen to have a "document
number" in the application that is strictly for the end user and is also
required to be unique with a constraint on the table.

Another thing you can do is generate the GUID before adding the record
and pass that in to the INSERT statement.  Then you KNOW what it is!

--
David Cornelius
Cornelius Concepts

On 11/19/12 22:35, Barry wrote:
> I was hoping I could execute:
>
> select lastidentity('mytable','cust_id');
>
> and this would return the GUID that was generated for 'cust_id' column,
> Cust_Id is GUID and is defined with a default to "Current_GUID()" and it is the primary key.
>
> But LastIdentity() on a GUID column always returns 0 after the row is inserted.
>
> So how do I get the Cust_Id column value that was just inserted?
> BTW, I don't see any way of defining a GUID column as identity.
>
> Am I missing something?
>
> TIA
> Barry
>

>John
As ever an interesting approach. However, I think you've solved the wrong problem

My interpretation of what Barry wants is to use it as an automatically generated key. You can do that by creating it as GENERATED and using CURRENT_GUID as the expression. <

Roy, you don't want to use Generated, because the GUID gets regenerated *every* time the row is saved. I tried that once and it creates quite a mess with RI for the other tables. VBG

Instead you need to use Current_GUID() as the DEFAULT value for the column so it gets assigned only when a new row is inserted.

>However, you can't create it as an IDENTITY hence LastIdentity doesn't work. You can only create identities using the various types of integers.<

WHY just integers? A GUID is just as much of an identity as an AutoInc, perhaps more so.  I figure there should be a LastIdentityStr function should return a string of the last identity value entered.

Actually John Hay is closer to a solution than anyone else.

Since the problem is when I am adding a row to the table in Delphi, all I have to do is execute:
   select Current_GUID();
*then* use that returned value as the GUID column value when inserting the row. I won't need to retrieve the identity column *after* the post, because I got it *before* the post. Like any column with a default value, the default value can be overridden if a value is supplied for the column. The default value only gets assigned if the column value is NULL during posting.

And unlike the sequential AutoInc values, if the transaction fails, I can throw away the GUID value without creating any holes and I don't have to worry about some other session "stealing" the same GUID.

Once again thanks for everyone's participation. Another problem solved in record time.

Barry

>Another thing you can do is generate the GUID before adding the record
and pass that in to the INSERT statement.  Then you KNOW what it is!
<

Right you are. I didn't see your message until after I posted a solution. (I should type faster!)

Barry

Barry

> And unlike the sequential AutoInc values, if the transaction fails, I can throw away the GUID value without creating
any holes and I don't have to worry about some other session "stealing" the same GUID.
>

I am curious as to why you are using a guid instead of any identity field.  As far as I can see, if you are using guids,
you are not worried about holes and an identity field can't (or cetainly shouldn't) be "stolen" by another session.

I use guids mainly if I have a process where I am creating a batch of related records in multiple tables offline which
then need to be uploaded to a central database.  This simplifies the uploading while maintaining the correct master
child relationships.

John

Barry

>Roy, you don't want to use Generated, because the GUID gets regenerated *every* time the row is saved. I tried that once and it creates quite a mess with RI for the other tables. VBG

Woops. One of these days I'll get generated and computed sussed

Roy

"John Hay" wrote:

>I am curious as to why you are using a guid instead of any identity field.  As far as I can see, if you are using guids,
you are not worried about holes and an identity field can't (or cetainly shouldn't) be "stolen" by another session.

I use guids mainly if I have a process where I am creating a batch of related records in multiple tables offline which
then need to be uploaded to a central database.  This simplifies the uploading while maintaining the correct master
child relationships.<

I use GUID's because there will be a database at different locations (branch) where they will "own" the data. They will enter and maintain the data. There could be a dozen of these branches operating independently of each other. That is phase 1 of the project. In phase 2 of the project, I know "head office" will want to collect the data so they can run analysis on it, using a single database. GUID's eliminate the problem with collisions when merging AutoInc's into one database.

I know I could have used a compound index (Branch_Id, AutoInc) on the records, but this creates problems if head office decides to add their own data to the database. Since I'll be using replication, GUID's make a lot of sense in this scenario.

Barry

> I know I could have used a compound index (Branch_Id, AutoInc) on the records, but this creates problems if head
office decides to add their own data to the database. Since I'll be using replication, GUID's make a lot of sense in
this scenario.
>

I definitely prefer your guid approach for creating primary keys in the scenario you describe.

John

Barry


Out of interest are you using it as a primary key or a secondary key?

Roy Lambert

>Out of interest are you using it as a primary key or a secondary key?<

Primary Key.

I received a reply from Tim about the problem about getting the GUID that was just inserted. It looks like Tim has pulled another rabbit out of his hat.  

I'll post his reply below:

If you want to get it for an SQL insert, you can do something like this:

EDBQuery1.SQL.Clear;
EDBQuery1.SQL.Add('INSERT INTO Table1 (ID, Text1)');
EDBQuery1.SQL.Add('VALUES(:ID, :Text1)');
EDBQuery1.Prepare;
EDBQuery1.ParamByName('Text1').AsString:='Some text';
EDBQuery1.ExecSQL;
ShowMessage(EDBQuery1.ParamByName('ID').AsString);

If you have any other questions, please let me know.

Tim Young
Elevate Software