New to Opto 22, Need help figuring out SQL communication


#1

Hey All,

I just started using Opto 22 products, and forgive my ignorance, but I have some questions on setting up SQL communication through OptoDataLink.

I have a process that runs, and at the end of it’s run I want it to write to a database Date/Timestamp, and Duration of that run. This process runs for ~3 mins, pretty frequently.

I saw on OptoDataLink I can establish it to read from my PAC device at some defined interval. How can I set it up for the PAC controller to simply write to it? How do I format it in such a way that it sends those in it’s correct form (date/time stamp, duration as a double?)

Would this be done by keeping duration as a variable, and then somehow writing the date/time of when it writes and then pulling that variable? How does the PAC handle network issues? If the connection is down does the data get lost or is it stored? Would the process continue to run even without a proper connection? How can this be handled in the most reliable way?

Still trying to get a firm grasp of how to use this PAC system, coming from labVIEW it’s been a pretty large change. Any help is appreciated!

Thanks!


#2

I guess my questions were premature. I was able to figure all this out with a little tinkering around in OptoDataLink.

However, I have a question about how to do network buffering? My PAC system is located offsite from our office, and I VPN in to modify the PAC if necessary. That also means that OptoDataLink is running from the office. However, I would like to understand what is the best way to accomplish this with Opto 22? If the connection is down, should my PAC continue to write to a table or something? Then, when connection is reestablished my computer pulls all of the table?

Please let me know what direction I should head in creating a more reliable database connection with Opto 22.

Thanks!


#3

Hello Tooshay,

Welcome to the OptoForums! Your idea of “a table or something” is a good one, since ODL doesn’t do any buffering if the network is down. In particular, you’ll want a PERSISTENT table (the data persists through a power cycle).

You could use a concept similar to what’s in the [U][B]Message Logging example[/B][/U] – which shows how to move messages from the message queue to a persistent table and then set a flag as a trigger for PAC Display (or in this case ODL) to log one sample. If there’s a backlog, it will move one item from the table to a string and then set the flag to trigger that one string to be logged. Then when the notification comes back indicating that one is done, it goes to the next table index for processing, etc.

More examples you might like: [U][B]FTP/Data Logging Example[/B][/U]
http://www.opto22.com/site/downloads/dl_drilldown.aspx?aid=3614

and a few more log-related examples here in the [U][B]Code Samples and Tips Greatest Hits[/B][/U]]:http://forums.opto22.com/t/greatest-hits-and-mini-lessons/443

I hope that helps!

-OptoMary


#4

Hi Mary,

Thanks for the reply! Discussed this and I think we want to do something a little bit different from the Message Logging example. Maybe you have some idea how I can do this?

We’ve decided to just write directly to the microSD card on the controller immediately after a certain process. We’re going to have a different chart continually reading the microSD card, if there’s data in that card it will read the test data for the oldest ran test on the card and then write to DB. If it can’t communicate to the Database it will continue to hold onto those values until its connection is re-established. Once that’s done it will purge that data and then move on to the next data set, ideally sending all the data out quickly upon reconnecting.

My question involved how to purge the data efficiently from the .txt. I’m set up currently to append each data set to the end of the .txt. But how would I delete the top data set of the .txt file? All I could come up with was saving the whole .txt to a string table, deleting the first data set (say index 0-7) and then just writing index 8-??? back to the .txt.

A secondary question to that is, can the persistent table data be stored through the microSD card? This is more as a concern for memory. If that’s possible then the whole .txt isn’t an issue and I can just store all of that data in the table and just shift the table to delete the top portion as data is sent.

Thanks!


#5

Did you get this figured out? I think you might have asked something similar elsewhere?

You can append to the end of a file, but modifying some other part would require starting a new file and copying only the parts you want to keep. Might be better to have more, smaller files and delete those you no longer need.

I’m not sure I understand your “secondary question” here. To get data from a persistent table into a file, you’ll need a comm handle. If you’re concerned about running out of memory, you might find this [U][B]tech note on Memory Usage (form 1646)[/B][/U] helpful.