You can delete multiple rows with one SQL statement by specifying an array-type variable in which the data for multiple deletions has been set.

Using the DELETE 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 delete a large volume of data at high speed.

(a) Static execution

In the DELETE 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 deletions to be performed by batch processing.

(b) Dynamic execution

To execute the DELETE facility using arrays:

1. Use the PREPARE statement to preprocess the DELETE statement (specify the ? parameter 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 DELETE statement, and specify an embedded variable in the BY clause. Use the embedded variable specified in the BY clause to control the number of deletions 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 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 deletions from 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 i;
 
  EXEC SQL BEGIN DECLARE SECTION;
    short   xdelete_num;
    /* Declare array-type embedded variables */
    char    xpcode[50][5]; /* For search condition to PCODE (CHAR(4) 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 deletion count (up to 50 deletions) */
  xdelete_num=50;
  while (!feof(input)) {
    /* Set search condition data for 50 deletions (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 element count up to last data */
        /* in batch deletion count and escape for statement */
        xdelete_num= i;
        break;
      }
      sscanf(indata, "%4s", in_pcode);
      /* Set search condition data into array variable elements */
      strncpy(xpcode[i], in_pcode, 5);
    }
    /* DELETE execution */
    EXEC SQL FOR :xdelete_num
      DELETE FROM STOCK 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);
}