Order by fails

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQL Server in Delphi and C++Builder
Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Order by fails

Post by Stellar » Thu 05 Apr 2018 09:24

SELECT
999999 AS RECNO,
CAST(db_name() AS VARCHAR(128)) AS CATALOG_NAME,
CAST(user_name(o.uid) AS VARCHAR(128)) AS SCHEMA_NAME,
CAST(o.name AS VARCHAR(128)) AS TABLE_NAME,
CAST(x.name AS VARCHAR(128)) AS INDEX_NAME,
CAST(c.name AS VARCHAR(128)) AS COLUMN_NAME,
xk.keyno AS COLUMN_POSITION,
CAST(NULL AS VARCHAR(1)) AS PKEY_NAME,
(CASE WHEN x.status & 0x800 <> 0 THEN 4 ELSE 0 END) +
(CASE WHEN x.status & 0x2 <> 0 THEN 2 ELSE 1 END)
AS INDEX_TYPE,
(CASE WHEN indexkey_property(x.id, x.indid, 1, N'isdescending') <> 0 THEN 'D' ELSE 'A' END) AS SORT_ORDER,
CAST(NULL AS VARCHAR(1)) AS FILTER
FROM
sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk
WHERE
o.id = x.id
AND o.id = c.id
AND o.id = xk.id
AND x.indid = xk.indid
AND c.colid = xk.colid
AND xk.keyno <= x.keycnt
AND o.xtype <> 'S'
AND LEFT(x.name, 8/*_WA_Sys_*/) <> '_WA_Sys_'
AND o.name LIKE 'Menu'
ORDER BY x.name, xk.keyno
This query is generated by dbExpress Driver for SQL Server 5.0.3, it will get the information about the Menu table indexes. You can check this by executing this query. At that time, the standard driver won't get the information about the indexes, the query text :

Code: Select all

exec [DataBase] .. sp_indexes_rowset N'Menu',NULL,N'sa'
We changed your sample leaving only the work with dbExpress Driver for SQL Server. Please see the sample and specify where the sorting issue occurs.
https://drive.google.com/open?id=1jy_z7 ... B11BKCgjzW
Last edited by Stellar on Fri 06 Apr 2018 06:56, edited 1 time in total.

Albervan
Posts: 12
Joined: Mon 12 Mar 2018 16:10

Re: Order by fails

Post by Albervan » Thu 05 Apr 2018 11:56

Stellar wrote: Thu 05 Apr 2018 09:24 We changed your sample leaving only the work with dbExpress Driver for SQL Server. Please see the sample and specify where the sorting issue occurs.
devart.com/pub/dac/OrderTest.zip
Stellar, the changed project download link is not available.

But if you see, along with this project, I sent a step-by-step how to simulate the problem, in it I am not considering the drivers for other databases.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Order by fails

Post by Stellar » Fri 06 Apr 2018 07:59

Thank you for the information, we updated the link for downloading the archive with the test sample. When the poRetainServerOrder option is enabled in TDataSetProvider, the sorting issue does not occur in your sample. Please do not write about the same issue in different forum branches.

Albervan
Posts: 12
Joined: Mon 12 Mar 2018 16:10

Re: Order by fails

Post by Albervan » Fri 06 Apr 2018 16:05

Actually the property "poRetainServerOrder" resolves the problem, but the definition of it is as follows:
Signals that the client should not attempt to re-establish the default order by sorting the records in the data packet. This prevents the client from changing the record order because, for example, it is unable to duplicate the locale on the server.
http://docs.embarcadero.com/products/ra ... tions.html
The problem I see with your drive is that it is not applying the default sort of the query when you open the TClientDataSet. I understand, it should bring the data into the default sort order of the query the first time, even though the "poRetainServerOrder" property was not checked.

But if that is a feature of operating the driver, is fine....

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Order by fails

Post by Stellar » Fri 13 Apr 2018 14:33

dbExpress Driver for SQL Server cannot influence the TClientDataSet behavior. The driver returns correctly sorted data. Unfortunately, we cannot influence the order of data displaying by DBX.

Post Reply