I’m using IS 6.1 and JDBC Adapter 6.0.3. I have a flow service that loops over a document list, calls a Java service to create a ‘unique’ key value, and uses a JDBC adapter insert service to insert a record into a table for each item in the list.
When I trace or step through the service, it completes with no errors. When I execute the service some of the inserts fail because of a primary key violation reported by the SQL Server database:
678 Violation of PRIMARY KEY constraint ‘PK_OrderItem’. Cannot insert duplicate key in object ‘dbo.OrderItem’."
I’m attempting to use my generated value as the primary key. Yes, I realize that it would be preferable to let the database generate it, but I inherited the process and it would be a significant effort to change it. Anyway, when I log the values during service execution, sure enough the values are not updating/resetting prior to insert. When I debug, they are.
I’ve tried numerous tactics to clear the old value from the pipeline after each iteration
- Using the java service as a transformer and as a separate flow step
- Mapping the key to a variable and mapping the variable to the document and dropping it after the insert (in a separate MAP step even).
- Mapping the key to a variable and mapping the variable directly to the adapter service input and dropping it after the insert.
- Inserting a clearPipeline step after the insert and only preserving necessary objects
- Altering the Java service to attempt to find and destroy any existing key value before inserting the new value into the pipline
I have also tried numerous small variations on the above. As I say, this is difficult to solve since it doesn’t happen when debugging. Oh, I also understand that the explicit transaction steps don’t behave correctly when stepping or tracing, as different threads could execute different steps, compromising the transactional integrity. Also doesn’t matter if I enable or disable those steps.
I feel I am overlooking something obvious. Any ideas?