You can use the FETCH statement to fetch the retrieval results for multiple rows at a time. To do this, specify an array-type embedded variable in the INTO clause or specify the number of retrieval rows in an embedded variable of the BY clause. This method is effective when the HiRDB client accesses the HiRDB system and retrieves a large volume of data. Unlike the block transfer facility, the FETCH facility using arrays clearly specifies in the program that multiple rows of retrieval results are to be fetched.

(a) Static execution

Convert all embedded and indicator variables specified in the INTO clause of the FETCH statement into array-type variables. The number of rows to be retrieved at one time becomes the minimum number of array elements for the specified embedded variables.

(b) Dynamic execution

To execute the FETCH facility using arrays:

1. Use the PREPARE statement to preprocess the SELECT statement.
2. Use the DESCRIBE statement to fetch information about the SQL descriptor area of the preprocessed SELECT statement.
3. In the SQLDATA area indicated in the SQL descriptor area, specify the receiving area for each data item. For variable-length data, specify the size of one element in the SQLSYS area.
4. Specify the SQL descriptor area in the USING DESCRIPTOR clause of the FETCH statement and specify an embedded variable in the BY clause. Use the embedded variable to specify the number of rows to be retrieved at one time.

1. A cursor specified with the FETCH facility using arrays becomes a dedicated cursor for that facility. When that cursor is used, the block transfer facility becomes ineffective. If that cursor is used to execute the normal FETCH facility, Note 4 applies. When the same module (preprocessing unit) uses both the FETCH facility using arrays and the normal FETCH facility, use a separate cursor for each.
2. Note that, unlike the normal FETCH facility, the FETCH facility using arrays fetches data up to the row before the NOT FOUND occurrence if the rows to be fetched run out during retrieval processing. Similarly, if an error occurs, the FETCH facility using arrays fetches the data up to the row in which the error occurred.
3. If the FETCH facility using arrays is executed dynamically, the UAP area may be destroyed if the number of rows specified in the embedded variable of the BY clause is larger than the receiving area.
4. The FETCH facility using arrays cannot be used if one of the following conditions applies:
   
   • A query specification contains a BLOB-type selection expression.
   • A query specification contains a BINARY-type selection expression, and the defined length for one element in the receiving area of the BINARY-type selection expression is not a multiple of 4.
   • The search includes a BINARY-type selection expression having a defined length of 32,001 bytes or more, and the version of either HiRDB Server or HiRDB Client Library is 07-00 or earlier.

Following is a coding example of a FETCH operation using arrays:

Example 1

This example uses FETCH statement format 3. The target table consists of the PCODE (CHAR(4)), PNAME (VARCHAR(17)), COLOR (NCHAR(1)), PRICE (INTEGER), and SQUANTITY (INTEGER) columns.

  long sel_cnt;
  long data_cnt;
  short i;
  char work[17];
 
  /* Declaration of array-type embedded variables */
  EXEC SQL BEGIN DECLARE SECTION;
      char    xpcode[50][5];
      SQL  TYPE  IS  VARCHAR(17)  xpname[50];
      char    xcolor[50][3];
      long    xprice[50];
      long    xsquantity[50];
  EXEC SQL END DECLARE SECTION;
 
  EXEC SQL
      DECLARE CR3 CURSOR FOR
         SELECT PCODE,PNAME,COLOR,PRICE,SQUANTITY
         FROM STOCK;
 
  EXEC SQL WHENEVER SQLERROR GOTO FIN;
 
  EXEC SQL OPEN CR3;
 
  /*  Heading */
 
  printf("    *****  Stock Table List  *****\n\n");
  printf("    Product code  Product name  Color  Price      Stock quantity\n");
  printf("  ----  ----------------  --  --------  --------\n");
 
  EXEC SQL WHENEVER SQLERROR GOTO FIN;
  EXEC SQL WHENEVER NOT FOUND GOTO FIN;
 
  /* FETCH */
  sel_cnt = 0;
  for(;;){
      EXEC SQL
          FETCH CR3 INTO :xpcode,:xpname,:xcolor,:xprice,:xsquantity;
      /* Store total row count retrieved with this  */
      /* cursor to SQLERRD2                         */
      data_cnt = SQLERRD2 - sel_cnt;       /* Calculate number of retrieved rows */
      for(i=0; i < data_cnt; i++){
          memcpy(work, xpname[i].str, xpname[i].len);
          work[xpname[i].len] = '\0';
          printf("    %4s      %-16s  %2s  %8d  %8d\n",
                  xpcode[i], work, xcolor[i], xprice[i], xsquantity[i]);
      }
      sel_cnt = SQLERRD2;
  }
 
FIN:
/*                                                    */
/* Display remaining data because data is read even   */
/* if error or NOT FOUND occurs                       */
/*                                                    */
  if(sel_cnt  !=  SQLERRD2){
      data_cnt = SQLERRD2 - sel_cnt;
      for(i=0; i < data_cnt; i++){
          memcpy(work, xpname[i].str, xpname[i].len);
          work[xpname[i].len] = '\0';
          printf("    %4s      %-16s  %2s  %8d  %8d\n",
                  xpcode[i], work, xcolor[i], xprice[i], xsquantity[i]);
      }
  }
FIN:
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL CLOSE CR3;
  EXEC SQL COMMIT;

Example 2

This example uses FETCH statement format 2. The target table consists of the PCODE (CHAR(4)), PNAME (VARCHAR(17)), COLOR (NCHAR(1)), PRICE (INTEGER), and SQUANTITY (INTEGER) columns.

#include <pdbsqlda.h>     /* Include this file to use */
                          /* user-defined SQLDA*/
 
  long sel_cnt;
  long data_cnt;
  short i;
  char work[17];
 
  /* Declaration of user-defined SQLDA */
  PDUSRSQLDA(5)  xsqlda;
 
  /* Declaration of array-type embedded variables */
  EXEC SQL BEGIN DECLARE SECTION;
      char    xpcode[50][5];
      SQL  TYPE  IS  VARCHAR(17)  xpname[50];
      char    xcolor[50][3];
      long    xprice[50];
      long    xsquantity[50];
      short   arry_num;
  EXEC SQL END DECLARE SECTION;
 
  EXEC SQL WHENEVER SQLERROR GOTO FIN;
 
  /* Preprocessing of retrieval SQL */
  EXEC SQL PREPARE SEL1 FROM
         'SELECT * FROM STOCK' ;
 
  /* Acquisition of retrieval SQL output information */
  PDSQLN(xsqlda) = 5 ;      /* Set SQLVAR count */
  EXEC SQL DESCRIBE SEL1 INTO :xsqlda ;
 
  EXEC SQL
      DECLARE CR3 CURSOR FOR SEL1 ;
 
  EXEC SQL OPEN CR3;
 
  /* SQLVAR setting: Normally, it would better if I/O */
  /* area was allocated dynamically from SQLDA.       */
  /* However, the specification is omitted because    */
  /* this is an example.                              */
  /* Values that were set during DESCRIBE processing  */
  /* are used for SQLLEN, SQLXDIM, and SQLSYS.        */
  /* PCODE column information settings */
  PDSQLDATA(xsqlda, 0) = (void *)xpcode ;    /* Set address */
  PDSQLIND(xsqlda, 0) = NULL ;               /* Clear NULL indicator variable */
  PDSQLCOD(xsqlda, 0) = PDSQL_CHAR ;         /* Set data code */
  /* PNAME column information settings */
  PDSQLDATA(xsqlda, 1) = (void *) xpname;    /* Set address */
  PDSQLIND(xsqlda, 1) = NULL ;               /* Clear NULL indicator variable */
  PDSQLCOD(xsqlda, 1) = PDSQL_VARCHAR ;      /* Set data code */
  PDSQLSYS(xsqlda, 1) = sizeof(xpname[0]) ;  /* Set SQLSYS because this is */
                                             /* variable-length data                 */
  /* COLOR column information settings */
  PDSQLDATA(xsqlda, 2) = (void *) xcolor;      /* Set address */
  PDSQLIND(xsqlda, 2) = NULL ;               /* Clear NULL indicator variable */
  PDSQLCOD(xsqlda, 2) = PDSQL_NCHAR ;        /* Set data code */
  /* PRICE column information settings */
  PDSQLDATA(xsqlda, 3) = (void *) xprice;    /* Set address */
  PDSQLIND(xsqlda, 3) = NULL ;               /* Clear NULL indicator variable */
  PDSQLCOD(xsqlda, 3) = PDSQL_INTEGER ;      /* Set data code */
  /* SQUANTITY column information settings */
  PDSQLDATA(xsqlda, 4) = (void *) xsquantity;   /* Set address */
  PDSQLIND(xsqlda, 4) = NULL ;               /* Clear NULL indicator variable */
  PDSQLCOD(xsqlda, 4) = PDSQL_ INTEGER;      /* Set data code */
 
  /*  Heading */
 
  printf("    *****  Stock Table List  *****\n\n");
  printf("    Product code  Product name            Color  Price      Stock quantity\n");
  printf("    ----        ----------------  --  --------  --------\n");
 
  EXEC SQL WHENEVER SQLERROR GOTO FIN;
  EXEC SQL WHENEVER NOT FOUND GOTO FIN;
 
  /* FETCH */
  sel_cnt = 0;
  for(;;){
      arry_num = 50 ;
      EXEC SQL
          FETCH CR3 USING DESCRIPTOR :xsqlda BY :arry_num ROWS ;
      /* Store total row count retrieved with this  */
      /* cursor to SQLERRD2                         */
      data_cnt = SQLERRD2 - sel_cnt;       /* Calculate number of fetched rows */
      for(i=0; i < data_cnt; i++){
          memcpy(work, xpname[i].str, xpname[i].len);
          work[xpname[i].len] = '\0';
          printf("    %4s      %-16s  %2s  %8d  %8d\n",
                  xpcode[i], work, xcolor[i], xprice[i], xsquantity [i]);
      }
      sel_cnt = SQLERRD2;
  }
 
FIN:
/*                                                    */
/* Display remaining data because data is read even   */
/* if error or NOT FOUND occurs                       */
/*                                                    */
  if(sel_cnt  !=  SQLERRD2){
      data_cnt = SQLERRD2 - sel_cnt;
      for(i=0; i < data_cnt; i++){
          memcpy(work, xpname[i].str, xpname[i].len);
          work[xpname[i].len] = '\0';
          printf("    %4s      %-16s  %2s  %8d  %8d\n",
                  xpcode[i], work, xcolor[i], xprice[i], xsquantity [i]);
      }
  }
FIN:
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL CLOSE CR3;
  EXEC SQL COMMIT;

Example 3

The example uses FETCH statement format 3. The target table consists of the XCODE (INTEGER) and ROW_DATA (BINARY(3002)) columns.

  long sel_cnt;
  long data_cnt;
  short i;
 
  /* Declaration of array-type embedded variables */
  EXEC SQL BEGIN DECLARE SECTION;
      long    xcode[50];
      /* To fetch data using BINARY-type array,   */
      /* define area length with multiple of 4    */
      SQL  TYPE  IS  BINARY(3004)  xrow_data[50];
  EXEC SQL END DECLARE SECTION;
 
  EXEC SQL
      DECLARE CR3 CURSOR FOR
         SELECT * FROM T_BINARY;
 
  EXEC SQL WHENEVER SQLERROR GOTO FIN;
 
  EXEC SQL OPEN CR3;
 
  /*  Heading */
 
  printf("    *****  Binary Data Table  *****\n\n");
 
  EXEC SQL WHENEVER SQLERROR GOTO FIN;
  EXEC SQL WHENEVER NOT FOUND GOTO FIN;
 
  /* FETCH */
  sel_cnt = 0;
  for(;;){
 
      EXEC SQL
          FETCH CR3 INTO : xcode,: xrow_data;
      /* Store total row count retrieved with this  */
      /* cursor to SQLERRD2                         */
      data_cnt = SQLERRD2 - sel_cnt;       /* Calculate number of fetched rows */
      for(i=0; i < data_cnt; i++){
          printf("    CODE=%8d\n",xcode[i]);
          printf("    DATA_LENGTH=%d\n", xrow_data [i].len);
/* Do not display BINARY data section because this is */
/* only an example                                    */
/* Convert xrow_data[i].str to individual format of   */
/* each UAP                                           */
      }
      sel_cnt = SQLERRD2;
  }
 
FIN:
/*                                                    */
/* Display remaining data because data is read even   */
/* if error or NOT FOUND occurs                       */
/*                                                    */
  if(sel_cnt  !=  SQLERRD2){
      data_cnt = SQLERRD2 - sel_cnt;
      for(i=0; i < data_cnt; i++){
          printf("    CODE=%8d\n",xcode[i]);
          printf("    DATA_LENGTH=%d\n", xrow_data [i].len);
/* Do not display BINARY data section because this is */
/* only an example                                    */
/* Convert xrow_data[i].str to individual format of   */
/* each UAP                                           */
      }
  }
FIN:
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL CLOSE CR3;
  EXEC SQL COMMIT;

Example 4

This example uses FETCH statement format 2. The target table consists of the XCODE (INTEGER) and ROW_DATA (BINARY(3002)) columns.

#include <pdbsqlda.h>     /* Include this file to use */
                          /* user-defined SQLDA       */
 
  long sel_cnt;
  long data_cnt;
  short i;
 
  /* Declaration of user-defined SQLDA */
  PDUSRSQLDA(2)  xsqlda;
 
  /* Declaration of array-type embedded variable */
  EXEC SQL BEGIN DECLARE SECTION;
      long    xcode[50];
      /* To fetch data using BINARY-type array,   */
      /* define area length with multiple of 4    */
      SQL  TYPE  IS  BINARY(3004)  xrow_data[50];
      short    arry_num;
  EXEC SQL END DECLARE SECTION;
 
  EXEC SQL WHENEVER SQLERROR GOTO FIN;
 
  /* Preprocessing of retrieval SQL */
  EXEC SQL PREPARE SEL1 FROM
         'SELECT * FROM T_BINARY ;
 
  /* Acquisition of retrieval SQL output information */
  PDSQLN(xsqlda) = 2 ;      /* Set SQLVAR count */
  EXEC SQL DESCRIBE SEL1 INTO :xsqlda ;
 
  EXEC SQL
      DECLARE CR3 CURSOR FOR SEL1 ;
 
  EXEC SQL OPEN CR3;
 
  /* SQLVAR setting: Normally, it would better if I/O */
  /* area was allocated dynamically from SQLDA.       */
  /* However, the specification is omitted because    */
  /* this is an example.                              */
  /* Values that were set during DESCRIBE processing  */
  /* are used for SQLLEN, SQLXDIM, and SQLSYS.        */
  /* XCODE column information settings */
  PDSQLDATA(xsqlda, 0) = (void *)xcode ;    /* Set address */
  PDSQLIND(xsqlda, 0) = NULL ;              /* Clear NULL indicator variable */
  PDSQLCOD(xsqlda, 0) = PDSQL_INTEGER ;     /* Set data code */
  /* R_DATA column information settings */
  PDSQLDATA(xsqlda, 1) = (void *) xrow_data; /* Set address */
  PDSQLIND(xsqlda, 1) = NULL ;               /* Clear NULL indicator variable */
  PDSQLCOD (xsqlda, 1) = PDSQL_BINARY ;      /* Set data code*/
  PDSQLLEN (xsqlda, 1) = 3004 ;              /* Reset because defined length */
                                             /* is not multiple of 4 */
 
  /*  Heading */
 
  printf("    *****  Binary Data Table  *****\n\n");
 
  EXEC SQL WHENEVER SQLERROR GOTO FIN;
  EXEC SQL WHENEVER NOT FOUND GOTO FIN;
 
  /* FETCH */
  sel_cnt = 0;
  for(;;){
      arry_num = 50 ;
      EXEC SQL
          FETCH CR3 USING DESCRIPTOR :xsqlda BY :arry_num ROWS ;
      /* Store total row count retrieved with this  */
      /* cursor to SQLERRD2                         */
      data_cnt = SQLERRD2 - sel_cnt;       /* Calculate number of fetched rows */
      for(i=0; i < data_cnt; i++){
          printf("    CODE=%8d\n",xcode[i]);
          printf("    DATA_LENGTH=%d\n", xrow_data [i].len);
/* Do not display BINARY data section because this is an example  */
/* Convert xrow_data[i].str to individual format of each UAP */
      }
      sel_cnt = SQLERRD2;
  }
 
FIN:
/*                                                    */
/* Display remaining data because data is read even   */
/* if error or NOT FOUND occurs                       */
/*                                                    */
  if(sel_cnt  !=  SQLERRD2){
      data_cnt = SQLERRD2 - sel_cnt;
      for(i=0; i < data_cnt; i++){
          printf("    CODE=%8d\n",xcode[i]);
          printf("    DATA_LENGTH=%d\n", xrow_data [i].len);
/* Do not display BINARY data section because this is */
/* only an example                                    */
/* Convert xrow_data[i].str to individual format of   */
/* each UAP                                           */
      }
  }
FIN:
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL CLOSE CR3;
  EXEC SQL COMMIT;