So I’ve been experimenting with using delta commands to sort of “write back through a join”.
What I mean is that I have a table with several foreign keys into other tables.
I’ve used a manual SQL statement for this table in my schema, which JOINs in the other tables by their foreign keys, so the resulting field list contains all the fields from the table itself but also values from the other tables.
Normally I’d use lookup fields in the datatable, linked to a separate datatables for the foreign key tables. The reason I’m investigating doing it with a JOIN is because, in some cases, the foreign tables could have thousands of rows and retrieving all of these into a datatable purely to serve as the source for a lookup field is inefficient.
Now, in order that I can perform insert, update and delete functions on this table, I’ve then created the delta commands within the schema. This all works correctly and, because I disabled Log Changes on the foreign key fields, these are correctly omitted from the generated SQL in the delta commands.
Now, on the client, if I update the main table and change a foreign key value, I obviously need the server to requery the database to get the new “lookup” value from the foreign key table.
If I call ApplyUpdates with the first refresh parameter set to True then this works, as it basically runs the query again to get the new values.
My first question is, is this the only way of doing this or is there some way I can get the server to requery only the individual record(s) that have been updated?
My second question is, does using the delta commands mean that the generated SQL will always set all field values, even if they haven’t changed? Normally, with AutoSQL, the generated SQL for an UPDATE will only include fields that have actually changed in the SET clause. With a delta command, the manual SQL obviously includes all fields in the SET clause, meaning the generated SQL will include every field, even if it’s just setting it to the existing value. Is there any way to ‘optimise’ this with delta commands?
If neither of the above are possible then fair enough. Basically I’m just experimenting with this approach at the moment and weighing the pros and cons.