SELECT SCOPE_IDENTITY()

[stanlyn]

Hi,

1. How does one get the identity value of a newly created child record? I read that identity() is not reliable and that SCOPE_IDENTITY() is?

2. How does one implement this in a CA?

Thanks,
Stanley

 

[Olaf Doschke]

Use uniqueidentifier, default newid() on SQL Server. Make it an updateable field, so you can also generate keys on the VFP side, when creating new records in CA cursors

Use this code as GUID.prg on VFP side to create uniqueidentifier strings via calling UniqueIdentifier():

Procedure BinaryGUID()
   * create a binary Char(16) GUID
   Local lcBuffer
   
   lcBuffer = Space(16)+Chr(0)
   =CoCreateGuid(@lcBuffer)
   Return Left(lcBuffer,16)
Endproc

Procedure FullGUID()
   * create GUID in the format for a Char(38) field
   * {4DDF8EA2-30F5-4016-9554-5B9C16FA2D91}
   Local lcBuffer
   
   lcBuffer = Space(128)
   =StringFromGUID2( BinaryGUID(), @lcBuffer, Len(lcBuffer) )
   
   Return Left(Chrtran(lcBuffer,Chr(0),''),38)
Endproc

Procedure UniqueIdentifier()
   * create GUID in the format, which SQL Server displays and returns, needs Char(36)
   * 4DDF8EA2-30F5-4016-9554-5B9C16FA2D91
   Return Chrtran(FullGUID(),'{}','')
Endproc

Procedure CoCreateGuid(tcBuffer)
   Declare Integer CoCreateGuid In ole32;
      STRING @ pguid

   Return CoCreateGuid(@tcBuffer)
Endproc

Procedure StringFromGUID2(tcGUID, tcBuffer, tnMaxlen)
   Declare Integer StringFromGUID2 In ole32;
      STRING    rguid,;
      STRING  @ lpsz,;
      INTEGER   cchMax   

   Return StringFromGUID2(tcGUID, @tcBuffer, tnMaxlen)
Endproc

If you still want to go with IDENTITY, look into AfterInsert Event and InsertCmdRefreshCmd. The pint is, the ID is generated at Tableupdate only, if you just APPEND to the CA cursor or insert into it, nothing is done on the server side yet.

If you would want to use IDENTITY, you can only see IDs after inserting into sql server, as only the server generates the values.

Creating your own uniqueidentifier values helps simplifying to generate parent/child primary/foreign keys even before submitting to SQL Server. This is the only uncomplicated way to go.

Bye, Olaf.

 

[Mike Lewis]

Stanley,

Your mention of SCOPE_IDENTITY() suggests that you are usign SQL Server? Is that right?

If so, you have basically three options:

SCOPE_IDENTITY()
IDENT_CURRENT()
and
@@IDENTITY

The choice depends on various circumstances. You can find a detailed discussion of the differences here:
Find the IDENTITY value of the last inserted row

If yo are using something other than SQL Server as your back end, let us know what it is.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

True, Mike,

this is a useful resource and it turns out SCOPE_IDENTIY() is already a good choice for a multiuser environment to determin the last ID your session inserted into a certain table.

It's just the CA does not automatically refresh a cursor after Tableupdate().

But the problem begins even earlier, if you want to add new data to a hierarchy of tables and create this hierarchy with keys used as childkeys and especially if you also need childkeys used as grandchild keys, before the server creates them.

A solution obviously is to commit early, eg to insert an order, before orderitems are added. The SQL Server would be storing the orderdata as far as it's known and you'd edit it "live", instead of keeping the entry completely at the client, until you finish.

Typical internet shops go that route, as you don't have decent client side storage anway, only cookies in a very limited way. That will change with more modern browsers and HTML5, but the live editing is not unconventional or bad, still I'd always opt for decoupled data entry.

The problem with that is you can't relay that problem to the storage of data, if you want to show child data during the data entry process, you need a relation of the data right away, and uniqueidentifiers are a solution to that, as they can be created decoupled from the central database and will sill not cause doublettes. It's also a plus for distributed databases and replication.

The reasoning to only let the database server create keys is only important and valid, if you have the problem of doublettes of primary keys, otherwise. But that's not the case with uniqueidentifiers.

This takes out the need to refresh CA data after inserts, you already know what is inserted including primary key. If you like to have integer keys for backward compatibility, you can still keep identity integer fields as secondary key fields of your SQL Server table, but you don't need to know their value for relating data.

Bye, Olaf.

 

[stanlyn]

Olaf,

I'm now using you above routine successfully. I had already decided that I was not trusting SQL Server (well, actually me and my limited sql skill set) for trying to get the identity key from the server. Instead, I'll handle all that myself with 100% confidence that the results are correct. I'm simply to new to SQL Server to rely on SQL...

Actually, in all the VFP stuff that I've done, I've always used this seperated approach ... At least I know what is going on all the time, so I was a little suprised when you mentioned it over the new "automated by the tool" way. Ever table stands alone and when I need to fill a child cursor, populate it with a key lookup and fill it. Back long ago, I was amazed that the speed of VFP made it all look as if the data was all in a single table. Actually, my results with sql is just as fast...

Thanks for the code,
Stanley