|
Hi dear colleagues. I haved a rare problem in this days with an old project built in delphi 7, using ALS 9.. It was working fine since 5 years till now. this part failing, saves credit accont data from customers.. There is an ADSTable with this data and this structure (I removed some field in the example below) The problem is in the way to orden BY DATE, in this case table is using an DESCENDING DATE INDEX. resulting in a BAD ORDERING. If you see the number on ID (autoinc field), you'll see the problem. All records has the same date, because are on the same day. Till now Order was correct, now it began to switch some records order. using sql query (order by date or order by date desc) the same problem. Browsing the table with Data architect, records are shown switched too. Tried deleting adi index file to force ALS to recreate it and reindex but doesn't work. Tried update the ALS dlls to newer version. nothing. I am worried because I do not work anymore with Delphi 7 and some third-party components that were used, so modify the project will generate me complications. I'm using now XE5and ALS 11.10. I hope there is something wrong with the database. suggestions are welcome. Thanks |
|
If you want to order by date and then order by id using SQL, you have to explicitly state that: If you only order by date the sort order of rows with the same date is undefined and not guaranteed to stay the same. The SQL engine will only use an index if it exactly fits the order expressions (with certain exceptions). For performance reasons it would make sense to have an index, e.g. like this: |
|
As you sugested I used a compound index. I had an index called DATE, i modified it and made compound adding id field to the existing index. So now when project use DATE index really is sorting by Date+ID. With this solved 50% of troubles (the adstables order problem). Because as say before, can't rebuild the exe project. So can't edit some sqlquerys to add ID field to the order by clause. In my querys order clause is ORDER BY DATE DESC Is there any whay to cheat sqlengine from outside the EXE, for when run ORDER BY DATE, it use DATE index instead of DATE FIELD? I do not understand how so many years was running smoothly so far. Thanks for your great support. 1
Not that I know of. However it is possible to modify the existing executable by overwriting the existing SQL query using a hexeditor. The main problem here is that you need to shave off whitespace to fit additional bytes (",ID DESC" which is 8 bytes) from the original query in order for the executable size to remain the same.
(25 Oct '15, 17:33)
Jens Mühlenhoff
1
See also http://stackoverflow.com/questions/2455644/modifying-rdata-unicode-strings-from-windows-pe-files
(25 Oct '15, 17:36)
Jens Mühlenhoff
I trided editting exe with ressource hacker and, can modify the sql string, but as you say , the problem is the needed extra bytes. If add some extra bytes get error. I tried changing "order by date" by "order by ID"(with spaces to fill bytes" and works. So how can I find the Corss references to change length parameter as suggests in your stackoverflow link.? I tried adding extra bytes in other parts of exe, like label.captiosn, or forms.captions, without problems. but if in sql string add som extra space crashes.
(26 Oct '15, 18:08)
AmadiSoft
|