Forum Stats

  • 3,839,996 Users
  • 2,262,557 Discussions
  • 7,901,120 Comments

Discussions

ADF Table - not sorting my language characters like č,ć,ž correctly

Robert Šajina
Robert Šajina Member Posts: 94
edited Aug 29, 2017 4:55AM in JDeveloper and ADF

Hi all,

i have just noticed that default column sorting is not sorting correctly when there is my language characters (č.ć,ž,đ etc.).

Example

Sorting result:

     - c

     - d

     - z

     - ć

     - ž

Correctly:

     - c

     - ć

     - d

     - z

     - ž

Is there a global solution to solve this problem? Does the filtering executes just on the client?


Thank you

JDev: 12.2.1.2.0

Tagged:

Best Answer

  • kdario
    kdario Member Posts: 3,531 Silver Crown
    edited Aug 29, 2017 4:20AM Answer ✓
    I have printed records in createRowFromResultSet() and from there its already not sorted correctly

    So this is some issue with collation in database.

    Check global NLS parameters related to collation (NLS_SORT, etc.).

    If they are ok, check what is set on db session opened from ADF BC (for example, you can override AMImpl.prepareSession() method and manually execute sql query to retrieve current db session NLS parameters...)

    If nothing else helps, use nlssort() function in order by clause.

    Or you can manually change NLS parameters in AMImpl.prepareSession(), but this will be overkill.

    Dario

    Robert Šajina

Answers

  • kdario
    kdario Member Posts: 3,531 Silver Crown
    edited Aug 28, 2017 11:23AM

    Data sorting is server side operation.

    You need to configure right collation(set db NLS parameters globally or use nls_sort() in your sql query)

    Dario

  • Robert Šajina
    Robert Šajina Member Posts: 94
    edited Aug 28, 2017 1:47PM

    Hi kdario,

    Thank you for your answer.

    Can you elaborate your reply a little bit more?

    Also i have found out that it works on some computers. We have a GlassFish server with our application, and when using the same application, with the same table, the sorting works properly on some computer, but mostly it doesn't.


    Thank you

  • kdario
    kdario Member Posts: 3,531 Silver Crown
    edited Aug 28, 2017 2:26PM
    We have a GlassFish server with our application, and when using the same application, with the same table, the sorting works properly on some computer, but mostly it doesn't.

    Just to confirm: you have one GlassFish server and different end users are getting different sort results?  (that would be strange, except if you are programmatically changing db session parameters after user login)

    Or you have multiple GF servers and sort order is correct only on some of them? (this behavior can be explained in relation to my previous reply, because regional settings on underlying OS probably is not the same on all machines)

    And what is technology stack behind your af:table?  ADF BC + Oracle database  or something else?

    Anyway, sorting is server side operation so ADF BC will issue "select columns from some_table order by some_column" query.

    And row order produced by "order by" part of sql statement depends on something called "collation" (basically this is set of rules for linguistic sorting).

    So different collations can produce different sort order for the same set of data.

    In Oracle database, this is controlled by some of NLS parameters(globally) or on session level(with: alter session set ...), and you can override this behavior with "order by nlssort(some_column, 'some_collation')"

    Dario

  • Robert Šajina
    Robert Šajina Member Posts: 94
    edited Aug 28, 2017 2:36PM

    Hi Dario,

    Thank you for your response,

    Yes, one GlassFish server with different results on the end user. Application is still in the development so it is using the "default" connection to the oracle database(not changing anything, no users, just a connection to db).
    Technology is ADF BC and oracle db, yes.

    Perhaps it is depends on some browser settings? Or maybe reading client NLS parameters?

    Robert

  • kdario
    kdario Member Posts: 3,531 Silver Crown
    edited Aug 28, 2017 2:54PM
    Perhaps it is depends on some browser settings? Or maybe reading client NLS parameters?

    Only if regional settings from user browser is somehow propagated to db session(but as far as I know this should not happen automatically).

    You can try to debug this issue, for example:

    1. override ViewObjectImpl.createRowFromResultSet() method in your view object and print data from sorted column(so you can isolate issue to db or to adf layer)

    2. enable debugging for ADFBC and see what sql statement is issued to db (probably this will be standard "select ... order by column" statement, but who knows... )

       to enable adfbc debugging, see this blog post: http://huysmansitt.blogspot.ba/2012/09/adf-bc-logging.html

    Dario

  • Robert Šajina
    Robert Šajina Member Posts: 94
    edited Aug 28, 2017 3:20PM

    I will try this tomorrow, also will check NLS params

    Thank you for your help

    Robert

  • oladayo.s
    oladayo.s Member Posts: 48 Blue Ribbon
    edited Aug 28, 2017 11:39PM

    Try add column attribute sortStrength="Primary"  there are about 4 valid values.

  • Robert Šajina
    Robert Šajina Member Posts: 94
    edited Aug 29, 2017 3:18AM

    Hi oladayo,

    Adding sortStrength to column does not change anything.

    I have printed records in createRowFromResultSet() and from there its already not sorted correctly

    Robert

  • kdario
    kdario Member Posts: 3,531 Silver Crown
    edited Aug 29, 2017 4:20AM Answer ✓
    I have printed records in createRowFromResultSet() and from there its already not sorted correctly

    So this is some issue with collation in database.

    Check global NLS parameters related to collation (NLS_SORT, etc.).

    If they are ok, check what is set on db session opened from ADF BC (for example, you can override AMImpl.prepareSession() method and manually execute sql query to retrieve current db session NLS parameters...)

    If nothing else helps, use nlssort() function in order by clause.

    Or you can manually change NLS parameters in AMImpl.prepareSession(), but this will be overkill.

    Dario

    Robert Šajina
  • Robert Šajina
    Robert Šajina Member Posts: 94
    edited Aug 29, 2017 4:55AM

    Thank you Dario for your help,
    NLS_SORT was the problem. Changing that solved the issue.

    Robert

This discussion has been closed.