I'm curious about the original issue, so I went back to the original email.

Why would the second insert fail? Not knowing anything more about the program, I can only imagine that there is some type of editing that might cause it to fail between inserts.

Would it be possible to create a duplicate (empty) file in qtemp, insert the two records into the temporary file, then copy the two records into the master file at the end of the cycle? If the second record has to be rejected, the copy would not take place and the temporary file cleared for the next try.

Or is there some other issue that I'm not aware of that would cause the second record not to be written?

Also, although there is no unique key to the file, the two records are "complementary", how does the system know that they belong together? Does the system have a way to weed out orphans?

There's so much more to this situation that needs to be known to offer solutions.


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg Wilburn
Sent: Friday, May 13, 2022 10:14 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: How to Undo an SQL Insert

I'm working with a legacy transaction file from our ERP (i.e. Commit=*none).
My SQLRPGLE service program procedure inserts two records/rows into the file (they are complementary rows - both are necessary)

If the first insert fails, I simply exit the procedure with an error.

But I'm not sure how to handle the situation where the first insert is successful, but the second insert fails. If this occurs, I need to remove the row I successfully inserted.

Is there some way to capture the exact row I inserted successfully? Like maybe the relative record number? Or am I out of luck here.

[Logo]<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.totalbizfulfillment.com_&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=LifWsbmXTz4aXbMMEwOi1aJ3sWD5nsP_QI4reJ8MbDM&m=1dkg_DqhIjM8ah7B3doXYRSnE0O9hAbcNm9zgeNI25M&s=p4sOd3nZ61iMiEEVeY7JWJYjC2jQx7NB4uGWVhu1vWY&e=> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
1 Corporate Dr
Grantsville, MD 21536
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_rpg400-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=LifWsbmXTz4aXbMMEwOi1aJ3sWD5nsP_QI4reJ8MbDM&m=1dkg_DqhIjM8ah7B3doXYRSnE0O9hAbcNm9zgeNI25M&s=J7N6kZpgV9lFTFVSe8XnOkArVvvK5GcHuD4a1p5ffnQ&e=
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_rpg400-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=LifWsbmXTz4aXbMMEwOi1aJ3sWD5nsP_QI4reJ8MbDM&m=1dkg_DqhIjM8ah7B3doXYRSnE0O9hAbcNm9zgeNI25M&s=VairlStb2EU7F3r4FFmbNXKgLbcS9S11k_arYweLepg&e=.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support https://urldefense.proofpoint.com/v2/url?u=http-3A__midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=LifWsbmXTz4aXbMMEwOi1aJ3sWD5nsP_QI4reJ8MbDM&m=1dkg_DqhIjM8ah7B3doXYRSnE0O9hAbcNm9zgeNI25M&s=of_Y7nZ6ZPhbZRfDd92f29S1N4ny3uFCpDCl04TmqXU&e= by shopping at https://urldefense.proofpoint.com/v2/url?u=http-3A__amazon.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=LifWsbmXTz4aXbMMEwOi1aJ3sWD5nsP_QI4reJ8MbDM&m=1dkg_DqhIjM8ah7B3doXYRSnE0O9hAbcNm9zgeNI25M&s=U26QCnWsO8ifg6gmIeLgG2TJcuE7NR6l-FCKVPveFmQ&e= with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=LifWsbmXTz4aXbMMEwOi1aJ3sWD5nsP_QI4reJ8MbDM&m=1dkg_DqhIjM8ah7B3doXYRSnE0O9hAbcNm9zgeNI25M&s=Pnjzgh43Iv5Zo33G1WoUChEEGsfohN6i2RevN435M10&e=
CONFIDENTIALITY NOTICE: This electronic message transmission is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. If you have received this transmission, but are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this information is strictly prohibited. If you have received this e-mail in error, please contact NALC Health Benefit Plan at 703-729-4677 and delete and destroy the original message and all copies.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.