I made a desktop app with Multi-Project driver substitution (TPS and MS-SQL).
So i have a TPS version and a MS-SQL version now, that works great.
Both have the same dct with all te relations set in the dct.
Problem:
In the TPS version when i change a unique key (which is also a primary key), the field (Parent) is updated as well as all the child records, with that same field, based upon the dct relations.
When i do the same in the MS_SQL version i get the error changed by another station and it shows again the field prior to the changes. So no update takes place.
Question:
Is there a way to make the MS-SQL version react the same as in the TPS version?
That means updating the unique (Parent) field and also all the same child fields.
Changed by another station is usually a result of server stored time stamps vs clarion. Clarion is only accurate to 100th of a second many time stamps are to the 1000th. and .999 <> .9999 so clarion objects.
There is a way to define the time/datetime to the appropriate accuracy, but I’m unsure on mssal how to do that.
IMHO: If you are having to change a primary identifier, then there is something wrong with your design. The identifiers should only ever get set when a record is created and should never be changed. If you are changing, then this probably means you are in need of a separate field for the data.
So an explanation as to why you are having to change this might be useful.
Assuming you have a form on the parent field, an the cascade (NOT server) set in the dictionary, what happens is:
Clarion loops through all the child records belonging to the parent, changing the parent field. If that all works then it changes the parent.
In order for this to work in MS-SQL, assuming that there is a foreign key set up on the parent_ID in the child, you would have to use deferrable constraints. That is, you have to tell the foreign key constraint not to check that its parent exists until the commit happens.
If you don’t do that, then when you try to change the first child record, the server will tell you it can’t because you have a foreign key constraint violation, and it has changed no records. I would have thought you would get the FK violation from the server reported, but if that didn’t happen, then the “Record changed…” error would be a response to no records being changed when it expected to change one.
Personally, I’m with Niels and Mark, and think changing linking fields is a horrible idea.