DSN8DLPL
Populates the PSEG_PHOTO (500K BLOB) and BMP_PHOTO (100K BLOB) columns of the EMP_PHOTO_RESUME sample table with data read from sequential data sets.
/*********************************************************************
* Module name = DSN8DLPL (DB2 sample program) *
* *
* DESCRIPTIVE NAME = Populate LOB columns that exceed 32K with data *
* read from sequential data sets. *
* *
* *
* LICENSED MATERIALS - PROPERTY OF IBM *
* 5655-DB2 *
* (C) COPYRIGHT 1997 IBM CORP. ALL RIGHTS RESERVED. *
* *
* STATUS = VERSION 6 *
* *
* Function: Populates the PSEG_PHOTO (500K BLOB) and BMP_PHOTO (100K *
* BLOB) columns of the EMP_PHOTO_RESUME sample table with *
* data read from sequential data sets. *
* *
* LOB locators are used to avoid having to contain all the *
* data in the application's storage. *
* *
* Notes: *
* Dependencies: Requires IBM C/C++ for OS/390 V1R3 or higher *
* *
* Restrictions: *
* *
* Module type: C program *
* Processor: IBM C/C++ for OS/390 V1R3 or subsequent release *
* Module size: See linkedit output *
* Attributes: Re-entrant and re-usable *
* *
* Entry Point: CEESTART (Language Environment entry point) *
* Purpose: See Function *
* Linkage: Standard MVS program invocation, no parameters *
* *
* Input: Symbolic label/name = PSEGINnn, where 00 <= nn <= 99 *
* Description = PSEG photo image data *
* *
* Symbolic label/name = BMPINnn, where 00 <= nn <= 99 *
* Description = BMP photo image data *
* *
* Output: Symbolic label/name = SYSPRINT *
* Description = Report and messages *
* *
* *
* Normal Exit: Return Code = 0000 *
* - Message: none *
* *
* Error Exit: Return Code = 0008 *
* - Message: *** ERROR: DSN8DLPL DB2 Sample Program *
* Unable to open BMPINnn DD data *
* set. Processing terminated. *
* *
* - Message: *** ERROR: DSN8DLPL DB2 Sample Program *
* Unexpected SQLCODE encountered *
* at location xxx *
* Error detailed below *
* Processing terminated *
* (DSNTIAR-formatted message *
* follows). *
* *
* External References: *
* - Routines/Services: DSNTIAR *
* - Data areas : DSNTIAR error_message *
* - Control blocks : None *
* *
* *
* Pseudocode: *
* DSN8DLPL: *
* - Set DD counter (nn) to 00 *
* - Do while more PSEGINnn DD's to process *
* - Call openPSEGfile to open the data set associated with *
* DD PSEGINnn *
* - Call getPSEGrec to read the first record of the data set *
* - Extract the employee serial from this record *
* - Call openBMPfile to open the data set associated with *
* DD BMPINnn *
* - Call getBMPrec to read the first record of the data set *
* - Call primeBLOBcols to: *
* (a) UPDATE the PSEG_PHOTO and BMP_PHOTO columns of the *
* employee's row in the EMP_PHOTO_RESUME table with the *
* contents of these first records *
* (b) SELECT the PSEG_PHOTO and BMP_PHOTO columns back into *
* BLOB locators *
* - Call getPSEGrec to read the next record from the PSEGINnn DD *
* - Do while not end of file for the PSEGINnn DD *
* - Call buildPSEGcol to append the current PSEGINnn record to *
* the PSEG BLOB locator *
* - Call getPSEGrec to read the next record from PSEGINnn *
* - Call getBMPrec to read the next record from the BMPINnn DD *
* - Do while not end of file for the BMPINnn DD *
* - Call buildBMPcol to append the current BMPINnn record to *
* the BMP BLOB locator *
* - Call getBMPrec to read the next record from BMPINnn *
* - Call updateBLOBcols to apply the BLOB locators to the *
* PSEG_PHOTO and BMP_PHOTO columns of the employee's row in *
* the EMP_PHOTO_RESUME table *
* - If all went well, call commitWorkUnit to commit the changes *
* - Else call rollbackWorkUnit to roll back the changes *
* - Print a status line *
* - Close the PSEGINnn and BMPINnn DD's *
* - Increment DD counter (nn) by 1. *
* - If an SQL error occurs, invoke the sql_error routine to gener- *
* ate and display message text *
* End DSN8DLPL *
* *
* openPSEGfile: *
* - Open the data set associated with the PSEGINnn DD *
* - If the open fails, set validDD to false *
* End openPSEGfile *
* *
* getPSEGrec: *
* - Read a record from the data set associated with the PSEGINnn DD*
* - If end of file, set morePSEGrecs to false *
* End getPSEGrec *
* *
* openBMPfile: *
* - Open the data set associated with the BMPINnn DD *
* End openBMPfile *
* *
* getBMPrec: *
* - Read a record from the data set associated with the BMPINnn DD *
* - If end of file, set moreBMPrecs to false *
* End getBMPrec *
* *
* primeBLOBcols: *
* - extract the employee serial from bytes 10-15 of the PSEG *
* buffer. *
* - UPDATE the PSEG_PHOTO and BMP_PHOTO columns for the employee's *
* row in the EMP_PHOTO_RESUME table from the PSEG and BMP records*
* - SELECT the PSEG_PHOTO and BMP_PHOTO columns for the employee *
* into LOB locators blPSEG1 and blBMP1
* End primeBLOBcols *
* *
* buildPSEGcol: *
* - append the contents of the PSEG input record to the PSEG BLOB *
* locator blPSEG1 and assign to BLOB locator blPSEG2 *
* - free BLOB locator blPSEG1 *
* - set BLOB locator blPSEG1 from BLOB locator blPSEG2 *
* - free BLOB locator blPSEG2 *
* End buildPSEGcol *
* *
* buildBMPcol: *
* - append the contents of the BMP input record to the BMP BLOB *
* locator blBMP1 and assign to BLOB locator blBMP2 *
* - free BLOB locator blBMP1 *
* - set BLOB locator blBMP1 from BLOB locator blBMP2 *
* - free BLOB locator blBMP2 *
* End buildBMPcol *
* *
* updateBLOBcols: *
* - UPDATE the PSEG_PHOTO and BMP_PHOTO columns for the employee's *
* row in the EMP_PHOTO_RESUME table from the PSEG and BMP BLOB *
* locators BMP blPSEG1 and blBMP1 *
* End updateBLOBcols *
* *
* commitWorkUnit: *
* - commit the changes *
* End commitWorkUnit *
* *
* rollbackWorkUnit: *
* - roll back the changes *
* End rollbackWorkUnit *
* *
* sql_error: *
* - call DSNTIAR to format the unexpected SQLCODE. *
* End sql_error *
* *
*********************************************************************/
/********************** C library definitions ***********************/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
/***************************** Equates ******************************/
#define NO 0 /* False */
#define YES 1 /* True */
#define NOT_OK 0 /* Run status indicator: Error*/
#define OK 1 /* Run status indicator: Good */
#define TIAR_DIM 10 /* Max no. of DSNTIAR msgs */
#define TIAR_LEN 80 /* Length of DSNTIAR messages */
/****************************** Files *******************************/
FILE *BMPin; /* pointer to BMP input file */
FILE *PSEGin; /* pointer to PSEG input file */
/************************** Global Storage **************************/
int status = OK; /* run status flag */
char PSEGinDD[12]; /* PSEGin DD template */
char BMPinDD[12]; /* BMPin DD template */
short int DDcounter = 0; /* DD allocation counter */
char DDnum[2]; /* DD number string template */
short int validDD = YES; /* unprocessed DD indicator */
short int morePSEGrecs = YES; /* eof indicator for PSEGINnn */
short int moreBMPrecs = YES; /* eof indicator for BMPINnn */
short int PSEGblkLen = 0; /* length of PSEG input block */
short int PSEGblkPos = 0; /* offset in PSEG input block */
short int PSEGrecLen = 0; /* length of PSEG input record*/
short int PSEGrecPos = 0; /* offset in PSEG input record*/
long int PSEGcolLen = 0; /* length of PSEG column data */
short int BMPblkLen = 0; /* length of BMP input block */
short int BMPblkPos = 0; /* offset in BMP input block */
short int BMPrecLen = 0; /* length of BMP input record */
short int BMPrecPos = 0; /* offset in BMP input record */
long int BMPcolLen = 0; /* length of BMP column data */
int byteIn; /* current incoming byte */
/******************** DB2 SQL Communication Area ********************/
EXEC SQL INCLUDE SQLCA;
/**************************** DB2 Tables ****************************/
EXEC SQL DECLARE EMP_PHOTO_RESUME TABLE
( EMPNO CHAR(06) NOT NULL,
EMP_ROWID ROWID,
PSEG_PHOTO BLOB( 500K ),
BMP_PHOTO BLOB( 100K ),
RESUME CLOB( 5K ) );
/************** DB2 Host and Null Indicator Variables ***************/
EXEC SQL BEGIN DECLARE SECTION;
char hvEMPNO[7]; /* Host var for employee no. */
SQL TYPE IS BLOB(8K) PSEGinRec; /* Area for PSEG input record */
short int niPSEG_PHOTO = 0; /* Null ind for PSEG photo col*/
SQL TYPE IS BLOB(8K) BMPinRec; /* Area for BMP input record */
short int niBMP_PHOTO = 0; /* Null ind for BMP photo col */
EXEC SQL END DECLARE SECTION;
/******************** DB2 LOB Locator Variables *********************/
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS BLOB_LOCATOR blPSEG1; /* BLOB loc for PSEG photo col*/
SQL TYPE IS BLOB_LOCATOR blPSEG2; /* BLOB loc for PSEG photo col*/
SQL TYPE IS BLOB_LOCATOR blBMP1; /* BLOB loc for BMP photo col */
SQL TYPE IS BLOB_LOCATOR blBMP2; /* BLOB loc for BMP photo col */
EXEC SQL END DECLARE SECTION;
/********************** DB2 Message Formatter ***********************/
struct error_struct { /* DSNTIAR message structure */
short int error_len;
char error_text[TIAR_DIM][TIAR_LEN];
} error_message = {TIAR_DIM * (TIAR_LEN)};
#pragma linkage( dsntiar, OS )
extern short int dsntiar( struct sqlca *sqlca,
struct error_struct *msg,
int *len );
/************************* Global Functions *************************/
int main( void ); /* main routine */
void openPSEGfile( void ); /* open PSEGINnn DD file */
void getPSEGrec( void ); /* read PSEG image file */
void openBMPfile( void ); /* open BMPINnn DD file */
void getBMPrec( void ); /* read BMP image file */
void primeBLOBcols( void ); /* set PSEG and BMP BLOB locs */
void buildPSEGcol( void ); /* add to PSEG BLOB locator */
void buildBMPcol( void ); /* add to BMP BLOB locator */
void updateBLOBcols( void ); /* apply PSEG and BMP locs */
void commitWorkUnit( void ); /* commit changes */
void rollbackWorkUnit( void ); /* roll back changes */
void sql_error( char *locmsg ); /* generate msg for SQL error */
/********************************************************************/
/*************************** main routine ***************************/
/********************************************************************/
int main( void )
{
/******************************************************************/
/************************* Initialization *************************/
/******************************************************************/
/*******************************************************************
* Write identification header *
*******************************************************************/
printf( "****************************************"
"****************************************\n" );
printf( "* DSN8DLPL DB2 Sample Program\n" );
printf( "****************************************"
"****************************************\n" );
/******************************************************************/
/*************************** Processing ***************************/
/******************************************************************/
/*******************************************************************
* Cycle through PSEGINnn and BMPINnn DD pairs, incrementing the DD *
* counter, until all pairs have been processed. *
*******************************************************************/
for( DDcounter=0; validDD == YES && status == OK; DDcounter++ )
{
/***************************************************************
* Fetch the PSEG data from the current PSEGINnn DD. The first *
* record contains the serial number of the employee associated *
* with the photo. *
***************************************************************/
openPSEGfile();
if( validDD == YES && status == OK )
getPSEGrec();
/***************************************************************
* Fetch the first record from the BMPINnn DD *
***************************************************************/
if( validDD == YES && status == OK )
openBMPfile();
if( validDD == YES && status == OK )
getBMPrec();
/***************************************************************
* Init the PSEG and BMP BLOB table columns for the employee *
***************************************************************/
if( validDD == YES && status == OK )
primeBLOBcols();
/***************************************************************
* Read the second records from the PSEG and BMP data sets *
***************************************************************/
if( validDD == YES && status == OK )
getPSEGrec();
if( validDD == YES && status == OK )
getBMPrec();
/***************************************************************
* Append remaining PSEG recs to PSEG photo col using BLOB loc *
***************************************************************/
while( morePSEGrecs == YES && validDD == YES && status == OK )
{
buildPSEGcol();
if( status == OK )
getPSEGrec();
}
/***************************************************************
* Append remaining BMP recs to BMP photo col using BLOB locator*
***************************************************************/
while( moreBMPrecs == YES && validDD == YES && status == OK )
{
buildBMPcol();
if( status == OK )
getBMPrec();
}
/***************************************************************
* Apply the data associated with the PSEG and BMP BLOB locators*
* to the table *
***************************************************************/
if( validDD == YES && status == OK )
updateBLOBcols();
/***************************************************************
* If clear status, commit the work unit; otherwise, rollback *
***************************************************************/
if( validDD == YES )
if( status == OK )
commitWorkUnit();
else
rollbackWorkUnit();
/***************************************************************
* Print report line *
***************************************************************/
if( validDD == YES && status == OK )
{
printf( "* LOB population statistics for employee "
"number %s follow:\n", hvEMPNO );
printf( "* - PSEG photo bytes: %d\n",PSEGcolLen );
printf( "* - BMP photo bytes: %d\n",BMPcolLen );
printf( "****************************************"
"****************************************\n" );
}
/***************************************************************
* Close data sets for current PSEGINnn and BMPINnn DDs *
***************************************************************/
fclose(PSEGin);
fclose(BMPin);
} /* end for( DDcounter=0; validDD == YES && status == OK ... */
/******************************************************************/
/***************************** Cleanup ****************************/
/******************************************************************/
/*******************************************************************
* Set return code *
*******************************************************************/
if( status == OK )
return( 0 );
else
return( 8 );
} /* end main */
void openPSEGfile( void )
/*********************************************************************
* Opens the data set associated with the PSEGINnn DD, where "nn" is *
* the current setting of the DD counter from the main loop. *
* *
* If the DD cannot be allocated, then no further data sets remain to *
* be processed so signal end of job. *
*********************************************************************/
{
/*******************************************************************
* intialize work variables *
*******************************************************************/
morePSEGrecs = YES;
PSEGblkPos = 0;
PSEGblkLen = 0;
/*******************************************************************
* form the DD name for the next PSEG data set *
*******************************************************************/
strcpy( PSEGinDD,"DD:PSEGIN\0" ); /* init PSEGin DD template */
sprintf( DDnum,"%02d",DDcounter ); /* convert DD cntr to string */
strcat( PSEGinDD,DDnum ); /* form PSEGINnn DD name */
/*******************************************************************
* open the PSEGINnn DD data set *
*******************************************************************/
PSEGin = fopen( PSEGinDD,"rb,recfm=u" );
if( PSEGin == NULL ) /* if no pointer returned */
validDD = NO; /* .. no more data sets left */
} /* end openPSEGfile */
void getPSEGrec( void )
/*********************************************************************
* Called by the main routine to read the next record from the data *
* set associated with the current PSEGINnn DD into a buffer, PSEGin- *
* Rec. *
* *
* If this is the first record from the PSEGINnn DD data set, it con- *
* tains the serial number of an employee in bytes 10-15 and it will *
* be UPDATEd into the PSEG_PHOTO column of that employee's row in *
* the sample EMP_PHOTO_RESUME table. This column and row will then *
* be SELECTed into a BLOB locator, blPSEG1, to be used for accumu- *
* lating the remaining records from the current PSEGINnn DD data set *
* to form a complete PSEG_PHOTO entry for the current employee. *
* *
* If this is not the first record from the PSEGINnn DD data set, it *
* will be appended to previously read records for this data set in *
* a DB2 data area associated with the BLOB locator, blPSEG1. *
* *
* When all records of the data set have been read and accumulated in *
* the locator area, the locator will be applied to the PSEG_PHOTO *
* column of the current employee's row in the EMP_PHOTO_RESUME table.*
**********************************************************************
* Because the C language is not record-oriented in the sense of MVS *
* data sets, it's necessary to treat the PSEG data set, which has a *
* variable-blocked format, as an unformatted dataset in order to *
* access the block descriptor word (BDW) of each input block and the *
* record descriptor word (RDW) of each input record. *
* *
* Each RDW provides the number of bytes of data in its record, *
* including 4 bytes for itself. *
* *
* Each BDW provides the number of bytes of data in its block, *
* including 4 bytes for each RDW in the block and 4 bytes for *
* itself. *
*********************************************************************/
{
/*******************************************************************
* intialize work variables *
*******************************************************************/
PSEGrecLen = 0;
PSEGrecPos = 0;
PSEGinRec.length = 0;
/*******************************************************************
* read the 1st byte of the record *
*******************************************************************/
byteIn = getc(PSEGin);
/*******************************************************************
* get remaining bytes of the record if not EOF *
*******************************************************************/
if( byteIn != EOF )
{
/***************************************************************
* if at end of block, read next BDW *
***************************************************************/
if( PSEGblkPos >= PSEGblkLen && PSEGrecPos >= PSEGrecLen)
{
/***********************************************************
* length of block = (16**2) * BDW[0] *
* ............... + (16**0) * BDW[1] *
* ............... - 4 (length of BDW) *
***********************************************************/
PSEGblkLen = 256 * byteIn;
byteIn = getc(PSEGin);
PSEGblkLen = PSEGblkLen + byteIn - 4;
/***********************************************************
* skip remainder of BDW *
***********************************************************/
byteIn = getc(PSEGin);
byteIn = getc(PSEGin);
PSEGblkPos = 0;
/***********************************************************
* read first byte of RDW *
***********************************************************/
byteIn = getc(PSEGin);
}
/***************************************************************
* process the RDW *
****************************************************************
* length of record = (16**2) * RDW[0] *
* ................ + (16**0) * RDW[1] *
* ................ - 4 (length of RDW) *
***************************************************************/
PSEGrecLen = 256 * byteIn;
byteIn = getc(PSEGin);
PSEGrecLen = PSEGrecLen + byteIn - 4;
/***************************************************************
* skip remainder of RDW *
***************************************************************/
byteIn = getc(PSEGin);
byteIn = getc(PSEGin);
PSEGrecPos = 0;
/***************************************************************
* update position in block *
***************************************************************/
PSEGblkPos = PSEGblkPos + PSEGrecLen + 4;
}
/*******************************************************************
* build the PSEG record according to the record length *
*******************************************************************/
while( PSEGrecPos < PSEGrecLen && byteIn != EOF )
{
byteIn = getc(PSEGin);
PSEGinRec.data[PSEGinRec.length++] = byteIn;
PSEGrecPos++;
}
/*******************************************************************
* signal end of file when applicable *
*******************************************************************/
if( byteIn == EOF )
morePSEGrecs = NO;
} /* end getPSEGrec */
void openBMPfile( void )
/*********************************************************************
* Opens the data set associated with the BMPINnn DD, where "nn" is *
* the current setting of the DD counter from the main loop. *
* *
* If the DD cannot be allocated, then an error has occurred because *
* each BMPINnn DD must be paired with a PSEGINnn data set. *
*********************************************************************/
{
/*******************************************************************
* intialize work variables *
*******************************************************************/
moreBMPrecs = YES;
BMPblkPos = 0;
BMPblkLen = 0;
/*******************************************************************
* form the DD name for the next BMP data set *
*******************************************************************/
strcpy( BMPinDD,"DD:BMPIN\0" ); /* init BMPin DD template */
sprintf( DDnum,"%02d",DDcounter ); /* convert DD cntr to string */
strcat( BMPinDD,DDnum ); /* form BMPINnn DD name */
/*******************************************************************
* open the current BMPINnn DD data set *
*******************************************************************/
BMPin = fopen( BMPinDD,"rb,recfm=u" );
if( BMPin == NULL )
{
printf( "************************************************\n" );
printf( "*** ERROR: DSN8DLPL DB2 Sample Program\n" );
printf( "*** Unable to open BMPIN%s DD data set\n",
DDnum );
printf( "*** Processing terminated.\n" );
printf( "************************************************\n" );
status = NOT_OK;
}
} /* end openBMPfile */
void getBMPrec( void )
/*********************************************************************
* Called by the main routine to read the next record from the data *
* set associated with the current BMPINnn DD into a buffer, BMPinRec.*
* *
* If this is the first record from the BMPINnn DD data set, it con- *
* tains the serial number of an employee in bytes 10-15 and it will *
* be UPDATEd into the BMP_PHOTO column of that employee's row in *
* the sample EMP_PHOTO_RESUME table. This column and row will then *
* be SELECTed into a BLOB locator, blBMP1, to be used for accumulat- *
* ing the remaining records from the current BMPINnn DD data set to *
* form a complete BMP_PHOTO entry for the current employee. *
* *
* If this is not the first record from the BMPINnn DD data set, it *
* will be appended to previously read records for this data set in *
* a DB2 data area associated with the BLOB locator, blBMP1. *
* *
* When all records of the data set have been read and accumulated in *
* the locator area, the locator will be applied to the BMP_PHOTO *
* column of the current employee's row in the EMP_PHOTO_RESUME table.*
**********************************************************************
* Because the C language is not record-oriented in the sense of MVS *
* data sets, it's necessary to treat the BMP data set, which has a *
* variable-blocked format, as an unformatted dataset in order to *
* access the block descriptor word (BDW) of each input block and the *
* record descriptor word (RDW) of each input record. *
* *
* Each RDW provides the number of bytes of data in its record, *
* including 4 bytes for itself. *
* *
* Each BDW provides the number of bytes of data in its block, *
* including 4 bytes for each RDW in the block and 4 bytes for *
* itself. *
*********************************************************************/
{
/*******************************************************************
* intialize work variables *
*******************************************************************/
BMPrecLen = 0;
BMPrecPos = 0;
BMPinRec.length = 0;
/*******************************************************************
* read the 1st byte of the record *
*******************************************************************/
byteIn = getc(BMPin);
/*******************************************************************
* get remaining bytes of the record if not EOF *
*******************************************************************/
if( byteIn != EOF )
{
/***************************************************************
* if at end of block, read next BDW *
***************************************************************/
if( BMPblkPos >= BMPblkLen )
{
/***********************************************************
* length of block = (16**2) * BDW[0] *
* ............... + (16**0) * BDW[1] *
* ............... - 4 (length of BDW) *
***********************************************************/
BMPblkLen = 256 * byteIn;
byteIn = getc(BMPin);
BMPblkLen = BMPblkLen + byteIn - 4;
/***********************************************************
* skip remainder of BDW *
***********************************************************/
byteIn = getc(BMPin);
byteIn = getc(BMPin);
BMPblkPos = 0;
/***********************************************************
* read first byte of RDW *
***********************************************************/
byteIn = getc(BMPin);
}
/***************************************************************
* process the RDW *
****************************************************************
* length of record = (16**2) * RDW[0] *
* ................ + (16**0) * RDW[1] *
* ................ - 4 (length of RDW) *
***************************************************************/
BMPrecLen = 256 * byteIn;
byteIn = getc(BMPin);
BMPrecLen = BMPrecLen + byteIn - 4;
/***************************************************************
* skip remainder of RDW *
***************************************************************/
byteIn = getc(BMPin);
byteIn = getc(BMPin);
BMPrecPos = 0;
/***************************************************************
* update position in block *
***************************************************************/
BMPblkPos = BMPblkPos + BMPrecLen + 4;
}
/*******************************************************************
* build the BMP record according to the record length *
*******************************************************************/
while( BMPrecPos < BMPrecLen && byteIn != EOF )
{
byteIn = getc(BMPin);
BMPinRec.data[BMPinRec.length++] = byteIn;
BMPrecPos++;
}
/*******************************************************************
* signal end of file when applicable *
*******************************************************************/
if( byteIn == EOF )
moreBMPrecs = NO;
} /* end getBMPrec */
void primeBLOBcols( void )
/*********************************************************************
* Called by the main routine to apply the first PSEG input record *
* (from getPSEGrec) and the first BMP input record (from getBMPrec) *
* to the PSEG_PHOTO and BMP_PHOTO BLOB columns, respectively, and *
* then fetch those columns using BLOB locators. *
* *
* The PSEG BLOB locator will be used by the buildPSEGcol function *
* to build a BLOB entity of up to 500K bytes from the remaining *
* PSEGin records without consuming application workspace. *
* *
* The BMP BLOB locator will be used by the buildBMPcol function to *
* build a BLOB entity of up to 500K bytes from the remaining BMPin *
* records, again without consuming application workspace. *
* *
* When all PSEG and BMP records have been processed, the data will *
* be applied from the BLOB locators to the EMP_PHOTO_RESUME table by *
* the updateBLOBcols function. *
*********************************************************************/
{
char *empser; /* */
/*******************************************************************
* Extract the employee number from bytes 10-15 of the PSEG record *
*******************************************************************/
empser = &PSEGinRec.data[9];
strncpy( hvEMPNO,empser,6 );
/*******************************************************************
* Initialize the BLOB columns with data from the 1st input records *
*******************************************************************/
EXEC SQL UPDATE EMP_PHOTO_RESUME
SET PSEG_PHOTO = :PSEGinRec,
BMP_PHOTO = :BMPinRec
WHERE EMPNO = :hvEMPNO;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "primeBLOBcols @ UPDATE" );
}
/*******************************************************************
* Select the initial BLOB data into locators *
*******************************************************************/
if( status == OK )
{
EXEC SQL SELECT PSEG_PHOTO, BMP_PHOTO
INTO :blPSEG1 :niPSEG_PHOTO,
:blBMP1 :niBMP_PHOTO
FROM EMP_PHOTO_RESUME
WHERE EMPNO = :hvEMPNO;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "primeBLOBcols @ SELECT" );
}
}
/*******************************************************************
* Set initial lengths of PSEG_PHOTO anf BMP_PHOTO columns *
*******************************************************************/
PSEGcolLen = PSEGinRec.length;
BMPcolLen = BMPinRec.length;
} /* end primeBLOBcols */
void buildPSEGcol( void )
/*********************************************************************
* Called by the main routine to build a PSEG_PHOTO column entry for *
* the current employee. *
* *
* This is done by appending the current record of the PSEG input file*
* (from getPSEGrec) to the entity associated with blPSEG1, the BLOB *
* locator for the PSEG_PHOTO column. *
* *
* When all PSEG input records have been appended to this entity, the *
* updateBLOBcols function will be invoked to update the PSEG_PHOTO *
* column in the EMP_PHOTO_RESUME table from blPSEG1. *
*********************************************************************/
{
/*******************************************************************
* Generate a new BLOB locator that contains the current input *
* record appended to the current PSEG_PHOTO locator *
*******************************************************************/
EXEC SQL SET :blPSEG2 = SUBSTR( :blPSEG1,1,LENGTH(:blPSEG1) )
|| :PSEGinRec;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "buildPSEGcol @ SET LOCATOR #2" );
}
/*******************************************************************
* Regenerate the PSEG_PHOTO locator from the updated locator *
*******************************************************************/
if( status == OK )
{
EXEC SQL FREE LOCATOR :blPSEG1;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "buildPSEGcol @ FREE LOCATOR #1" );
}
}
if( status == OK )
{
EXEC SQL SET :blPSEG1 = :blPSEG2;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "buildPSEGcol @ SET LOCATOR #1" );
}
}
if( status == OK )
{
EXEC SQL FREE LOCATOR :blPSEG2;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "buildPSEGcol @ FREE LOCATOR #2" );
}
}
/*******************************************************************
* Update length of PSEG_PHOTO column *
*******************************************************************/
if( status == OK )
PSEGcolLen = PSEGcolLen + PSEGinRec.length;
} /* end buildPSEGcol */
void buildBMPcol( void )
/*********************************************************************
* Called by the main routine to build a BMP_PHOTO column entry for *
* the current employee. *
* *
* This is done by appending the current record of the BMP input file *
* (from getBMPrec) to the entity associated with blBMP1, the BLOB *
* locator for the BMP_PHOTO column. *
* *
* When all BMP input records have been appended to this entity, the *
* updateBLOBcols function will be invoked to update the BMP_PHOTO *
* column in the EMP_PHOTO_RESUME table from blBMP1. *
*********************************************************************/
{
/*******************************************************************
* Generate a new BLOB locator that contains the current input *
* record appended to the current BMP_PHOTO locator *
*******************************************************************/
EXEC SQL SET :blBMP2 = SUBSTR( :blBMP1,1,LENGTH(:blBMP1) )
|| :BMPinRec;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "buildBMPcol @ SET LOCATOR #2" );
}
/*******************************************************************
* Regenerate the BMP_PHOTO locator from the updated locator *
*******************************************************************/
if( status == OK )
{
EXEC SQL FREE LOCATOR :blBMP1;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "buildBMPcol @ FREE LOCATOR #1" );
}
}
if( status == OK )
{
EXEC SQL SET :blBMP1 = :blBMP2;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "buildBMPcol @ SET LOCATOR #1" );
}
}
if( status == OK )
{
EXEC SQL FREE LOCATOR :blBMP2;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "buildBMPcol @ FREE LOCATOR #2" );
}
}
/*******************************************************************
* Update length of BMP_PHOTO column *
*******************************************************************/
if( status == OK )
BMPcolLen = BMPcolLen + BMPinRec.length;
} /* end buildBMPcol */
void updateBLOBcols( void )
/*********************************************************************
* Called by the main routine to apply the BLOB entities constructed *
* from the PSEGin and BMPin input files by the buildPSEGcol and *
* buildBMPcol functions and pointed to by the blPSEG1 and blBMP1 *
* BLOB locators to the PSEG_PHOTO and BMP_PHOTO columns of the *
* EMP_PHOTO_RESUME_TABLE. *
*********************************************************************/
{
EXEC SQL UPDATE EMP_PHOTO_RESUME
SET PSEG_PHOTO = :blPSEG1,
BMP_PHOTO = :blBMP1
WHERE EMPNO = :hvEMPNO;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "updateBLOBcols @ UPDATE" );
}
} /* end updateBLOBcols */
void commitWorkUnit( void )
/*********************************************************************
* Called by the main routine to commit the current unit of work, *
* which is composed of a fully-built PSEG entry and a fully-built *
* BMP entry for the current employee. *
*********************************************************************/
{
EXEC SQL COMMIT;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "commitWorkUnit @ COMMIT" );
}
} /* end commitWorkUnit */
void rollbackWorkUnit( void )
/*********************************************************************
* Called by the main routine to rollback the current unit of work, *
* which is composed of a fully-built PSEG entry and a fully-built *
* BMP entry for the current employee. *
*********************************************************************/
{
EXEC SQL ROLLBACK;
if( SQLCODE != 0 )
{
status = NOT_OK;
sql_error( "rollbackWorkUnit @ ROLLBACK" );
}
} /* end rollbackWorkUnit */
void sql_error( char *locmsg )
/*********************************************************************
* SQL error handler *
*********************************************************************/
{
short int rc; /* DSNTIAR Return code */
int j,k; /* Loop control */
static int lrecl = TIAR_LEN; /* Width of message lines */
/*******************************************************************
* print the location message *
*******************************************************************/
printf( "*****************************************************\n" );
printf( "*** ERROR: DSN8DLPL DB2 Sample Program\n" );
printf( "*** Unexpected SQLCODE encountered at location\n" );
printf( "*** %.68s\n", locmsg );
printf( "*** Error detailed below\n" );
printf( "*** Processing terminated\n" );
printf( "*****************************************************\n" );
/*******************************************************************
* format and print the SQL message *
*******************************************************************/
rc = dsntiar( &sqlca, &error_message, &lrecl );
if( rc == 0 )
for( j=0; j<TIAR_DIM; j++ )
{
for( k=0; k<TIAR_LEN; k++ )
putchar(error_message.error_text[j][k] );
putchar('\n');
}
else
{
printf( " *** ERROR: DSNTIAR could not format the message\n" );
printf( " *** SQLCODE is %d\n",SQLCODE );
printf( " *** SQLERRM is \n" );
for( j=0; j<sqlca.sqlerrml; j++ )
printf( "%c", sqlca.sqlerrmc[j] );
printf( "\n" );
}
} /* end sql_error */