Quality Codes in a Stored Procedure Transaction Group

The main interaction between Ignition and the database is by stored procedures and stored procedure transaction groups so as to minimize SQL code written outside of MSSQL. Thus far I have become unsuccessful developing a method to return a “Success” or “Fail” to the user based on interaction between Ignition and the database.

Furthermore, I have found that occasionally upon an unsuccessful insert, update, or query, the “trigger tag” is not reset to its inactive state. Beyond this, the “handshake failure” option is not written to the assigned tag. Under these circumstances, there is an unsuccessful interaction with the database, the user is not made aware of the failure, and he cannot use the stored procedure again until the “trigger tag” is reset.

I have been unable to use the “store quality code” option on the transaction group screen - the group errors at every execution. I assume this is because SQL tags do not have the property option where one can choose “quality.”

I am at a loss to find a sure-fire method to maintain quality assurance.

Is there a way to provide a success or failure signal to the user on execution of a stored procedure transaction group? Would it be more efficient to utilize the return codes built into sql?

1 Like

I typically keep two results codes, one for Ignition and one for MSSQL. I’ll use the result code from the SPROC for MSSQL and the transactions success/failure handshakes. I keep these separate from the trigger; Ignition won’t reset my trigger, just react to it. Once the trigger fires, the controller the transaction is interacting with starts an internal timer. If the Ignition result code doesn’t get a succes/failure value when the timer expires, I’ll throw a “Transaction Timed Out” alarm.

Seems like there’s a lot to work through here, so let’s try to break it down:

  1. We need to determine what kind of failures are occurring, and why the failure handshake isn’t being written. Is the stored procedure throwing an exception? If not, the group may think everything is OK when it’s not. Do you see any kind of message logged in the group when the stored procedure “fails”?

  2. Store quality code: all items have quality associated with them. That particular feature combines the quality of all of the items in the group, and sends it as an integer to the specified parameter. What kind of error are you getting when you try to use it?

  3. Trigger not being reset: If an error occurs, the trigger won’t be reset. But, the failure handshake should be written. So, something is going wrong somewhere. Do you get any kind of messages in the group when these events occur?

Also, what version of Ignition are you using?