I want to insert records into a db2 table using a storeprocedure. I create a hold file that has the insert statements (See code). The problem is that the END (DL3) statement must start on on new line. The question is how can i do that?
Another problem is that attribuut TAB_VLGNUM has to be filled with a piece alphanumeric and a piece numeric like 'VLGNUM' plus a sequence number. Example : record1 'VLGNUM0001' record2 'VLGNUM0002' etc.
-* File insert.fex
-SET &ECHO=ALL;
-DEFAULT &APLNAM = 'Just_a_Name';
-DEFAULT &USRCDE = 'XXXX';
-DEFAULT &USRREF = '-2147482861';
DEFINE FILE CAR
APLNAM/A11 = '&APLNAM';
USRCDE/A5 = '&USRCDE';
USRREF/I11 = &USRREF;
KOMMA/A2 = ', ';
QUOTE/A1 = '''';
TAB_VLGNUM/A6 = 'VLGNUM';
DL1/A57 = 'SQL DB2 CALL STOREPROCEDURE_INSERT (';
DL2/A1 = ')';
DL3/A4 = ' END';
END
TABLE FILE CAR
PRINT
COUNTRY NOPRINT
DL1
QUOTE
APLNAM
QUOTE
KOMMA
QUOTE
USRCDE
QUOTE
KOMMA
QUOTE
USRREF
QUOTE
KOMMA
QUOTE
TAB_VLGNUM
QUOTE
KOMMA
QUOTE
COUNTRY
QUOTE
DL2
-* DL3
WHERE RECORDLIMIT EQ 4
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD AS INS FORMAT ALPHA
END
-*-RUN
-*-INCLUDE INS
This message has been edited. Last edited by: Kerry,
October 22, 2012, 12:56 PM
Dan Satchell
Try these ideas. Use function HEXBYT to create a line feed character and PRINT it before your END statement. Create a numeric counter that increments with each new record. You may need to query your DB2 table to determine the last record counter in order to start with the next number for your new records.
-SET &ECHO=ALL;
-*
-DEFAULT &APLNAM = 'Just_a_Name';
-DEFAULT &USRCDE = 'XXXX';
-DEFAULT &USRREF = '-2147482861';
-*
DEFINE FILE CAR
APLNAM/A11 = '&APLNAM';
USRCDE/A5 = '&USRCDE';
USRREF/I11 = &USRREF;
KOMMA/A2 = ', ';
QUOTE/A1 = '''';
DL1/A57 = 'SQL DB2 CALL STOREPROCEDURE_INSERT (';
DL2/A1 = ')';
DL3/A4 = ' END';
NEWLINE/A1 = HEXBYT(10,'A1');
END
-*
TABLE FILE CAR
PRINT
COUNTRY NOPRINT
DL1
QUOTE
APLNAM
QUOTE
KOMMA
QUOTE
USRCDE
QUOTE
KOMMA
QUOTE
USRREF
QUOTE
KOMMA
QUOTE
COMPUTE SEQ_NO/I4 = LAST SEQ_NO + 1 ; NOPRINT
COMPUTE TAB_VLGNUM/A10 = 'VLGNUM' || EDIT(SEQ_NO);
QUOTE
KOMMA
QUOTE
COUNTRY
QUOTE
DL2
NEWLINE
DL3
WHERE RECORDLIMIT EQ 4
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS INS FORMAT ALPHA
END
-*
TABLE FILE INS
PRINT *
END
WebFOCUS 7.7.05
October 22, 2012, 01:18 PM
Francis Mariani
Keep the SQL call and the END out of the INS file:
-* File insert.fex
-SET &ECHO=ALL;
-DEFAULT &APLNAM = 'Just_a_Name';
-DEFAULT &USRCDE = 'XXXX';
-DEFAULT &USRREF = '-2147482861';
DEFINE FILE CAR
APLNAM/A11 = '&APLNAM';
USRCDE/A5 = '&USRCDE';
USRREF/I11 = &USRREF;
KOMMA/A2 = ', ';
QUOTE/A1 = '''';
TAB_VLGNUM/A6 = 'VLGNUM';
END
TABLE FILE CAR
PRINT
COUNTRY NOPRINT
QUOTE
APLNAM
QUOTE
KOMMA
QUOTE
USRCDE
QUOTE
KOMMA
QUOTE
USRREF
QUOTE
KOMMA
QUOTE
TAB_VLGNUM
QUOTE
KOMMA
QUOTE
COUNTRY
QUOTE
WHERE RECORDLIMIT EQ 4
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD AS INS FORMAT ALPHA
END
-RUN
SQL DB2 CALL STOREPROCEDURE_INSERT (
-INCLUDE INS
)
END
-RUN
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 22, 2012, 04:53 PM
Waz
Why not use MODIFY, it would be very simple.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 23, 2012, 02:15 AM
MrM
Thanks far all the reply.
The ideas from Dan did the trick : - NEWLINE/A1 = HEXBYT(10,'A1'); - COMPUTE SEQ_NO/I4 = LAST SEQ_NO + 1; NOPRINT and - COMPUTE TAB_VLGNUM/A10 = 'VLGNUM' || EDIT(SEQ_NO);
Thanks for that.
Francis because it is a stored procedure you have to call it for every record that is in the holdfile. So you have to repeat 'SQL DB2 CALL STOREPROCEDURE_INSERT (' and ') END' that's why I put it in the holdfile. But thanks for your reply.
Waz probably is MODIFY a good solution but you must have a license for it that we don't have. But also thanks for your reply.
October 23, 2012, 04:29 PM
Waz
You don't need a license for MODIFY, its MAINTAIN that you need a license for.