detect incorrectly generated SqlDelete commands

Ludek · Post by **Ludek** » Thu 06 Sep 2007 14:12

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.

Antaeus · Post by **Antaeus** » Fri 07 Sep 2007 10:43

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.

Ludek · Post by **Ludek** » Fri 07 Sep 2007 12:29

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.

Antaeus · Post by **Antaeus** » Fri 07 Sep 2007 15:29

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

Ludek · Post by **Ludek** » Mon 10 Sep 2007 06:32

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.

Antaeus · Post by **Antaeus** » Tue 11 Sep 2007 09:56

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.

Ludek · Post by **Ludek** » Tue 11 Sep 2007 15:17

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.

Code: Select all

if @@rowcount > ... 
  raiserror...

Thanks for the tip!