Dynamic number of host-variables in embedded SQL

Question

I have this code in MicroFocus COBOL:

move 'select * from TABLE where a = ? and b = ? and c = ? to w-sql
exec sql
  open cur_read_2 using  :w-a,
                         :w-b,
                         :w-c
end-exec

But this select can be variable, so I can have here for example only

... where a = ? ...

or

... where a = ? and c = ? ...

So, can I somehow setup exec-sql part dynamically, to have proper number of host-variables here? In actual case I have 7 WHERE variables and full list of combinations is possible.

Answer 1

analyze this example. Maybe help you.

WORKING STORAGE SECTION.

 *VARIABLE COMMAND
01 DSTRING.
   02 STR-LEN       PIC S9(04)  COMP VALUE +200
   02 STR-TEXT      PIC  X(200).               

01 W-ABC.
   03 W-A-FROM         PIC 99 VALUE 0.
   03 W-A-TO           PIC 99 VALUE 99.

   03 W-B-FROM         PIC XX VALUE LOW-VALUES.
   03 W-B-TO           PIC XX VALUE HIGH-VALUES.

   03 W-C-FROM         PIC 99 VALUE 0.
   03 W-C-TO           PIC 99 VALUE 99.


 *DECLARE CURSOR
    EXEC SQL
         DECLARE CURSOR1 CURSOR
             FOR STMT1
    END-EXEC.



 PROCEDURE DIVISION.

    MOVE 'TABLE'          TO TS-TABLA.

* YOU MUST MOVE THE VALUE THAT CORRESPONDS TO THE VARIABLE TO USE.
* THE VARIABLE THAT IS NOT USED IS LEFT WITH THE DEFAULT VALUE
*       
    MOVE W-INPUT-A        TO W-A-FROM.
    MOVE W-INPUT-A        TO W-A-TO.

* THE W-B VARIABLE IS NOT USED. LEFT WITH THE DEFAULT VALUE.

    MOVE W-INPUT-C        TO W-C-FROM.
    MOVE W-INPUT-C        TO W-C-TO.

*OPEN CURSOR
    MOVE SPACES           TO STR-TEXT
    STRING ‘SELECT * FROM’
    ‘ ‘                 DELIMITED BY SIZE
    TS-TABLA            DELIMITED BY SPACE
    ‘ ‘                 DELIMITED BY SIZE
    ‘WHERE’             DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘A’                 DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘BETWEEN ?’         DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘AND ?’             DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘AND’               DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘B’                 DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘BETWEEN ?’         DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘AND ?’             DELIMITED BY SIZE
    ‘AND’               DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘C’                 DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘BETWEEN ?’         DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘AND ?’             DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘ORDER BY’          DELIMITED BY SIZE
    ‘ ‘                 DELIMITED BY SIZE
    ‘CODIGO’            DELIMITED BY SIZE
    INTO STR-TEXT.

*OPEN CURSOR (cont)

    EXEC  SQL
        PREPARE STMT1 FROM :DSTING
    END-EXEC.

    EXEC  SQL
        OPEN CURSOR1 USING :WS-CODIGO-SQL
    END-EXEC.