How to: Access the Second Column of the Result Set Example: |
The server materializes result sets automatically. But with the introduction of the Answer Set Large Objects (ALOB) data type, result set materialization becomes a developer issue. The purpose of this section is to describe how to use ALOBs and the tokens-called ALOB locators-that represent them.
The server treats ALOBs as legitimate data types. Accordingly, ALOB values can occur in result sets. The strategy of actually nesting result sets which can present serious data management problems; instead, the server represents embedded ALOB values with ALOB locators. Because these referential handles are compact, applications can manipulate them without difficulty.
To process an embedded ALOB, the application must first obtain a locator and, using it, materialize the required result set. We have extended the EDASQL method so that it can be used for this purpose. The following shows sample code:
/*---------------------------------------------------------------------*/
/* Sample code to read in messages and deal with their */
/* components. This code is extracted from rdaappx and */
/* simplified as a demonstration. Support functions that */
/* deal with the data contents and display status errors */
/* are omitted; these routines are application-specific */
/* and/or shown elsewhere in this manual. */
/* */
/* Not shown: */
/* check_stat() looks at an iWay status code and */
/* returns the code. */
/* check_msg() displays/handles any messages */
/* via EDAACCEPT(). */
/* process_response() deal with a returned result set. */
/*---------------------------------------------------------------------*/
#define EDA_VARIABLES
#include "eda.h"
#include "stdlib.h"
#define MIDSIZE 24 /* size of a message id */
/*---------------------------------------------------------------------*/
/* Simple routine to display a UOW ID. This routine is */
/* based on a UOW ID format unique to iWay, and may not */
/* be the same in future releases. No application should */
/* depend upon the layout of a UOW ID. */
/*---------------------------------------------------------------------*/
unsigned char *mid2string(unsigned char *id)
{
static unsigned char idstring[(2*MIDSIZE)+1];
int i,j;
memset(idstring,0,sizeof idstring); /* start with a fresh buffer */
for(i=j=0;i<MIDSIZE;i++,j+=2)
{
sprintf(idstring+j, "%X%X", id[i] >> 4, id[i]&0x0f);
}
return idstring; /* Return the Result! */
}
/*--------------------------------------------------------------------*/
/* Given a row retrieved from the message queue via */
/* EDANEXT, display the fields. Notice that we don't */
/* display any handles for the contents (a LOB) until */
/* specifically requested to do so by the caller. */
/*--------------------------------------------------------------------*/
static unsigned long DisplayOneBrowseRow(int rowNumber, EDA_SCB * pscb)
{
long fldno=EDA_BMSG_FLD_UOWID;
long alpha=EDA_ALPHANUM;
char field[256];
long len;
unsigned long uowAttrib;
unsigned long lWork;
char szTxt[256];
char uowId[MIDSIZE+1];
long result;
printf("row %d:\n",rowNumber);
memset(field,0,sizeof(field));
len=MIDSIZE;
EDAFIELD(pscb,&fldno,uowId,&len, &alpha);
printf("\tUOW id: %s\n",mid2string(uowId));
len=8;
fldno=EDA_BMSG_FLD_USERID;
EDANULL(pscb,&fldno,&result);
if (result == EDA_IS_NULL)
{
printf("\tUser: NULL\n",field);
}
else
{
EDAFIELD(pscb,&fldno,field,&len, &alpha);
printf("\tUser id: %.8s\n",field);
}
fldno=EDA_BMSG_FLD_STATE;
memset(field,0,sizeof(field));
EDAFIELD(pscb,&fldno,field,&len, &alpha);
fldno++;
lWork=atol(field);
printf("\tStatus: %ld",lWork);
if (lWork == EDA_UOWSTATE_CONFIRM)
{
printf(" (EDA_UOWSTATE_CONFIRM)\n");
}
else if (lWork == EDA_UOWSTATE_FAILED)
{
printf(" (EDA_UOWSTATE_FAILED)\n");
}
else if (lWork == EDA_UOWSTATE_INDOUBT)
{
printf(" (EDA_UOWSTATE_INDOUBT)\n");
}
fldno=EDA_BMSG_FLD_ASRC;
memset(field,0,sizeof(field));
EDAFIELD(pscb,&fldno,field,&len, &alpha);
lWork=atol(field);
printf("\tMsgRc: %ld",lWork);
fldno=EDA_BMSG_FLD_TARGRC;
memset(field,0,sizeof(field));
EDAFIELD(pscb,&fldno,field,&len, &alpha);
lWork=atol(field);
printf("\tTarget Rc: %ld\n",lWork);
fldno=EDA_BMSG_FLD_SENTSTAMP;
EDANULL(pscb,&fldno,&result);
if (result == EDA_IS_NULL)
{
printf("\tSent: NULL\n",field);
}
else
{
memset(field,0,sizeof(field));
EDAFIELD(pscb,&fldno,field,&len, &alpha);
printf("\tSent: %s GMT",field);
}
fldno=EDA_BMSG_FLD_PROCSTAMP;
EDANULL(pscb,&fldno,&result);
if (result == EDA_IS_NULL)
{
printf("\tSent: NULL\n",field);
}
else
{
memset(field,0,sizeof(field));
EDAFIELD(pscb,&fldno,field,&len, &alpha);
printf("\tProcessed: %s GMT",field);
}
fldno=EDA_BMSG_FLD_MODULE;
len=32;
memset(field,0,sizeof(field));
EDAFIELD(pscb,&fldno,field,&len, &alpha);
strcpy(szTxt,field);
}
/*---------------------------------------------------------------------*/
/* Purge all message components related to the passed */
/* message. */
/*---------------------------------------------------------------------*/
void PurgeMessage(EDA_SCB * pscb)
{
long len=16L;
long alpha=EDA_ALPHANUM;
long edabase=EDABASE_TBL_MSGS;
long command=EDABASE_DELETE;
long filter=EDABASE_SEL_MSGS_UOWID;
long fldno=1; /* content */
char uowId[MIDSIZE+1];
len=8;
EDAFIELD(pscb,&fldno,uowId,&len, &alpha);
printf("\tUOW id: %s\n",mid2string(uowId));
/*------------------------------------------------------------------*/
/* Tell API/SQL to delete all msgs with the passed */
/* UOW ID. */
/*------------------------------------------------------------------*/
EDABROWSE(pscb,&edabase,&filter,&uowId,&command);
if (pscb->status < EDA_SUCCESS)
{
if (check_stat(pscb->command, pscb->status) < EDA_SUCCESS)
return;
}
printf("UOW purged\n");
return;
}
/*---------------------------------------------------------------------*/
/* Routine to handle an ALOB that arrived in an attachment. */
/* Note that the processing of the rows themselves are */
/* in an external routine. */
/*---------------------------------------------------------------------*/
void DisplayAlob(EDA_ID * peid, EDA_SCB * pscb)
{
char alob[16];
long fldno=11; /* content */
long nts=EDA_NULL_DELIM;
long len=16L;
long alpha=EDA_ALPHANUM;
long semiplex=EDA_CONNECT_SEMIPLEX;
EDA_SCB alobScb;
long sixteen=16L;
long result;
long zero=0L;
long execute=EDA_EXECUTE_ALOB;
int more = 1;
int answer_sets = 0;
int rowCount=1;
char szError[128];
/*------------------------------------------------------------------*/
/* EDAFIELD can return the ALOB handle because it is not a */
/* BLOB or CLOB. */
/*------------------------------------------------------------------*/
EDAFIELD(pscb,&fldno,alob,&len, &alpha);
/*------------------------------------------------------------------*/
/* Create a connection to the same server as the browse */
/* handler. This connection will be used to retrieve the */
/* result set associated with the ALOB. */
/*------------------------------------------------------------------*/
EDACONNECT(peid,&alobScb,"",&nts,"",&nts,(EDACPTR)pscb,&semiplex);
if (check_stat(alobScb.command, alobScb.status) < EDA_SUCCESS)
return;
/*------------------------------------------------------------------*/
/* Now materialize the result set on the new SCB. */
/*------------------------------------------------------------------*/
EDASQL(&alobScb,(EDACPTR)alob,&sixteen,
&zero,&zero,
"",&zero,
&execute);
if (check_stat(alobScb.command, alobScb.status) < EDA_SUCCESS)
return;
EDATEST(&alobScb,NULL);
if (check_stat(alobScb.command, alobScb.status) < EDA_SUCCESS)
{
return;
}
else if (alobScb.status > EDA_SUCCESS)
{
GetMessageText(alobScb.status,szError);
printf("%s\n",szError);
check_msg(&alobScb);
EDAXCONNECT(&alobScb);
return;
}
while (more)
{
check_msg(&alobScb); /* external routine to handle messages */
if (alobScb.status != EDA_SUCCESS)
{
GetMessageText(alobScb.status,szError);
printf("%s\n",szError);
}
else if (process_response(&alobScb)) /* deal with this answer set */
{
break;
}
EDAQUIT(&alobScb);
if (alobScb.status == EDA_END_OF_TRAN)
more = 0;
check_msg(&alobScb);
answer_sets++;
}
if (answer_sets > 1)
{
printf("\n\n<<< Server responded with %d result sets. >>>\n",
answer_sets);
}
EDAXCONNECT(&alobScb);
}
/*---------------------------------------------------------------------*/
/* Display the contents of a user request attachment. */
/* The request is in llidf format, and this routine */
/* breaks down the llidf. Use this code as a sample of */
/* how llidf's are managed in a program. */
/*---------------------------------------------------------------------*/
void DisplayRequest(EDA_ID * peid, EDA_SCB * pscb)
{
#define MAXDATALINE 60 /* most I can display on one line */
char * pblob;
long alpha=EDA_ALPHANUM;
long fldno=11; /* content */
long ll=32000;
long bytesdone=0;
long result;
long colinfovar=EDA_VAR_COLINFO_PTR;
char * ptext;
PEDA_LLIDF pllidf;
char szLine[82];
EDA_COLINFO * pcolinfo;
pblob=calloc(1,32000);
pcolinfo=calloc(32,sizeof(EDA_COLINFO));
EDASET(peid,pscb,&colinfovar,pcolinfo,&result);
EDAFIELD(pscb,&fldno,pblob,&ll, &alpha);
ll=(pcolinfo+11)->colRetrieved;
if (!ll)
{
printf("No data available to display\n");
return;
}
printf("Bytes retrieved=%ld\n",ll);
ptext=pblob;
for (;;)
{
pllidf = (PEDA_LLIDF) ptext;
ptext = ptext+sizeof(EDA_LLIDF);
printf("ll(%u) id(%x) f(%c) ",pllidf->ll,pllidf->id, pllidf->f);
bytesdone += pllidf->ll;
if (pllidf->ll > sizeof(EDA_LLIDF))
{
int datalen;
int offset=0;
char * pdata=ptext;
datalen=pllidf->ll-sizeof(EDA_LLIDF);
printf("'");
for (;;)
{
memset(szLine,0,sizeof(szLine));
if (offset)
{
szLine[0]='\t'; /* tab subsequent lines */
}
if (datalen > MAXDATALINE)
{
memcpy(szLine+offset,pdata,MAXDATALINE);
strcat(szLine,"\n");
datalen -= MAXDATALINE;
pdata+=MAXDATALINE;
offset=1;
printf(szLine);
}
else
{
memcpy(szLine+offset,pdata,datalen);
printf(szLine);
datalen=0;
}
if (!datalen)
{
break;
}
}
printf("'\n");
}
else
{
printf("\n");
}
ptext+=pllidf->ll;
ptext-=sizeof(EDA_LLIDF);
if (bytesdone >= ll)
{
printf("End of request\n");
break;
}
}
/*-----------------------------------------------------------------*/
/* Eliminate the memory we took for this subroutine. */
/*-----------------------------------------------------------------*/
EDASET(peid,pscb,&colinfovar,NULL,&result); /* unlink the colinfo */
free(pcolinfo);
free(pblob);
#undef MAXDATALINE
}
void DisplayBrowseMsgRows(EDA_ID * peid, EDA_SCB * pscb)
{
char szLine[128];
char * p;
int rowNumber=0;
long result;
long uowAttrib; /* loaded during row contents display */
EDATEST(pscb,NULL);
printf("--Browse Rows Follow:\n");
for (;;)
{
EDANEXT(pscb);
if (pscb->status == EDA_END_OF_SET)
{
EDAQUIT(pscb);
printf("End of the queue\n");
break;
}
rowNumber++;
whattodo:
uowAttrib = DisplayOneBrowseRow(rowNumber,pscb);
printf("\nEnter N(ext) D(isplay) P(urge) or Q(uit)..\n");
for (;;)
{
gets(szLine); /* to R1 later 09-05-96 DB */
p=szLine;
while (*p)
{
if (*p < 'A')
p++;
else
break;
}
if (!*p) /* treat no char as a request for NEXT */
p="N";
if (CharIsA(*p,'N') ||
CharIsA(*p,'Q') ||
CharIsA(*p,'D') ||
CharIsA(*p,'P'))
break;
}
if (CharIsA(*p,'N')) /* recycle */
{
continue;
}
else if (CharIsA(*p,'Q'))
{
EDAQUIT(pscb);
break;
}
else if (CharIsA(*p,'P'))
{
PurgeMessage(pscb);
break;
}
else
{
if (uowAttrib & EDA_ATT_ATTRIB_SQLRESP)
{
DisplayAlob(peid,pscb);
}
else if (uowAttrib & EDA_ATT_ATTRIB_LLIDF)
{
DisplayRequest(peid,pscb);
}
else
{
printf("\nCan't display this type of attachment\n");
}
goto whattodo;
}
}
}
/*---------------------------------------------------------------------*/
/* start here */
/*---------------------------------------------------------------------*/
void main()
{
EDA_ID eid;
EDA_SCB scb;
long result;
long lEdaNTS=EDA_NULL_DELIM;
long edabase=EDABASE_TBL_MSGS;
EDAINIT(&eid,&result);
if (check_stat(scb.command, scb.status) < EDA_SUCCESS)
return ; /* recycle */
EDACONNECT(&eid, &scb, "user", &lEdaNTS, "password", &lEdaNTS,
"asynsrvr", &lEdaNTS);
if (check_stat(scb.command, scb.status) < EDA_SUCCESS)
return ; /* recycle */
EDABROWSE(&scb,&edabase,NULL,NULL,NULL);
if (check_stat(scb.command, scb.status) < EDA_SUCCESS)
return ; /* recycle */
DisplayBrowseMsgRows(&eid, &scb);
EDAXCONNECT(&scb);
EDATERM(&eid,&result);
}
Any result set can now contain one or more columns of handles that represent other result sets. The handles are known as ALOB locators. We also provide an extended form of EDASQL for exploding ALOB locators into result sets. The system does not materialize nested result sets automatically.
Suppose the data type field of the EDAINFO block that describes column two of a result set contains EDA_ALOB_TYPE_CODE. This indicates that the second column of the result set contains ALOB values. The following is a formula for accessing column two of the current row of the result set.
This procedure leaves SCB1 in the issued state.
Note:
| iWay Software |