Informix

Sebastien:

Your code never executed the FETCH between the second OPEN and the third OPEN! Fixed the code and it works regardless of whether OPTOFC is set or not and whether the first FETCH sequence is included or not.  

The only thing that's a bit confusing is why the third OPEN without the USING clause does not generate an error. It runs without setting SQLCODE though it does not overwrite the existing value in v_pkey which is expected. Code attached.

Art

Oh, forgot to mention, I also added explicit closes between the OPENs (which are commented out in the file I uploaded) and it does not matter whether they are there or not, regardless of the setting of OPTOFC or commenting out the first OPEN... FETCH sequence.

Art

Hi Art and thanks for your quick reaction, but understand that changing the ESQL/C code is not possible in this case:

As I wrote, the origin of the issue comes from 4GL and legacy code that does an OPEN USING + FOREACH without USING, where you don't want to do a FETCH in between!

Here my original 4GL code:

MAIN
    DEFINE p1 INTEGER
    DEFINE rec RECORD pkey INTEGER, name VARCHAR(20) END RECORD

    DATABASE test1

    CREATE TEMP TABLE tmp1 ( pkey INTEGER, name VARCHAR(50) )
    INSERT INTO tmp1 VALUES (101,"aaa")
    INSERT INTO tmp1 VALUES (102,"bbb")

    PREPARE s1 FROM "SELECT * FROM tmp1 WHERE pkey = ?"
    DECLARE c1 CURSOR FOR s1

    LET p1 = 101
    OPEN c1 USING p1
    FETCH c1 INTO rec.*
    DISPLAY "FETCH 101   rec: ", rec.pkey, " ", rec.name

    LET p1 = 102
    OPEN c1 USING p1
    FOREACH c1 INTO rec.*                 # Fail: Reuses p1=101 when OPTOFC=1 !
       DISPLAY "FOREACH     rec: ", rec.pkey, " ", rec.name
    END FOREACH

END MAIN

See:

$ fglcomp -M main.4gl && fglrun main.42m
FETCH 101   rec:         101 aaa
FOREACH     rec:         102 bbb               <= OK

$ fglcomp -M main.4gl && OPTOFC=1 fglrun main.42m
FETCH 101   rec:         101 aaa
FOREACH     rec:         101 aaa                <= Not OK...

Same issue occurs with Informix 4GL 7.51.FC4 (c4gl and RDS fglpc/fglgo) - give it a try!

Seb

Sebastien:

I just compiled your code using C4GL  7.50.FC5 and it seems to work just fine:

art@Gandolf:~/Downloads$ vi test01.4gl
art@Gandolf:~/Downloads$ c4gl -o test01c test01.4gl
art@Gandolf:~/Downloads$ ./test01c
FETCH 101 rec:         101 aaa
FOREACH rec:         102 bbb
art@Gandolf:~/Downloads$ c4gl -V
IBM INFORMIX-4GL Version 7.50.FC5   
Software Serial Number RDS#N000000

Looks like a compiler bug.

Art

Hello Art,

You have tested with Informix 4GL 7.50.FC5.

I can reproduce with Informix 4GL two 7.51.FC2 and FC4 on my Linux Debian 12:

$ . /opt3/dbs/ifx/DEV-7.51.FC2/informix.env 
$ c4gl -V
IBM INFORMIX-4GL Version 7.51.FC2   
Software Serial Number RDS#N000000
$ esql -V
IBM Informix CSDK Version 4.10, IBM Informix-ESQL Version 4.10.FC6  
Software Serial Number RDS#N000000
$ c4gl -o main.bin main.4gl
$ export OPTOFC=1
$ ./main.bin
FETCH 101   rec:         101 aaa
FOREACH     rec:         101 aaa                       <= WRONG

$ . /opt3/dbs/ifx/DEV-7.51.FC4/informix.env 
$ c4gl -V
IBM INFORMIX-4GL Version 7.51.FC4   
$ esql -V
IBM Informix CSDK Version 15.0, IBM Informix-ESQL Version 15.0.0.1
$ c4gl -o main.bin main.4gl
$ export OPTOFC=1
$ ./main.bin 
FETCH 101   rec:         101 aaa
FOREACH     rec:         101 aaa                       <= WRONG

$ unset OPTOFC
$ ./main.bin 
FETCH 101   rec:         101 aaa
FOREACH     rec:         102 bbb                       <= OK

Can you try a more recent version of I4GL?

Seb

Sebastien:

OK, just compiled with c4gl latest (7.51.FC5) against my HCL Informix v15.0.1.6 on Ubuntu (x86-64).

It's working fine here.

art@Gandolf:~/Downloads$ c4gl -o test01 test01.4gl
art@Gandolf:~/Downloads$ dirtail
-rw-rw-r--  1 art art      3888 Apr 23 11:56 test02.txt
-rw-rw-r--  1 art art       574 Apr 23 13:46 test01.4gl~
-rw-rw-r--  1 art art       589 Apr 23 13:56 test01.4gl
-rwxrwxr-x  1 art art     20000 Apr 23 13:56 test01c*
-rwxrwxr-x  1 art art     20000 Apr 23 14:04 test01C*
-rw-rw-r--  1 art art    285546 Apr 23 14:10 IR260424.pdf
drwxr-xr-x 58 art art      4096 Apr 23 16:00 ../
-rw-rw-r--  1 art art     12192 Apr 24 06:30 test01.o
-rwxrwxr-x  1 art art     20000 Apr 24 06:30 test01*
drwxr-xr-x  2 art art     36864 Apr 24 06:30 ./
art@Gandolf:~/Downloads$ ./test01
FETCH 101 rec:         101 aaa
FOREACH rec:         102 bbb
art@Gandolf:~/Downloads$ c4gl -V
HCL INFORMIX-4GL Version 7.51.FC5   
art@Gandolf:~/Downloads$ 

Hi Art,

Sorry to insist, but did you set the OPTOFC=1 env var ?

I cannot see this in your command lines...

export OPTOFC=1

I will try to find c4gl 7.51.FC5 and test on my side.

Seb

Apologies, I did forget to try that. Here, and you are correct, this is a bug in the v7.51 compiler:

art@Gandolf:~/Downloads$ ./test01
FETCH 101 rec:         101 aaa
FOREACH rec:         102 bbb
art@Gandolf:~/Downloads$ c4gl -V
HCL INFORMIX-4GL Version 7.51.FC5   
art@Gandolf:~/Downloads$ OPTOFC=1 ./test01
FETCH 101 rec:         101 aaa
FOREACH rec:         101 aaa
art@Gandolf:~/Downloads$ 

Hi Art,

Glad that you could reproduce with OPTOFC=1 !!!

Since the problem exist also with ESQL/C, I believe it's rather an ESQL/C bug.

We should now register a defect.

Seb

Sebastien:

I have to disagree, the generated ESQL/C is producing the same result but that is because the generated code is faulty. Just look at and test the version that I sent you. 

The generated code never fetches after the second OPEN but only after the third one. It works when you do not set OPTOFC because the FETCH after the third OPEN, the one without a USING clause, fetches the data from the second OPEN. That third OPEN just silently fails which is a bug in itself, but has nothing to do with the code not working when you set OPTOFC. 

Here is what is actually happening: Remember that OPTOFC defers the OPEN until the FETCH and automatically closes the cursor when the last row is fetched. In the generated code, since there is not FETCH immediately after the OPEN the open is queued in the local library's memory but not executed. Then the third OPEN executes with not USING so it is completely ignored (I agree that it should generate an error) and probably clears the scheduled OPEN from the second OPEN call. Then the FETCH that follows the third OPEN also silently fails and the target host variables simply have retained their values from the first successful fetch. 

When you comment out the first OPEN and FETCH, there is no data in the target host variables so you finally get an error.

In the correctly built code I send, where there is a second FETCH, the results are correct regardless of OPTOFC and regardless of whether you comment out the first fetch.

Now, not returning errors when the third OPEN is presented on top of the second one with no fetch? Maybe when OPTOFC is NOT set. It should not like either the back-to-back OPENs nor that the third OPEN does not present a value to use. On the other hand, if you OPEN a cursor again without first issuing a CLOSE, that is not an error and the OPEN executes a CLOSE internally first, so maybe not an error. In the OPTOFC case, I think not an ESQL compiler or engine bug but rather that the second OPEN was just NO-OP because of how OPTOFC works.

Nope, I maintain that the ONLY problem is the C4GL V7.51 compiler that has generated bad ESQL/C code (the 7.50 compiler gets it correct. I would like to see JC or Jonathan Leffler chime in on this one.

FORGET WHAT I SAID. I just realized what the issue is. The 4GL code it bad. You have:
LET p1 = 102
OPEN c1 USING p1
FOREACH  c1 INTO rec.* 
    # Fail: Reuses p1=101 when OPTOFC=1 ! 
    DISPLAY "FOREACH rec: ", rec.pkey, " ", rec.name 
END FOREACH 
END MAIN

But the FOREACH does its own OPEN. That is the 3rd open and and since the FOREACH does not have a USING clause that 3rd OPEN doesn't either in the generated code.

The code should be:

LET p1 = 102

FOREACH  c1 using p1 INTO rec.* 
    # Fail: Reuses p1=101 when OPTOFC=1 ! 
    DISPLAY "FOREACH rec: ", rec.pkey, " ", rec.name 
END FOREACH 
END MAIN

That works perfectly regardless of which compiler version I use. There is still the issued of whether or not the third open should have generated an error and why the FOREACH is indeed fetching the value 101 instead of 102, but bottom line? Correct 4GL code generates correct ESQL/C code.

Art

Hello Art,

I fully agree that proper FOREACH programming is to put a USING clause if there are ? parameters in the query.

However, AFAIKR, in first versions of I4GL, the FOREACH instruction did not allow a USING clause, and that's why you can find sequences like: 

1. OPEN curs USING ...
2. FOREACH curs INTO ...      (without USING clause)

And in such case, it's "normal" to have no FETCH between OPEN and FOREACH!

By the way, we have a migration/update page about this in our Genero documentation:

https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_MigI4GL_031.html

I4GL or Genero users will face the issue discussed here, if they want to use OPTOFC=1 and cannot or don't want to touch their legacy code.

I can understand that it's a 4GL-only bug (not an ESQL/C bug), assuming that this tricky OPEN USING followed by OPEN or FOREACH without USING is to be handled at the 4GL compiler level. But since (I assume) the OPTOFC optimization is implemented in ESQL/C libs, maybe it would be good to raise error -254 when OPTOFC is enabled, and an OPEN without USING clause is performed right after an OPEN c USING. This would at least prevent legacy code to fetch invalid data.

Thanks for your investigation, it's a tricky one.

Seb

PS: You wrote that you sent me your sample but I did not get any email