Order by problem

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQL Server in Delphi and C++Builder
Post Reply
Jelly
Posts: 18
Joined: Sat 09 Jul 2005 13:49
Contact:

Order by problem

Post by Jelly » Mon 20 Nov 2006 14:40

We have a strange behaviour in the following SQL statement:

Code: Select all

SELECT 
    TruckPlanningList.VBELN_VL, TruckPlanningList.ActivationDate, HostState.HostStateId,  HostState.Name AS HostStateName
FROM  
    TruckPlanningList with(nolock) 
INNER JOIN
    HostState with(nolock) ON TruckPlanningList.HostStateId = HostState.HostStateId
order by 
    HostState.Name, TruckPlanningList.ActivationDate
In the query Analyzer of the SQL Server 2000, it gets to the following, correct result:

Code: Select all

VBELN_VL   ActivationDate                                         HostStateId          HostStateName                                      
---------- ------------------------------------------------------ -------------------- -------------------------------------------------- 
ABCDE12346 2006-11-20 14:50:05.977                                1                    Active
ABCDE12345 NULL                                                   0                    Inactive

(2 row(s) affected)
If I run EXACTLY THE SAME statement in a TSimpleDataset in Delphi, using the dbexpress driver for MSSQL (dbxsda200.exe), the order is wrong. It gets to the following:

Code: Select all

VBELN_VL   ActivationDate                                         HostStateId          HostStateName                                      
---------- ------------------------------------------------------ -------------------- -------------------------------------------------- 
ABCDE12345 NULL                                                   0                    Inactive
ABCDE12346 2006-11-20 14:50:05.977                                1                    Active

(2 row(s) affected)
Is this a bug?

Jackson
Posts: 512
Joined: Thu 26 Jan 2006 10:06

Post by Jackson » Tue 21 Nov 2006 09:18

We couldn't reproduce the problem.
Please send us (evgeniym*crlab*com) a complete small test project to reproduce the problem;
it is desirable to use Northwind or Master schema objects, otherwise include definition of your own database objects; don't use third party components.

Also supply us following information
- Exact version of Delphi or C++ Builder
- Exact version of DbxSda. You can see it in ReadMe.html
- Exact version of Microsoft SQL Server and OLE DB provider that you use.
You can see it in version info of SQLOLEDB.DLL and SQLNCLI.DLL.

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

Re: Order by problem

Post by Albervan » Mon 12 Mar 2018 16:19

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 Jelly's post, regarding the ordering of our queries.

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).

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

Re: Order by problem

Post by Stellar » Mon 19 Mar 2018 15:03

In order for us to analyze the issue, please provide us with the query that demonstrates it, as well as a script for creating tables.

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

Re: Order by problem

Post by Albervan » Fri 06 Apr 2018 03:19

We're solving this problem in the topic:
viewtopic.php?f=10&t=24794

Post Reply