Define custom POST method for MyDAC

Question

I have three tables objects, (primary key object_ID) flags (primary key flag_ID) and object_flags (cross-tabel between objects and flags with some extra info).

I have a query returning all flags, and a one or zero if a given object has a certain flag:

SELECT
  f.*,
  of.*,
  of.objectID IS NOT NULL AS object_has_flag,
FROM
  flags f
  LEFT JOIN object_flags of
    ON (f.flag_ID = of.flag_ID) AND (of.object_ID = :objectID);

In the application (which is written in Delphi), all rows are loaded in a component. The user can assign flags by clicking check boxes in a table, modifying the data.

Suppose one line is edited. Depending on the value of object_has_flag, the following things have to be done:

• If object_has_flag was true and still is true, an UPDATE should be done on the relevant row in objects_flags.
• If object_has_flag was false but is now true, and INSERT should be done
• If object_has_flag was true, but is now false, the row should be deleted

It seems that this cannot be done in one query https://stackoverflow.com/questions/7927114/conditional-replace-or-delete-in-one-query.

I'm using MyDAC's TMyQuery as a dataset. I have written separate code that executes the necessary queries to save changes to a row, but how do I couple this to the dataset? What event handler should I use, and how do I tell the TMyQuery that it should refresh instead of post?

EDIT: apparently, it is not completely clear what the problem is. The standard UpdateSQL, DeleteSQL and InsertSQL cannot be used because sometimes after editing a line (not deleting it or inserting a line), an INSERT or DELETE has to be done.

Answer 1

It seems that the easiest way is to use the BeforePost event, and determine what has to be done using the OldValue and NewValue properties of several fields.

Answer 2

The short answer is, to paraphrase your answer here:

• Look up the documentation for "Updating Data with MyDAC Dataset Components" (as of MyDAC 5.80).

Every TCustomDADataSet (such as TMyQuery) descendant has the capability to set update SQL statements using SQLInsert, SQLUpdate and SQLDelete properties.

TMyUpdateSQL is also a promising component for custom update operations.