Data exchange via local SQL DB table

I am currently working on a project where we use Ignition as the HMI of several machines. Each machine has its own PC and its own Ignition.

The customer has an MES system in the company to which each machine must pass a barcode, which it reads from the input carts that bring the material into the machine.
The MES system then sends back the material data for this barcode (number of pieces, piece type, weight, dimensions, …), which is then used by the machine.
The method of data exchange between the machine and the MES system is precisely determined by the customer.
Data exchange must take place via a local SQL database (MS SQL Express) located on the same PC as the Ignition on the machine.

The exchange should take place as follows:

  1. The PLC reads the barcode on the cart and forwards it to Ignition
  2. Ignition writes the barcode (and some other data) to Table A in the local database
  3. The MES system reads Table A
  4. The MES system writes the data for this barcode in Table B
  5. Ignition reads the data from Table B and forwards it to the PLC

The problem I have is that I currently have no idea how Ignition should reliably know or detect when the MES system has written new data to Table B?

I don’t know how the client has this solved on their site (they probably have some kind of service that scans all the databases on the machines), but he says that their MES system detects immediately that a new record has been inserted in Table A and that they write the necessary data back immediately in table B.

How can I detect a new record in Table B on my side in Ignition?
Has anyone ever done something like this before?
Any suggestions?

Add some sort of status column in table B. The db can assign a default value to it, so the MES doesn’t have to do anything extra. We use a similar approach with these values, as an example.

1 - Waiting for Processing
2- Processing
3 - Success
4 - Error 1
5 - Error 2
etc.

The external system writes to the table only what columns it needs to, with the db automatically putting a 1 in the status column. We just poll the database for a record that has a 1 in it, write a 2 to it during processing (which takes a 15-ish seconds), then write a 3 on success, or whatever error code is appropriate. After that we look for a record with a 1 in it, and do it all over again.

Thanks for the replay, Jordan.
Yes, we already have that kind of column in all tables.

As I said, the customer has specified/prepared all tables that are/will be in use and give them to us.

Maybe I wasn’t clear enough:
I know how to detect new records in the database with various queries and pull the data into Ignition tags and to the PLC.

What I’m not sure about is how/when to run these queries…

The more I think about it, the more it seems to me that there is no other way than to use the Gateway Timer script (1s, 500ms, …).
However, I am concerned about how this will affect the entire system (Ignition, SQL DB).
Or maybe I’m overreacting… :thinking:

Normally, I'd say the buffer cache should be sufficient for this operation. However, you'll need to balance SQL Server memory requirements to Ignition's.

That's probably just a way of saying it polls very fast. If you'd look into the performance statistics of the database, I guess you'll see many polling queries. MES systems are usually also slower than the PLC side, so "immediate" is likely quite relative here.

SQL generally has no problem running multiple queries per second. At least not when well designed. On one of our most DB heavy projects, we're currently running around 40-50 queries per second without issues.

The timing depends on what is expected by the client. 1s polling is probably quite good. Usually, tables in Ignition have 5s polling, and that's still ok for most people.

The only issue you may have is when the service was down, and has queued up a lot of requests. It may take a long time to catch up in that case. If you are afraid of that, you can loop over all queued messages inside the polling method, instead of just process one.

Thanks for your thoughts… :+1:

I ended up with Gateway Timer script 1s.
In my initial testing, all is good, but I’ll see later this week on the machine itself when I go to the commissioning.

1 Like