The STACKERR Table

When running the interface program using the '-m' option, each error message is broken up into several lines. Thus, for example, instead of the following message:

Line 1- Price Quotation A9000019: You cannot base the order on a price quotation of Draft‎status.''

the user will receive 4 messages when the '-m' option is used:

1: Sales Rep Order Num
2: form 'Sales Orders' 3: Price Quotation A9000019
4: You cannot base the order on a price quotation of Draft status.

This can be problematic if the user tries to open two sales orders based on two different price quotations and both quotations have the Draft status. In such a case, certain messages will not appear in the ERRMSGS table, since the unique key of the ERRMSGS table is: USER, TYPE, MESSAGE. In the above example, message number 4 will be the same in both cases and will therefore appear only once.

This problem is easily resolved by using the '-stackerr' option. This will insert all error messages into the STACK_ERR table, rather than using the ERRMSGS table to store interface messages. The original LINE value from the load table will be stored in the INTDATA1 column of this table, and the error message will be stored in the MESSAGE column.

This option is also useful when you write a procedure that executes more than one interface. In such a case, the second interface would ordinarily delete any messages received by the first one. In order to maintain the messages generated by the first interface, use the '-stackerr' option and assign each interface a different parameter for the linked file. This will create a separate linked file of the STACK_ERR table for each interface you execute.

Example: To open sales orders based on price quotations, and then open a shipping document based on the new sales orders, the following code would be used. (In this example, it is assumed that the initial status for sales orders is flagged as Allow Shipmt/ProjRep.)

SELECT SQL.TMPFILE INTO :G1 FROM DUMMY;
SELECT SQL.TMPFILE INTO :G2 FROM DUMMY;
SELECT SQL.TMPFILE INTO :S1 FROM DUMMY;
SELECT SQL.TMPFILE INTO :S2 FROM DUMMY;

LINK GENERALLOAD ORD TO :G1;
GOTO 99 WHERE :RETVAL <= 0;

LINK GENERALLOAD DOC TO :G2;
GOTO 99 WHERE :RETVAL <= 0;

INSERT INTO GENERALLOAD ORD(LINE,RECORDTYPE,TEXT2)
SELECT SQL.LINE, '1', CPROFNUM 
FROM CPROF 
WHERE PDATE = SQL.DATE8;

EXECUTE INTERFACE 'OPENORDBYCPROF', SQL.TMPFILE, '-L', :G1, '-stackerr', :S1;

INSERT INTO GENERALLOAD DOC(LINE,RECORDTYPE,TEXT1)
SELECT SQL.LINE, '1', ORDNAME
FROM ORDERS, GENERALLOAD ORD
WHERE ORD.LOADED = 'Y'
AND ORDERS.ORD = ATOI(ORD.KEY1);

UNLINK GENERALLOAD ORD;

EXECUTE INTERFACE ' OPENDOC', SQL.TMPFILE, '-L', :G2, '-stackerr', :S2;

UNLINK GENERALLOAD DOC;

LINK STACK_ERR S1 TO :S1;
GOTO 99 WHERE :RETVAL <= 0;

SELECT * FROM STACK_ERR S1 FORMAT;
UNLINK STACK_ERR S1;

LINK STACK_ERR S2 TO :S2;
GOTO 99 WHERE :RETVAL <= 0;

SELECT * FROM STACK_ERR S2 FORMAT;
UNLINK STACK_ERR S2;

LABEL 99;

Further Reading

Click for information on additional advanced programming tools.