Working with external databases in ClearSCADA

-
I've been working with an external SQL database lately & trying to use ClearSCADA to present some data from it.  The hope is to give my users a single login & a cohesive user interface.  I realize ClearSCADA is not designed to be a general front end platform for any database, but given the fact that it offers the ability to query linked tables, I'm trying.  Below is what I've found so far in terms of limitations.  If anyone from Schneider happens across this, it may be good feedback for the developers. 
  • Joins appear to be done in ClearSCADA rather than the RDMS.  This leads to some very inefficient queries.  I've seen one of my queries turned into 20 queries and then joined in ClearSCADA.
  • The Mimic SQL List refreshes every 10 seconds.  Would be quite handy if this could be disabled and refreshed manually (via button or script).  On heavier queries, the page essentially freezes while the query runs.
  • There is no way to get a value from a row displayed in the mimic SQL list.
  • Any SQL queries built dynamically (based on user interaction) utilize VBScript which runs on the user's machine.  So, the content of the SQL queries could probably be read a potential hacker.  This exposes the internal structure of your database.
  • Are queries scrubbed for SQL injection?
  • When dealing with an external database, you must still use ClearSCADA's (fairly limited) flavor of SQL.  It would be very nice to use TSQL when talking to a SQL Server database for example.
  • The mimic list filter functionality also exposes database structure to the end user and allows them to change the query to perhaps display data that they were not intended to see.
  • For many users the browser will freeze if list displays more than 300 rows or so, but there is no paging functionality for external databases
  • SQL errors are shown directly on the grid which again exposes underlying database structure.
  • The "TOP" clause is processed in ClearSCADA as well (entire result set pulled into ClearSCADA, then pared down for display).  Again, this introduces inefficiency issues. Queries that 300ms in SQL Server, take several seconds in ClearSCADA.  Would not be a huge issue except for the fact that it runs every 10 seconds.

Again, I realize I'm doing more with ClearSCADA than it is designed for, but these are just the issues I've encountered so far.

Comments

Post a Comment

Popular posts from this blog

Fixing Conan Lock Issues

-
I've been implementing Conan as our C++ package manager, and I had the following issue cause me a fair amount of grief over the last couple days: [MyPackageName] is locked by another concurrent conan process, wait... If not the case, quit, and do 'conan remove --locks'  You would be forgiven for thinking conan remove --locks should do the trick, but alas, it does not.  After finding hints on several Conan Github issues, I finally figured out that manually removing the package folders in one or both of the following places will alleviate the issue: Windows : C:\Users\username\.conan\data\packagename WSL/Linux : /home/username/.conan/data/packagename
Read more

Dynamic Object Oriented programming in ArchestrA

-
As with most developers using Wonderware products, I often have to get creative to compensate for the limitations in the development platform, scripting language, etc. Lately, I've been need to create "objects" (in the object oriented programming sense of the word, not the app server sense). That is I need collections of data describing some real world thing. For example, I have been creating a callout feature for a client's SCADA host system. The callouts are stored in a SQL database and my scripts pull out a whole set at a time. Each callout has various attributes such as the callout type, the comparison operator (>, <, =, >=, <=), the setpoint, etc. This presents two issues. First, how do I keep all of these pieces of information together for each callout without creating & deploying App Server objects for each one. Second, at any given time, there will be a variable number of callouts. I have used the following method (read complete hack
Read more

Initialize With Care

-
Pointers are funny things.  They are one of the make or break concepts for beginners, and even years later, they can cause grief to experienced developers.  I am no exception.  Here is one such story: I was faced with a class which I wanted to refactor.  It can be simplified as follows: In the interest of breaking up the responsibilities, I added a couple of interfaces. The idea is that I can pass around smart pointers to these interfaces and begin to decouple portions of the code.  For example, I can inject them into classes that need them: However, I made a mistake.  I blame it on years of using boost::intrustive_ptr instead of std::shared_ptr, but enough excuses.  Let's see if you can spot it. Do you see it?  If so, give yourself 5 points.  If not, maybe after seeing the output: 'second' going out of scope... Destructor 'first' going out of scope... 'root' going out of scope... All done... Both shared pointers ( first & second ) are created from the
Read more