Error Response from SQL Query while sending data via OPC to DB and DB to OPC transactions

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.

image

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.

You have to turn off the store and forward system for that transaction, then the failure handshake should work.

1 Like

So I’ve disabled the Store and Forward as you suggested. But I don’t see any change.

The handshake tag still shows Success(1). My handhshake buit is LotHistory/HandShakeOPCTODB bit in the screenshot.

Thanks
Anoop

Go to the “Options” tab of your transaction group to “Bypass Store and Forward system”.

1 Like

Thanks a lot and that helped me for OPC to DB transactions.

How about if I am doing a DB to OPC transaction and I am requesting limits from the database table for a DeviceID which doesn’t exist in the database. Even in this case, the handshake bit displays success though the real result is a failure.

In this case, bypassing Store and Forward doesn’t help because it’s a DB to OPC transaction.

What else is the solution here ?

In the below screenshot, you can see the LatchedValue column displaying Bad_NotFound but handshake bit which is at the last still shows 1.

Hope you can guide in this too.

I asked the same question to support team but no response yet.

https://support.inductiveautomation.com/index.php?/Tickets/Ticket/View/108024

One of the support team member gave the reply as below including the example in the image. I’ve tried the similar SQL Query expression item, It didn’t work in my case.

Hi Anoop,

One idea that comes to mind is creating a Triggered Expression Item which executes a query to check if the rows exist. If the row doesn't exist, the query could return a message or some status code to indicate such. Then one of the Basic OPC/Group Item tags can reference this Triggered Expression Item as a target so the PLC is aware something went wrong.

I've attached a screenshot which illustrates what I described. The transaction group updates my tags from the sqlth_te based on a row with a matching id however the id of 0 does not actually exist in my database table. The Triggered Expression Item runs the query and because there is no rows returned for that id, it sets the "status tag" to "corresponding values does not exist in DB". 

Regards, 
Kevin Tran
Inductive Automation
Defect Analyst
1.800.266.7798 (USA)
1.916.456.1045 (International) 

Thanks
Anoop

Huh, got me. I’ve generally scripted my SELECT operations instead of using transaction groups.

But in my case, does that work?

Using SQL scripting in expression items instead of transaction groups to get information from DB to PLC via ignition?

Is that what you meant? Can you elaborate?

Thanks
Anoop

No, not using a transaction group at all. Using a gateway tag change event or timer event with scripted data retrieval and SQL calls. Catching any SQL errors and scripting the tag writes with custom results.