Verifying Database entries

So here is what were doing and what I want to do:
We hard currently using the ignition designer to pull data from our plc’s to help track parts. Things like test readings and verifying that a part has completed stations. Something we would like to do that we currently are not is verifying the data on the database after it is sent. We figured out a way to do it using only tags and just retriggering a read after we send the data over but it is cumbersome and frankly not very sophisticated both on the plc side and the ignition designer side.
So what it comes down to is what are good/the best ways to verify data has made it to a database and matches what I sent?

Hi there,
The system.db.runPrepUpdate function is used to insert or update data in a database. The function returns the number of rows that have been affected in the database after making changes to the database. So you know if the function has successfully inserted/updated data in the database by looking at its return value.

Why would you need to verify that what you just inserted into a database is in the database? Why would it be different? Can you explain more why this is needed?

Best,

Some of the reasons are that our database is sight so there is the possibility for data lost or corruption over a few miles of cable. We would also like to just be sure the data is getting there without having to open up ignition each time to check. Also want to make sure there isn’t a lost of connection to the database as well but that’s a different thing entirely.

Basically one of the concerns is that we could think we are actively moving/updating numbers into the database but not actually doing so. We had this happen to us where the database wasn’t collecting data for week or so because we had no reason to actively check and make sure as it is only really used to track issues when they come up. So I was asked if I could make a sort of feed back to make sure that data is being put onto the database and is updated to the right spot. Whether that be a fault on the database side or the machine side.

Okay. Here are some tools:

  1. You can use try/catch to catch exceptions that are raised when you attempt to use a function such as system.db.runPrepUpdate and it fails due to something wrong with your database connection. When you catch an exception you can set an alarm or send an email or whatever you want to do to notify you that the database insert/update failed. Here’s some links about exceptions:
    wiki.python.org/moin/HandlingExceptions
    perfectabstractions.com/blog … nt-scripts

  2. You might also want to create a simple Ignition project that runs a database query and displays all the data you expect to see. This is a quick manual way of verifying that the data is coming in how you expect.

  3. You could write a Gateway Timer script that does a database query that verifies that all the data is coming in as expected.

If your database uses a TCP/IP connection (such as used by MySQL and MSSQL and others) you are very unlikely to have data corruption because of connection problems. Various integrity checks are done and data is resent etc. If a database function isn’t able to completely and correctly send a query to the database then it fails and an exception is raised so you catch it and do something with it.

  1. Also, if you have multiple insert/update queries that you want to either succeed or fail together, then you will want to consider using database transactions.

Best,

You can also use the Store and Forward system in Ignition. The store and forward system was designed for storing data in databases over unreliable connections.

Transaction groups already use the store and forward system.

You can also use this database function which uses the store and forward system: system.db.runSFPrepUpdate

Best,