I have 3 tables a,b,c connected with foreign keys in ms sql. I am using Aurelius , xdata and xdatawebdataset. To be able to post my changes to table c I use $expand in the querystring of the dataset. It posts and applies to table c but the problem is it overwrites data for other 2 tables. It overwrites data for fields in tables a and b which were updated by another user while I was making changes to table c. How can I update just a single table which has foreign keys? Can you explain please.
I think I should mention that some fields from the foreign key have the same names in master and detail tables. Maybe that is why I have to do the expand.
Hi Farias. Thank you for the response. I will prepare the code to share. You are right the concurrency control in Aurelius should not allow this to happen. Only modified fields should be updated in the database. Is that control always on by default? I don’t understand how this can happen. I even checked in sql server monitor and I saw how the data from the field which I did not changes goes to the database in update statement.
Hi Zohair. The concurrency control only takes place when you set a [VERSION] attribute, so if versions don't match than the update do not succeed.
About updating only modifyed fields:
make sure your XDataServer PutMethod is the default 'Merge'
If some other user modified the record while you had an old version of it and you do not have [VERSION] control in place, the modified attributes (from the other user) will be overriden because MERGE will compare your copy of the object with the current one in the database to decide wich attributes are different.
Thank you. But the version control will reject the transaction if record was modified by another user. That is not what I need. I want to overwrite that changes with the changes I made. That is ok. I just need to update modified fields only. There is a setting for that in Aurelius connection component on the server. Updatechangedfields in UpdateOptions. It is true in my case. One thing I noticed however: there is no AureliusManager component on the server link to Aurelius connection. And I am not creating the object manager in the code. Can that be the reason ?
That tells Aurelius to build de SQL only with modified fields. The "problem" is how to determine the modified fields? When using XData (CRUD) you have the copy of the object you are sending for update, compared to de state the object is persisted in the database. IF that state is diffent from your original object (eg. modified by some other user after you loaded (GET) the object) than it will be treated as a modified field.
Maybe your "problem" may not be a problem at all, but It's hard to say withou knowing how the entities relate and what exactly you are doing (update)
Hello Anderson,
We went live with the app and the issue is happening in Production environment. We need to fix this issue ASAP. Does TMS have any paid support where you can log into our development machine and check what are we doing wrong?
We would need more details to help you out. The code you are using to perform the changes, the mapping of the entities, the content of the HTTP request that is being sent to the server, etc.. Can you please provide more objective and exact information?
Yes, we can offer that, you can send us a private message to discuss that. But maybe as a second step, since regardless, we would've to ask what I'm already asking you now to start understanding what's going on.
I am attaching the simple project created for testing,
the part of aurelius schema related to the table in the project and a screenshot of the properties of the components and aurelius connection update_options in our xdata server. So in this simple example if I run two copies of my client and update one field in one copy and another field in another copy and then post and apply all fields from the last screen get updated in the database including those i did not modify - it overwrites the change made from another screen with the data i did not change - just selected and showing on the screen TEST.zip (130.3 KB)
Can you also tell us please where should we send you the 'private message' ?
This is what the manual says. As I understand it is not related to versioning which is a different thing. And to see which field is updated It doesn’t need to compare the local data to database. It supposed to use the copy of the record as it was loaded from the database. And this is a default behaviour of Aurelius. The only thing is that I am using the xdatawebdataset not doing it manually through the manager as you can see in the project.
If you think the project info I sent you still doesn’t give you all details you need please let us know how can we get the paid support we were asking about. We really need to fix this issue ASAP.
Changed fields
When updating objects, Aurelius detects which property have changed since the entity was loaded from the database in the manager, and it only updates those columns in the database. For example, suppose two users load the same TCustomer (with same id) from the database at the same time:
Here are the SQL executed by Aurelius for each user (SQL were simplified for better understanding, the actual SQL uses parameters):
-- User1:
Update Customer
Set City = 'New City'
Where Id = 1
-- User2:
Update Customer
Set Document = '012345'
Where Id = 1
Even if TCustomer class has lots of customer, and some properties might be outdated in memory, it doesn't cause any trouble or data loss here, because only changed data will be commited to the database. In the end, the TCustomer object in database will have both the new city and new document correct.
This is a basic mechanism that solves concurrency problems in many cases. If it's not enough, you can use entity versioning.
Not with XData. The client will send only it's current/modified state of the object (serialized) along with PUT request. There's no way for the XData server to know the state of the object the client had before any changes where made.
Your question here is different from the original one. Those are two different things.
Updating only the modified fields
That is not currently possible using the TXDataWebDataset. In this case you have to refactor your user interface in a way that you get the modified fields yourself, and then send a raw PATCH request to XData, passing a JSON object with only the modified fields. If you use TXDataWebDataset, the whole object is always sent and update, and you avoid concurrency issues using the [Version] attribute, which will reject changes from user A if another user B have modified the record since it was loaded from the server by user A.
Updating only an object and not its associated objects
This is possible to achieve by removing the TCascadeType.Flush from the associations. If your object A has an association B, calling
Client.Put(A);
will update fields of both A and B objects, if the B association has TCascadeType.Flush. However, if you remove the flush cascade, calling Put(A) will only update fields of A object, and changed to fields of B object will not be applied.
