• State Verified Answer
  • Locked Locked
  • Replies 8 replies
  • Answers 2 answers
  • Subscribers 83 subscribers
  • Views 4033 views
  • Users 0 members are here
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

SCADA Signal SQL Error

Stephen Jackson
Offline

I am using WaterGEMS SELECTSeries 6, build 08.11.06.113 32-bit.

I am attempting to set up a SCADA Signal connection to a GE Proficy Historian database. The connection has been successfully established, and by following the instructions in the article listed below I can get the list of tags to show up properly, and configure the SQL queries as needed. However, the actual queries fail. 

The Signal Data SQL Statement I am using is:

set rowcount=5000, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where (@requestedsignals) and ((TimeStamp>=1/1/2015) and (TimeStamp<=Now))

If I check the 'View SCADA log', I can see that the problem is an improperly constructed SQL statement:

set rowcount=5000, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where ([Tagname]='CHURCH_DISCH_PSI')  and ((TimeStamp>=1/1/2015) and (TimeStamp<=Now))

It has put brackets around the Tagname field. In the Edit Database Connection window, under Advanced, there are options to set the SQL Query Field Name Delimiters, which by default have brackets. I have deleted these, so there should be no prefix and suffix added, and yet the brackets are still appearing.

If I modify the query and replace "@requestedsignals" with a specific tag, such as "Tagname='CHURCH_DISCH_PSI'", the data points are properly imported. So I know that all other parts of the query are functioning as desired. How do I get @requestedsignals to properly populate the tagname query?

(By the way, if anyone else has the issue that you can't establish a database connection to something like Proficy Historian, and get an error that the provider is not registered on the local machine, try using the 32-bit version of WaterGEMS rather than the 64-bit version. The 32-bit application is located at C:\Program Files (x86)\Bentley\WaterGEMS\WaterGEMS.exe. This appears to be an issue with how and where dll files are registered.)

Referenced article:

communities.bentley.com/.../24309.configuring-scadaconnect-for-ge-historian-ge-proficy

  • 0 Offline
    Steven, our guy who set up SQL in SCADA says

    "The default character put around tags (in order to allow spaces within tags) is the opening/closing bracket.
    You must not delete the delimiter but specify a single quote character instead. A space character is also fine if your tags do not contain spaces and you want to get rid of the additional decoration."

    Answer Verified By: Stephen Jackson 

  • 0 Offline in reply to Tom Walski
    Tom,

    Replacing the brackets with a single space character worked. Thank you!

    (Note to anyone else trying to set up a connection to Proficy Historian: the Advanced Options that work for me are Date/Time delimiters of a double quote ("), and Field Name delimiters of a single space. I am currently using explicit dates rather than @startdatetime/@enddatetime for the reason listed below.)

    I have two follow up questions to finish getting the SCADA signals set up.

    1)If the SCADA query is getting the entire range of available data, when you open a SCADA Element graph the view defaults to the entire range of data (which may be months or years). To zoom in on the simulated time you need to open the Chart Options and change the bottom axis min and max values. Is there a way to have the SCADA Element graphs default to showing the simulated time range as opposed to the SCADA time range? The workaround is to always change the date range in the SCADA Signal query every time you change the simulation start or end time, but it would be convenient if these could be linked in some fashion.

    2) When importing SCADA Elements from GIS, what is the appropriate way to import the Field setting (i.e. Pressure, Level, Flow). If I import using the 'Field (label)' property or 'Field (ID)' on a text field that says 'Pressure' or 'Flow', I get a warning 'Unable to assign attribute "Field" for element because enumeration value "Pressure" is not valid'. If I use the 'Field (ID)' on an integer field it does not give any warnings, but I have tried a range of values and nothing results in a field value being populated, and the behavior is different depending on what integer I try.

    For example, trying to import a SCADA Element that is linked to a Junction, using the Field (ID) property on an integer GIS field:

    For values 1, 2, 3, 4: The Field shows up as blank, and if I try to manually change it using the dropdown I get an error "The given key was not present in the dictionary"
    For values 53, 54, 55: The Field shows up as <none>, and the dropdown works. So there is no error, but it didn't import the setting so everything would have to be set manually.
  • 0 Offline in reply to Stephen Jackson
    Hi Stephen,

    For "2) When importing SCADA Elements from GIS...", I assume you are referring to ModelBuilder. You can determine the correct enumeration values to specify by creating a model with the desired assignments, and exporting that out to see what values to use.

    Rob.

  • 0 Offline in reply to Rob Gurrieri
    Rob,

    Yes, I am using ModelBuilder. Can you clarify what you mean by exporting?

    I tried File --> Export --> Export to Excel and got a table that has the plain text values for Field (i.e. Flow, Pressure, Level).

    Here's what I think is going on:

    Problem 1: 'Field (label)' import is not working due to some issue with the configuration of the ModelBuilder enumeration code. If you have a text description (i.e. Flow or Pressure) under 'Field (label)' in ModelBuilder, it is imported to the SQLite database as a 0.

    Problem 2: 'Field (ID)' import is not working because the SQLite database is expecting negative numbers, but ModelBuilder defaults to 0 if a negative number is encountered. The end result is stored in the database in the ScadaElement_Scada_Data table under TargetAttribute. Creating the elements from scratch in WaterGEMS shows that Flow/Pressure/Level are values are stored as -44, -54, and -55 respectively. However, if I store the negative values in GIS and try to import under 'Field (id)', ModelBuilder defaults these values to 0. If I store the positive numbers (44, 54, 55) in GIS, ModelBuilder imports the values to TargetAttribute, but since WaterGEMS is expecting negative values it does not recognize the attribute.


    -Stephen
  • 0 Offline in reply to Stephen Jackson
    Hi Stephen,

    Sorry I should have been clearer.

    Do a sync out in ModelBuilder.
    (Create a model with the desired assignments, and e.g. an excel spreadsheet with the appropriate tables(sheets)/columns that you can sync out to).

    Rob.

Communities
Support and Services
Training and Learning
Social Media