Devart Forums

Hello,
already 2 of our customers experienced a critical problem - many record from a table have been deleted and nobody knows, how it could happen. I analyzed all sql command in the database triggers, stored procedures and sources and none of them could cause this - there is no such wrong "delete from..." command coded.
I assume, there is somewhere a TMSQuery component on a form (strictupdate = false), where SDAC generates and executes such very dangerous delete.
So:
Is there a way to analyze all queries on all forms and output their automatically generated delete commands to a file? I could then analyze them somehow.

Thanks very much!
Ludek.

This problem can happen if the client does not have the primary key of the table, or not all fields of the primary key are requester.
You can catch all queries in your application if you place TMSSQLMonitor on the main form of your project and add a handler to its OnSQL event.

Yes, I know, that there's somewhere primary key missing. It is perhaps a query, that is even not meant to be modified - as you know, all bde queries without a TUpdateSql were not editable, so in old BDE times I didn't care about them; all TQueries without TUpdateSQL were just read-only. But sdac unhappily allows changes in may of such queries with enough simple sql query
I have really no idea, where's the bug hidden. Catching all generated and performed sql commands is not enough, because I simply don't know, how to perform all deletions on all queries in our projects... pressing ctrl+del in all grids of our app is no solutions - there are notebooks with (temporarily) hidden pages etc...
so: is there a programmatic way to generate and show/store the sql command for delete? I would make a simple routine that runs all forms in all projects, forces the generation and analyses the result.

Unfortunately we cannot suggest you a way to perform this task, as we do not know peculiarities of your application.

We probably don't understand each other I just would like to know, how could I generate the DeleteSQL command for one particular query. If you tell me that, I could generate it for all queries very easily.
I just found a TMSSQLGenerator class in the sources and I guess, that it's the class I'm looking for. I just can't find any docs to it, could you tell me, where could I find it? Or, just a few words to its possible usage...
Thanks.

Usage of SQLGenerator is pretty complicated. You can try to debug the TDASQLGeneratorFrame.btGenerateClick procedure in the DASQLGeneratorFrame to see how it works.
You can also try one of the following solutions:
- make a procedure that passes all TCustomMyDataSet objects in your application, calls for each of them FieldDefs.Update, and verifies whether it has a primary key by the created FieldDefs;
- generate Delete SQLs manually at design time, and check whether they are generated correctly. DataSet Manager will be useful for this task. It is installed together with MyDAC 5. You can run it from the Tools menu of your IDE.

Hmmm, sounds complicated...
I'll give it a try as soon as possible.
Up to the time I programmed a simple trigger that disallows such large delete operations and hope that somebody notices the error and tells me enough about the circumstances.


Thanks for the tip!