Order by fails
-
- Posts: 8
- Joined: Mon 09 Jan 2012 09:46
Order by fails
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
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
Re: Order by fails
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.
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.
-
- Posts: 8
- Joined: Mon 09 Jan 2012 09:46
Re: Order by fails
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.
Re: Order by fails
I have executed the following code:, and there were no problems with fields ordering. Please try to reproduce the problem using this 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;
-
- Posts: 8
- Joined: Mon 09 Jan 2012 09:46
Re: Order by fails
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
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
Re: Order by fails
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.
Re: Order by fails
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).
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).
-
- Posts: 8
- Joined: Mon 09 Jan 2012 09:46
Re: Order by fails
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
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
Re: Order by fails
Great day!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:
...
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;
Re: Order by fails
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.
Re: Order by fails
Thanks for the reply "Stellar".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.
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?
Re: Order by fails
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.
Re: Order by fails
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
Re: Order by fails
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];
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];
Re: Order by fails
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.
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.
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'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.