[Imported] Collation used by ClearSCADA for the ORDER BY clause in SQL queries

[sbeadle]

>>Message imported from previous forum - Category:Scripts and Tips<<
User: dlepeire, originally posted: 2019-04-04 08:12:01 Id:395
Hi everyone,

I've been using ClearSCADA since release release 2010 R2 and now I'm using release 2017 R3.

I've noticed that when I execute an SQL query in ClearSCADA (in a Mimic Script or a Logic Program) or in the QueryPad tool, containing an ORDER BY clause on a field of type string, to sort the results, the sorting order does not look "natural" to me.

Here is an example of the returned resultset for the following SQL query : "SELECT Name FROM CTemplate ORDER BY Name"

Row | Name
=======================
0 | 13_test_order_by
1 | 1_test_order_by
2 | 3_test_order_by
3 | Test_order_by
4 | _test_order_by
5 | test_order_by
6 | é_test_order_by

As a French user, I was expecting the following sorting order :

_test_order_by
1_test_order_by
13_test_order_by
3_test_order_by
é_test_order_by
Test_order_by
test_order_by

This is the sorting order that I could get in another database system like SQL Server 2017, configured with a collation "French_CI_AS" (Case Insensitive + Accent Sensitive)

Now I have 2 questions for you guys :

1) Do you know which "collation" is used internally by the SQL engine implemented in ClearSCADA ? To me it looks like the strings in the above example have been sorted **by ascending order of the ASCII code of the first character** of the Name (digits first, then uppercase letters, then underscore, then lowercase letters, then accents)

2) Do you know if there is a way the ClearSCADA server configuration or in the Windows registry to change the way ClearSCADA will sort the strings in an SQL query containing an ORDER BY clause ?

Thanks for your help,

Regards,

David

[sbeadle · Accepted solution]

>>Responses imported from previous forum

Reply From User: adamwoodland, posted: 2019-04-05 01:21:59
Hi David,

I don't have an answer to either of your questions, I suspect the answer to the second question is no though.

Independently of any discussions that may happen here, I would recommend you open a ticket with support on this so it can get to the right people to perhaps get an improvement implemented.