SCOPE_IDENTITY() and remote views in VFP

By Mike Lewis

In a recent Hex Central article, Lynn Evans recommended using the SCOPE_IDENTITY() function to find the primary key of the most recently inserted row in a SQL Server table. That function is a better choice than @@IDENTITY or IDENT_CURRENT(), because it is specific to the session and the scope.

As a Visual FoxPro developer, I often use SCOPE_IDENTITY() when inserting records via SQL Pass-Through (SPT). But I recently found that it doesn't work with remote views. Specifically, if you use a view to insert a row, then use SQLEXEC() to send a call to SCOPE_IDENTITY(), you always get back a NULL - even if the SPT call uses the same connection handle as the view. That's because the two operations occur in different scopes.

Fortunately, there's a workaround. Instead of SCOPE_IDENTITY(), you should use @@IDENTITY to return the required primary key. @@IDENTITY returns the value of the primary key (more specifically, the value of whichever column has an IDENTITY attribute) for the last inserted row in the current connection, regardless of scope.

For that to work, the @@IDENTITY call must use the same connection handle as the remote view. In summary, the relevant code might look something like this.

* Open the view

USE MyView IN 0 ALIAS MyView

* Set buffering

CURSORSETPROP("Buffering", 5, "MyView")

* Store the view's connection handle

lnConn = CURSORGETPROP("ConnectHandle", "MyView")

* Add a record

INSERT INTO MyView (cust, amount) VALUES ("abc", 100)

* Commit it

TABLEUPDATE(.T., "MyView")

* Get the IDENTITY value into a cursor, using the

* remote view's connection handle

SQLEXEC(lnConn, "SELECT @@IDENTITY AS Ident", "csrTemp")

* Store the IDENTITY value

lnIdent = csrTemp.Ident

There's one important caveat. If the table being updated has a trigger, and if that trigger itself inserts a row into a table, then, in certain circumstances, @@IDENTITY will return the primary key of the table that the trigger is updating, which is not what you want (Lynn explains this point in more detail in her article). If this isn't an issue for you, you can safely use @@IDENTITY when using a Visual FoxPro remote view to add rows to a SQL Server table.