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