Nonstop Database, HiRDB Version 9 UAP Development Guide
You can use the FETCH statement to fetch the retrieval results for multiple rows at a time. To do this, specify an array-type embedded variable in the INTO clause or specify the number of retrieval rows in an embedded variable of the BY clause. This method is effective when the HiRDB client accesses the HiRDB system and retrieves a large volume of data. Unlike the block transfer facility, the FETCH facility using arrays clearly specifies in the program that multiple rows of retrieval results are to be fetched.
Convert all embedded and indicator variables specified in the INTO clause of the FETCH statement into array-type variables. The number of rows to be retrieved at one time becomes the minimum number of array elements for the specified embedded variables.
To execute the FETCH facility using arrays:
Following is a coding example of a FETCH operation using arrays:
long sel_cnt; long data_cnt; short i; char work[17]; /* Declaration of array-type embedded variables */ EXEC SQL BEGIN DECLARE SECTION; char xpcode[50][5]; SQL TYPE IS VARCHAR(17) xpname[50]; char xcolor[50][3]; long xprice[50]; long xsquantity[50]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE CR3 CURSOR FOR SELECT PCODE,PNAME,COLOR,PRICE,SQUANTITY FROM STOCK; EXEC SQL WHENEVER SQLERROR GOTO FIN; EXEC SQL OPEN CR3; /* Heading */ printf(" ***** Stock Table List *****\n\n"); printf(" Product code Product name Color Price Stock quantity\n"); printf(" ---- ---------------- -- -------- --------\n"); EXEC SQL WHENEVER SQLERROR GOTO FIN; EXEC SQL WHENEVER NOT FOUND GOTO FIN; /* FETCH */ sel_cnt = 0; for(;;){ EXEC SQL FETCH CR3 INTO :xpcode,:xpname,:xcolor,:xprice,:xsquantity; /* Store total row count retrieved with this */ /* cursor to SQLERRD2 */ data_cnt = SQLERRD2 - sel_cnt; /* Calculate number of retrieved rows */ for(i=0; i < data_cnt; i++){ memcpy(work, xpname[i].str, xpname[i].len); work[xpname[i].len] = '\0'; printf(" %4s %-16s %2s %8d %8d\n", xpcode[i], work, xcolor[i], xprice[i], xsquantity[i]); } sel_cnt = SQLERRD2; } FIN: /* */ /* Display remaining data because data is read even */ /* if error or NOT FOUND occurs */ /* */ if(sel_cnt != SQLERRD2){ data_cnt = SQLERRD2 - sel_cnt; for(i=0; i < data_cnt; i++){ memcpy(work, xpname[i].str, xpname[i].len); work[xpname[i].len] = '\0'; printf(" %4s %-16s %2s %8d %8d\n", xpcode[i], work, xcolor[i], xprice[i], xsquantity[i]); } } FIN: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE CR3; EXEC SQL COMMIT;
#include <pdbsqlda.h> /* Include this file to use */ /* user-defined SQLDA*/ long sel_cnt; long data_cnt; short i; char work[17]; /* Declaration of user-defined SQLDA */ PDUSRSQLDA(5) xsqlda; /* Declaration of array-type embedded variables */ EXEC SQL BEGIN DECLARE SECTION; char xpcode[50][5]; SQL TYPE IS VARCHAR(17) xpname[50]; char xcolor[50][3]; long xprice[50]; long xsquantity[50]; short arry_num; EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLERROR GOTO FIN; /* Preprocessing of retrieval SQL */ EXEC SQL PREPARE SEL1 FROM 'SELECT * FROM STOCK' ; /* Acquisition of retrieval SQL output information */ PDSQLN(xsqlda) = 5 ; /* Set SQLVAR count */ EXEC SQL DESCRIBE SEL1 INTO :xsqlda ; EXEC SQL DECLARE CR3 CURSOR FOR SEL1 ; EXEC SQL OPEN CR3; /* SQLVAR setting: Normally, it would better if I/O */ /* area was allocated dynamically from SQLDA. */ /* However, the specification is omitted because */ /* this is an example. */ /* Values that were set during DESCRIBE processing */ /* are used for SQLLEN, SQLXDIM, and SQLSYS. */ /* PCODE column information settings */ PDSQLDATA(xsqlda, 0) = (void *)xpcode ; /* Set address */ PDSQLIND(xsqlda, 0) = NULL ; /* Clear NULL indicator variable */ PDSQLCOD(xsqlda, 0) = PDSQL_CHAR ; /* Set data code */ /* PNAME column information settings */ PDSQLDATA(xsqlda, 1) = (void *) xpname; /* Set address */ PDSQLIND(xsqlda, 1) = NULL ; /* Clear NULL indicator variable */ PDSQLCOD(xsqlda, 1) = PDSQL_VARCHAR ; /* Set data code */ PDSQLSYS(xsqlda, 1) = sizeof(xpname[0]) ; /* Set SQLSYS because this is */ /* variable-length data */ /* COLOR column information settings */ PDSQLDATA(xsqlda, 2) = (void *) xcolor; /* Set address */ PDSQLIND(xsqlda, 2) = NULL ; /* Clear NULL indicator variable */ PDSQLCOD(xsqlda, 2) = PDSQL_NCHAR ; /* Set data code */ /* PRICE column information settings */ PDSQLDATA(xsqlda, 3) = (void *) xprice; /* Set address */ PDSQLIND(xsqlda, 3) = NULL ; /* Clear NULL indicator variable */ PDSQLCOD(xsqlda, 3) = PDSQL_INTEGER ; /* Set data code */ /* SQUANTITY column information settings */ PDSQLDATA(xsqlda, 4) = (void *) xsquantity; /* Set address */ PDSQLIND(xsqlda, 4) = NULL ; /* Clear NULL indicator variable */ PDSQLCOD(xsqlda, 4) = PDSQL_ INTEGER; /* Set data code */ /* Heading */ printf(" ***** Stock Table List *****\n\n"); printf(" Product code Product name Color Price Stock quantity\n"); printf(" ---- ---------------- -- -------- --------\n"); EXEC SQL WHENEVER SQLERROR GOTO FIN; EXEC SQL WHENEVER NOT FOUND GOTO FIN; /* FETCH */ sel_cnt = 0; for(;;){ arry_num = 50 ; EXEC SQL FETCH CR3 USING DESCRIPTOR :xsqlda BY :arry_num ROWS ; /* Store total row count retrieved with this */ /* cursor to SQLERRD2 */ data_cnt = SQLERRD2 - sel_cnt; /* Calculate number of fetched rows */ for(i=0; i < data_cnt; i++){ memcpy(work, xpname[i].str, xpname[i].len); work[xpname[i].len] = '\0'; printf(" %4s %-16s %2s %8d %8d\n", xpcode[i], work, xcolor[i], xprice[i], xsquantity [i]); } sel_cnt = SQLERRD2; } FIN: /* */ /* Display remaining data because data is read even */ /* if error or NOT FOUND occurs */ /* */ if(sel_cnt != SQLERRD2){ data_cnt = SQLERRD2 - sel_cnt; for(i=0; i < data_cnt; i++){ memcpy(work, xpname[i].str, xpname[i].len); work[xpname[i].len] = '\0'; printf(" %4s %-16s %2s %8d %8d\n", xpcode[i], work, xcolor[i], xprice[i], xsquantity [i]); } } FIN: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE CR3; EXEC SQL COMMIT;
long sel_cnt; long data_cnt; short i; /* Declaration of array-type embedded variables */ EXEC SQL BEGIN DECLARE SECTION; long xcode[50]; /* To fetch data using BINARY-type array, */ /* define area length with multiple of 4 */ SQL TYPE IS BINARY(3004) xrow_data[50]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE CR3 CURSOR FOR SELECT * FROM T_BINARY; EXEC SQL WHENEVER SQLERROR GOTO FIN; EXEC SQL OPEN CR3; /* Heading */ printf(" ***** Binary Data Table *****\n\n"); EXEC SQL WHENEVER SQLERROR GOTO FIN; EXEC SQL WHENEVER NOT FOUND GOTO FIN; /* FETCH */ sel_cnt = 0; for(;;){ EXEC SQL FETCH CR3 INTO : xcode,: xrow_data; /* Store total row count retrieved with this */ /* cursor to SQLERRD2 */ data_cnt = SQLERRD2 - sel_cnt; /* Calculate number of fetched rows */ for(i=0; i < data_cnt; i++){ printf(" CODE=%8d\n",xcode[i]); printf(" DATA_LENGTH=%d\n", xrow_data [i].len); /* Do not display BINARY data section because this is */ /* only an example */ /* Convert xrow_data[i].str to individual format of */ /* each UAP */ } sel_cnt = SQLERRD2; } FIN: /* */ /* Display remaining data because data is read even */ /* if error or NOT FOUND occurs */ /* */ if(sel_cnt != SQLERRD2){ data_cnt = SQLERRD2 - sel_cnt; for(i=0; i < data_cnt; i++){ printf(" CODE=%8d\n",xcode[i]); printf(" DATA_LENGTH=%d\n", xrow_data [i].len); /* Do not display BINARY data section because this is */ /* only an example */ /* Convert xrow_data[i].str to individual format of */ /* each UAP */ } } FIN: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE CR3; EXEC SQL COMMIT;
#include <pdbsqlda.h> /* Include this file to use */ /* user-defined SQLDA */ long sel_cnt; long data_cnt; short i; /* Declaration of user-defined SQLDA */ PDUSRSQLDA(2) xsqlda; /* Declaration of array-type embedded variable */ EXEC SQL BEGIN DECLARE SECTION; long xcode[50]; /* To fetch data using BINARY-type array, */ /* define area length with multiple of 4 */ SQL TYPE IS BINARY(3004) xrow_data[50]; short arry_num; EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLERROR GOTO FIN; /* Preprocessing of retrieval SQL */ EXEC SQL PREPARE SEL1 FROM 'SELECT * FROM T_BINARY ; /* Acquisition of retrieval SQL output information */ PDSQLN(xsqlda) = 2 ; /* Set SQLVAR count */ EXEC SQL DESCRIBE SEL1 INTO :xsqlda ; EXEC SQL DECLARE CR3 CURSOR FOR SEL1 ; EXEC SQL OPEN CR3; /* SQLVAR setting: Normally, it would better if I/O */ /* area was allocated dynamically from SQLDA. */ /* However, the specification is omitted because */ /* this is an example. */ /* Values that were set during DESCRIBE processing */ /* are used for SQLLEN, SQLXDIM, and SQLSYS. */ /* XCODE column information settings */ PDSQLDATA(xsqlda, 0) = (void *)xcode ; /* Set address */ PDSQLIND(xsqlda, 0) = NULL ; /* Clear NULL indicator variable */ PDSQLCOD(xsqlda, 0) = PDSQL_INTEGER ; /* Set data code */ /* R_DATA column information settings */ PDSQLDATA(xsqlda, 1) = (void *) xrow_data; /* Set address */ PDSQLIND(xsqlda, 1) = NULL ; /* Clear NULL indicator variable */ PDSQLCOD (xsqlda, 1) = PDSQL_BINARY ; /* Set data code*/ PDSQLLEN (xsqlda, 1) = 3004 ; /* Reset because defined length */ /* is not multiple of 4 */ /* Heading */ printf(" ***** Binary Data Table *****\n\n"); EXEC SQL WHENEVER SQLERROR GOTO FIN; EXEC SQL WHENEVER NOT FOUND GOTO FIN; /* FETCH */ sel_cnt = 0; for(;;){ arry_num = 50 ; EXEC SQL FETCH CR3 USING DESCRIPTOR :xsqlda BY :arry_num ROWS ; /* Store total row count retrieved with this */ /* cursor to SQLERRD2 */ data_cnt = SQLERRD2 - sel_cnt; /* Calculate number of fetched rows */ for(i=0; i < data_cnt; i++){ printf(" CODE=%8d\n",xcode[i]); printf(" DATA_LENGTH=%d\n", xrow_data [i].len); /* Do not display BINARY data section because this is an example */ /* Convert xrow_data[i].str to individual format of each UAP */ } sel_cnt = SQLERRD2; } FIN: /* */ /* Display remaining data because data is read even */ /* if error or NOT FOUND occurs */ /* */ if(sel_cnt != SQLERRD2){ data_cnt = SQLERRD2 - sel_cnt; for(i=0; i < data_cnt; i++){ printf(" CODE=%8d\n",xcode[i]); printf(" DATA_LENGTH=%d\n", xrow_data [i].len); /* Do not display BINARY data section because this is */ /* only an example */ /* Convert xrow_data[i].str to individual format of */ /* each UAP */ } } FIN: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE CR3; EXEC SQL COMMIT;
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.