I’m using a SNAP-PAC R1 to run a piece of production test equipment. I need to be able to transfer test data and results from the controller to a SQL database. I’m using OptoDataLink (9.4e) running off a local server at our manufacturing plant to do the transfer. Due to some IT limitations we have relatively frequent network outages between the controller and server. The connection between the server and SQL database remains stable. I’ve handled this on the controller side by buffering data to an SD card when there’s a connection problem. Unfortunately, OptoDataLink will send duplicate rows of the last data transfer to the database until the connection to the controller is reestablished. The result is a messy data set with a lot of duplicates. I contacted Opto22 Product Support and was told that OptoDataLink is working as intended. Ideally if the connection dropped the last data transfer would be written to the database only once.
Has anyone else run into this? Is there a workaround? Are there any other ways people are sending data from a controller to a SQL database that I may be missing?
Attached is a screenshot of my OptoDataLink settings.
I don’t have a solution for OptoDataLink inserting the same data over and over until the network comes back up… Sorry.
Just wanted to let you know of a method that I have been using for eleventy eight years with no problems.
You can read about it here; http://www.opto22.com/community/showthread.php?t=380
I am pointing it out because data can only be inserted when the controller calls the script. If the network is down between the controller and the computer, no data will be inserted.
(When you look at that page, keep in mind you just need the first few steps, just enough to put the data into SQL, you don’t need all the graph stuff).
Honestly, it is not for everyone, but if you want really fine/total control, it might be the solution.
Also, I do not have any experience with SQL, since I am a bit of a Linux hack, it’s MySQL for me. I am just guessing that the same insert method used in MySQL will work in SQL.
Hope that this is helpful. There is usually many different ways to tackle things with Opto, just a matter of picking the one you like.
FYI - we’re adding a feature request to make ODL a little smarter, rather than just blindly sending the same data at a fixed interval. But that’s no guarantee if/when such a feature might get added.
In the meantime, besides working-around ODL completely, a few other ideas to ponder…
Use something like Kepware to interface to the SQL database, but you’d have to check with Kepware to see how they handle the same issue and if they have the option to not send data when the connection to the controller is lost.
Add a local controller (local to the OptoOPCServer computer) or just add SoftPAC on that same computer. Then have the local controller/SoftPAC get the data from the R1. Have OptoDataLink access the local controller/SoftPAC instead of the R1. The logic in the local controller would have to populate the data to the values you want to use when the local controller can’t get data from the R1. You may have to log the data in ODL via a trigger in the local controller instead of on a time base. This way if there’s not new data, nothing will get logged.
…and on the topic of using a trigger vs. just a time base, perhaps you could build a trigger into your existing system, even w/out these extra parts? Do you have a way of knowing if/when the network is up? Perhaps you could ping something from the PAC to make this determination? Or use the I/O watchdog in the R1?
While we’re brainstorming here, another OptoForum user suggested this method for setting up a “watchdog” between a PAC and the PAC Display computer. Perhaps something similar could work for you?
