Simplifying transaction groups

I’m looking for a more elegant way of doing a recipe change.

[li] -Check for change to Status field in DB to indicate a changing recipe.[/li]
[li] -Write to Status field to indicate a recipe change in progress.[/li]
[li] -Update recipe values using from database.[/li]
[li] -Write to Status field to indicate the recipe change has completed.[/li][/ul]

I’ve had to toss out stored procedures due lack of support in the legacy database, and I can’t change the database schema as the recipe generation is handled by an existing application (hence the handshaking).

I’m currently planning on scripting this, but transaction groups seem like they might be viable alternative. Any ideas?

You may or may not be able to get a standard transaction group to do what you want.

  1. Set your (execute once) trigger to an (SQL Type) expression tag that queries for the trigger.
  2. You can have another item in the group do a write (is this to a PLC register or DB value). You may have to override the item type from the group (OPC->DB for example).
  3. That’s what the group does
  4. Check “write handshake on success”

Possible issues:

  1. Your schema. You can do some mapping, but it depends on what you need exactly.
  2. “in progress” indication. I’m not sure that you can currently guarantee that this happens first. Is it critical that this write occurs first?

This video from 2008 uses FactorySQL to illustrate a technique to do batch read/writes. The SQL Bridge module in Ignition is pretty similar, although the interface looks different. You can also add another DB column to identify which batch and trigger either way (for a “read” versus “write”). Does this apply at all to what you’re looking for?