PREPARE does not work with VARLENGTH groups (ocesql compat)

[GitMensch]

`PREPARE in OCESQL only worked with VARLENGTH groups, rechecking with GIXSQL 1.0.16 shows it only works with plain PIC X fields.

GIXSQL*    EXEC SQL AT :DBS
GIXSQL*        PREPARE P1 FROM :S-SQLCOMMAND
GIXSQL*    END-EXEC.
GIXSQL     CALL "GIXSQLPrepareStatement" USING
GIXSQL         BY REFERENCE SQLCA
GIXSQL         BY REFERENCE DBS
GIXSQL         BY VALUE 10
GIXSQL         BY REFERENCE "P1" & x"00"
GIXSQL         BY REFERENCE S-SQLCOMMAND
GIXSQL         BY VALUE 0
GIXSQL     END-CALL

Reason: while the length is passed as 0 (which I guess means: get it from varlength) GIXSQLPrepareStatement directly calls get_hostref_or_literal which just takes the passed data as plain string instead of getting the length out of the group and using that with the length offset.

[mridoni]

Can you confirm that S-SQLCOMMAND (in your example) is something like this below (either explicitly coded or generated from a VARYING group/xxx SQL TYPE IS VARCHAR field)?

01 S-SQLCOMMAND.
      49 S-SQLCOMMAND-LEN PIC 9(4) COMP-5.
      49 S-SQLCOMMAND-ARR PIC X(99).

Thanks

[GitMensch]

9(8), but otherwise: yes - and this time I've also checked the level numbers

[mridoni]

I made the following modifications (in my internal repo):

1. In the preprocessor, when a hostref_or_literal_t is encountered, an additional check is performed: if it is a variable length field, its length is passed, just like for a plain string, but with a negative sign.
2. In the runtime, when a negative length is intercepted in get_hostref_or_literal, the data is treated as being of variable-length

Note: the check in 1) for now only applies to PREPARE/EXECUTE IMMEDIATE statements, to avoid the risk of breaking too much stuff at once. Other parts of the preprocessor that use hostref_or_literal_t are still subject to the limitations in this issue (e.g. connection strings or names) but I'll try to fix them before the next release (v1.0.17), that should also include a fix for #6 . Depending on the time it takes to finish fixing #6 I might release a "dev" tarball.

I also had to modify a few test cases that were not properly exposing the problem detailed in this very issue.

The following test is precompiled and passes:

   IDENTIFICATION DIVISION.
   
   PROGRAM-ID. TSQL017C. 
   
   ENVIRONMENT DIVISION. 
   
   CONFIGURATION SECTION. 
   SOURCE-COMPUTER. IBM-AT. 
   OBJECT-COMPUTER. IBM-AT. 
   
   INPUT-OUTPUT SECTION. 
   FILE-CONTROL. 
   
   DATA DIVISION.  

   FILE SECTION.
  
   WORKING-STORAGE SECTION. 
   
       01 DATASRC     PIC X(64).
       01 DBS         PIC X(64).
       01 DBUSR       PIC X(64).
       01 DBPWD       PIC X(64).

       01 KEY01       PIC 9(6).
       01 COL1        PIC 9(6).
       01 COL2        PIC 9(6).

       01 TOT-KEY01   PIC 9(6).
       01 TOT-COL1    PIC 9(6).
       01 TOT-COL2    PIC 9(6).

       01 IDX         PIC 9(6).
    
       01  S-SQLCOMMAND SQL TYPE IS VARCHAR(250).

   EXEC SQL 
        INCLUDE SQLCA 
   END-EXEC. 

   PROCEDURE DIVISION. 

   000-CONNECT.
       DISPLAY "DATASRC" UPON ENVIRONMENT-NAME.
       ACCEPT DATASRC FROM ENVIRONMENT-VALUE.
       DISPLAY "DATASRC_USR" UPON ENVIRONMENT-NAME.
       ACCEPT DBUSR FROM ENVIRONMENT-VALUE.
       DISPLAY "DATASRC_PWD" UPON ENVIRONMENT-NAME.
       ACCEPT DBPWD FROM ENVIRONMENT-VALUE.
       
       MOVE 'CONN1' TO DBS.

       EXEC SQL
          CONNECT TO :DATASRC AS :DBS USER :DBUSR USING :DBPWD
       END-EXEC.      

       DISPLAY 'CONNECT SQLCODE: ' SQLCODE.
       IF SQLCODE <> 0 THEN
          GO TO 100-EXIT
       END-IF.

   100-MAIN.

       MOVE 1 TO IDX.

       MOVE 'INSERT INTO TAB_A (KEY01, COL1, COL2)
  -          ' VALUES ($1, $2, $3)' TO S-SQLCOMMAND-ARR.

       MOVE FUNCTION LENGTH(FUNCTION TRIM(S-SQLCOMMAND-ARR))
         TO S-SQLCOMMAND-LEN.
         

       EXEC SQL AT :DBS
           PREPARE ST1 FROM :S-SQLCOMMAND 
       END-EXEC.

       DISPLAY 'PREPARE SQLCODE: ' SQLCODE.
       IF SQLCODE <> 0 THEN
          DISPLAY 'SQLERRMC: ' SQLERRMC(1:SQLERRML)
          GO TO 100-EXIT
       END-IF.

  *  loop until no more data
       PERFORM UNTIL IDX > 10 OR SQLCODE < 0 OR SQLCODE = 100

       MOVE IDX TO KEY01, COL1, COL2
       ADD 100 TO COL1
       ADD 200 TO COL2

       EXEC SQL AT :DBS 
            EXECUTE ST1 USING :KEY01, :COL1, :COL2 
       END-EXEC     
       DISPLAY 'EXECUTE SQLCODE: ' IDX SQLCODE
       IF SQLCODE <> 0 THEN
          GO TO 100-EXIT
       END-IF

       ADD 1 TO IDX

       END-PERFORM.

       EXEC SQL AT :DBS
            SELECT SUM(KEY01), SUM(COL1), SUM(COL2)
                INTO :TOT-KEY01, :TOT-COL1, :TOT-COL2
                    FROM TAB_A
       END-EXEC.

       DISPLAY 'SUM SQLCODE: ' SQLCODE.
       IF SQLCODE <> 0 THEN
          DISPLAY 'SUM SQLERRMC: ' SQLERRMC
          GO TO 100-EXIT
       END-IF.
   
  *  display the record

       DISPLAY 'TOT-KEY01: [' TOT-KEY01 ']'.
       DISPLAY 'TOT-COL1: [' TOT-COL1 ']'.
       DISPLAY 'TOT-COL2: [' TOT-COL2 ']'.
   
   CLOSE-LOOP.


   EXEC SQL CONNECT RESET :DBS END-EXEC.

   100-EXIT. 
         STOP RUN.

   200-END.

and generates the following output:

CONNECT SQLCODE: +0000000000
PREPARE SQLCODE: +0000000000
EXECUTE SQLCODE: 000001+0000000000
EXECUTE SQLCODE: 000002+0000000000
EXECUTE SQLCODE: 000003+0000000000
EXECUTE SQLCODE: 000004+0000000000
EXECUTE SQLCODE: 000005+0000000000
EXECUTE SQLCODE: 000006+0000000000
EXECUTE SQLCODE: 000007+0000000000
EXECUTE SQLCODE: 000008+0000000000
EXECUTE SQLCODE: 000009+0000000000
EXECUTE SQLCODE: 000010+0000000000
SUM SQLCODE: +0000000000
TOT-KEY01: [000055]
TOT-COL1: [001055]
TOT-COL2: [002055]

[GitMensch]

fixed (per release notes)

[GitMensch]

Not sure if this should be reopened, but get_hostref_or_literal always ignores the varlength size specified and instead does a trim. To my understanding the maximum size that may be used is the varlength defined size - not the max - and a trim is likely not necessary in this case.
Scenario: LINKAGE with not as much memory as the varlength group has, but as much as the -LEN var says (obviously only possible for getting data out of it, not for writing data into it).

Thoughts?

[mridoni]

The trim_copy there is wrong anyway, it should respect the CHAR/VARCHAR semantics, I am reopening this.

[mridoni]

I removed the trim_copy from get_hostref_or_literal . This in turn broke a few things that I had to fix: connection string parameters and connection IDs (i.e AT MYCONN) are now trimmed where needed, like in the ConnectionManager and DataSourceInfo stuff.

[GitMensch]

mentioned to be fixed, so closing for now