Problem with derived sort order

carlmon · Post by **carlmon** » Thu 16 Oct 2008 10:12

Greetings,

We recently upgraded from 3.80 to 4.50(.0.36) and are experiencing issues with the order of returned queries.

I have traced the problem to the following example scenario:

A clean, newly created TClientDataset is populated from:
" SELECT LastName
FROM Customer
ORDER BY CustomerID, FirstName, LastName "

The DataSet comes back ordered by LastName (not CustomerID and FirstName first). On inspecting the DataSet, I see LastName was somehow added to the DataSet's .IndexFields, but .IndexFieldNames is blank. The dataset should have no explicit sorting - just retain SQL Server's query result record order.

Adding FirstName and CustomerID to the SELECT list 'resolves' the sorting issue. Unfortunately this is not a solution as we have many reports on many implementations.

Is there any way to turn off this behaviour? It worked fine on 3.80.

Please advise.

Thanks,
Carl

Challenger · Post by **Challenger** » Mon 20 Oct 2008 13:03

Please set the TDataSetProvider.Options.poRetainServerOrder property to True.

carlmon · Post by **carlmon** » Mon 20 Oct 2008 13:05

Hi Challenger,

Thanks for the reply, but poRetainServerOrder is not one of the options in Delphi 5... Here is the complete option set:

TProviderOption = (poFetchBlobsOnDemand, poFetchDetailsOnDemand,
poIncFieldProps, poCascadeDeletes, poCascadeUpdates, poReadOnly,
poAllowMultiRecordUpdates, poDisableInserts, poDisableEdits,
poDisableDeletes, poNoReset, poAutoRefresh, poPropogateChanges,
poAllowCommandText);

Regards,
Carl

carlmon · Post by **carlmon** » Mon 20 Oct 2008 13:47

I just did some further investigation. It looks like TCustomDADataSet.PSGetDefaultOrder orders by whichever ORDER BY fields exist in the SELECT list. Any missing ORDER BY fields are simply ignored.

Please advise on a fix/workaround.

Regards,
Carl

Dimon · Post by **Dimon** » Tue 28 Oct 2008 11:14

This problem arises because in SDAC version 4 the TCustomDADataSet.PSGetDefaultOrder method was added, that returns a list of index fields. This behaviour is coordinated with standatd BDE components. If TCustomDADataSet.PSGetDefaultOrder returns all fields that are included in ORDER BY list and not included in SELECT list, than the TClientDataSet component raises an error when trying to find a non-existent field in the fields list.
In order to solve the problem with sorting you should add in SELECT clause all fields included in ORDER BY clause. You can exclude unused fields in the fields list of TClientDataSet.
Also if you have the source code of SDAC you can redo the TCustomDADataSet.PSGetDefaultOrder method that will return nil.

carlmon · Post by **carlmon** » Tue 28 Oct 2008 14:24

Thanks Dimon,

I sent you an example application if you still need to replicate this.

Could you confirm that it is safe to modify TCustomDADataSet.PSGetDefaultOrder to always return nil?

Regards,
Carl

Dimon · Post by **Dimon** » Thu 30 Oct 2008 12:11

Thank you for the information. The PSGetDefaultOrder method is used only for supporting TDataSetProvider, therefore its change does not influence the behaviour of other parts of SDAC.