Order by fails

[dbxsda445src]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.