Page 2 of 2
Re: Order by fails
Posted: Thu 05 Apr 2018 09:24
by Stellar
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
Re: Order by fails
Posted: Thu 05 Apr 2018 11:56
by Albervan
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.
Re: Order by fails
Posted: Fri 06 Apr 2018 07:59
by Stellar
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.
Re: Order by fails
Posted: Fri 06 Apr 2018 16:05
by Albervan
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....
Re: Order by fails
Posted: Fri 13 Apr 2018 14:33
by Stellar
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.