Re: SQL Insert question

Hi Greg,

Temporal tables make automatic copies of records while the are updated or deleted. So You update 1 column and the whole record is „saves" before the update into a separate table (called the history table). If you configure it, also a copy of a record is „saved“ before a delete takes place.

This is all done by the database, and completely transparent for the application. Simon has written some good posts about that:

https://www.rpgpgm.com/2016/10/looking-back-into-past-of-your-data.html;

https://www.rpgpgm.com/2019/10/more-tips-for-using-temporal-tables.html;

We stopped all manual audit tables in favor of this.

The other idea was - create a view over your audit table, which only has the columns, that the production table also has. Thern you can simply do

INSERT INTO auditableview SELECT * FROM productiontable WHERE …

Essentially - to make this work, both tables need exactly the same number, type and sequence of columns (ok you can do automatic casting in a few cases, but this is not very robust).

If you want to know more about the temporal tables feature, feel free to contact me personally.

HTH
Daniel

Am 10.05.2023 um 15:37 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

Yes, so the whole point is not having to explicitly specify each column on the insert from ICPRTMIA to AUDIA

ICPRTMIA AUDIA
IACOM# IACOM#
IAPRT# IAPRT#
IA101 IA101
etc... etc..
IAUSRN
IAUSER
IATRND
IATRT
IAPRCF IAPRCF
IAVIAC IAVIAC
IAHCD1 IAHCD1
<end> <end>

So I think I understand some of the suggestions... (I don't understand the "temporal-tables / history tables" mentioned by Daniel).

These files each have over 100 fields... the AUDIA table that I'm inserting into has 5 more fields (user, user id, date, time, transaction code) that are not at the end.
I tried to create two data structures as Darryl suggested, but the compiler didn't like that (I didn't qualify them)... so the fields are the same name.

dcl-ds PartMaster extname('ICPRTMIA') inz;
end-ds;

dcl-ds PartAudit extname('AUDIA') inz;
end-ds;

Compiler error for every field:
RNF3804: Name IAWEBF in external description AUDIA is not renamed; external name is ignored.

Am I defining these incorrectly?

Buck, I'm not understanding how a stored procedure would make this any easier - I'm already in an RPG program that is called from the CGI. But I would definitely place this into an RPG service program so I could call it from other places. This is why I didn't want to use native access to write the AUDIA.

Thanks,
Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Tuesday, May 9, 2023 5:06 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Insert question

Hi Greg,

what kind of columns are those in the audit table?

Maybe the temporal-tables / history tables / versioning feature is already enough.

Another method is using an INSERT view on the table, which only consists of the same columns as the original - the audit columns would need good default values for this to work.

A bit more information would be good - so one can think of a specific solution.

HTH
Daniel

Am 09.05.2023 um 22:51 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

I have two files/tables that are nearly identical. They are:

1. Part Master file
2. Part Master Audit file

The Audit file contains 5 additional fields that are not found in the Part Master. All of the other fields in the file are identical (mirror images of each other in name and attributes), and are in the same order EXCEPT for the last ten fields in the Audit file. The Audit file contains the 5 additional fields inserted just before the last 5 matching fields.

With RPG, I could simply chain (or read) the Part Master record, eval the 5 additional field values, and then just write the Part Master Audit record.
I'm looking for an "elegant" way to do the same thing with SQL without using native access. This will be running in a CGI job.

Any ideas?
TIA
[Logo]<https://www.totalbizfulfillment.com/> 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
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
--
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://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
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://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
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://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.