procob handle array-type host variable

l  Use select statement when we know the max rows will bereturned.

WORKING-STORAGE SECTION.

01 TABLE-ROWS .

  03S-SEQ-NUM-S    PIC S9(8) COMP-3    OCCURS 3 TIMES.

  03S-NAME-S       PIC X(8)            OCCURS 3 TIMES.

  03S-ID-S          PIC S9(4) COMP-3    OCCURS 3 TIMES.

  03SCORE-Y-S      PIC X(4)            OCCURS 3 TIMES.

PROCEDURE DIVISION.

  EXECSQL

    SELECTS_SEQ_NUM, S_NAME, S_ID, SCORE_Y

     INTO :TABLE-ROWS:I-TABLE-ROWS

     FROM V16TABLE

  END-EXEC.

  IFSQLCODE = 0

    DISPLAY"RETURN OK"

    DISPLAY"RETURN ROWS: " SQLERRD(3)                  *>exactly 3 rows data are selected.

  ELSEIF SQLCODE = 100

    DISPLAY"NO DATA ROW IS FOUND"

    DISPLAY"RETURN ROWS: " SQLERRD(3)                  *>SQLERRD(3) indicate how many real data rows are selected; host array size 3 meanswe wish exactly 3 rows are selected, but the real data rows can be less than 3,e.g., 2, 1, 0

  ELSEIF SQLCODE = -2112

    DISPLAY"RETURN TOO MANY ROWS"                      *>There should have more than 3 rows, but array size is 3, so first 3 rows arereturned, and SQLCODE is set to -2112

    DISPLAY"RETURN ROWS: " SQLERRD(3)

  ELSE

    DISPLAY"ORACLE ERROR DETECTED:"

    DISPLAY"SQLCODE = " SQLCODE

    DISPLAY"SQLERRM = " SQLERRMC

  END-IF.

Limitation: we cannot indicate the expectedrows size returned, except the array size.

l  Use fetch statement when we don’t know the max rows will bereturned.

WORKING-STORAGE SECTION.

01 SELECT-ROW-REQ                            PIC S9(09)  COMP.

01 TABLE-ROWS.

   03S-SEQ-NUM-S            OCCURS  00005  PIC S9(8) COMP-3.

   03S-NAME-S               OCCURS  00005  PIC X(8).

   03S-ID-S                 OCCURS  00005  PIC S9(4) COMP-3.

   03SCORE-Y-S              OCCURS  00005  PIC X(4).

PROCEDURE DIVISION.

   EXEC SQL

    DECLARE CUR_SQL SCROLL CURSOR WITH HOLD FOR

    SELECT S_SEQ_NUM, S_NAME, S_ID, SCORE_Y

    FROM V16TABLE

    WHERE S_SEQ_NUM = :TABLE-KEYS

END-EXEC.

EXEC SQLOPEN  CUR_SQL END-EXEC.

MOVE 2 TOSELECT-ROW-REQ.

EXEC SQL      

  FETCH NEXT ROWSET CUR_SQL    

  FOR :SELECT-ROW-REQ ROWS                                                            *>we expect to fetch 2 rows

  INTO :S-SEQ-NUM-S, :S-NAME-S, :S-ID-S,:SCORE-Y-S

END-EXEC.

IF SQLCODE = 0

  DISPLAY "RETURN OK"

  DISPLAY "RETURN ROWS: " SQLERRD(3)

ELSE IF SQLCODE= 100                                                                              *>SQLERRD(3) indicate how many real data rows are selected; SELECT-ROW-REQ=2 meanswe wish exactly 2 rows are selected, but the real data rows can be less than 2,e.g., 1, 0

  DISPLAY "NO DATA ROW IS FOUND"

  DISPLAY "RETURN ROWS: " SQLERRD(3)

ELSE

  DISPLAY "ORACLE ERROR DETECTED:"

  DISPLAY "SQLCODE = " SQLCODE

  DISPLAY "SQLERRM = " SQLERRMC

END-IF.

EXEC SQL CLOSECUR_SQL END-EXEC.

l  Host array limitation

-         Simple host variables cannot bemixed with host arrays in the INTO clause of a SELECT or FETCH statement. Ifany of the host variables is an array, all must be arrays.

-         Using host arrays in the WHEREclause of a SELECT statement is not allowed except in a sub-query, only simplehost variable in WHERE clause of a SELECT statement is allowed, so this is theonly time that you can mix array host variables and simple host variables.

Following code snippet will have same result (the host arrayin WHERE clause is treated as simple host variable):

WORKING-STORAGE SECTION.

 01 SELECT-ROW-REQ                            PIC S9(09)  COMP.

 01 TABLE-ROWS.

    03 S-SEQ-NUM-S            OCCURS  00005  PIC S9(8) COMP-3.

    03 S-NAME-S               OCCURS  00005  PIC X(8).

    03 S-ID-S                 OCCURS  00005  PIC S9(4) COMP-3.

    03 SCORE-Y-S              OCCURS  00005  PIC X(4).

01 TABLE-KEYS                OCCURS  00005  PIC S9(8) COMP-3.

PROCEDURE DIVISION.

     EXEC SQL

      DECLARE CUR_SQLSCROLL CURSOR WITH HOLD FOR

      SELECT S_SEQ_NUM,S_NAME, S_ID, SCORE_Y

      FROM V16TABLE

      WHERE S_SEQ_NUM = :TABLE-KEYS

     END-EXEC.

     MOVE 2 TOSELECT-ROW-REQ.

     MOVE 4 TO TABLE-KEYS(1).

    MOVE5 TO TABLE-KEYS(2).

     EXEC SQL OPEN  CUR_SQL END-EXEC.

     EXEC SQL     

      FETCH NEXT ROWSETCUR_SQL   

      FOR:SELECT-ROW-REQ ROWS

      INTO:S-SEQ-NUM-S, :S-NAME-S, :S-ID-S, :SCORE-Y-S

     END-EXEC.

vs.

WORKING-STORAGE SECTION.

 01 SELECT-ROW-REQ                            PIC S9(09)  COMP.

 01 TABLE-ROWS.

    03 S-SEQ-NUM-S            OCCURS  00005  PIC S9(8) COMP-3.

    03 S-NAME-S               OCCURS  00005  PIC X(8).

    03 S-ID-S                 OCCURS  00005  PIC S9(4) COMP-3.

    03 SCORE-Y-S              OCCURS  00005  PIC X(4).

01 TABLE-KEYS                PIC S9(8) COMP-3.

PROCEDURE DIVISION.

     EXEC SQL

      DECLARE CUR_SQLSCROLL CURSOR WITH HOLD FOR

      SELECT S_SEQ_NUM,S_NAME, S_ID, SCORE_Y

      FROM V16TABLE

      WHERE S_SEQ_NUM = :TABLE-KEYS

     END-EXEC.

     MOVE 2 TOSELECT-ROW-REQ.

     MOVE 4 TO TABLE-KEYS.

     EXEC SQL OPEN  CUR_SQL END-EXEC.

     EXEC SQL     

      FETCH NEXT ROWSETCUR_SQL   

      FOR:SELECT-ROW-REQ ROWS

      INTO:S-SEQ-NUM-S, :S-NAME-S, :S-ID-S, :SCORE-Y-S

     END-EXEC.