Forum Home › Forums › Understanding the Software › SQL Updates to Minutely and Hourly Data
Tagged: SQL UPDATE historical data
- This topic has 8 replies, 2 voices, and was last updated 6 years, 1 month ago by Mikhail.
-
AuthorPosts
-
March 16, 2018 at 4:35 pm #3298yotoolParticipant
I know that it was mentioned that the actual “storage format” of the collected data by the “Server” service is done using a proprietary/open format.
…have successfully used the Windows-based grid “edit” feature (of the Server desktop App) for the “Minute” data. That is a nice feature for “fixing” a known-bad data point, or at least a handful of minor corrections.
Is there a SQL UPDATE feature/command (as in standard SQL) to UPDATE the stored historical data on a larger scale (including WHERE clauses etc)?
Where would one run this SQL command from and where/how would one establish a SQL connection to the MINUTE/HOUR channels historical value “database”?
The situation requiring this historical data update is that I ran into that “Incorrect PDU length” problem on my ModBus (Channel #1). It seems like my device may be “answering twice” for “only one question” as it were. I have a dirty Ethernet connection to my target caused by using Ethernet-Over-Power-Line adapters (my only short-term connection choice between the Scada Communicator and my data collection device[PoKeys PO57E]).
I added the delay between polls as that topic suggested, and time will tell if that fixes that problem. Unfortunately, most of the time when that “Incorrect PDU length” occurs, it will continually collect and save BAD channel data until the Scada Communicator service is restarted. At that point the BAD DATA has already been stored every minute until the Scada Communicator RESTART.
Thanks greatly for a suggestion.
March 17, 2018 at 12:36 pm #3304MikhailModeratorThe archive is not a real SQL database, so there is no SQL engine to manipulate it. Usually I write little command line apps to fix data in such situations. Probably, it is possible to use Linq which is SQL like to work with archive, but I haven’t tried this.
I can send you examples of these console apps, and you will modify them to fit your needs.March 17, 2018 at 3:02 pm #3305yotoolParticipantPlease do send me some command line examples. This would be very helpful.
Is there any documentation on the archive data’s actual file format?
What app is actually used to both view and edit the archive data (“data grid Editor” sub app) within the “Server” application?
Thank you for taking the time to support this app Mikhail.
March 17, 2018 at 3:24 pm #3306yotoolParticipant…just so that you know that I did try to find a solution by searching, I did also find these existing topics/documents (nobody likes a lazy forum user repeatedly asking questions already answered):
Modyfying Archive Data Forum Post
and
I was just looking for something quick-and-easy”.
You did show some good info about how this .NET DatTableobject and the SrezTableLight “entrancement” are implemented. Most of this is table-READ based however.
March 17, 2018 at 3:42 pm #3307yotoolParticipantSorry for the multiple posts, but I could not find a forum pose “edit” feature.
LINQ: .NET Language-Integrated Query (interesting possible approach…)
The answer to my question about the existing archive data file editor sub-app is also answered via the GitHub source code for that object/app:
(.Net C#)
* Product : Rapid SCADA
* Module : SCADA-Server Control
* Summary : Editing or viewing snapshot table formOne cool thing is that this archive data editing can all be tested/manipulated completely OFFLINE simply by copying one or more target archive data files locally “to play with”.
Nice work!
March 19, 2018 at 4:49 pm #3313MikhailModeratorMarch 19, 2018 at 4:50 pm #3314MikhailModeratorMost of this is table-READ based however.
You can write into a snapshot table (SrezTable). I will show how.
March 19, 2018 at 4:52 pm #3315MikhailModeratorLinq:
I think, you can select data from the archive using Linq, but I have’t tried before.One cool thing is that this archive data editing can all be tested/manipulated completely OFFLINE simply by copying one or more target archive data files locally “to play with”.
Yes, it’s true.
March 19, 2018 at 4:54 pm #3316 -
AuthorPosts
- You must be logged in to reply to this topic.