E9.2 how to know if one of the record insertions failed in an iterative database connectors?

[skwong]

Hi,
I am using database connector to insert number of records in a working file. Is there a way to know if one of the record insertion fails? The reason why I want to know if any of the record insertion fails is because if one of the record fails, I want to delete the whole batch of records inserted.


It is to implement kind of commitment control.

 

[JohnDanter2]

Why don't you do a select yourself afterwards in a data request and get the record count, set up a rule, if it's 0 it failed

 

[DaveWagoner]

If you are bypassing error then use a tactic like John's.

If you are not bypassing error, and the record insert fails mid-iteration, you can use an error handling orchestration attached to that step to "roll back" the records inserted up to that point. It's a "hidden" feature that I think many people haven't used because they can't see it

 

[skwong]

Thanks John.
Currently, each iteration of database connector can return the status itself. The output is like below.
{
"ServiceRequest1": {
"TIMESTAMP": "20240819151827",
"counter": 0
},
"ConnectorRequest2": {
"batch_number": 100001,
"item_sequence": 1000,
"responses": 200
},
"ConnectorRequest3": {
"batch_number": 100001,
"item_sequence": 2000,
"responses": ORA-00001: Unique constraint (F55IAP01_PK) violated.
},
"ConnectorRequest4": {
"batch_number": 100001,
"item_sequence": 1000,
"responses": 200
},
"ConnectorRequest5": {
"batch_number": 100001,
"item_sequence": 2000,
"responses": 200
},
"ConnectorRequest6": {
"batch_number": 100002,
"item_sequence": 1000,
"responses": 200
}
}

The input json file has an array of 5 records within a batch, and the database connector repeats 5 times. The second insertion hits the error, and the record is not inserted. Since there is an error within this batch, I would like to NOT 'commit' the operation. I do not want to insert any record of this batch. Any way to tackle that?

 

[skwong]

Thanks Dave.

I am using these statements to capture the error.
rescue Exception => e
anyError = e.to_s
end

if anyError != nil
return_hash["responses"] = anyError
else
return_hash["responses"] = "200"
end

 

[DaveWagoner]

I'm not sure I'd want to handle the error on each iteration without killing the parent orch, in your case. If you kill the parent (via scripting "throw exception") then you could just build in your single "rollback" of the previous inserts (in your case a rollback is just a delete, since I'm pretty sure you can't start a transaction, insert a bunch, then commit in disparate statements/connections)

 

[DaveWagoner]

Last but not least--- which version of JDE are you on? Do you have logic extensions? If so then LEX is custom fit for your scenario. You can handle your insert error, rollback, etc., all within the logic extension and ditch out on the outdated orch studio interface and scripting.

 

[skwong]

I am on JDE tools release 9.2.6.2. I haven't yet tried using LEX.

Last but not least--- which version of JDE are you on? Do you have logic extensions? If so then LEX is custom fit for your scenario. You can handle your insert error, rollback, etc., all within the logic extension and ditch out on the outdated orch studio interface and scripting.

 

[skwong]

I'm not sure I'd want to handle the error on each iteration without killing the parent orch, in your case. If you kill the parent (via scripting "throw exception") then you could just build in your single "rollback" of the previous inserts (in your case a rollback is just a delete, since I'm pretty sure you can't start a transaction, insert a bunch, then commit in disparate statements/connections)

I thought about using a 'delete' to remove all records by timestamp. however, there isn't a good way to know if there is any 'fail' during iterations.

 

[DaveWagoner]

I would really recommend jumping into logic extensions to do this. You can check your primary keys prior in a separate loop, if any are violated then you can just not continue to the insert loop and spit back a "operation stopped" message.

LEX is super easy if you've done NER/FDA/RDA event rules programming in the past. The table IOs work the exact same way.

 

[JohnDanter2]

If you want to delete all the records you just entered then add a UKID column to your worktable and use X00022 to get a UKID next number for that table.
Getit at the start of your run, keep it safe and map into your worktables UKID.
If you detected any errors, set a flag, at the end ask if the flag is not blank/null and delete those suckers!!!

 

[DaveWagoner]

If you want to delete all the records you just entered then add a UKID column to your worktable and use X00022 to get a UKID next number for that table.
Getit at the start of your run, keep it safe and map into your worktables UKID.
If you detected any errors, set a flag, at the end ask if the flag is not blank/null and delete those suckers!!!

I'm going to remember this for a future killer app thanks!

 

[skwong]

Nice. Thanks a lot.