Any help would be greatly appreciated.

My goal is to loop through the output of a file, call a stored procedure to perform a row insert in the database, check the SP result set to confirm the success of the insert, and then generate an email message to the intended recipient.

When I skip over the call to the stored procedure, the loop increments and properly selects the next value in the file. When I allow execution of the SP, the record pointer gets stuck on the first record. I have verified this by forcing a couple of extra reads after the SP and seeing that indeed the 2nd and 3rd records are then read, only to re-read the first record when returning to the top of the loop.

My code is:

  
-* File oot_email_main.fex
-INCLUDE _setserv_max.fex

SET PRINTPLUS = ON
SET EMPTYREPORT = OFF
SET NODATA = ''

ENGINE SQLORA SET DEFAULT_CONNECTION &MYSERV
SQL SQLORA PREPARE OOTWORKORDER FOR
SELECT
		workorder.wonum,
		workorder.assetnum,
		workorder.pmnum
FROM	workorder, snlcoot
WHERE	workorder.wonum = snlcoot.wonum
AND		snlcoot.isnoticerequired = 1
AND		snlcoot.acceptedby is null
;
END

-* Output workorder numbers to file for use in loop
TABLE FILE OOTWORKORDER
PRINT
	WONUM
	ASSETNUM
	PMNUM
BY WONUM NOPRINT
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE HOLD AS WOHOLD FORMAT ALPHA
END
-RUN

-DEFAULT &WONUM = '';
-SET &WFFMT = 'PDF';
-SET &RECCNT = &LINES;
-SET &CNTR = 0;
-SET &DATESENT = HCNVRT(HGETC(8,'HYYMDS'),'(HYYMDS)',19,'A19');
-SET &DATEDUE = HCNVRT(HADD(HGETC(8,'HYYMDS'), 'DAY', 28, 8, 'HYYMDS'),'(HYYMDS)',19,'A19');

-* Main loop start
-REPEAT TABLELOOP &RECCNT TIMES
-READ WOHOLD &HOLDREC.A64.
-TYPE &HOLDREC
-SET &CNTR = &CNTR + 1;
-SET &WONUM = SUBSTR(65, &HOLDREC, 7, 24, 18, 'A18');
-SET &ASSETNUM = SUBSTR(65, &HOLDREC, 31, 50, 20, 'A20');
-SET &PMNUM = SUBSTR(65, &HOLDREC, 57, 62, 8, 'A8');
-SET &PMNUM = IF &PMNUM EQ '.' THEN '' ELSE &PMNUM;

-* Non-conformance notification update and email generation
-* Update database with notification record
-DEFAULT &NOTIFTYPE = 'OOT';
-DEFAULT &CATEGORY = 'INITIAL';
-DEFAULT &RECIPIENTS = 'myself';

-TYPE WONUM &WONUM ASSETNUM &ASSETNUM PMNUM &PMNUM NOTIFTYPE &NOTIFTYPE CATEGORY &CATEGORY RECIPIENTS &RECIPIENTS
-*GOTO TABLELOOP

ENGINE SQLORA SET DEFAULT_CONNECTION &MYSERV
SQL SQLORA
EX mxescal_dev.mxes_webfocus_notify '&ASSETNUM', '&WONUM', '&PMNUM',
	'&NOTIFTYPE', '&CATEGORY', '&DATESENT', '&DATEDUE', '&RECIPIENTS';

TABLE FILE SQLOUT
PRINT
	ASSETNUM AS 'MYASSET'
	WONUM AS 'MYWORKORDER'
ON TABLE HOLD AS OOT_INSERTS
END
-RUN

-* Check if any rows in result set - an indication that the insert was successful
-* If so, generate the email notification
-IF &LINES LE 0 THEN GOTO PROCFAIL;
-* TODO: add call to email generating fex here

-PROCFAIL

-TABLELOOP
-CLOSE WOHOLD

TIA!

Jim

This is expected behaviour, a -RUN will close the file. Use the NOCLOSE option on the -READ.

Boy do I feel dumb (and I wish I asked sooner - wasted a lot of time on this one)!

Thanks for your help.