I am using a transaction group of type OPC to DB. In this transaction group, I am sending data from PLC to DB via ignition. I have few failure cases to handle as mentioned below.
Case 1 ) When I send data which already exists in DB(duplicate record for Primary Key) , In the logs I can see an error saying that
**java.sql.BatchUpdateException: Violation of PRIMARY KEY constraint 'PK_LotHistory'. Cannot insert duplicate key in object 'dbo.LotHistory'. The duplicate key value is (test, 654, job2).**
How can i access this failure response in the transaction group itself , So that based on the response, i can send a failure response to the PLC for that particular transaction.
the handshake tag which is available as part of transaction groups doesn’t help in this case, it still shows success though in reality it’s a failure.
Case 2 ) If I send an extra column which doesn’t exist in the DB, It’s an error in this case too which doesn’t reflect via simple handshake tag.
I’ve attached multiple images at the end related to each case and scenario.
Many other cases are possible where handshake indicates it’s a success but in reality it’s a failure transaction.
I would like to read the SQL query response and store it in a tag and send a success/failure to PLC via another tag.
I understand that Triggerred Expression items will help with this because, we can write a SQL query and return Integer or string based on the response. Guide me with that if possible.
Hope to hear back from someone who has a suggestion to get a solution for this.