RE: How to Undo an SQL Insert -- RPG400-L

× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.

MIDRANGE dot COM Mailing List Archive

Subject: RE: How to Undo an SQL Insert
From: Duane Scott <dscott@xxxxxxxxxxx>
Date: Fri, 20 May 2022 13:37:02 +0000
List-archive: <https://archive.midrange.com/rpg400-l/>
List-post: <mailto:rpg400-l@lists.midrange.com>
List-subscribe: <https://lists.midrange.com/mailman/listinfo/rpg400-l>, <mailto:rpg400-l-request@lists.midrange.com?subject=subscribe>
List-unsubscribe: <https://lists.midrange.com/mailman/options/rpg400-l>, <mailto:rpg400-l-request@lists.midrange.com?subject=unsubscribe>

Single question, dozen answers, two dozen to tell why they won't work. Sometimes.

But asking the question does make us think.

I tend to give too much information when I ask or answer. I'm too chatty.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg Wilburn
Sent: Thursday, May 19, 2022 2:20 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: How to Undo an SQL Insert

Duane,

The second insert should not ever fail, IF I have validated the data correctly prior to insert. I experienced a failure during development/testing. So I asked what I thought to be a simple question. Turns out it's not so simple.

While the two records created are "complimentary", there is no real connection between the two... they are really "offsetting" so that the change in Qty on Hand for that Company/Warehouse/Part nets to zero. There are two "header" or parent files that ensure these stay in sync. (They can get out of sync during abnormal ends to other ERP applications). There is a resolution program that runs.

IC Balance - Qty on Hand for a Company/Warehouse/Part (header) IC Detail - Qty on Hand for a Company/Warehouse/Part/Location (detail) IC Location Transaction Details (the file I'm working with) - transactions by Company/Warehouse/Part/Location

So when something creates IC Location Transaction Details, the background programs in the ERP update the corresponding (parent) IC Balance & Detail files.
Records are never deleted from this file - only created.

So I think I'm OK w/out a solution. Although I may try the journaling option mentioned by Charles.

Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Duane Scott
Sent: Thursday, May 19, 2022 9:22 AM
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: How to Undo an SQL Insert

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.

Duane

-----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.

TIA,
Greg
[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
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.totalbizfulfillment.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=LifWsbmXTz4aXbMMEwOi1aJ3sWD5nsP_QI4reJ8MbDM&m=1dkg_DqhIjM8ah7B3doXYRSnE0O9hAbcNm9zgeNI25M&s=gjXJds_WzBySBXMKFDiA5Ebr9L6OGS7LEDZZFWtfAdQ&e=<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.totalbizfulfillment.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=LifWsbmXTz4aXbMMEwOi1aJ3sWD5nsP_QI4reJ8MbDM&m=1dkg_DqhIjM8ah7B3doXYRSnE0O9hAbcNm9zgeNI25M&s=gjXJds_WzBySBXMKFDiA5Ebr9L6OGS7LEDZZFWtfAdQ&e=>
--
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.
--
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=UJI65KKLR_Dbl_jINlt-eRhd_bKegvdS4NavFTG6h6o&s=5KAaQtPsMqiWiJ_QzpTm09UNyB7n3R8WPlupR6sCxro&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=UJI65KKLR_Dbl_jINlt-eRhd_bKegvdS4NavFTG6h6o&s=zZSuJIpHaJYCQ242KvLOZR4jU6utFkYRd9Vn1A3IjjE&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=UJI65KKLR_Dbl_jINlt-eRhd_bKegvdS4NavFTG6h6o&s=Lz3Wx9gLd9pua7fe4DO3EsYOec6DGhI7lICoOej7gfw&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=UJI65KKLR_Dbl_jINlt-eRhd_bKegvdS4NavFTG6h6o&s=9omR_vhZIv-fOyvnOiALw6nK99dCRZB0eZ5A3yngHv0&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=UJI65KKLR_Dbl_jINlt-eRhd_bKegvdS4NavFTG6h6o&s=GOjJasN9dtPb9ylVbly08Z-aMdTNBQYCTXw6I2TGuPk&e=
--
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=UJI65KKLR_Dbl_jINlt-eRhd_bKegvdS4NavFTG6h6o&s=5KAaQtPsMqiWiJ_QzpTm09UNyB7n3R8WPlupR6sCxro&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=UJI65KKLR_Dbl_jINlt-eRhd_bKegvdS4NavFTG6h6o&s=zZSuJIpHaJYCQ242KvLOZR4jU6utFkYRd9Vn1A3IjjE&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=UJI65KKLR_Dbl_jINlt-eRhd_bKegvdS4NavFTG6h6o&s=Lz3Wx9gLd9pua7fe4DO3EsYOec6DGhI7lICoOej7gfw&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=UJI65KKLR_Dbl_jINlt-eRhd_bKegvdS4NavFTG6h6o&s=9omR_vhZIv-fOyvnOiALw6nK99dCRZB0eZ5A3yngHv0&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=UJI65KKLR_Dbl_jINlt-eRhd_bKegvdS4NavFTG6h6o&s=GOjJasN9dtPb9ylVbly08Z-aMdTNBQYCTXw6I2TGuPk&e=

As an Amazon Associate we earn from qualifying purchases.

Follow-Ups:

RE: How to Undo an SQL Insert , Greg Wilburn

This mailing list archive is Copyright 1997-2024 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.