You can update multiple table columns with one SQL statement by specifying an array-type variable in which the data for multiple columns has been set.

Using the UPDATE facility using arrays reduces the number of communications between the HiRDB client and the HiRDB server. This facility is therefore effective when you want to access the HiRDB server from the HiRDB client and update a large volume of data at high speed.

(a) Static execution

In the UPDATE statement, specify an embedded variable in the FOR clause and change all embedded and indicator variables specified in the search condition to array-type variables. Use the embedded variable specified in the FOR clause to control the number of updates to be performed by batch processing.

(b) Dynamic execution

To execute the UPDATE facility using arrays:

1. Use the PREPARE statement to preprocess the UPDATE statement. (Specify the ? parameter for the update values and in the search condition.)
2. In the USING clause of the EXECUTE statement, use an array to specify the values to be assigned to the input ? parameter of the preprocessed UPDATE statement, and specify an embedded variable in the BY clause. Use the embedded variable specified in the BY clause to control the number of updates to be performed by batch processing.

Notes about dynamic execution are described below.

• If you specify an embedded variable in the USING clause, change all embedded and indicator variables to array-type variables.
• If you specify an SQL Descriptor Area in the USING clause, use the array format to specify data in all areas indicated by SQLDATA. In the SQLSYS area, specify values that correspond to the data type.

1. If a count that exceeds the write area is specified in the embedded variable in the FOR clause of the UPDATE statement or the BY clause of the EXECUTE statement, database destruction or UAP area destruction may occur.

Example

This example sets the data read from a file into an array-format embedded variable and performs several updates to the STOCK table by batch processing.

 
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
 
#define MAXCOLUMN 80
#define INFILE   "inputf1"
 
void abnormalend();
 
FILE *input ;
 
main() {
  char indata[MAXCOLUMN];
  char in_pcode[5];
  int in_quantity;
  int i;
 
  EXEC SQL BEGIN DECLARE SECTION;
    short   xupdate_num;
    /* Declare array-type embedded variables */
    char    xpcode[50][5]; /* For search condition to PCODE (CHAR(4) type column) */
    long    squantity[50];    /* For specifying update value to SQUANTITY (INTEGER type column) */
  EXEC SQL END DECLARE SECTION;
 
      -------(CONNECT processing to HiRDB (omitted))-------
 
  input = fopen(INFILE, "r");
  if (input == NULL) {
    fprintf(stderr, "can't open %s.", INFILE);
    goto FIN;
  }
 
  EXEC SQL WHENEVER SQLERROR PERFORM abnormalend;
 
  /* Batch update count (up to 50 updates) */
  xupdate_num=50;
  while (!feof(input)) {
    /* Set update/search condition data for 50 updates (if last */
    /* data in file, up to that row) to array variables */
    for (i = 0; i < 50; i++) {
      /* Read data from file */
      fgets(indata, MAXCOLUMN, input);
      if (feof(input)){
        /* If last data in file, set array elements up to last data */
        /* in batch update count, and escape for statement */
        xupdate_num= i;
        break;
      }
      sscanf(indata, "%4s %8d", in_pcode, &in_quantity);
      /* Set update/search condition data into array variable elements */
      strncpy(xpcode[i], in_pcode, 5);
      xquantity[i] = in_quantity;
    }
    /* UPDATE execution */
    EXEC SQL FOR :xupdate_num
      UPDATE STOCK SET ZQUANTITY = :xquantity WHERE PCODE = :xpcode ;
  }
 
  EXEC SQL COMMIT;
  printf(" *** normal ended ***\n");
FIN:
  if (input != NULL) {
  fclose(input);
  }
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL WHENEVER SQLWARNING CONTINUE;
  EXEC SQL DISCONNECT;
  return(0);
}
void abnormalend()
{
  int  wsqlcode;
 
  if (input != NULL) {
    fclose(input);
  }
  wsqlcode = -SQLCODE;
  printf("\n*** HiRDB SQL ERROR SQLCODE = %d \n", wsqlcode);
  printf("SQLERRMC = %s\n", SQLERRMC);
  EXEC SQL ROLLBACK;
  EXEC SQL DISCONNECT;
  exit(1);
}