[Imported] Collation used by ClearSCADA for the ORDER BY clause in SQL queries
>>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"
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 ?