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