Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84611members
353910posts

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

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
sbeadle
Janeway Janeway
Janeway
0 Likes
1
487

[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"

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


Accepted Solutions
sbeadle
Janeway Janeway
Janeway
0 Likes
0
486

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

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

See Answer In Context

1 Reply 1
sbeadle
Janeway Janeway
Janeway
0 Likes
0
487

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

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