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.
detect incorrectly generated SqlDelete commands
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.
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.
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.
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.
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!
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!