Example: Defining queries
These ILE C programs show how to use the Query (QQQQRY) API to define a simple query for ordering; a join query; and a join query with selection, grouping, and ordering.
These programs use the QQAPI header
(or include) file and the QQFUNCS query code.
Note: By using the code examples,
you agree to the terms of the Code license and disclaimer information.
QQAPI header
/********************************************************************/
#ifndef _QQAPIH
#define _QQAPIH
/*******************************************************************/
/*******************************************************************/
/* */
/* FUNCTION: Defines constants and structures for use */
/* with the QQQQRY API examples. */
/* */
/* LANGUAGE: ILE C */
/* */
/* APIs USED: None */
/* */
/*******************************************************************/
/*******************************************************************/
/* The following define will enable some debug procedures and code */
/* #define QQDEBUG */
/* Query Open options */
#define QO_INPUT 1
#define QO_OUTPUT 2
#define QO_UPDATE 4
#define QO_DELETE 8
/* simple defines */
#define ON 1
#define OFF 0
/* user defined limits - change as needed */
#define MAX_ORDERBY 20
/* max number of order by fields (8000 max)*/
#define MAX_JOINTESTS 20
/* max number of join tests (999 max)*/
#define MAX_GROUPBY 20
/* max number of order by fields (120 max)*/
/* storage sizes - increase if needed */
#define QDT_SIZE 6000
#define FORMAT_SIZE 5000
#define SELECT_SIZE 5000
#define AP_SIZE 65535 /* Initialize access plan size to 64K */
/* Required definitions - do NOT change, hard limits */
#define MAX_FILES 32 /* Maximum number of files in a query */
#define REQ_REL "01" /* Required value for release field */
#define REQ_VER "00" /* Required value for version field */
#define QFLD_SIZE 30 /* QQ API field size - see qqqqry.h */
/* define error code structure */
typedef struct
{
int bytes_provided;
int bytes_available;
char msgid[(7]);
char reserved;
char data[(512]);
} error_code;
/* define attribute record for QUSCUSAT API */
typedef _Packed struct
{
int numAttrs;
int key;
int length;
_Packed union {
long spaceSize; /* key = 1 */
char initialValue; /* key = 2 */
char autoExtend; /* key = 3 */
} data;
} QUSCUSAT_T;
/* define access plan structure */
typedef _Packed struct
{
_SPCPTR storagePtr;
long size;
char reserved[(28]);
} ACCPLN_T;
/* Function prototypes: */
void dumpPtr(char *, char *, int );
char *strcnv400(char *, int );
int strcpy400(char *, char *, int );
void initUFCB(QDBUFCB_T *, int , Qdb_Qddfmt_t *);
void initQDT(QDBQH_T *, char , int , int ,
char , int );
void initFile(QDBQFHDR_T *, char , char );
void initFormat(Qdb_Qddfmt_t *, char *);
void initSelection(QDBQS_T *);
void initOrderBy(QDBQKH_T *);
void initGroupBy(QDBQGH_T *);
void initJoin(QDBQJHDR_T *);
int addFile(QDBQFHDR_T *, QDBQN_T *,
char *, char *, char *, char *);
int getRecordFmt(Qdb_Qddfmt_t *, long,
char *, char *, char *);
long copyField(Qdb_Qddfmt_t *, char *, int ,
Qdb_Qddfmt_t *);
void setFieldUsage(Qdb_Qddfmt_t *, char *, char );
int addSelectField(QDBQS_T *, char *, int );
int addSelectLiteral(QDBQS_T *, void *, int );
int addSelectOperator(QDBQS_T *, char *);
int addOrderBy(QDBQKH_T *, QDBQKF_T *,
char *, int );
int addGroupBy(QDBQGH_T *, QDBQGF_T *,
char *, int );
int addJoinTest(QDBQJHDR_T *, QDBQJFLD_T *, char *,
int , char *, int , char *);
void addQDTsection(QDBQH_T *, char *, int , int *);
long createAccessPlanSpace(ACCPLN_T *, char *, long );
int saveQDT(QDBQH_T *, ACCPLN_T *);
int saveAccessPlan(ACCPLN_T *);
int loadQDT(QDBQH_T *, ACCPLN_T *);
long loadAccessPlan(ACCPLN_T *, char *);
#endif
/********************************************************************/QQFUNCS query code
/********************************************************************/
#include <stdio.h>
#include <string.h>
#include <qdbrtvfd.h>
#include <qqqqry.h>
#include <quscrtus.h>
#include <qusptrus.h>
#include <quscusat.h>
#include <qusrusat.h>
#include "qqapi.h"
/*******************************************************************/
/*******************************************************************/
/* */
/* FUNCTION: This module contains all of the functions */
/* used by the examples to build the API information. */
/* */
/* LANGUAGE: ILE C */
/* */
/* APIs USED: QDBRTVFD, QUSCRTUS, QUSCUSAT, QUSPTRUS, QUSRUSAT */
/* */
/*******************************************************************/
/*******************************************************************/
#ifdef QQDEBUG
/* dumpPtr(comment string, pointer, length)
- prints a comment then dumps data in hexadecimal starting at the
given pointer location for the specified length */
void dumpPtr(char *text, char *ptr, int len)
{
int i;
printf("%s\n", text);
for (i=0; i < len; i++, ptr++)
{
printf("%02X ", (int) *ptr);
if ((i+1) % 16 == 0)
printf("\n");
}
printf("\n");
}
#endif
/* strcnv400(source string, string length)
- convert a string to a zero terminated string */
char *strcnv400(char *str, int len)
{
static char buffer[256];
strncpy(buffer, str, len);
buffer[len] = (char) 0;
return(buffer);
}
/* strcpy400(destination string, source string, source length)
- copy a zero terminated string to a string, pad with blanks
if necessary */
int strcpy400(char *dest, char *src, int len)
{
int i;
if ((i = strlen(src)) > len)
len = i;
if (len)
memcpy(dest, src, strlen(src));
if (i < len)
memset((dest+i), ' ', len-i);
return(len);
}
/* initUFCB(ufcb, open options, record format)
- initialize the UFCB structure */
void initUFCB(QDBUFCB_T *ufcbPtr, int openFlags,
Qdb_Qddfmt_t *formatPtr)
{
_Packed struct qufcb *ufcb;
/* verify parameters */
if (ufcbPtr == NULL || openFlags == 0)
{
printf("Invalid UFCB settings\n");
return;
}
/* Clear the entire UFCB */
memset((void *) ufcbPtr, (char) 0, sizeof(QDBUFCB_T));
/* Now start initializing values */
ufcb = &ufcbPtr->qufcb;
strcpy400((char *) ufcb->relver.release, REQ_REL,
sizeof(ufcb->relver.release));
strcpy400((char *) ufcb->relver.version, REQ_VER,
sizeof(ufcb->relver.version));
/* Blocked Records (BLKRCD) should be on if CPYFRMQRYF is used */
ufcb->markcnt.flg2brcd = ON;
ufcb->parameter.maximum = MAXFORMATS;
/* Set the open option */
if (openFlags&QO_INPUT)
ufcb->open.flagui = ON;
if (openFlags&QO_OUTPUT)
ufcb->open.flaguo = ON;
if (openFlags&QO_UPDATE)
ufcb->open.flaguu = ON;
if (openFlags&QO_DELETE)
ufcb->open.flagud = ON;
/* set up options to match _Ropen options */
ufcb->parameter.keyfdbk = KEYFDBK;
ufcb->parameter.keyonoff = ON; /* Key feedback ON */
ufcb->parameter.filedep = FILEDEP;
ufcb->parameter.fldonoff = ON; /* File dependent I/O ON */
/* turn the rest of the parameters off */
ufcb->parameter.seqonly = NOTSEQUPROC;
ufcb->parameter.primrln1 = NOTRECORDLTH;
ufcb->parameter.commitc = NOTCOMITCTL;
/* if the format is supplied,
define it in the UFCB and do level checking */
if (formatPtr != NULL)
{
ufcb->parameter.lvlchk = LEVELCK;
ufcb->parameter.lvlonoff = ON; /* Level check ON */
ufcb->parameter.curnum = 1; /* only one format */
/* set the format name and format level identifier */
ufcb->parameter.recfmts = FORMATSEQ;
memcpy(ufcb->parameter.formats[0].name, formatPtr->Qddfname,
sizeof(ufcb->parameter.formats[0].name));
memcpy(ufcb->parameter.formats[0].number, formatPtr->Qddfseq,
sizeof(ufcb->parameter.formats[0].number));
}
else /* no format and level checking */
{
ufcb->parameter.lvlchk = NOTLEVELCK;
ufcb->parameter.recfmts = NOTFORMATSEQ;
}
ufcb->ufcbend = ENDLIST;
}
/* initQDT(qdt, options...)
- initialize the QDT header */
void initQDT(QDBQH_T *qdtHdr, char alwCpyDta,
int optAllAp, int statusMsgs,
char optimize, int forRows)
{
if (qdtHdr == NULL)
{
printf("Invalid QDT settings\n");
return; /* invalid pointer */
}
/* Clear the entire QDT */
memset((void *) qdtHdr, (char) 0, sizeof(QDBQH_T));
/* set the initial QDT space used size */
qdtHdr->qdbspcsize = sizeof(QDBQH_T);
/* QDT options... */
/* ordering not specified */
qdtHdr->qdbqkeyo = -1;
/* set optimize parameter (ALLIO, FIRSTIO, MINWAIT) */
if (optimize == QDBQFINA || optimize == QDBQFINF ||
optimize == QDBQFINM || optimize == QDBQFINC)
qdtHdr->qdbqfin = optimize; /* OPTIMIZE() parameter */
else
qdtHdr->qdbqfin = QDBQFINA; /* default to OPTIMIZE(*ALLIO) */
/* set allow copy data parameter (YES, NO, OPTIMIZE) */
if (alwCpyDta == QDBQTEMN || alwCpyDta == QDBQTEMO ||
alwCpyDta == QDBQTEMA)
qdtHdr->qdbqtem = alwCpyDta; /* ALWCPYDTA() parameter */
else
qdtHdr->qdbqtem = QDBQTEMA; /* default to ALWCPYDTA(*YES) */
/* status messages (YES, NO) */
qdtHdr->qdbqattr.qdbqnst = statusMsgs ? ON : OFF;
/* optimize all access path parameter (YES, NO) */
qdtHdr->qdbqdt_7.qdbqopta = optAllAp ? ON : OFF;
/* optimizer for n rows parameter */
qdtHdr->qdbq_optmrows = forRows > 0 ? forRows : 0;
}
/* initFile(file section, join type, join order option)
- initialize the file header section */
void initFile(QDBQFHDR_T *fileHdr, char joinType, char joinOrder)
{
if (fileHdr == NULL)
{
printf("Invalid File Header settings\n");
return; /* invalid pointer */
}
/* Clear the header */
memset((void *) fileHdr, (char) 0, sizeof(QDBQFHDR_T));
/* File Spec options... */
/* inner, partial outer or exception join type */
if (joinType == QDBQINNJ || joinType == QDBQOUTJ ||
joinType == QDBQEXCJ)
fileHdr->qdbqmfop = joinType;
else
fileHdr->qdbqmfop = QDBQINNJ;
/* join order - any order or join as specified */
fileHdr->qdbqmfor = joinOrder == QDBQMFON ? QDBQMFON : QDBQMFOA;
}
/* initFormat(format section, format name)
- initialize the format header section */
void initFormat(Qdb_Qddfmt_t *formatHdr, char *name)
{
if (formatHdr == NULL)
{
printf("Invalid Format Header settings\n");
return; /* invalid pointer */
}
/* Clear the header */
memset((void *) formatHdr, (char) 0, sizeof(Qdb_Qddfmt_t));
/* Format Spec options... */
strcpy400(formatHdr->Qddfname, name, sizeof(formatHdr->Qddfname));
formatHdr->Qddfrcid = 65535;
formatHdr->Qddfsrcd = 65535;
formatHdr->Qddflgs.Qddfrsid = 1;
memset(formatHdr->Qddfseq, ' ', sizeof(formatHdr->Qddfseq));
memset(formatHdr->Qddftext, ' ', sizeof(formatHdr->Qddftext));
/* Format size (so far) */
formatHdr->Qddbyava = sizeof(Qdb_Qddfmt_t);
formatHdr->Qddbyrtn = formatHdr->Qddbyava;
}
/* initSelection(selection section)
- initialize the selection header section */
void initSelection(QDBQS_T *selectHdr)
{
if (selectHdr == NULL)
{
printf("Invalid selection settings\n");
return; /* invalid pointer */
}
/* Clear the header */
memset((void *) selectHdr, (char) 0, sizeof(QDBQS_T));
/* set initial selection spec size (minus dummy selection spec) */
selectHdr->qdbqsl = sizeof(QDBQS_T) - sizeof(selectHdr->qdbqspec);
}
/* initOrderBy(orderby section)
- initialize order by header section */
void initOrderBy(QDBQKH_T *orderByHdr)
{
if (orderByHdr == NULL)
{
printf("Invalid Order By settings\n");
return; /* invalid pointer */
}
/* Clear the header */
memset((void *) orderByHdr, (char) 0, sizeof(QDBQKH_T));
}
/* initGroupBy(groupby section)
- initialize group by header section */
void initGroupBy(QDBQGH_T *groupByHdr)
{
if (groupByHdr == NULL)
{
printf("Invalid Group By settings\n");
return; /* invalid pointer */
}
/* Clear the header */
memset((void *) groupByHdr, (char) 0, sizeof(QDBQGH_T));
}
/* initJoin(join section)
- initialize join header section */
void initJoin(QDBQJHDR_T *joinHdr)
{
if (joinHdr == NULL)
{
printf("Invalid Join settings\n");
return; /* invalid pointer */
}
/* Clear the header */
memset((void *) joinHdr, (char) 0, sizeof(QDBQKH_T));
/* set initial join spec size */
joinHdr->qdbqjln = sizeof(QDBQJHDR_T);
}
/* addFile (file section, file spec section, file name, file library,
file member, file format)
- add file information to the file section */
int addFile(QDBQFHDR_T *fileHdr, QDBQN_T *fileSpec,
char *filename, char *library, char *member, char *format)
{
int i;
QDBQFLMF_T *fileSpecPtr;
if (fileHdr == NULL || fileSpec == NULL || filename == NULL)
return(0); /* invalid data */
if (fileHdr->qdbqfilnum == MAX_FILES)
return(0); /* no more files allowed */
/* increment the count of file specs */
i = fileHdr->qdbqfilnum++;
/* initialize the file spec area */
memset((void *) &fileSpec[i], (char) 0, sizeof(QDBQN_T));
fileSpecPtr = (QDBQFLMF_T *) &fileSpec[i].qdbqflmf;
/* fill in the data... */
strcpy400(fileSpecPtr->qdbqfile, filename,
sizeof(fileSpecPtr->qdbqfile));
if (library == NULL)
strcpy400(fileSpecPtr->qdbqlib, QDBQLIBL,
sizeof(fileSpecPtr->qdbqlib));
else
strcpy400(fileSpecPtr->qdbqlib, library,
sizeof(fileSpecPtr->qdbqlib));
if (member == NULL)
strcpy400(fileSpecPtr->qdbqmbr, QDBQFRST,
sizeof(fileSpecPtr->qdbqmbr));
else
strcpy400(fileSpecPtr->qdbqmbr, member,
sizeof(fileSpecPtr->qdbqmbr));
if (format == NULL)
strcpy400(fileSpecPtr->qdbqfmt, QDBQONLY,
sizeof(fileSpecPtr->qdbqfmt));
else
strcpy400(fileSpecPtr->qdbqfmt, format,
sizeof(fileSpecPtr->qdbqfmt));
/* return the amount of storage used in the file specs */
return(fileHdr->qdbqfilnum*sizeof(QDBQN_T));
}
/* getRecordFmt(format, format storage size(max),
file name, file library, file format)
- get a record format (using QDBRTVFD) */
int getRecordFmt(Qdb_Qddfmt_t *formatPtr, long spaceSize,
char *filename, char *libname, char *formatname)
{
error_code errcod;
char override = '1'; /* process overrides */
char fileLibname[20];
char outFilLib[20];
char format[10];
if (formatPtr == NULL || filename == NULL)
return(0); /* missing data */
errcod.bytes_provided = 512;
errcod.msgid[0] = (char) 0;
/* set up temporary variables... */
strcpy400(fileLibname, filename, 10);
if (libname == NULL)
strcpy400(&fileLibname[10], QDBQLIBL, 10);
else
strcpy400(&fileLibname[10], libname, 10);
if (formatname == NULL)
strcpy400(format, filename, 10);
else
strcpy400(format, formatname, 10);
/* call the RTVFD API to get the record format */
QDBRTVFD((char *) formatPtr, spaceSize, outFilLib,
"FILD0200",
fileLibname, format, &override,
"*LCL ", "*EXT ", &errcod);
if (errcod.msgid[0])
{
printf("API QDBRTVFD failed\n");
printf("msgid = %7s\n", strcnv400(errcod.msgid,
sizeof(errcod.msgid)));
}
if (formatPtr->Qddbyrtn != formatPtr->Qddbyava)
return(0); /* missing data */
/* return total storage used in format */
return(formatPtr->Qddbyrtn);
}
/* copyField(format, field name, file number, existing format)
- copy a field from an existing format */
long copyField(Qdb_Qddfmt_t *formatPtr, char *fieldName, int fieldFile,
Qdb_Qddfmt_t *oldFormatPtr)
{
int i;
long fieldSize;
char padField[30];
Qdb_Qddffld_t *fieldPtr, *oldFieldPtr;
if (formatPtr == NULL || fieldName == NULL || oldFormatPtr == NULL)
return(0); /* missing data */
strcpy400(padField, fieldName, 30);
/* set up field pointers */
fieldPtr = (Qdb_Qddffld_t *) ((char *) formatPtr +
formatPtr->Qddbyava);
oldFieldPtr = (Qdb_Qddffld_t *) (oldFormatPtr + 1);
/* loop through all the fields, looking for a match */
for (i=0; i < oldFormatPtr->Qddffldnum; i++,
oldFieldPtr = (Qdb_Qddffld_t *) ((char *) oldFieldPtr +
oldFieldPtr->Qddfdefl))
/* if a match was found... */
if (memcmp(oldFieldPtr->Qddfflde, padField, 30) == 0)
{
/* copy the field over */
fieldSize = oldFieldPtr->Qddfdefl;
memcpy(fieldPtr, oldFieldPtr, fieldSize);
/* set the file number it was defined in */
fieldPtr->Qddfjref = fieldFile;
/* increment the format header information */
formatPtr->Qddffldnum++;
formatPtr->Qddfrlen += fieldPtr->Qddffldb;
formatPtr->Qddbyava += fieldSize;
formatPtr->Qddbyrtn = formatPtr->Qddbyava;
break;
}
/* return total storage used in format */
return(formatPtr->Qddbyrtn);
}
/* setFieldUsage(format, field name, usage)
- set the field usage in a format */
void setFieldUsage(Qdb_Qddfmt_t *formatPtr, char *fieldName, char usage)
{
int i;
char padField[30];
Qdb_Qddffld_t *fieldPtr;
if (formatPtr == NULL)
return; /* missing data */
if (fieldName != NULL)
strcpy400(padField, fieldName, 30);
/* set up field pointers */
fieldPtr = (Qdb_Qddffld_t *) (formatPtr + 1);
/* loop through all the fields, looking for a match */
for (i=0; i < formatPtr->Qddffldnum; i++,
fieldPtr = (Qdb_Qddffld_t *) ((char *) fieldPtr +
fieldPtr->Qddfdefl))
/* if all fields to be set or a match was found... */
if (fieldName == NULL ||
memcmp(fieldPtr->Qddfflde, padField, 30) == 0)
fieldPtr->Qddffiob = usage;
}
/* addSelectField(section section, field name, file number for field)
- add a selection for a file field to the selection section */
int addSelectField(QDBQS_T *selectHdr, char *fieldName, int fieldFile)
{
QDBQSIT_T *selectItemPtr;
QDBQSOPF_T *selectFldPtr;
int itemSize;
if (selectHdr == NULL || fieldName == NULL)
return(0); /* invalid data */
/* set up all the section for adding a field */
selectItemPtr = (QDBQSIT_T *) ((char *) selectHdr +
selectHdr->qdbqsl);
itemSize = sizeof(QDBQSIT_T) - sizeof(selectItemPtr->qdbqsitm);
memset((void *) selectItemPtr, (char) 0, itemSize);
selectFldPtr = (QDBQSOPF_T *) ((char *) selectItemPtr + itemSize);
memset((void *) selectFldPtr, (char) 0, sizeof(QDBQSOPF_T));
/* set up the selection item information for a field */
selectItemPtr->qdbqslen = itemSize + sizeof(QDBQSOPF_T);
/* length */
selectItemPtr->qdbqsitt = QDBQOPF; /* type is field */
/* now set up the field */
strcpy400(selectFldPtr->qdbqsofn, fieldName,
sizeof(selectFldPtr->qdbqsofn));
selectFldPtr->qdbqsofj = fieldFile;
/* update the header statistics */
selectHdr->qdbqsnum++; /* increment number of select specs */
selectHdr->qdbqsl += selectItemPtr->qdbqslen; /* total length */
/* return the total storage now in the selection section */
return(selectHdr->qdbqsl);
}
/* addSelectLiteral(selection section, literal, size of literal data)
- add a selection for a literal to the selection section */
int addSelectLiteral(QDBQS_T *selectHdr, void *literal, int sizeLit)
{
QDBQSIT_T *selectItemPtr;
QDBQSOCH_T *selectLitPtr;
void *selectDataPtr;
int itemSize;
if (selectHdr == NULL || literal == NULL || sizeLit < 1)
return(0); /* invalid data */
/* set up all the sections for adding a literal */
selectItemPtr = (QDBQSIT_T *)
((char *) selectHdr + selectHdr->qdbqsl);
itemSize = sizeof(QDBQSIT_T) - sizeof(selectItemPtr->qdbqsitm);
memset((void *) selectItemPtr, (char) 0, itemSize);
selectLitPtr = (QDBQSOCH_T *) ((char *) selectItemPtr + itemSize);
memset((void *) selectLitPtr, (char) 0, sizeof(QDBQSOCH_T));
selectDataPtr = (void *) (selectLitPtr + 1);
/* set up the selection item information for a literal */
selectItemPtr->qdbqslen = itemSize + sizeof(QDBQSOCH_T) + sizeLit;
selectItemPtr->qdbqsitt = QDBQOPC; /* literal type */
/* now set up the literal */
selectLitPtr->qdbqsocl = sizeLit; /* literal size */
selectLitPtr->qdbqsoft = '\xFF';
/* use job format for date/time fields */
memcpy(selectDataPtr, literal, sizeLit);
/* save the literal value */
/* update the header statistics */
selectHdr->qdbqsnum++; /* increment number of select specs */
selectHdr->qdbqsl += selectItemPtr->qdbqslen; /* total length */
/* return the total storage now in the selection section */
return(selectHdr->qdbqsl);
}
/* addSelectOperator(selection section, operator type)
- add a selection for an operator to the selection section */
int addSelectOperator(QDBQS_T *selectHdr, char *operator)
{
QDBQSIT_T *selectItemPtr;
QDBQSOPR_T *selectOprPtr;
QDBQSOP2_T *selectWldPtr;
int itemSize;
int oprSize;
if (selectHdr == NULL || operator == NULL)
return(0); /* invalid data */
/* set up all the sections for adding an operator */
selectItemPtr = (QDBQSIT_T *)
((char *) selectHdr + selectHdr->qdbqsl);
itemSize = sizeof(QDBQSIT_T) - sizeof(selectItemPtr->qdbqsitm);
memset((void *) selectItemPtr, (char) 0, itemSize);
selectOprPtr = (QDBQSOPR_T *) ((char *) selectItemPtr + itemSize);
oprSize = sizeof(QDBQSOPR_T) + sizeof(QDBQSOP2_T);
memset((void *) selectOprPtr, (char) 0, oprSize);
/* set up the selection item information for an operator */
selectItemPtr->qdbqslen = itemSize + oprSize; /* length */
selectItemPtr->qdbqsitt = QDBQOPTR; /* operator type */
/* now set up the operator */
memcpy(selectOprPtr->qdbqsop, operator,
sizeof(selectOprPtr->qdbqsop));
/* wildcard operator set up */
if (memcmp(operator, QDBQWILD, 2) == 0)
{
selectOprPtr->qdbqswc1 = '_';
selectOprPtr->qdbqswc2 = '*';
selectWldPtr = (QDBQSOP2_T *) (selectOprPtr + 1);
memcpy(selectWldPtr->qdbqsdb1,"\42_", 2);
memcpy(selectWldPtr->qdbqsdb2,"\42*", 2);
}
/* update the header statistics */
selectHdr->qdbqsnum++; /* increment number of select specs */
selectHdr->qdbqsl += selectItemPtr->qdbqslen; /* total length */
/* return the total storage now in the selection section */
return(selectHdr->qdbqsl);
}
/* addOrderBy(orderby section, orderby specs section, key field name,
descend sort option
- add an order by to the order by section */
int addOrderBy(QDBQKH_T *orderByHdr, QDBQKF_T *orderByFld,
char *keyfield, int descend)
{
int i;
QDBQKF_T *orderByFldPtr;
if (orderByHdr == NULL || orderByFld == NULL || keyfield == NULL)
return(0);
if (orderByHdr->qdbqknum == MAX_ORDERBY)
return(0);
/* increment the order by spec counter */
i = orderByHdr->qdbqknum++;
/* add the new orderby data */
orderByFldPtr = &orderByFld[i];
memset((void *) orderByFldPtr, (char) 0, sizeof(QDBQKF_T));
strcpy400(orderByFldPtr->qdbqkfld, keyfield,
sizeof(orderByFldPtr->qdbqkfld));
orderByFldPtr->qdbqksq.qdbqksad = (descend) ? ON : OFF;
/* return the space used by the order by specs */
return(orderByHdr->qdbqknum*sizeof(QDBQKF_T));
}
/* addGroupBy(groupby section, groupby field spec section,
groupby field name, file number of groupby field)
- add a group by to the group by section */
int addGroupBy(QDBQGH_T *groupByHdr, QDBQGF_T *groupByFld,
char *groupfield, int fromFile)
{
int i;
QDBQGF_T *groupByFldPtr;
if (groupByHdr == NULL || groupByFld == NULL || groupfield == NULL)
return(0);
if (groupByHdr->qdbqgfnum == MAX_GROUPBY)
return(0);
/* increment the group by spec counter */
i = groupByHdr->qdbqgfnum++;
/* add the new groupby data */
groupByFldPtr = (QDBQGF_T *) &groupByFld[i];
memset((void *) groupByFldPtr, (char) 0, sizeof(QDBQGF_T));
strcpy400(groupByFldPtr->qdbqgfld, groupfield,
sizeof(groupByFldPtr->qdbqgfld));
groupByFldPtr->qdbqgflj = fromFile;
/* return the space used by the group by specs */
return(groupByHdr->qdbqgfnum*sizeof(QDBQGF_T));
}
/* addJoinTest(join section, join test section, join from field name,
join from file number, join to field name, join to file number,
join operator)
- add a join test to the join section */
int addJoinTest(QDBQJHDR_T *joinHdr,
QDBQJFLD_T *joinSpec, char *fromFld,
int fromFile, char *toFld, int toFile, char *joinOp)
{
int i;
QDBQJFLD_T *joinSpecPtr;
if (joinHdr == NULL || joinSpec == NULL)
return(0);
if (joinHdr->qdbqjknum == MAX_JOINTESTS)
return(0);
/* increment the join test counter */
i = joinHdr->qdbqjknum++;
memset((void *) &joinSpec[i], (char) 0, sizeof(QDBQJFLD_T));
/* add the new join data */
joinSpecPtr = &joinSpec[i];
strcpy400(joinSpecPtr->qdbqjfnm, fromFld,
sizeof(joinSpecPtr->qdbqjfnm));
joinSpecPtr->qdbqjfnum = fromFile; /* 1, 2, 3, etc */
strcpy400(joinSpecPtr->qdbqjtnm, toFld,
sizeof(joinSpecPtr->qdbqjtnm));
joinSpecPtr->qdbqjtnum = toFile; /* 1, 2, 3, etc */
/* Join operator - see #defines in QQ API include */
strcpy400(joinSpecPtr->qdbqjop, joinOp,
sizeof(joinSpecPtr->qdbqjop));
/* set size of entire join spec */
joinHdr->qdbqjln += sizeof(QDBQJFLD_T);
/* return the space used by the join tests */
return(joinHdr->qdbqjknum*sizeof(QDBQJFLD_T));
}
/* addQDTsection(qdt, new section, size of new section, qdt offset)
- place a new section into the QDT */
void addQDTsection(QDBQH_T *qdtHdr, char *newSection,
int newSize, int *offset)
{
char *sectionPtr;
/* position to the current end of the QDT */
sectionPtr = (char *) qdtHdr + qdtHdr->qdbspcsize;
/* append in the new section data */
memcpy(sectionPtr, newSection, newSize);
/* if an offset is to be stored, remember it now */
if (offset != NULL)
*offset = qdtHdr->qdbspcsize;
/* update the QDT size */
qdtHdr->qdbspcsize += newSize;
}
/* createAccessPlanSpace(access plan, user space name, size)
- creates a *USRSPC object for storing the access plan */
long createAccessPlanSpace(ACCPLN_T *accessPlan, char *name,
long spaceSize)
{
QUSCUSAT_T chgAttr;
_SPCPTR usrSpcPtr;
char library[10];
char value = (char) 0;
char text[50];
error_code errcode;
errcode.bytes_provided = 512;
strcpy400(text,"Access Plan for QQ API example",50);
/* Create the User Space */
QUSCRTUS(name,
"ACCESSPLAN",
spaceSize,
&value,
"*ALL ",
text,
"*YES ",
&errcode,
"*USER ");
if (errcode.msgid[0])
{
printf("Create User Space API failed!\n");
printf("msgid = %7s\n", strcnv400(errcode.msgid,
sizeof(errcode.msgid)));
return(-1);
}
/* Change the User Space to allow Auto-Extend */
strcpy400(library,&name[10],10);
chgAttr.numAttrs = 1;
chgAttr.key = 3; /* Auto extend */
chgAttr.length = sizeof(char);
chgAttr.data.autoExtend = '1';
QUSCUSAT(library,
name,
&chgAttr,
&errcode);
if (errcode.msgid[0])
{
printf("Change User Space Attributes FAILED!\n");
printf("msgid = %7s\n", strcnv400(errcode.msgid,
sizeof(errcode.msgid)));
return(-1);
}
/* Retrieve Space Pointer to the User Space */
QUSPTRUS(name,
&usrSpcPtr,
&errcode);
if (errcode.msgid[0])
{
printf("Retrieve Space Pointer to User Space FAILED!\n");
printf("msgid = %7s\n", strcnv400(errcode.msgid,
sizeof(errcode.msgid)));
return(-1);
}
/* Now move to the access plan itself (on 16 byte boundary) */
accessPlan->storagePtr = (_SPCPTR) ((char*) usrSpcPtr + 16);
return(0);
}
/* saveAccessPlan(access plan)
- update the size in the access plan (QQQQRY actually wrote the data) */
int saveAccessPlan(ACCPLN_T *accessPlan)
{
_SPCPTR usrSpcPtr;
/* Position to the start of the user space */
usrSpcPtr = (_SPCPTR) ((char*) accessPlan->storagePtr - 16);
/* Write the access plan size out at the start */
memcpy(usrSpcPtr, (void *) &accessPlan->size,
sizeof(accessPlan->size));
#ifdef QQDEBUG
printf("AP size = %ld\n", accessPlan->size);
#endif
return(0);
}
/* saveQDT(qdt, access plan)
- append the QDT to the end of the access plan */
int saveQDT(QDBQH_T *qdtPtr, ACCPLN_T *accessPlan)
{
_SPCPTR usrSpcPtr;
/* Position to the just after the access plan */
usrSpcPtr = (_SPCPTR) ((char*) accessPlan->storagePtr +
accessPlan->size);
/* Write the QDT size out */
memcpy(usrSpcPtr, &qdtPtr->qdbspcsize, sizeof(qdtPtr->qdbspcsize));
#ifdef QQDEBUG
printf("qdt size = %ld\n", qdtPtr->qdbspcsize);
#endif
/* Move up the user space pointer */
usrSpcPtr = (_SPCPTR) ((char *) usrSpcPtr + 16);
/* Write the QDT itself out */
memcpy(usrSpcPtr, qdtPtr, qdtPtr->qdbspcsize);
return(0);
}
/* loadQDT(qdt, access plan)
- load the QDT from the end of the access plan */
int loadQDT(QDBQH_T *qdtPtr, ACCPLN_T *accessPlan)
{
_SPCPTR usrSpcPtr;
/* Position to the just after the access plan */
usrSpcPtr = (_SPCPTR) ((char*) accessPlan->storagePtr +
accessPlan->size);
/* Write the QDT size out */
memcpy((void *) &qdtPtr->qdbspcsize, usrSpcPtr,
sizeof(qdtPtr->qdbspcsize));
#ifdef QQDEBUG
printf("qdt size = %ld\n", qdtPtr->qdbspcsize);
#endif
/* Move up the user space pointer */
usrSpcPtr = (_SPCPTR) ((char *) usrSpcPtr + 16);
/* Write the QDT itself out */
memcpy((void *) qdtPtr, usrSpcPtr, qdtPtr->qdbspcsize);
return(qdtPtr->qdbspcsize);
}
/* loadAccessPlan(access plan, userspace name)
- loads an access plan from a *USRSPC object */
long loadAccessPlan(ACCPLN_T *accessPlan, char *name)
{
Qus_SPCA_0100_t usrSpcAttr;
_SPCPTR usrSpcPtr;
error_code errcode;
errcode.bytes_provided = 512;
errcode.msgid[0] = (char) 0;
/* Retrieve Space Pointer to the User Space */
QUSPTRUS(name, &usrSpcPtr, &errcode);
if (errcode.msgid[0])
{
printf("Retrieve Space Pointer to User Space FAILED!\n");
printf("msgid = %7s\n", strcnv400(errcode.msgid,
sizeof(errcode.msgid)));
return(0);
}
/* Retrieve Size of Access Plan */
QUSRUSAT(&usrSpcAttr,
sizeof(Qus_SPCA_0100_t),
"SPCA0100",
name,
&errcode);
if (errcode.msgid[0])
{
printf("Retrieve User Space Attributes FAILED!\n");
printf("msgid = %7s\n", strcnv400(errcode.msgid,
sizeof(errcode.msgid)));
return(0);
}
#ifdef QQDEBUG
else
{
printf("Original User Space Attributes\n");
printf("Bytes Returned ==> %d\n",usrSpcAttr.Bytes_Returned);
printf("Bytes Available ==> %d\n",usrSpcAttr.Bytes_Available);
printf("Space Size ==> %d\n",usrSpcAttr.Space_Size);
printf("Auto Extend ==> %c\n",
usrSpcAttr.Automatic_Extendability);
}
#endif
/* Pull the access plan size out first */
memcpy((void *) &accessPlan->size, usrSpcPtr,
sizeof(accessPlan->size));
#ifdef QQDEBUG
printf("AP size = %ld\n", accessPlan->size);
#endif
/* Now move to the access plan itself (on 16 byte boundary) */
accessPlan->storagePtr = (_SPCPTR) ((char*) usrSpcPtr + 16);
return(accessPlan->size);
}
/********************************************************************/Defining a simple query
This simple query is equivalent to an SQL query:
SELECT * FROM OPENFILE1
ORDER BY LNAME/********************************************************************/
/* PROGRAM: QQAPI1 */
/* */
/* LANGUAGE: ILE C */
/* */
/* DESCRIPTION: THIS PROGRAM DEFINES A SIMPLE QUERY TO PERFORM */
/* ORDERING. */
/* */
/* APIs USED: QQQQRY */
/* */
/********************************************************************/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <recio.h>
#include <qdbrtvfd.h>
#include <qqqqry.h>
#include "qqapi.h"
/* get the record format from the file */
#pragma mapinc("recfmt","APIQQ/OPENFILE1(OPENFILE1)","input","p z",,)
#include "recfmt"
/* main - start of the program
*
* Flow:
* - initialize variables
* - override to set up sharing
* - build various QDT sections
* - build QDT with those sections
* - QQQQRY to run the query
* - open the data path
* - read the data and display it
* - close the data paths
*
*/
main()
{
/* record I/O variables */
_RIOFB_T *feedback;
_RFILE *file1;
APIQQ_OPENFILE1_OPENFILE1_i_t recBuf;
int recCount = 0;
/* Query variables */
QDBUFCB_T ufcbBuf;
char qdtBuf[QDT_SIZE];
char formatBuf[FORMAT_SIZE];
QDBQH_T *qdtPtr;
Qdb_Qddfmt_t *formatPtr;
QDBQFHDR_T fileHdr;
QDBQN_T fileSpec[MAX_FILES];
QDBQKH_T orderByHdr;
QDBQKF_T orderByFld[MAX_ORDERBY];
int formatSize;
int fileSpecSize;
int orderBySize;
error_code errcod;
errcod.bytes_provided = 512;
/* initialize the pointers */
qdtPtr = (QDBQH_T *) qdtBuf;
formatPtr = (Qdb_Qddfmt_t *) formatBuf;
/* initialize the headers */
initQDT(qdtPtr, QDBQTEMO, ON, ON, QDBQFINA, 0);
initFile(&fileHdr, QDBQINNJ, QDBQMFOA);
initOrderBy(&orderByHdr);
/* set up override to allow sharing */
system("OVRDBF FILE(OPENFILE1) SHARE(*YES)");
/* Note: If level checking is not done
(ie. no format on initUFCB) then
the override above must specify LVLCHK(*NO) */
/* build the individual QDT sections */
fileSpecSize = addFile(&fileHdr, fileSpec, "OPENFILE1",
NULL, NULL, NULL);
formatSize = getRecordFmt(formatPtr, FORMAT_SIZE, "OPENFILE1",
NULL, NULL);
orderBySize = addOrderBy(&orderByHdr, orderByFld, "LNAME", OFF);
/* initialize the UFCB */
initUFCB(&ufcbBuf, QO_INPUT, formatPtr);
/* Now build the real QDT... */
addQDTsection(qdtPtr, (char *) &fileHdr,
sizeof(fileHdr), &qdtPtr->qdbqfilo);
addQDTsection(qdtPtr, (char *) fileSpec, fileSpecSize, NULL);
addQDTsection(qdtPtr, (char *) formatPtr,
formatSize, &qdtPtr->qdbqfldo);
addQDTsection(qdtPtr, (char *) &orderByHdr, sizeof(orderByHdr),
&qdtPtr->qdbqkeyo);
addQDTsection(qdtPtr, (char *) orderByFld, orderBySize, NULL);
/* Finally, run the query! */
QQQQRY("RUNQRY ", (char *) &ufcbBuf, qdtBuf, NULL, NULL,
&errcod);
if (errcod.msgid[0])
{
printf("API QQQQRY failed\n");
printf("msgid = %7s\n", strcnv400(errcod.msgid,
sizeof(errcod.msgid)));
}
/* Now access the data */
if ((file1 = _Ropen("OPENFILE1", "rr riofb=N")) == NULL)
{
printf("Error opening file\n");
exit(1);
}
/* Perform any record I/O here... */
_Rformat(file1, "OPENFILE1 ");
printf("First name Last name State\n");
feedback = _Rreadn(file1, (void *) &recBuf, sizeof(recBuf), __DFT);
while (feedback->num_bytes == sizeof(recBuf))
{
recCount++;
printf("%s ", strcnv400(recBuf.FNAME, sizeof(recBuf.FNAME)));
printf("%s ", strcnv400(recBuf.LNAME, sizeof(recBuf.LNAME)));
printf("%s\n", strcnv400(recBuf.STATE, sizeof(recBuf.STATE)));
feedback = _Rreadn(file1, (void *) &recBuf,
sizeof(recBuf), __DFT);
}
printf("%d records selected\n", recCount);
/* Close the file */
_Rclose(file1);
/* close out the QDT file handle */
system("RCLRSC");
}Defining a join query
This join query is equivalent to an SQL query:
SELECT * FROM OPENFILE1 A, OPENFILE2 B
WHERE STATE = 'AK' AND
A.ACCTNUM = B.CUSTNUM/********************************************************************/
/* PROGRAM: QQAPI7 */
/* */
/* LANGUAGE: ILE C */
/* */
/* DESCRIPTION: THIS PROGRAM DEFINES A JOIN QUERY. */
/* */
/* APIs USED: QQQQRY */
/* */
/********************************************************************/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <recio.h>
#include <qdbrtvfd.h>
#include <qqqqry.h>
#include "qqapi.h"
/* get the record format from the file */
#pragma mapinc("recfmt","APIQQ/FORMAT1(FORMAT1)","input","p z",,)
#include "recfmt"
/* main - start of the program
*
* Flow:
* - initialize variables
* - override to set up sharing
* - build various QDT sections
* - build QDT with those sections
* - QQQQRY to run the query
* - open the data path
* - read the data and display it
* - close the data paths
*
*/
main()
{
/* record I/O variables */
_RIOFB_T *feedback;
_RFILE *file1;
APIQQ_FORMAT1_FORMAT1_i_t recBuf;
int recCount = 0;
/* Query variables */
QDBUFCB_T ufcbBuf;
char qdtBuf[QDT_SIZE];
char formatBuf[FORMAT_SIZE];
char selectBuf[SELECT_SIZE];
QDBQH_T *qdtPtr;
Qdb_Qddfmt_t *formatPtr;
QDBQS_T *selectPtr;
QDBQFHDR_T fileHdr;
QDBQN_T fileSpec[MAX_FILES];
QDBQJHDR_T joinHdr;
QDBQJFLD_T joinSpec[MAX_JOINTESTS];
int formatSize;
int fileSpecSize;
int selectSize;
int joinSize;
error_code errcod;
errcod.bytes_provided = 512;
/* initialize the pointers */
qdtPtr = (QDBQH_T *) qdtBuf;
formatPtr = (Qdb_Qddfmt_t *) formatBuf;
selectPtr = (QDBQS_T *) selectBuf;
/* initialize the headers */
initQDT(qdtPtr, QDBQTEMO, ON, ON, QDBQFINA, 0);
initFile(&fileHdr, QDBQINNJ, QDBQMFOA);
initSelection(selectPtr);
initJoin(&joinHdr);
/* set up override to allow sharing */
system("OVRDBF FILE(OPENFILE1) SHARE(*YES) LVLCHK(*NO)");
/* Note: If level checking is not done
(ie. no format on initUFCB) then
the override above must specify LVLCHK(*NO) */
/* build the individual QDT sections */
addFile(&fileHdr, fileSpec, "OPENFILE1", NULL, NULL, NULL);
fileSpecSize = addFile(&fileHdr, fileSpec, "OPENFILE2",
NULL, NULL, NULL);
formatSize = getRecordFmt(formatPtr, FORMAT_SIZE, "FORMAT1",
NULL, NULL);
joinSize = addJoinTest(&joinHdr, joinSpec, "ACCTNUM", 1,
"CUSTNUM" , 2, "EQ");
/* build selection test: STATE = 'AK' */
addSelectField(selectPtr, "STATE", 1);
addSelectLiteral(selectPtr, "'AK'", 4);
selectSize = addSelectOperator(selectPtr, QDBQEQ);
/* initialize the UFCB */
initUFCB(&ufcbBuf, QO_INPUT, NULL);
/* Now build the real QDT... */
addQDTsection(qdtPtr, (char *) &fileHdr,
sizeof(fileHdr), &qdtPtr->qdbqfilo);
addQDTsection(qdtPtr, (char *) fileSpec, fileSpecSize, NULL);
addQDTsection(qdtPtr, (char *) formatPtr,
formatSize, &qdtPtr->qdbqfldo);
addQDTsection(qdtPtr, (char *) &joinHdr,
sizeof(joinHdr),&qdtPtr->qdbqjoio);
addQDTsection(qdtPtr, (char *) joinSpec, joinSize, NULL);
addQDTsection(qdtPtr, (char *) selectPtr,
selectSize, &qdtPtr->qdbqselo);
/* Finally, run the query! */
QQQQRY("RUNQRY ", (char *) &ufcbBuf, qdtBuf, NULL, NULL,
&errcod);
if (errcod.msgid[0])
{
printf("API QQQQRY failed\n");
printf("msgid = %7s\n", strcnv400(errcod.msgid,
sizeof(errcod.msgid)));
}
/* Now access the data */
if ((file1 = _Ropen("OPENFILE1", "rr riofb=N")) == NULL)
{
printf("Error opening file\n");
exit(1);
}
/* Perform any record I/O here... */
_Rformat(file1, "FORMAT1");
printf("Last name Item name\n");
feedback = _Rreadn(file1, (void *) &recBuf, sizeof(recBuf), __DFT);
while (feedback->num_bytes == sizeof(recBuf))
{
recCount++;
printf("%s ", strcnv400(recBuf.LNAME, sizeof(recBuf.LNAME)));
printf("%s\n", strcnv400(recBuf.ITEMNAME,
sizeof(recBuf.ITEMNAME)));
feedback = _Rreadn(file1, (void *) &recBuf,
sizeof(recBuf), __DFT);
}
printf("%d records selected\n", recCount);
/* Close the file */
_Rclose(file1);
/* close out the QDT file handle */
system("RCLRSC");
}Defining a join query with selection, grouping, and ordering
This join query with selection, grouping, and ordering is equivalent to an SQL query:
SELECT LNAME, FNAME, ITEMCODE, ITEMNAME, STATUS
FROM OPENFILE1, OPENFILE2
WHERE STATE = 'AK' AND CUSTNUM = ACCTNUM
GROUP BY LNAME, FNAME, ITEMCODE, ITEMNAME, STATUS
ORDER BY ITEMNAME/********************************************************************/
/* PROGRAM: QQAPI11 */
/* */
/* LANGUAGE: ILE C */
/* */
/* DESCRIPTION: THIS PROGRAM DEFINES A JOIN QUERY WITH SELECTION */
/* GROUPING AND ORDERING. */
/* */
/* APIs USED: QQQQRY */
/* */
/********************************************************************/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <qdbrtvfd.h>
#include <qqqqry.h>
#include "qqapi.h">
/* main - start of the program
*
* Flow:
* - initialize variables
* - override to set up sharing
* - build various QDT sections
* - build QDT with those sections
* - QQQQRY to run the query
* - open the data path
* - read the data and display it
* - close the data paths
*
*/
main()
{
/* file I/O variables */
#define REC_SIZE 52
FILE *file1;
char recBuf[REC_SIZE];
int recCount = 0, found;
/* Query variables */
QDBUFCB_T ufcbBuf;
char qdtBuf[QDT_SIZE];
char formatBuf[FORMAT_SIZE];
char tempFormatBuf[FORMAT_SIZE];
char selectBuf[SELECT_SIZE];
QDBQH_T *qdtPtr;
Qdb_Qddfmt_t *formatPtr;
Qdb_Qddfmt_t *tempFormatPtr;
QDBQS_T *selectPtr;
QDBQFHDR_T fileHdr;
QDBQN_T fileSpec[MAX_FILES];
QDBQJHDR_T joinHdr;
QDBQJFLD_T joinSpec[MAX_JOINTESTS];
QDBQKH_T orderByHdr;
QDBQGH_T groupByHdr;
QDBQKF_T orderByFld[MAX_ORDERBY];
QDBQGF_T groupByFld[MAX_GROUPBY];
int formatSize;
int fileSpecSize;
int orderBySize;
int groupBySize;
int selectSize;
int joinSize;
error_code errcod;
memset( (void *) &errcod, (char) 0, sizeof(error_code) );
errcod.bytes_provided = 512;
/* initialize the pointers */
qdtPtr = (QDBQH_T *) qdtBuf;
formatPtr = (Qdb_Qddfmt_t *) formatBuf;
tempFormatPtr = (Qdb_Qddfmt_t *) tempFormatBuf;
selectPtr = (QDBQS_T *) selectBuf;
/* initialize the headers */
initQDT(qdtPtr, QDBQTEMO, ON, ON, QDBQFINA, 0);
initFile(&fileHdr, QDBQINNJ, QDBQMFOA);
initFormat(formatPtr, "JOINFMT01");
initOrderBy(&orderByHdr);
initGroupBy(&groupByHdr);
initSelection(selectPtr);
initJoin(&joinHdr);
/* set up override to allow sharing */
system("OVRDBF FILE(OPENFILE1) SHARE(*YES) LVLCHK(*NO)");
/* Note: If level checking is not done
(ie. no format on initUFCB) then
the override above must specify LVLCHK(*NO) */
/* build the individual QDT sections */
addFile(&fileHdr, fileSpec, "OPENFILE1", NULL, NULL, NULL);
fileSpecSize = addFile(&fileHdr, fileSpec, "OPENFILE2",
NULL, NULL, NULL);
/* get the first format and copy some fields */
getRecordFmt(tempFormatPtr, FORMAT_SIZE, "OPENFILE1",
NULL, NULL);
copyField(formatPtr, "LNAME", 1, tempFormatPtr);
copyField(formatPtr, "FNAME", 1, tempFormatPtr);
/* clear the old format data */
memset(tempFormatPtr, 0, FORMAT_SIZE);
/* get the second format and copy some more fields */
getRecordFmt(tempFormatPtr, FORMAT_SIZE, "OPENFILE2", NULL, NULL);
copyField(formatPtr, "ITEMCODE", 2, tempFormatPtr);
copyField(formatPtr, "ITEMNAME", 2, tempFormatPtr);
formatSize = copyField(formatPtr, "STATUS", 2, tempFormatPtr);
/* set all the fields to input only */
setFieldUsage(formatPtr, NULL, 1);
/* build selection test: STATE = 'AK' */
addSelectField(selectPtr, "STATE", 1);
addSelectLiteral(selectPtr, "'AK'", 4);
selectSize = addSelectOperator(selectPtr, QDBQEQ);
joinSize = addJoinTest(&joinHdr, joinSpec, "ACCTNUM", 1,
"CUSTNUM" , 2, "EQ");
orderBySize = addOrderBy(&orderByHdr, orderByFld,
"ITEMNAME", OFF);
addGroupBy(&groupByHdr, groupByFld, "LNAME", 0);
addGroupBy(&groupByHdr, groupByFld, "FNAME", 0);
addGroupBy(&groupByHdr, groupByFld, "ITEMCODE", 0);
addGroupBy(&groupByHdr, groupByFld, "ITEMNAME", 0);
groupBySize = addGroupBy(&groupByHdr, groupByFld, "STATUS", 0);
/* initialize the UFCB */
initUFCB(&ufcbBuf, QO_INPUT, NULL);
/* set up for sequential only processing since it is a group by */
ufcbBuf.qufcb.parameter.seqonly = SEQUPROC;
ufcbBuf.qufcb.parameter.seqonoff = ON;
ufcbBuf.qufcb.parameter.numonoff = ON;
ufcbBuf.qufcb.parameter.numrecs = 1;
/* Now build the real QDT... */
addQDTsection(qdtPtr, (char *) &fileHdr,
sizeof(fileHdr), &qdtPtr->qdbqfilo);
addQDTsection(qdtPtr, (char *) fileSpec, fileSpecSize, NULL);
addQDTsection(qdtPtr, (char *) formatPtr,
formatSize, &qdtPtr->qdbqfldo);
addQDTsection(qdtPtr, (char *) &joinHdr,
sizeof(joinHdr),&qdtPtr->qdbqjoio);
addQDTsection(qdtPtr, (char *) joinSpec, joinSize, NULL);
addQDTsection(qdtPtr, (char *) selectPtr,
selectSize, &qdtPtr->qdbqselo);
addQDTsection(qdtPtr, (char *) &orderByHdr, sizeof(orderByHdr),
&qdtPtr->qdbqkeyo);
addQDTsection(qdtPtr, (char *) orderByFld, orderBySize, NULL);
addQDTsection(qdtPtr, (char *) &groupByHdr, sizeof(groupByHdr),
&qdtPtr->qdbqgrpo);
addQDTsection(qdtPtr, (char *) groupByFld, groupBySize, NULL);
/* Finally, run the query! */
QQQQRY("RUNQRY ", (char *) &ufcbBuf, qdtBuf,
NULL, NULL, &errcod);
if (errcod.msgid[0])
{
printf("API QQQQRY failed\n");
printf("msgid = %7s\n", strcnv400(errcod.msgid,
sizeof(errcod.msgid)));
}
/* Now access the data */
if ((file1 = fopen("OPENFILE1", "rb")) == NULL)
{
printf("Error opening file\n");
exit(1);
}
/* Perform any record I/O here... */
printf("Last name First name Code \
Item St\n");
found = fread((void *) &recBuf, REC_SIZE, 1, file1);
while (found)
{
recCount++;
printf("%s ", strcnv400(recBuf, 15));
printf("%s ", strcnv400(&recBuf[15], 10));
printf("%s ", strcnv400(&recBuf[25], 5));
printf("%s ", strcnv400(&recBuf[30], 20));
printf("%s\n", strcnv400(&recBuf[50], 2));
found = fread((void *) &recBuf, REC_SIZE, 1, file1);
}
printf("%d records selected\n", recCount);
/* Close the file */
fclose(file1);
/* close out the QDT file handle */
system("RCLRSC");
}