Had a look again this morning and still can't see anything wrong here.
Pretty sure Art already explained what was going one, but just in case.
Look at this 4GL code:
informix@t14ito:~/7.51.FC3$ cat t1.4gl
database sysmaster
MAIN
DEFINE p1 INTEGER
DEFINE p2 INTEGER
DEFINE v1 INTEGER
LET p1 = 1
LET p2 = 2
PREPARE s1 FROM "SELECT tabid FROM systables WHERE tabid = ?"
DECLARE c1 CURSOR FOR s1
OPEN c1 USING p1
#FETCH c1 INTO v1
FOREACH c1 USING p2 INTO v1
#FOREACH c1 INTO v1
DISPLAY " v1: ", v1
END FOREACH
END MAIN
informix@t14ito:~/7.51.FC3$
We are doing an OPEN with "1" and FOREACH with "2".
but in reality the "c1" cursor is only opened once with the implicit OPEN created by the FOREACH.
FOREACH is basically the same as doing OPEN/FETCH/CLOSE. If you don't use "USING", you are NOT specifying any parameter to be used on the FETCH.
The code above results in:
informix@t14ito:~/7.51.FC3$ c4gl t1.4gl ; OPTOFC=1 ./a.out
v1: 2
informix@t14ito:~/7.51.FC3$
The "OPEN c1 USING p1" is ignored and "overriten" by the second OPEN (implicit) from the FOREACH statement:
informix@t14ito:~/7.51.FC3$ c4gl -keep t1.4gl ; grep open t1.ec
$ open i00160615zo0lsl2ln using $p1;
$ open i00160615zo0lsl2ln using $p2;
$ fetch i00160615zo0lsl2ln into $v1;
informix@t14ito:~/7.51.FC3$
It may be easier to understand if you try without supplying any parameters in the FOREACH.
Changing:
FOREACH c1 USING p2 INTO v1
for:
FOREACH c1 INTO v1
FOREACH will translate to OPEN/FETCH/CLOSE (notice the plain "open" statement)
informix@t14ito:~/7.51.FC3$ c4gl -keep t1.4gl ; grep -e open -e fetch t1.ec
$ open i0016064c50bsvy5u2 using $p1;
$ open i0016064c50bsvy5u2;
$ fetch i0016064c50bsvy5u2 into $v1;
informix@t14ito:~/7.51.FC3$
Without OPTOFC that plain OPEN will reuse the host variable already associated with the cursor (so 'p1'):
informix@t14ito:~/7.51.FC3$ c4gl t1.4gl ; OPTOFC=0 ./a.out
v1: 1
informix@t14ito:~/7.51.FC3$
but with OPTOFC enabled, it will not. And because we didn't run a FETCH, it throws the expected -254 error
informix@t14ito:~/7.51.FC3$ c4gl t1.4gl ; OPTOFC=1 ./a.out
Program stopped at "t1.4gl", line number 15.
SQL statement error number -254.
Too many or too few host variables given.
informix@t14ito:~/7.51.FC3$
this shows how the OPEN parameters are handled when using OPTOFC
And you can see the same behavior with plain a basic ESQL/C code
informix@t14ito:~/7.51.FC3$ cat e1.ec
#include <stdio.h>
#include <time.h>
$include "sqlca.h";
$include "sqlhdr.h";
$include "sqltypes.h";
main()
{
int error;
$ BEGIN DECLARE SECTION;
int p1,p2,v1;
$ END DECLARE SECTION;
p1=1;
p2=2;
$ whenever sqlerror stop;
$ connect to 'sysmaster';
$ prepare sel1 from 'SELECT tabid FROM systables where tabid=?';
$ declare ifxcursor cursor for sel1;
$ open ifxcursor using :p1;
$ fetch ifxcursor into :v1;
$ open ifxcursor using :p2;
$ open ifxcursor;
$ fetch ifxcursor into :v1;
printf(" v1 : %d\n",v1);
}
We do OPEN with "p1", FETCH the result and then another two OPENs.
one with "p2" and one without a parameter:
informix@t14ito:~/7.51.FC3$ c4gl e1.ec; OPTOFC=0 ./a.out
v1 : 2
informix@t14ito:~/7.51.FC3$ c4gl e1.ec; OPTOFC=1 ./a.out
v1 : 1
informix@t14ito:~/7.51.FC3$
With OPTOFC enabled, only the initial value of "p1" is used for the fetch.
Because there is no fetch after "open ifxcursor using :p2", the value of "p2" is never sent to the database or "applied" to memory.
When the plain "open ifxcursor" executes without parameters, the runtime falls back to "p1", which is the only parameter currently synchronized in memory.
As I see it:
1. 4GL's FOREACH is the same as writing OPEN, FETCH and CLOSE.
If you have a FOREACH without USING you will get an OPEN without USING. Nothing wrong with that.
2. The OPTOFC optimization affects when parameters are processed. We have documented several restrictions when using OPTOFC here:
https://www.ibm.com/docs/en/informix-servers/15.0.x?topic=close-restrictions-optofc
I do agree that maybe it will be a good idea to also add a statement describing how parameters handling is affected when its OPTOFC is enabled.