Error forwarding data: The INSERT statement conflicted with the FOREIGN KEY

Ignition
1

Hi everyone,

I’m facing an issue with some of my transaction groups on Ignition Designer. I’m updating a DB table with a few foreign keys column in it from a PLC (OPC to DB), I do it on trigger (OPC tag) and I write a handshake on success on an other tag.
I send my data, I see it in the group, I receive a handshake, however half of the time the data doesn’t go to the DB table and I sometimes receive this kind of message bellow.

Can someone please enlighten me about this.
Thank you.

Error forwarding data
java.sql.BatchUpdateException: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_ANDON_PROCESS”. The conflict occurred in database “IEAUTOMATION”, table “dbo.PROCESS”, column ‘ID’.
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1132)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeBatch(DelegatingStatement.java:60)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeBatch(SRConnectionWrapper.java:735)
at com.inductiveautomation.ignition.gateway.history.BasicHistoricalRecord.storeToConnection(BasicHistoricalRecord.java:256)
at com.inductiveautomation.ignition.gateway.history.sinks.RecordDatasourceSink.storeDataToDatasource(RecordDatasourceSink.java:40)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:137)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:115)
at com.inductiveautomation.ignition.gateway.history.sf.sinks.AggregateSink.storeData(AggregateSink.java:170)
at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:155)

2

You’ll need your database person to decipher what the foreign keys are doing, but it is normal to reject an insert or update that violates them. You probably need to set your transaction group to bypass the store-and-forward system if you wish your success notification to truly reflect a successful insertion. When the store and forward system is enabled, “success” in the transaction group just means it got into the store and forward system.

1 Like
3

Oh Thank you, didn’t know that the store and forward send a success too, I will bypass it to do some tests.
Thanks again.