StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Does FieldsToExcludeFromUpdate/Insert Work?

Does FieldsToExcludeFromUpdate/Insert Work?

Ben Kim — Sat, 03 Mar 2007 20:42:07 GMT

I have a handful of fields that are handled on the backend (MS-SQL 2K) in a trigger. I assumed that the FieldsToExcludeFrom Update would take care of my issue from collisions during update. Unfortunately the collision still occurs. Here are the fields I have excluded:

SQLRegionID
SQLLastUpdated
SQLIPAddress
SQLEmployeeID

I do not use these fields in the form I have created. The trigger code follows:

CREATE TRIGGER trgInsUpdOffClass ON dbo.OffClass
FOR INSERT, UPDATE
AS
UPDATE OffClass
SET SQLLastUpdated = GetDate(),
SQLEmployeeID = dbo.fnGetContextLogin(),
SQLIPAddress = dbo.fnGetMACAddress()
FROM Inserted i
WHERE i.SQLGUID = OffClass.SQLGUID

The SQLLastUpdated column is causing the collision.

Ideas?

Ben

RE: Does FieldsToExcludeFromUpdate/Insert Work?

Peter Jones — Sat, 03 Mar 2007 20:42:07 GMT

Hi Ben,

FYI - I've just posted the following in the enhancements request forum: Removing fields from the Data Collision form.

Cheers, Peter

RE: Does FieldsToExcludeFromUpdate/Insert Work?

StrataFrame Team — Fri, 02 Mar 2007 16:51:01 GMT

That is an understandable request. You can also use OptimisticAllFields and create stored procedures for UPDATES... within the sproc, you're in complete control over what gets checked and how it gets checked for concurrency purposes.

RE: Does FieldsToExcludeFromUpdate/Insert Work?

Ben Kim — Fri, 02 Mar 2007 12:40:13 GMT

Then can I request a new property be added to excluded specific fields from the concurrency check? Our DBA is not likely to add new fields to our database.

Ben

RE: Does FieldsToExcludeFromUpdate/Insert Work?

StrataFrame Team — Fri, 02 Mar 2007 09:58:09 GMT

What I meant was that when the OptimisticRowVersion or OptimisticTimeStamp is used, those are the only fields that are checked by the concurrency... so the concurrency issue wouldn't be detected.

As for why there is no timestamp column in the command that you posted... that's because you excluded the timestamp column from the update... if it's not included in the udpate, it cannot check it. (the DAL is smart enough to know not to update the timestamp column...)

RE: Does FieldsToExcludeFromUpdate/Insert Work?

Peter Jones — Thu, 01 Mar 2007 21:17:44 GMT

Hi,

Pardon the intereuption but I've been checking out concurrency control as well and just can't get the Row Version or Timestamp options working although OptimisticAllFields works ok. I'm not using stored procs for the CRUD. I thought adding my comments in this thread would be appropriate.

Refer to the attached screen shot. You can see the properties I have set for the BO and also the fact that TTTimeStamp exists and is a timestamp datatype. You can also see ATTTimeStamp is in the BOM and has not been customised.

I tested by opening my form and retrieving the data. I then change a row in the SQL Management Studio and save it. I then change the same row in my form exepecting an exception but none is fired.

I have the database debug code turned on and I see the update creates just the following code:
UPDATE [dbo].[tblATTAttrTypes] SET [ATTID] = @ATTID, [ATT_AGTID] = @ATT_AGTID, [ATTType] = @ATTType, [ATTDType] = @ATTDType, [ATTSequ] = @ATTSequ, [ATTName] = @ATTName, [ATTActive] = @ATTActive, [ATTRBatch] = @ATTRBatch, [ATTFBatch] = @ATTFBatch, [ATTSPBatch] = @ATTSPBatch, [ATTSBatch] = @ATTSBatch, [ATTFHide] = @ATTFHide, [ATTSHide] = @ATTSHide, [ATTLookUp] = @ATTLookUp, [ATTMandInLU] = @ATTMandInLU, [ATTRangeFrom] = @ATTRangeFrom, [ATTRangeTo] = @ATTRangeTo, [ATTInputMask] = @ATTInputMask, [ATTGroup1] = @ATTGroup1, [ATTGroup2] = @ATTGroup2, [ATTGroup3] = @ATTGroup3, [ATTExport1] = @ATTExport1, [ATTExport2] = @ATTExport2 WHERE [ATTID] = @ATTID_ORGPK;

No sign of any version checking....

Another related question (written before I read Ben's post):
As stated above, OptimisticAllFields works ok however the Data Collision form isn't quite what I expected. The Database has fields updated by a trigger e.g. update count, update date, timestamp. These fields are flagged in the BO as do not update on insert/update operation and this is what happens, i.e. they do not appear in the above SQL code. However, if a collision is detetected in concurrency control, they are presented to the user in the Data Collision form. Is there any way of only showing, in the Data Collision form, those fields that were part of SQL update code?

Does you anwer to Ben imply that when rowversion or timestamp is used the problem with showing these 'internal columns' on the Data Collision form just goes away?

Cheers, Peter

RE: Does FieldsToExcludeFromUpdate/Insert Work?

StrataFrame Team — Thu, 01 Mar 2007 17:00:10 GMT

Yes, the problem is that the FieldsToExcludeFromUpdate only excludes the fields from the SET portion of the UPDATE... they are still tested on the OptimisticAllFields concurrency. You could optionally change the concurrency type to OptimisticRowVersion or OptimisticTimestamp (either of which is MUCH more efficient anyways) and remove the dependency on that column for concurrency checking. Or you could write an update stored procedure for the table and handle the concurrency in whatever way you see fit.