2

I'm using a TDBGrid connected to a TDataSource. This TDataSource uses a TADOQuery as its dataset. The TADOQuery connects to a Oracle10g database and holds the following query:

SELECT ST.desc
FROM my.subsection ST
WHERE  ST.date_disp  = :dated
ORDER BY ST.desc

ST.desc is a string, and generally contains data like these:

'1st place'
'2nd place'
'A zone'
'Her zone'
'My zone'
'Zone'

Everything works perfectly, except that when I run the query in SQLTools, it returns the data sorted by LETTERS first, then NUMBERS. e.g.:

'A zone'
'Her zone'
'My zone'
'Zone'
'1st place'
'2nd place'

But when I run the application, the result is NUMBERS first, and then LETTERS! e.g:

'1st place'
'2nd place'
'A zone'
'Her zone'
'My zone'
'Zone'

I figured out that Oracle uses one sorting linguistic (http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm) and some Delphi component (TDBGrid or TDataSource?) another.

I then tried to make the result of the query fit Delphi's component order, which is NUMBERS then LETTERS. But this is not desired since the natural order for the users is LETTERS, then NUMBERS.

Does anyone know how I can be sure of which component is "re-sorting" the data and how can I make both sort orders the same?

RRUZ
  • 134,889
  • 20
  • 356
  • 483
gustavogbc
  • 695
  • 11
  • 33
  • 1
    This sounds like Oracle has `NLS_SORT` or at least `NLS_LANGUAGE` set to a different codification than the language codification of the computer where you are doing the tests. I would start by unlink the `Tdatasource` of the query component and check in which order the elements are returned. – Guillem Vicens Jun 01 '13 at 05:56
  • What do you mean with: "I would start by unlink the Tdatasource of the query component"? I've already checked that they both return different orders... The whole question is about how can I check/change the linguistic used by the component in Delphi. – gustavogbc Jun 03 '13 at 15:43
  • the `TDbGrid` does no sorting on its own. It is just used to show the data. The sorting (if done) is usually done by the query component instead. – Guillem Vicens Jun 05 '13 at 06:08
  • I'm not convinced of that, @GuillemVicens. Please read below for further details. – gustavogbc Jun 05 '13 at 19:14
  • I must say I agree with what @GolezTrol says in his last comments. The sorting is almost surely done by the ADO components. Not sure if you did, but you could try checking you have the latest Oracle ADO driver as well as the latest ADO version. Hope you find the solution :-) – Guillem Vicens Jun 06 '13 at 12:13

1 Answers1

1

You can specify what kind of sorting/comparison is used by using the NLSSORT function.

SELECT ST.desc
FROM my.subsection ST
WHERE  ST.date_disp  = :dated
ORDER BY 
  NLSSORT(ST.desc, 'NLS_SORT=BINARY_AI')

Instead of specifying the second parameter, you can also call nlssort with just the value, and set the default sorting in the session (after you connect to the database). The advantage of that, is that you can easily make a global change to it later, or even let the user choose their favourite way of sorting:

ALTER SESSION SET NLS_SORT = 'BINARY_AI';

In reverse, you can check the current sorting: 

select value from v$nls_parameters
WHERE parameter = 'NLS_SORT'
GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • Thanks @GolezTrol but I already did that as a workaround. What I need now is to know how to make Delphi stick to Oracle's sorting order, not the opposite. – gustavogbc Jun 03 '13 at 17:50
  • Delphi will use the sorting of the dataset, which should contain the records in the order they are returned. What I'm saying is that Oracle doesn't have one sorting, but allows you to specify it. If you don't do any sorting yourself, then it's like this configuration difference that's causing the problem. – GolezTrol Jun 03 '13 at 18:52
  • Oracle will always have one sorting. It doesn't depend on whether I set it or not, but on the linguistic that is configured on it. What I can do is tell Oracle that I want a specific order by using one of the alternatives that you perfectly said above, regardless of the linguistic setup on it. Unfortunatelly this is not what I need, since everytime I try to "reorder" the items in the TDBGrid it uses its own sorting order. This order is what I want to know how to configure so that it matches Oracle's order. – gustavogbc Jun 05 '13 at 15:01
  • How do you try to reorder the grid? Do you have a custom sorting in your application? If so, how? – GolezTrol Jun 05 '13 at 15:13
  • 1
    I think the driver may set the sorting for the session based on your system's locale. So to check if the settings for your application is really the same as the setting for SQLTools, you can execute the query I added to my post. It allows you to check NLS parameters. If they are the same, then there's probably some sorting applied in Delphi after the result is fetched from the database. – GolezTrol Jun 05 '13 at 15:17
  • "...then there's probably some sorting applied in Delphi after the result is fetched from the database." Yes, there is! That is the point where I was in the beggining. :) To sort things in the DBGrid I'm using something like: `procedure TForm1.dbGrid1TitleClick(Column: TColumn); begin ... MyADOQuery.Sort := Column.Field.FieldName + ' ASC'; // or ' DESC' ... end;` – gustavogbc Jun 05 '13 at 19:03
  • 1
    Oooookay. :) It wasn't clear to me at all that you did some sorting yourself. :) In that case I think you're out of luck. It isn't Delphi at all that does the sorting. TAdoDataSet is just a wrapper around an ADO Recordset interface. It is the implementation of that interface that does the sorting. If you set the Sort property, you will eventually call TCustomADODataSet.InternalSetSort(Value: WideString), which calls Recordset.Sort. After that, the best place to continue your search is [this page](http://msdn.microsoft.com/en-us/library/office/aa165280(v=office.10).aspx). – GolezTrol Jun 06 '13 at 07:53
  • 1
    Maybe a TClientDataSet offers more possibilities to do custom sorting. If so, you can link a TDataSetProvider to your ADODataSet and link a TClientDataSet to the provider. After that, you can do sorting an filtering on the clientdataset, although I'm not sure about the possibilities to change the way TClientDataSet sorts. – GolezTrol Jun 06 '13 at 07:56
  • Thanks @GolezTrol. I'll keep on searching for a solution. I'll try your sugestion and let you know if there was any positive result. :D – gustavogbc Jun 07 '13 at 21:58