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)

ID            Date      Price     amount 
(autoinc)              (double)  (money)
6774        21/10/2015  21      $ 256.80 
6773        21/10/2015  35      $ 235.80 
6740        20/10/2015  17      $ 94.80 
6739        20/10/2015  20      $ 77.80 
6738        20/10/2015  2.8     $ 57.80 
6743        20/10/2015  4       $ 200.80 
6742        20/10/2015  65      $ 196.80 
6741        20/10/2015  37      $ 131.80 
6601        08/10/2015  22      $ 55.00 
6595        08/10/2015  6       $ 33.00 
6594        08/10/2015  27      $ 27.00

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

asked 21 Oct '15, 23:01

AmadiSoft's gravatar image

AmadiSoft
16558
accept rate: 0%

edited 21 Oct '15, 23:02


If you want to order by date and then order by id using SQL, you have to explicitly state that:

ORDER BY
  [Date] DESC, ID DESC

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:

-- Note that this is a compound descending index (ADS_COMPOUND: 2 + ADS_DESCENDNG: 8 = 10)
EXECUTE PROCEDURE sp_CreateIndex90( 'yourtable', 'yourtable.adi', 'INDEX_1', 'Date;ID', '', 10, 512, NULL );
link

answered 22 Oct '15, 16:07

Jens%20M%C3%BChlenhoff's gravatar image

Jens Mühlenhoff
1.3k51037
accept rate: 18%

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.

link

answered 22 Oct '15, 22:40

AmadiSoft's gravatar image

AmadiSoft
16558
accept rate: 0%

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

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
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×325
×271
×25
×6

Asked: 21 Oct '15, 23:01

Seen: 2,140 times

Last updated: 26 Oct '15, 18:08

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.