Remote views and getting the PK after committing data.

[Chris Miller]

Since we had a discussion about this and Mike Lewis Hexcentral article about SCOPE_IDENTITY() not working, I thought about a general way.

The solution is using REFRESH() instead of REQUERY(). There's just a bummer in the description of REFRESH(): You already have to know the PK to use it to refresh your view records, and that's the very thing we don't know and want to refresh, so this seems like a deed end.

I already proposed the idea to add a column for storing something specific to your process so you can re-identify your own (new) records. To bring these two things together, REFRESH() could solve the refresh of the id if the additional column could be the PK temporarily, ie during the INSERT. And I was astonished: It's possible to change KeyFieldList of the view workarea.

So these are the ingredients:

[ol 1]
[li]Define your remote view as usual with the PK column and updatable columns. In the SQL you'll find something like

DBSetProp(ThisView+".id","Field","KeyField",.T.)

[/li]
[li]Add a tempid column to the table (maybe uniqueidentifier or an int, but no identity, no default value) and make that a sparse column.[/li]
[li]In the view definition, this needs to be added as a normal updatable column, not a KeyField, yet it will be temporally used for KeyFieldList.
[/li]
[li]Think of what to store in tempid, either you generate GUIDs or maybe use a sequence in the database. When you add records you set this column uniquely in your application in the view.
[/li][/ol]

Now to commmit your new view records with tempids set keyfieldlist to 'tempid' (by CURSORSETPROP, not by DBSETPROP). After the TABLEUPDATE you do a REFRESH which also uses tempid as the PK. Then set keyfieldlist back to the normal PK 'id'. To tidy up the database replace all current tempids with NULL in the view and commit that with another TABLEUPDATE. As tempid is sparse, NULLs are effectively not stored in comparison to a normal nullable column, so this will only slightly increase the database size, even if you introduce this to all your backend tables.

I don't recommend using tempid instead of id to simply make this a replacement for IDENTITY. Other database administration and maintenance and other client applications, which are not using tempid are still better served with a server-side generated key. Not making use of tempid it'll be NULL and that's fine.


Chriss

 

[Mike Lewis]

Chris,

That all sounds good. But do you have any thoughts about using @@IDENTITY instead of SCOPE_IDENTITY() ?

The problem with SCOPE_IDENTITY() is that it must be executed in the same scope as the INSERT, which will not be the case if you do the INSERT via a remote view (even if it shares a connection handle with the SCOPE_INDENTITY() ). @@IDENTITY doesn't have that problem.

(But I think you mentioned somewhere else that @@IDENTITY can sometimes cause problems with records inserted via a tigger. I don't recall the details.)

By the way, it's worth noting that all this is specific to SQL Server. Other back-ends will work differently in this respect.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vernpace]

SQL Server 2008 introduced the OUTPUT clause for the INSERT command - @@IDENTITY and SCOPE_IDENTITY() have been passe for years. Below is a section of a function demonstrating getting the primary key after an INSERT:

lcCmdIns = "INSERT INTO lbfolder (ikey_parent, lisvault, vfolder) [b]OUTPUT inserted.ikey_folder AS ikey_folder[/b] VALUES (?tiKeyParent, ?tiIsVault, ?tcFolder)"

IF SQLEXEC(liHandle, lcCmdIns, "cur_temp") = -1
   SQLROLLBACK(liHandle)
   SQLDISCONNECT(liHandle)
   RETURN -1
 ENDIF

IF SQLCOMMIT(liHandle) = -1
   SQLROLLBACK(liHandle)
ELSE
   IsCommitted = .T.
   [b]liKeyFolder = cur_temp.ikey_folder[/b]
ENDIF

SQLDISCONNECT(liHandle)

RETURN IIF(IsCommitted, [b]liKeyFolder[/b], -1)

Of course, this is passthrough and cannot be implemented with remote views which is one of the reasons I never use remote views. Cursor Adapters are far superior to remote views and I can't understand why more people don't use them.

 

[Chris Miller]

Mike,

In a recent post, I already saw my mistake about what the scope is in comparison to the connection/session but the motivation was never about being able to use SCOPE_IDENTITY(), I just wanted a solution ut to use remote views and still have full control about really getting the right PK id values.

And as you mention it, I especially like the tempid solution as it won't depend on the specific SQL dialect. And REFRESH() is underestimated.

A sample for @@IDENTITY giving the wrong value is an After Insert trigger, which inserts something to a secondary table with another IDENTITY column. There is SET NOCOUNT ON to avoid side effects on the number of affected rows by a trigger or stored proc, but I don't know a similar setting to suppress @@IDENTITY changes for the duration of a trigger and anything it triggers. Well, in the end SCOPE_IDENTITY was invented to have that last id unaffected as a trigger also is a change of the scope. But to keep this short, such solutions are not compatible with remote views.

I have seen such trigger automatisms in a shop systems, where a new order triggers adding a few records in other tables to control and monitor the workflow of the order. I always think of the sorcerers apprentice, when such automatisms are used, but it's not just an academic case. Of course, you can decide to avoid such triggrs and then @@IDENTITY is a shorter solution, but also think of inserting multiple rows.

And vernspace, I am familiar with the output clause, which also outputs multiple ids in the multiple rows case, but are you sure you always know which of the ids is for which record?

The tempid solution is intended for remote views, working with the usual SQL commands generated by TABLEUPDATE. In detail setting KeyFieldList makes TABLEUPDATE generate commands assuming tempid as the primary key. REFRESH also is tricked to use tempid for the refresh process. If you think this REFRESH and on top of it the second TABLEEUPDATE overcomplicates what the output clause can do with just the INSERT, think about the multiple rows case.

The sparse column is optional, but I think it's also helping the REFRESH getting the right rows faster from a filtered index on non-NULL tempids only. If you prefer the <output clasue, you could use it with SQL Passthrough or cursoradapter, of course. But remember the difference of SQL Server to DBC about triggers. In SQL Server you get the inserted and deleted pseudo table variables, which may also contain multiple records. In VFP you have one trigger call per record.

Chriss

 

[vernpace]

Chris, that still doesn't explain why you are still using remote views. Remote views are old and inferior technology compared to Cursor Adapters. When you refer to triggers, are you referring to DBC or SQL Server triggers? We stopped using DBCs years ago - unstable in certain environments.

 

[Chris Miller]

vernspace, this was intended to help stanlyn for his remote view problems.

I think any projects using remote views could benefit from this technique without the need to migrate to Cursoradapter.

are you referring to DBC or SQL Server triggers?

Would DBC triggers cause a wrong @@IDENTITY? Of course, I am talking about SQL Server triggers. See a use case above.



Chriss

 

[Mike Lewis]

Vernspace, thanks for the info and code re the OUTPUT clause. I wasn't aware of that, it being a new element in the language.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads