I have many DBI-based query sequences that look like this:
*** begin transaction ***
query #1: select ...
query #2: update ...
query #3: select ...
*** end transaction ***
If query #2 fails due to an exception (say, a duplicate key error), I can
identify that error **per query** via the DBI API, but then there appears
to be no possibility of even accessing the db again until/unless a
rollback is issued. All subsequent queries result in the famous line
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
I know this is an issue that has been discussed previously and a true fix
involves a ton of work that nobody really wants to do; I'm looking more
for workaround ideas.
One obvious (and very ugly) solution is to wrap every db access with
logic to catch errors and additionally then issue a rollback and begin a
new transaction to enable additional queries. I like to find a cleaner
way to manage errors at a finer level of control granularity that still
enabled delegation of logic without major contortions.
Does anyone have any experience with a cleaner manner in which to handle
this issue?
Regards,
Ed Loehr