You can insert multiple rows of data with one SQL statement by specifying an array-type variable in which the data for the multiple rows has been set. Using the INSERT 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 insert a large volume of data at high speed.

(a) Static execution

Specify the embedded variables in the INSERT statement's FOR clause and use an array-type variable to specify all embedded and indicator variables. The embedded variables specified in the FOR clause control the number of rows that can be inserted at one time (batch insertion).

(b) Dynamic execution

To execute the INSERT facility using arrays:

1. Use the PREPARE statement to preprocess the INSERT statement (specify one or more ? parameters).
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 INSERT statement, and specify an embedded variable in the BY clause. Use the embedded variable specified in the BY clause to control the number of rows to be inserted by batch insertion.
   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.
   

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

Explained as follows are coding examples for the INSERT facility using arrays.

Example 1

This example uses INSERT statement format 3 to set the data read from the file into an array-format embedded variable and to insert 50 rows at a time into the STOCK table.

The target table is consists of the PCODE (CHAR(4)), PNAME (VARCHAR(17)), COLOR (NCHAR(1)), PRICE (INTEGER), and SQUANTITY (INTEGER) columns.

#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];
  short in_pname_len;
  char in_pname[17];
  char in_color[3];
  int in_price;
  int i;
 
  EXEC SQL BEGIN DECLARE SECTION;
    short  xinsert_num;
    /* Declare array-type embedded variables */
    char    xpcode[50][5];            /* For specifying value to be inserted */
                                      /* in PCODE (CHAR(4) type column)      */
    SQL TYPE IS VARCHAR(17) xpname[50];
                                      /* For specifying value to be inserted */
                                      /* in PNAME (VARCHAR(17) type column) */
    char    xcolor[50][3];            /* For specifying value to be inserted */
                                      /* in COLOR (NCHAR(1) type column)      */
    long    xprice[50];               /* For specifying value to be inserted */
                                      /* in PRICE (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 insertion row count (up to 50 rows) */
    xinsert_num=50;
    while (!feof(input)) {
    /* Set input data for 50 rows (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 row count up to last data */
        /* in batch insertion row count, and escape for statement */
        xinsert_num= i;
        break;
      }
      sscanf(indata, "%4s %hd %16s %2s %8d",
      in_pcode, &in_pname_len, in_pname, in_color, &in_price);
      /* Set input data into array variable elements */
      strncpy(xpcode[i], in_pcode, 5);
      xpname[i].len = in_pname_len;
      strncpy(xpname[i].str, in_pname, 17);
      strncpy(xcolor[i], in_color, 3);
      xprice[i] = in_price;
    }
    /* INSERT execution */
    EXEC SQL FOR :xinsert_num
      INSERT INTO STOCK (PCODE, PNAME, COLOR, PRICE)
      VALUES (:xpcode, :xpname, :xcolor, :xprice);
  }
 
  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);
}

Example 2

This example uses INSERT statement format 3 to set data read from the data read function to an array-type embedded variable and to insert 50 rows at a time into the STOCK table.

The target table consists of the PCODE (CHAR(4)) and ROW_DATA (BINARY(3002)) columns.

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
 
void abnormalend();
 
main() {
  int i,rc;
 
  EXEC SQL BEGIN DECLARE SECTION;
    short  xinsert_num;
    /* Declaration of array-type embedded variables */
    char  xpcode[50][5];         /* For specifying value to be inserted */
                                 /* in PCODE (CHAR(4) type column) */
    SQL TYPE IS BINARY(3004) xrow_data[50];
                    /* For specifying value to be inserted in ROW_DATA (BINARY(3002) type column) */
                    /* However, set data length to multiple of 4. */
    EXEC SQL END DECLARE SECTION;
 
   -------(CONNECT processing to HiRDB (omitted))-------
 
    EXEC SQL WHENEVER SQLERROR PERFORM abnormalend;
    
  rc = 0 ;
  /* Batch insertion row count (up to 50 rows) */
  xinsert_num=50;
  while (0==rc) {
    /* Set input data for 50 rows (if last data in file, */
    /* up to that row) to array variables               */
    for (i = 0; i < 50; i++) {
      /* Read BINARY data: Function details omitted */
      rc = get_binarydata(&xpcode[i],&xrow_data[i]);
      if (0 != rc){
        /* If input data runs out, set row count up to last data */
        /* in batch insertion row count, and escape for statement */
        xinsert_num= i;
        break;
      }
    }
    /* INSERT execution */
    EXEC SQL FOR :xinsert_num
      INSERT INTO STOCK (PCODE, ROW_DATA)
      VALUES (:xpcode, :xrow_data);
  }
 
  EXEC SQL COMMIT;
  printf(" *** normal ended ***\n");
FIN:
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL WHENEVER SQLWARNING CONTINUE;
  EXEC SQL DISCONNECT;
  return(0);
}
void abnormalend()
{
  int  wsqlcode;
 
  wsqlcode = -SQLCODE;
  printf("\n*** HiRDB SQL ERROR SQLCODE = %d \n", wsqlcode);
  printf("SQLERRMC = %s\n", SQLERRMC);
  EXEC SQL ROLLBACK;
  EXEC SQL DISCONNECT;
  exit(1);
}

Example 3

This example uses EXECUTE statement format 2 to set data read from a file to array-format embedded variables and insert 50 rows at a time into the STOCK table.

The target table consists of the PCODE (CHAR(4)), PNAME (VARCHAR(17)), COLOR (NCHAR(1)), PRICE (INTEGER), and SQUANTITY (INTEGER) columns.

#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];
  short in_pname_len;
  char in_pname[17];
  char in_color[3];
  int in_price;
  int i;
 
  EXEC SQL BEGIN DECLARE SECTION;
    short  xinsert_num;
    /* Declaration of array-type embedded variables */
    char  xpcode[50][5];     /* For specifying value to be inserted */
                             /* in PCODE (CHAR(4) type column) */
    SQL TYPE IS VARCHAR(17) xpname[50];
                        /* For specifying value to be inserted in PNAME (VARCHAR(17) type column) */
    char  xcolor[50][3];  /* For specifying value to be inserted in COLOR (NCHAR(1) type column) */
    long  xprice[50];   /* For specifying value to be inserted in PRICE (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;
 
  /* SQL preprocessing execution */
  EXEC SQL PREPARE INS1 FROM
    'INSERT INTO STOCK(PCODE, PNAME, COLOR, PRICE) VALUES(?,?,?,?)';
 
  /* Batch insertion row count (up to 50 rows) */
  xinsert_num=50;
  while (!feof(input)) {
    /* Set input data for 50 rows (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 input data runs out, set row count up to last data */
        /* in batch insertion row count, and escape for statement */
        xinsert_num= i;
        break;
      }
      sscanf(indata, "%4s %hd %16s %2s %8d",
      in_pcode, &in_pname_len, in_pname, in_color, &in_price);
      /* Set input data to array variable elements */
      strncpy(xpcode[i], in_pcode, 5);
      xpname[i].len = in_pname_len;
      strncpy(xpname[i].str, in_pname, 17);
      strncpy(xcolor[i], in_color, 3);
      xprice[i] = in_price;
    }
    /* EXECUTE execution */
    EXEC  SQL  EXECUTE  INS1
      USING  :xpcode, :xpname, :xcolor, :xprice
      BY :xinsert_num ROWS ;
  }
 
  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);
}

Example 4

This example uses EXECUTE statement format 2 to set data read from a file to array-format embedded variables and uses a user-defined SQLDA to insert 50 rows at a time into the STOCK file.

The target table consists of the PCODE (CHAR(4)), PNAME (VARCHAR(17)), COLOR (NCHAR(1)), PRICE (INTEGER), and SQUANTITY (INTEGER) columns.

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <pdbsqlda.h>         /* Include file to use */
                              /* user-defined SQLDA  */
 
#define MAXCOLUMN 80
#define INFILE   "inputf1"
 
void abnormalend();
 
FILE *input ;
 
main() {
  char indata[MAXCOLUMN];
  char in_pcode[5];
  short in_pname_len;
  char in_pname[17];
  char in_color[3];
  int in_price;
  int i;
 
  /* Declaration of user-defined SQLDA */
  PDUSRSQLDA(4)  xsqlda;
 
  EXEC SQL BEGIN DECLARE SECTION;
    short  xinsert_num;
    /* Declaration of array-type embedded variables */
    char  xpcode[50][5];       /* For specifying value to be inserted */
                               /* in PCODE (CHAR(4) type column) */
    SQL TYPE IS VARCHAR(17) xpname[50];
                       /* For specifying value to be inserted to PNAME (VARCHAR(17) type column) */
    char  xcolor[50][3];      /* For specifying value to be inserted to COLOR (NCHAR(1) type column) */
    long  xprice[50];      /* For specifying value to be inserted to PRICE (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;
 
  /* SQL preprocessing execution */
  EXEC SQL PREPARE INS1 FROM
    'INSERT INTO STOCK(PCODE, PNAME, COLOR, PRICE) VALUES(?,?,?,?)';
 
  /* SQLVAR settings */
  PDSQLN(xsqlda) = 4 ;       /* Set SQLVAR count */
  PDSQLD(xsqlda) = 4 ;       /* Set ? parameter count */
  /* Set PCODE column information */
  PDSQLCOD(xsqlda, 0) = PDSQL_CHAR ;  /* Set data code */
  PDSQLXDIM(xsqlda, 0) = 1 ;          /* Set number of repeated */
                                      /* structure elements */
  PDSQLSYS(xsqlda, 0) = 0 ;           /* Length of one element */
                                      /* (fixed to 0 except for variable-length character strings)        */
  PDSQLLEN(xsqlda, 0) = 4 ;           /* Set data defined length */
  PDSQLDATA(xsqlda, 0) = (void *)xpcode ; /* Set data area address */
  PDSQLIND(xsqlda, 0) = NULL ;        /* Clear NULL indicator variable */
  /* Set PNAME column information */
  PDSQLCOD(xsqlda, 1) = PDSQL_VARCHAR ;  /* Set data code */
  PDSQLXDIM(xsqlda, 1) = 1 ;          /* Set number of repeated */
                                      /* structure elements */
  PDSQLLEN(xsqlda, 1) = 17 ;          /* Set data defined length */
  PDSQLSYS(xsqlda, 1) = sizeof(xpname[0]) ;  /* Length of one element */
  PDSQLDATA(xsqlda, 1) = (void *) xpname;  /* Set data area address */
  PDSQLIND(xsqlda, 1) = NULL ;        /* Clear NULL indicator variable */
  /* Set COLOR column information */
  PDSQLCOD(xsqlda, 2) = PDSQL_NCHAR ;  /* Set data code */
  PDSQLXDIM(xsqlda, 2) = 1 ;           /* Set number of repeated */
                                      /* structure elements */
  PDSQLSYS(xsqlda, 2) = 0 ;            /* Length of one element */
                                       /* (fixed to 0 except for variable-length character strings)      */
  PDSQLLEN(xsqlda, 2) = 1 ;            /* Set data defined length */
  PDSQLDATA(xsqlda, 2) = (void *) xcolor;  /* Set data area address */
  PDSQLIND(xsqlda, 2) = NULL ;         /* Clear NULL indicator variable */
  /* Set PRICE column information */
  PDSQLCOD(xsqlda, 3) = PDSQL_INTEGER ;  /* Set data code */
  PDSQLXDIM(xsqlda, 3) = 1 ;           /* Set number of repeated */
                                      /* structure elements */
  PDSQLSYS(xsqlda, 3) = 0 ;            /* Length of one element */
                                      /* (fixed to 0 except for variable-length character strings)        */
  PDSQLLEN(xsqlda, 3) = 4 ;            /* Set data defined length */
  PDSQLDATA(xsqlda, 3) = (void *) xprice;  /* Set data area address */
  PDSQLIND(xsqlda, 3) = NULL ;         /* Clear NULL indicator variable */
 
  /* Batch insertion row count (up to 50 rows) */
  xinsert_num=50;
  while (!feof(input)) {
    /* Set input data for 50 rows (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 row count up to last data */
        /* in batch insertion row count, and escape for statement */
          xinsert_num= i;
          break;
        }
        sscanf(indata, "%4s %hd %16s %2s %8d",
        in_pcode, &in_pname_len, in_pname, in_color, &in_price);
        /* Set input data to array variable elements */
        strncpy(xpcode[i], in_pcode, 5);
        xpname[i].len = in_pname_len;
        strncpy(xpname[i].str, in_pname, 17);
        strncpy(xcolor[i], in_color, 3);
        xprice[i] = in_price;
      }
      /* EXECUTE execution */
      EXEC SQL EXECUTE INS1
        USING DESCRIPTOR :xsqlda
        BY :xinsert_num ROWS ;
  }
 
  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);
}

Example 5

This example uses EXECUTE statement format 2 to set data read by a data read function to array-type embedded variables and uses a user-defined SQLDA to insert 50 rows at a time into the STOCK file.

The target table consists of the PCODE (CHAR(4)) and ROW_DATA (BINARY(3002)) columns.

 
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <pdbsqlda.h>         /* Include file for using */
                              /* user-defined SQLDA */
 
void abnormalend();
 
main() {
  int i,rc;
 
  /* Declaration of user-defined SQLDA */
  PDUSRSQLDA(4)  xsqlda;
 
  EXEC SQL BEGIN DECLARE SECTION;
    short  xinsert_num;
    /* Declaration of array-type embedded variables */
    char  xpcode[50][5];    /* For specifying value to be inserted */
                            /* to PCODE (CHAR(4) type column) */
    SQL TYPE IS BINARY(3004) xrow_data[50];
                  /* For specifying value to be inserted to ROW_DATA (BINARY(3002) type column) */
                  /* However, set data length to multiple of 4 */
  EXEC SQL END DECLARE SECTION;
 
   -------(CONNECT processing to HiRDB (omitted))-------
 
  EXEC SQL WHENEVER SQLERROR PERFORM abnormalend;
 
  /* SQL preprocessing execution */
  EXEC SQL PREPARE INS1 FROM
    'INSERT INTO STOCK(PCODE, ROW_DATA) VALUES(?,?)';
 
  /* SQLVAR settings */
  PDSQLN(xsqlda) = 2 ;                /* Set SQLVAR count */
  PDSQLD(xsqlda) = 2 ;                /* Set ? parameter count */
  /* Set PCODE column information */
  PDSQLCOD(xsqlda, 0) = PDSQL_CHAR ;  /* Set data code */
  PDSQLXDIM(xsqlda, 0) = 1 ;       /* Set number of repeated */
                                   /* structure elements */
  PDSQLSYS(xsqlda, 0) = 0 ;        /* Length of one element */
                                   /* (fixed to 0 except for variable-length character strings)             */
  PDSQLLEN(xsqlda, 0) = 4 ;        /* Set data defined length */
  PDSQLDATA(xsqlda, 0) = (void *)xpcode ;  /* Set data area address */
  PDSQLIND(xsqlda, 0) = NULL ;     /* Clear NULL indicator variable */
  /* Set ROW_DATA column information */
  PDSQLCOD(xsqlda, 1) = PDSQL_BINARY ;  /* Set data code */
  PDSQLXDIM(xsqlda, 1) = 1 ;       /* Set number of repeated */
                                   /* structure elements */
  PDSQLLOBLEN(xsqlda, 1) = 3004 ;  /* Set data defined length */
  PDSQLDATA(xsqlda, 1) = (void *) xrow_data;  /* Set data   */
                                              /* area address */
  PDSQLIND(xsqlda, 1) = NULL ;     /* Clear NULL indicator variable */
 
  rc = 0 ;
  /* Batch insertion row count (up to 50 rows) */
  xinsert_num=50;
  while (0==rc) {
    /* Set input data for 50 rows (if last data in file, */
    /* up to that row) to array variables               */
    for (i = 0; i < 50; i++) {
      /* Read BINARY data: Function details omitted */
      rc = get_binarydata(&xpcode[i],&xrow_data[i]);
      if (0 != rc){
        /* If input data runs out, set row count up to last data */
        /* in batch insertion row count, and escape for statement */
        xinsert_num= i;
        break;
      }
    }
    /* EXECUTE execution */
    EXEC  SQL  EXECUTE  INS1
      USING DESCRIPTOR :xsqlda
      BY :xinsert_num ROWS ;
  }
 
  EXEC SQL COMMIT;
  printf(" *** normal ended ***\n");
FIN:
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL WHENEVER SQLWARNING CONTINUE;
  EXEC SQL DISCONNECT;
  return(0);
}
void abnormalend()
{
  int  wsqlcode;
 
  wsqlcode = -SQLCODE;
  printf("\n*** HiRDB SQL ERROR SQLCODE = %d \n", wsqlcode);
  printf("SQLERRMC = %s\n", SQLERRMC);
  EXEC SQL ROLLBACK;
  EXEC SQL DISCONNECT;
  exit(1);
}