TMyQuery SQL/SQLDelete/SQLInsert/SQLUpdate

[NoComprende]

Can someone explain to me what the purpose of these different SQL's are as I'm confused?

If I have a TMyQuery called Q, is it the case that e.g.

Q->Delete();

removes the record from the query and executes the SQL in SQLDelete?


The SQLUpdate in particular confuses me.

Suppose Q->SQL="update Tbl set Fld1='X'";

and Q->SQLUpdate="update Tbl set Fld1='Y'";

what happens when I execute the statement Q->Execute();

[Antaeus]

Can someone explain to me what the purpose of these different SQL's are as I'm confused?

The values of these properties (SQLInsert, SQLUpdate, etc) are templates for query statements, and they cannot be manually executed. Usually there is no need to fill these properties because the text of the query is generated automatically.

In special cases, you can set these properties to perform more complicated processing during a query. These properties are automatically processed by MyDAC during the execution of the Post, Delete, or RefreshRecord methods, and are used to construct the query to the server. Their values can contain parameters with names of fields in the underlying data source, which will be later replaced by appropriate data values.

For example, you can use the SQLInsert template to insert a row into a query instance as follows.
1. Fill the SQLInsert property with the parametrized query template you want to use.
2. Call Insert.
3. Initialize field values of the row to insert.
4. Call Post.

The value of the SQLInsert property will then be used by MyDAC to perform the last step.

Setting these properties is optional and allows you to automatically execute additional SQL statements, add calls to stored procedures and functions, check input parameters, and/or store comments during query execution. If these properties are not set, the MyDAC dataset object will generate the query itself using the appropriate insert, update, delete, or refresh record syntax.

removes the record from the query and executes the SQL in SQLDelete?

Yes, you are right.

what happens when I execute the statement Q->Execute();

The statement that is stored in the SQL property (but not the SQLUpdate property) will be executed.

[NoComprende]

Thanks for the very fast reply Antaeus. I'm still a bit confused about their use and when exactly they are executed but, it's unlikely I'll require them anyway.

[NoComprende]

Antaeus, I have a table where I want deleted records to be marked as deleted rather than actually deleted. I tried this

QueryTbl->SQLDelete = "update Tbl set Deleted=true where ID=:Old_ID;";

(Deleted is a boolean field in table Tbl where ID is the unique key field)

hoping that MyDac would automatically remove the record from the query result set but that the above code would override the default behaviour so that the record wouldn't be deleted from the actual table. When I tried it though I got the exception "Update failed. Found 0 records.".

Where am I going wrong?

[NoComprende]

After further experimentation I take it that the SQLDelete is only executed after the default behaviour (i.e. the record is deleted from the Tbl) has taken place?

[Antaeus]

When I tried it though I got the exception "Update failed. Found 0 records.".

To suppress this error message you should set the StrictUpdate option of TMyQuery/TMyTable to Fase

I have a table where I want deleted records to be marked as deleted rather than actually deleted.

Probably CachedUpdates mode will be useful for you in this case. Please see the MyDAC help for detailed information.

After further experimentation I take it that the SQLDelete is only executed after the default behaviour (i.e. the record is deleted from the Tbl) has taken place?

Yes, you are right. Please see this topicof MyDAC FAQ for detailed information.

[NoComprende]

Thanks Antaeus.