In this section: |
In the server, support for BLOBs and CLOBs has been provided. A BLOB is a market-recognized term for data that an RDBMS can store in binary format. Typically, BLOBs are image or sound representations. A CLOB is a term for a fixed or variable character string greater than 256 bytes. The CLOB data type undergoes the CCSID translation for National Language Support (NLS), which a BLOB does not.
LOB data type support:
Some restrictions may apply to the processing of LOB data types. See the iWay Server Administration manual for your specific platform for more information.
When an EDAFETCH is issued against a row that contains LOB data types, a 16-byte handle will represent the data. Actual LOB data will not be retrieved with the EDAFETCH; instead, an EDAFIELD should be issued to retrieve the data. For more information, see EDAFETCH and EDAFIELD: Storing Fields in a Designated Data Area.
Note: Before the EDAFIELD( ) call was introduced, BLOB/CLOB write-support was done by the use of RPCs to buffer up data. This method is considered deprecated, but is still supported and documented.
The nature of LOB data types is such that their retrieval cannot always be completed in a single operation. Retrieval of a row containing an LOB data type will return only a handle for the LOB value.
If the value is not null and the ColInfo does not indicate a status of EDA_COLINFO_ERR (unable to compute the amount of data available), the ColAvailable value should be retrieved from the array. This will provide the application with the amount of data to be retrieved.
Issue EDAFIELD repeatedly until an EDA_SUCCESS status is returned, indicating that all data has been retrieved (up until that point an EDA_TRUNCATED will be returned). Once all the data has been returned, you can reference the ColInfo array to determine how many bytes have been retrieved. (ColRetrieved provides the number of bytes retrieved in the last EDAFIELD call.)
Note: An EDAFIELD call to another column in the row (or an EDAFETCH or EDANEXT, which positions the cursor to the next row in the result set) will interrupt LOB retrieval. In the former case, retrieval must start at the beginning of the LOB; in the latter case, the LOB will no longer be available for retrieval. LOB retrieval from rows other than the current row is not available at this time.
Example: Increasing Chunk Size of LOB Data Sending a File's Contents as a BLOB to Servers Receiving the Contents of a BLOB or CLOB From Servers Sending a BLOB to Transaction Adapters Reference: |
BLIM is a server parameter that can be used to control the buffer, or chunk size, used for retrieving LOB data. The current value for this is set at EDA_DEFAULT_BUFFER_SIZE. Unless otherwise set at the server, the BLIM is set to 1K.
The following setting in a server profile will set the chunk size of LOB data returned to the client to 4K.
SET BLIM=4096
This setting is provided for tuning purposes and is transparent to the application fetches. See the iWay Server Administration manual for your specific platform for details.
No error checking is done in this example.
:
:
FILE * fh;
long size;
long prec;
long scale=0L;
char * buffer;
long one=1L;
long zero=0L;
long sth; /* previously filled in by EDAPREPARE as statement handle */ long dtype = EDA_BLOB_TYPE_CODE;
fh = fopen("myblob","rb");
fseek(fh,0L,SEEK_END);
size = ftell(fh); /* how big? */
buffer = malloc(size); /* get space for the file */
fread(buffer,1,size,fh); /* load the entire file */
fclose(fh); /* release resources */
prec = size;
EDAUSING(&scb,&sth,&one,
buffer, /* point to start of data */
&size, /* length opf data to send */
&dtype, /* this is a blob */
&prec,
&scale,
&one); /* this is the last parameter */
...check and process any errors...
EDAEXECUTE(&scb,&sth,&zero,&zero,NULL,&zero);
...check and process any errors...
free(buffer); /* don't free until after the execute */
:
:
/****** routine to retrieve a blob or clob from a server *******/
#define MAXLOBBUF 32000
long getEdaLob(
EDA_ID *plEid, // eda context id
EDA_scb *pScb, // session control block
long lColno, // column number of lob
EDA_INFO_AREA *pInfoArea, // saved eda info area
EDA_COLINFO *pColInfo) // retrieval info area used to store
information about column
{
long lFmt,
lSize,
lRetBytes =0;
char szLobTemp[MAXLOBBUF];
EDA_INFO_AREA *edacol;
EDA_COLINFO *colinf;
edacol=pInfoArea+lColno-1;
colinf=pColInfo+lColno;
if(colinf->colInfo==EDA_COLINFO_NULL)
return(0);
/* set lob type retrieval format according to lob type */
switch(edacol->type)
{
case EDA_CLOB_TYPE_CODE:
lFmt=EDA_ALPHANUM;
break;
case EDA_BLOB_TYPE_CODE:
lFmt=EDA_BINARY;
break;
default:
printf("ERROR: Column is not of type BLOB or CLOB\n");
return(-1);
break;
}
/* if the lob size is known set it, not to exceed programs lob buffer area */
if(colinf->colAvailable)
lSize=colinf->colAvailable;
else
lSize=edacol->length;
if(lSize > MAXLOBBUF)
{
printf("LOB size %ld exceeds the maximum chuck size\n",lSize);
lSize=MAXLOBBUF;
}
else
if(lSize == 0)
{
printf("Unknown LOB size %ld\n",lSize);
lSize=MAXLOBBUF;
}
...open output stream or file...
/* get lob, may take a number of iteration, the number of iteration will */
/* depend on the lob buffer size */
do
{
memset(szLobTemp,' ',lSize); /* clear buffer */
EDAFIELD(pScb,&lColno,szLobTemp,&lSize,&lFmt);
if(pScb->status<EDA_SUCCESS) /* error */
printf("(ERR) Error Occured in EDAFIELD,status=%ld\n",pScb->status);
else
{
lRetBytes+=colinf->colRetrieved;
...send to output stream or file...
}
}while(pScb->status==EDA_TRUNCATED);
...close output stream or file...
return(lRetBytes);
}
:
:
/****** supporting routines used by getEdaLob ********/
long InitInfoArea(
EDA_ID *plEid, // eda context id
EDA_scb *pScb // session control block
:
:
{
long lStatus,
lColNo=0,
lEdaVarColinfoPtr=EDA_VAR_COLINFO_PTR;
:
:
/* save eda column metadata information */
pInfoArea=calloc((size_t)pScb->nbrcols,sizeof(EDA_INFO_AREA)));
for(lColNo=1;lColNo<=pScb->nbrcols;lColNo++,pInfoArea++)
EDAINFO(pSscb,&lColNo,pInfoArea);
:
:
/* setup colinfo area, needed for lob retrieval usage */
/* allocate memory for all columns in request in request */
/* ***PLEASE NOTE: the colinfo array must be number of columns plus 1 */
/* since colinfo is populated at offset 1 not 0 */
pColInfo=calloc((size_t)pScb->nbrcols+1,sizeof(EDA_COLINFO)));
EDASET(plEid,pScb,&lEdaVarColinfoPtr,(EDALPTR)pColInfo,&lStatus);
:
:
}
Transaction adapters do not support EDAPREPARE. EDAUSING, however, requires a prepare statement handle (STH).
EDAUSING must use EDA_VAR_BLOB_TP (-4) for the statement handle. Only a BLOB or a CLOB may be sent as part of a transaction.
The BLOB should not be included in the EDARPC parameter buffer.
:
:
EDA_ID lEid;
EDA_scb scb;
long lStatus,
lNts=EDA_NULL_DELIM,
lParmNo=1,
lEndParm=EDA_END_PARM_LIST,
lSth=EDA_VAR_BLOB_TP,
lDbType=EDA_BLOB_TYPE_CODE,
lScale=0,
lLobSize,
char *pszRpc ="ETPBLOB";
*pszPar ="";
*pLobData ="";
:
:
...load lob data into buffer...
/* store blob */
EDAUSING(&scb,&lSth,&lParmNo,pLobData,&lLobSize,&lDbType,&lLobSize,&lScale,
&lEndParm);
/* send blob and any other non-blob parameters to EDA TP */
EDARPC(&scb,pszRpc,&lNts,pszPar,&lParSize);
:
:
Before BLOB/CLOB support using EDAFIELD( ) existed, BLOB/CLOB support was done by using server Remote Procedure routines to buffer data for use in PREPARE and USING steps. These routines are available in Direct Passthru and Automatic Passthru modes only. They are internal server routines and are not found in any library or directory available to the server. While this method is considered deprecated, it is still supported and documented here for reference. The routine names are as follows:
BLOB |
|
EDABS |
Used to send binary image data to the server. |
EDABE |
Used to mark the end of the binary image. |
EDABK |
Used to purge the binary image from server storage. |
CLOB |
|
EDACS |
Used to send character data to the server. |
EDACE |
Used to mark the end of the character data. |
EDACK |
Used to purge the character data from server storage. |
For any given database, see that adapter manual as to specifics of BLOB/CLOB support.
In order to insert or update a binary image or a variable character data type, the following steps should be taken.
The syntax is:
EDARPC (scb, {EDABS|EDACS}, 5, databuff, bufflen)
where:
The syntax is:
EDARPC (scb, {EDABE|EDACE}, 5, '', 0)
where:
The syntax is:
EDAPREPARE (scb, sth, stmt, slength)
where:
'INSERT INTO TABLE VALUES(?,?);'
The syntax is:
EDAUSING (scb, sth, parm, vlu, vlulen, dtype, len)
where:
For example, if the first column represented in the INSERT statement in step 4 above is a CHAR(1) data type, and the second is the binary image or the variable character column, values would be as follows:
EDAUSING (scb1, 1, 1, 'A', 1, 5, 1)
For the second column, the values would be as follows:
EDAUSING (scb1, 1, 2, {'BLOB '|'CLOB '}, 16, 5, 16)
where:
EDAEXECUTE (scb1, 1, 0, 0, '', 0)
EDARPC (scb, {EDABK|EDACK}, 5, '', 0)
The syntax is:
EDARPC (scb, {EDABE|EDACE}, 5, '', 0)
where:
Note: To retrieve BLOB/CLOB data types for some databases, a SET CONVERSION LONGCHAR BLOB/CLOB must be issued before any CLOB EDACS, EDACE, or EDACIS, or BLOB EDABS, EDABE, or EDABIS usage. The set may be issued as a EDAFOCUS call, part of an EDARPC to a "set-up" procedure, or in a server profile. See the relevant adapter manual for requirements.
| iWay Software |