Nonstop Database, HiRDB Version 9 UAP Development Guide
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.
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).
To execute the INSERT facility using arrays:
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.
#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); }
#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); }
#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); }
#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); }
#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); }
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.