Order by fails

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQL Server in Delphi and C++Builder
dbxsda445src
Posts: 6
Joined: Mon 09 Jan 2012 09:46

Order by fails

Post by dbxsda445src » Wed 29 Aug 2012 10:33

Hi,

I have DBExpress Driver for SQ Server 5.0.3 with Delphi XE2 and a MS SQL 2008 server. I use TSimpleDataSet. In the dataset I use the query select * from tablename. The table is opened I see the results and I can sort all the comlumns by changing the query and refreshing the TSimpledataSet. If I opeen the tabel with "select * from tablename order by afiledname", I also get the table correct. But if I then want to change the sorting order by changing the query and refreshing the TSimpleDataSet, the table is not ordered any more. It sticks to the original sorting order.


What am I doing wrong?

Greetings,
Ronald

AndreyZ

Re: Order by fails

Post by AndreyZ » Wed 29 Aug 2012 12:08

Hello,

The problem with the incorrect fields ordering is caused by the specificity of the TDataSetProvider component (the TSimpleDataSet component uses it internally) work. To solve the problem, you should set the TDataSetProvider.Options.poRetainServerOrder option to True. The poRetainServerOrder option signals that the client should not attempt to re-establish the default order by sorting the records in the data packet. In order to set the TDataSetProvider.Options.poRetainServerOrder option to True, you should use the sequence of components like the following: TSQLConnection->TSQLQuery->TDataSetProvider->TClientDataSet , instead of using the TSimpleDataSet component.

dbxsda445src
Posts: 6
Joined: Mon 09 Jan 2012 09:46

Re: Order by fails

Post by dbxsda445src » Wed 29 Aug 2012 12:55

Thank you. Using TSimpleDataset is very convenient though. In the mantime I have discovered that the problem is solved when I uses data.close followed by dataset.open instead of dataset.refresh.

AndreyZ

Re: Order by fails

Post by AndreyZ » Fri 31 Aug 2012 06:48

I have executed the following code:

Code: Select all

SimpleDataSet1.DataSet.CommandText := 'select * from table1';
SimpleDataSet1.Open;
SimpleDataSet1.DataSet.CommandText := 'select * from table1 order by field1';
SimpleDataSet1.Refresh;
SimpleDataSet1.DataSet.CommandText := 'select * from table1 order by field2';
SimpleDataSet1.Refresh;
, and there were no problems with fields ordering. Please try to reproduce the problem using this code.

dbxsda445src
Posts: 6
Joined: Mon 09 Jan 2012 09:46

Re: Order by fails

Post by dbxsda445src » Fri 31 Aug 2012 07:34

I have tried that too and that works great, but if I do this then it does not work:

SimpleDataSet1.DataSet.CommandText := 'select * from table1 order by field1';
SimpleDataSet1.Open;
SimpleDataSet1.DataSet.CommandText := 'select * from table1 order by field2';
SimpleDataSet1.Refresh;

The probleem seems to arise when I open the tabel with an order by clause.

Greetings,
Ronald

AndreyZ

Re: Order by fails

Post by AndreyZ » Fri 31 Aug 2012 09:55

This is a specificity of TSimpleDataSet and TClientDataSet. To avoid the problem, you should (as you already found out) close and open a dataset instead of its refreshing.

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

Re: Order by fails

Post by Albervan » Tue 13 Mar 2018 12:37

Hi, great day!

We are migrating our application to the dbExpress MSSQL driver from Devart. Previously we used the native driver for Delphi 7.

In this process of migration we are evaluating the impacts of this change in the operation of our application.

The first impact identified is exactly as mentioned in the "https://forums.devart.com/viewtopic.php?f=10&t=8023" topic, regarding ordering queries. Apparently it's the problem quoted here as well.

Without any other change, just changing the connection driver, several of our queries were messed up. I would like to understand why this different behavior and how to solve it without needing a massive change in all our query screens (+ - 1300 source code files).

dbxsda445src
Posts: 6
Joined: Mon 09 Jan 2012 09:46

Re: Order by fails

Post by dbxsda445src » Tue 13 Mar 2018 12:56

Hi,

Devart works fine for me. I use it for MS SQL. I have asked them about the ordering some time ago. This was my question and their answer. I hope that works for you:


Q: I have DBExpress Driver for SQ Server 5.0.3 with Delphi XE2 and a MS SQL 2008 server. I use TSimpleDataSet. In the dataset I use the query select * from tablename. The table is opened I see the results and I can sort all the comlumns by changing the query and refreshing the TSimpledataSet. If I opeen the tabel with "select * from tablename order by afiledname", I also get the table correct. But if I then want to change the sorting order by changing the query and refreshing the TSimpleDataSet, the table is not ordered any more. It sticks to the original sorting order.
A: The problem with the incorrect fields ordering is caused by the specificity of the TDataSetProvider component (the TSimpleDataSet component uses it internally) work. To solve the problem, you should set the TDataSetProvider.Options.poRetainServerOrder option to True. The poRetainServerOrder option signals that the client should not attempt to re-establish the default order by sorting the records in the data packet. In order to set the TDataSetProvider.Options.poRetainServerOrder option to True, you should use the sequence of components like the following: TSQLConnection->TSQLQuery->TDataSetProvider->TClientDataSet , instead of using the TSimpleDataSet component.

Greetings,
Ronald

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

Re: Order by fails

Post by Albervan » Wed 14 Mar 2018 15:36

dbxsda445src wrote:Hi,

Devart works fine for me. I use it for MS SQL. I have asked them about the ordering some time ago. This was my question and their answer. I hope that works for you:
...
Great day!

We have already tested this alternative, but it has generated another problem:

With this change in the TDataSetProvider, some queries in which the data is processed considering its ordering, processing does not work correctly.

For example:

In the data of a query, we do a while in the TClientDataSet.
Previously one of the conditions did not happen, because of the ordering of the data.
Now it always comes into this condition.

Code: Select all

with cdsPedidos do
begin
  while NOT Eof do
  begin
    if FieldByName('Tipo').AsInteger = 1 then
    begin
      //Processing
    end;

    //Continue
  end;
end;

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

Re: Order by fails

Post by Stellar » Tue 20 Mar 2018 10:24

dbExpress Driver for SQL Server returns the result sorted according to the SQL expression, the issue with sorting occurs at the stage of filling the data in TClientDataSet. This behavior is not related to our driver, unfortunately we cannot influence this behavior.

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

Re: Order by fails

Post by Albervan » Tue 20 Mar 2018 12:35

Stellar wrote:
Tue 20 Mar 2018 10:24
dbExpress Driver for SQL Server returns the result sorted according to the SQL expression, the issue with sorting occurs at the stage of filling the data in TClientDataSet. This behavior is not related to our driver, unfortunately we cannot influence this behavior.
Thanks for the reply "Stellar".

But, I would like to understand why it was "working" (in theory) with the standard Delphi 7 driver, and only turn up that problem with the new driver?
Understanding this will help me implement a solution that is truly secure for our ERP. With that you could help me?

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

Re: Order by fails

Post by Stellar » Fri 23 Mar 2018 14:45

Unfortunately, we could not reproduce the issue with sorting data. In order for us to analyze the issue, please provide us with a sample demonstrating it, as well as the script for creating the table.

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

Re: Order by fails

Post by Albervan » Wed 28 Mar 2018 00:56

Stellar wrote:
Fri 23 Mar 2018 14:45
Unfortunately, we could not reproduce the issue with sorting data. In order for us to analyze the issue, please provide us with a sample demonstrating it, as well as the script for creating the table.
Stellar, great night!

As you requested, follow the link to download the compressed file with an example. In the file contains the project in Delphi 7, the database creation script, and a step-by-step how to reproduce the error.

Any questions, please contact me. I have great expectations in resolving this problem.

https://drive.google.com/open?id=1iNXpF ... OsRofVefXd

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

Re: Order by fails

Post by Stellar » Thu 29 Mar 2018 10:06

When using dbExpress Driver for SQL Server, the SchemaOverride=%.dbo option is set by default in the connection settings. This parameter is required for correct work with MS SQL Server, in particular for identifying metadata.
When executing a query, DBX requests the table indexes from the driver, since the correct value for the schema is set, the driver will receive a list of table indexes. You can try removing the SchemaOverride parameter from the connection settings, but we do not recommend doing this. Unfortunately, we cannot influence how DBX will use indexes.

Please try adding the poRetainServerOrder option for TDataSetProvider, for example:
DataSetProvider1.Options := DataSetProvider1.Options + [poRetainServerOrder];

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

Re: Order by fails

Post by Albervan » Mon 02 Apr 2018 13:56

I did the test by removing the "SchemaOverride =%.dbo" option, and it still has the same behavior. Also, I see that TSQLMonitor captures the index query that is done.

Code: Select all

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
I had also tested with the "poRetainServerOrder" option in TDataSetProvider. This option works, but in some cases causes a strange behavior, if I loop in the TClientDataSet considering the sorting of the query.
I'll try to explore this option better.

Anyway, I believe that this situation represents a bug in the driver, and that should be considered some parameter that allows to maintain the same behavior of the standard driver for sorting.

Post Reply