"RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx> wrote on 01/24/2017 09:29:26
AM:
I was looking at your suggestion but I couldn't see an example. Then I
saw Brian's suggestion and that seemed to be what I was looking for.
Do you have an example of how I would specify or use an extended
indicator array to limit the columns inserted?
Sorry for the late reply. I have been in training all week. I
can give you an edited-down example from one of our current applications.
To start with we have the following definitions and data structures in a
shared copybook.
dcl-c IMLFDTLT_Table 'IM_LIFO_Inventory_Detail_Info';
dcl-c IMLFDTLT_View1 'IM_LIFO_Inventory_Detail_V01';
dcl-c IMLFDTLT_View2 'IM_LIFO_Inventory_Detail_V02';
// number of columns in SQL table/view row
dcl-c IMLFDTL_NCol 19;
dcl-c IMLFDTL_VCol1 19;
dcl-c IMLFDTL_VCol2 40;
// data structures for SQL column data
dcl-ds IMLFDTL_Data extname('IMLFDTLT') qualified inz end-ds;
dcl-ds IMLFDTL_View1 extname('IMLFDTLV01') qualified inz end-ds;
dcl-ds IMLFDTL_View2 extname('IMLFDTLV02') qualified inz end-ds;
// SQL indicator array with overlay of named indicators
dcl-s IMLFDTL_Ary like(NULL_VALUE) dim(IMLFDTL_VCol2) inz;
dcl-s IMLFDTL_Ptr like(pointer_t) inz(%addr(IMLFDTL_Ary));
dcl-ds IMLFDTL_Ind based(IMLFDTL_Ptr) qualified;
ImLfDtlTID like(NULL_VALUE);
IM02TID like(NULL_VALUE);
BgnYrAge like(NULL_VALUE);
BgnYrCst like(NULL_VALUE);
EndYrCst like(NULL_VALUE);
QtyBgnYr like(NULL_VALUE);
EndQtyHnd like(NULL_VALUE);
EndQtyHld like(NULL_VALUE);
BoyItmExt like(NULL_VALUE);
YtdItmExt like(NULL_VALUE);
InvIdx like(NULL_VALUE);
TmsLstYtd like(NULL_VALUE);
TmsLstLRl like(NULL_VALUE);
AddByUsr like(NULL_VALUE);
AddByPgm like(NULL_VALUE);
AddTs like(NULL_VALUE);
ChgByUsr like(NULL_VALUE);
ChgByPgm like(NULL_VALUE);
ChgTs like(NULL_VALUE);
IM02TID2 like(NULL_VALUE);
IBSTAT like(NULL_VALUE);
IBITM like(NULL_VALUE);
IBBIN like(NULL_VALUE);
IBOHQ like(NULL_VALUE);
IBHDQ like(NULL_VALUE);
IBPHCT like(NULL_VALUE);
IBCSTA like(NULL_VALUE);
IBPYEC like(NULL_VALUE);
IBPCST like(NULL_VALUE);
IBPC1 like(NULL_VALUE);
IBPC2 like(NULL_VALUE);
IBPC3 like(NULL_VALUE);
IBAGE like(NULL_VALUE);
IMDSC1 like(NULL_VALUE);
IMDSC2 like(NULL_VALUE);
IMPUM like(NULL_VALUE);
IMPCUM like(NULL_VALUE);
IMIUM like(NULL_VALUE);
IMSTOP like(NULL_VALUE);
IMGRP like(NULL_VALUE);
end-ds;
dcl-s sqlstmt varchar(900);
The following would be an example of performing an INSERT -- with
error checking removed for brevity. Some of the constant-type values I am
using above and below are definitions we have in another copybook which is
for SQL constants we use in all SQL programs.
eval-corr IMLFDTL_Data = IMLFDTL_View2; // get just the 1st table's data
QWVCSTKE = GenUtl_FindCallStackEntry('*PRV': PROC_PGM);
IMLFDTL_Data.AddByPgm = %trimr(QWVPGMN); // get calling program name
IMLFDTL_Data.ChgByPgm = IMLFDTL_Data.AddByPgm; // should be same as add
pgm
IMLFDTL_Ind.IMLFDTLTID = IGNORE_VALUE; // ignore generate-always column
IMLFDTL_Ind.AddByPgm = USE_VALUE; // tell DB2 to use current value
IMLFDTL_Ind.AddByUsr = SET_DEFAULT; // take ddl-defined default
IMLFDTL_Ind.AddTs = SET_DEFAULT; // take ddl-defined default
IMLFDTL_Ind.ChgByPgm = USE_VALUE; // tell DB2 to use current value
IMLFDTL_Ind.ChgByUsr = SET_DEFAULT; // take ddl-defined default
IMLFDTL_Ind.ChgTs = IGNORE_VALUE; // ignore generate-always column
sqlstmt = 'Insert into ' + iLibrary + '/' + IMLFDTLT_Table;
sqlstmt += ' ( ImLfDtlT_ID+
, IM02T_ID+
, Beginning_of_Year_Age+
, Beginning_of_Year_Cost+
, End_of_Year_Cost+
, Quantity_at_Beginning_of_Year+
, Quantity_on_Hand_End_of_Year+
, Quantity_on_Hold_End_of_Year+
, BOY_Item_Extension+
, YTD_Item_Extension+
, Lifo_Inventory_Index+
, Timestamp_of_Last_Ytd_Capture+
, TimeStamp_of_Last_LIFO_Roll+
, Added_By_User+
, Added_By_Program+
, Added_Timestamp+
, Changed_By_User+
, Changed_By_Program+
, Changed_Timestamp+
)';
sqlstmt += ' Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
exec sql prepare IMLFDTDRSM_Insert_Stmt
from :sqlstmt;
exec sql execute IMLFDTDRSM_Insert_Stmt
using :IMLFDTL_Data :IMLFDTL_Ary;
exec sql values IDENTITY_VAL_LOCAL()
into :IMLFDTL_Data.IMLFDTLTID;
Sincerely,
Dave Clark
midrange.com
