DSN8BD3
This module lists employee phone numbers and optionally updates them.
/**********************************************************************/
/* */
/* Module name = DSN8BD3 */
/* */
/* Descriptive name = DB2 SAMPLE APPLICATION */
/* PHONE APPLICATION */
/* BATCH */
/* C LANGUAGE */
/* */
/* LICENSED MATERIALS - PROPERTY OF IBM */
/* 5695-DB2 */
/* (C) COPYRIGHT 1982, 1995 IBM CORP. ALL RIGHTS RESERVED. */
/* */
/* STATUS = VERSION 4 */
/* */
/* Function = This module lists employee phone numbers and */
/* optionally updates them. */
/* */
/* Notes = none */
/* */
/* */
/* Module type = C program */
/* Processor = DB2 precompiler, C compiler */
/* Module size = see link edit */
/* Attributes = not reentrant or reusable */
/* */
/* Entry point = DSN8BD3 */
/* Purpose = see function */
/* Linkage = invoked from DSN command processor subcommand RUN */
/* Input = */
/* */
/* symbolic label/name = CARDIN */
/* description = INPUT REQUEST FILE */
/* */
/* symbolic label/name = VPHONE */
/* description = VIEW OF TELEPHONE TABLE: PHONE */
/* */
/* Output = */
/* */
/* symbolic label/name = REPORT */
/* description = PRINTED REPORT AND RESULTS */
/* */
/* symbolic label/name = VEMPLP */
/* description = VIEW OF EMPLOYEE INFORMATION */
/* */
/* */
/* Exit-normal = return code 0 normal completion */
/* */
/* Exit-error = */
/* */
/* Return code = none */
/* */
/* Abend codes = none */
/* */
/* Error-messages = */
/* DSN8000I - REQUEST IS: ... */
/* DSN8004I - EMPLOYEE SUCCESSFULLY UPDATED */
/* DSN8007E - EMPLOYEE DOES NOT EXIST, UPDATE NOT DONE */
/* DSN8008I - NO EMPLOYEE FOUND IN TABLE */
/* DSN8053I - ROLLBACK SUCCESSFUL, ALL UPDATES REMOVED */
/* DSN8060E - SQL ERROR, RETURN CODE IS: */
/* DSN8061E - ROLLBACK FAILED, RETURN CODE IS: */
/* DSN8068E - INVALID REQUEST, SHOULD BE 'L' OR 'U' */
/* DSN8075E - MESSAGE FORMAT ROUTINE ERROR, */
/* RETURN CODE IS: */
/* */
/* External references = */
/* Routines/services = */
/* DSNTIAR - translate sqlca into messages */
/* */
/* Data-areas = none */
/* */
/* Control-blocks = */
/* SQLCA - sql communication area */
/* */
/* Tables = none */
/* */
/* Change-activity = */
/* 10/03/94 Updated cardin statement to prevent looping. KEW1351 @51*/
/* PN61293 @51*/
/* */
/* *Pseudocode* */
/* */
/* main: */
/* do while more input */
/* get input */
/* display request */
/* process request */
/* end */
/* */
/* Do_req: */
/* case (action) */
/* */
/* subcase ('L') */
/* create report heading */
/* if lastname is '*' then */
/* list all employees */
/* else */
/* if lastname contains '%' then */
/* list employees generic */
/* else */
/* list employees specific */
/* endsub */
/* */
/* subcase ('U') */
/* update phonenumber for employee */
/* write confirmation message */
/* */
/* otherwise */
/* invalid request */
/* endsub */
/* */
/* endcase */
/* */
/* Prt_row: */
/* print a row of the report */
/* */
/* Sql_err: */
/* if sql error occurs then */
/* rollback */
/* */
/**********************************************************************/
/**********************************************************************/
/* Include C library definitions */
/**********************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/**********************************************************************/
/* General declarations */
/**********************************************************************/
#define NOTFOUND 100
/**********************************************************************/
/* Input / Output files */
/**********************************************************************/
FILE *cardin; /* Input control cards */
FILE *report; /* Output phone report */
/**********************************************************************/
/* Input record structure */
/**********************************************************************/
EXEC SQL BEGIN DECLARE SECTION;
struct {
char action[2]; /* L for list or U for update */
char lname[16]; /* last name or pattern- L mode*/
char fname[13]; /* first name or pattern-L mode*/
char eno[7]; /* employee number- U mode */
char newno[5]; /* new phone number- U mode */
} ioarea;
char trail[43]; /* unused portion of input rec */
char slname[16]; /* unmodified last name pattern*/
EXEC SQL END DECLARE SECTION;
/**********************************************************************/
/* Report headings */
/**********************************************************************/
struct {
char hdr011[30];
char hdr012[32];
} hdr0 = {
" REQUEST LAST NAME ",
"FIRST NAME EMPNO NEW XT.NO"};
#define rpthdr0 hdr0.hdr011
struct {
char hdr111[29];
char hdr112[21];
char hdr113[30];
} hdr1 = {
"-----------------------------",
" TELEPHONE DIRECTORY ",
"-----------------------------"};
#define rpthdr1 hdr1.hdr111
struct {
char hdr211[10];
char hdr212[11];
char hdr213[ 8];
char hdr214[ 6];
char hdr215[ 9];
char hdr216[ 5];
char hdr217[ 5];
char hdr221[ 7];
char hdr222[ 7];
char hdr223[ 5];
char hdr224[ 5];
char hdr225[ 5];
} hdr2 = {
"LAST NAME",
"FIRST NAME",
"INITIAL",
"PHONE",
"EMPLOYEE",
"WORK",
"WORK",
"NUMBER",
"NUMBER",
"DEPT",
"DEPT",
"NAME"
};
#define rpthdr2 hdr2.hdr211,hdr2.hdr212,hdr2.hdr213,hdr2.hdr214,\
hdr2.hdr215,hdr2.hdr216,hdr2.hdr217,hdr2.hdr221,\
hdr2.hdr222,hdr2.hdr223,hdr2.hdr224,hdr2.hdr225
/**********************************************************************/
/* Report formats */
/**********************************************************************/
static char fmt1[] = "\n %s\n";
static char fmt2[] = " %9s%17s%10s%6s%10s%5s%5s\n%43s%7s%7s%5s%5s\n";
static char fmt3[] = " %-16s%-16s%-5s%-7s%-9s%-5s%-36s\n";
static char fmt4[] = " %1c%15c%12c%6c%4c%43c";
static char fmt5[] =
"\n\n %s\n %s\n --%-7s--%-15s--%-12s--%-5s--%-9s--\n";
/**********************************************************************/
/* Fields sent to message routine */
/**********************************************************************/
char outmsg[70]; /* error/information msg buffer*/
char module[ 8] = "DSN8BD3"; /* module name for message rtn */
extern DSN8MDG(); /* message routine */
/**********************************************************************/
/* SQL communication area */
/**********************************************************************/
EXEC SQL INCLUDE SQLCA;
/**********************************************************************/
/* SQL declaration for view VPHONE */
/**********************************************************************/
EXEC SQL DECLARE VPHONE TABLE
(LASTNAME VARCHAR(15) NOT NULL,
FIRSTNAME VARCHAR(12) NOT NULL,
MIDDLEINITIAL CHAR( 1) NOT NULL,
PHONENUMBER CHAR( 4) ,
EMPLOYEENUMBER CHAR( 6) NOT NULL,
DEPTNUMBER CHAR( 3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL);
/**********************************************************************/
/* Structure for pphone record */
/**********************************************************************/
/* Note: since the sample program data does not contain imbedded */
/* nulls, the C language null terminated string can be used to */
/* receive the varchar fields from DB2. */
EXEC SQL BEGIN DECLARE SECTION;
struct {
char lastname[16];
char firstname[13];
char middleinitial[2];
char phonenumber[5];
char employeenumber[7];
char deptnumber[4];
char deptname[37];
} pphone;
EXEC SQL END DECLARE SECTION;
/**********************************************************************/
/* SQL declaration for view VEMPLP (used for update processing) */
/**********************************************************************/
EXEC SQL DECLARE VEMPLP TABLE
(EMPLOYEENUMBER CHAR( 6) NOT NULL,
PHONENUMBER CHAR( 4) );
/**********************************************************************/
/* Structure for pemplp record */
/**********************************************************************/
EXEC SQL BEGIN DECLARE SECTION;
struct {
char employeenumber[7];
char phonenumber[5];
} pemplp;
EXEC SQL END DECLARE SECTION;
/**********************************************************************/
/* SQL cursors */
/**********************************************************************/
/* cursor to list all employee names */
EXEC SQL DECLARE TELE1 CURSOR FOR
SELECT *
FROM VPHONE;
/* cursor to list all employee names with a pattern */
/* (%) or (_) in last name */
EXEC SQL DECLARE TELE2 CURSOR FOR
SELECT *
FROM VPHONE
WHERE LASTNAME LIKE :lname;
/* cursor to list all employees with a specific last name */
EXEC SQL DECLARE TELE3 CURSOR FOR
SELECT *
FROM VPHONE
WHERE LASTNAME = :slname
AND FIRSTNAME LIKE :fname;
/**********************************************************************/
/* SQL return code handling */
/**********************************************************************/
EXEC SQL WHENEVER SQLERROR GOTO DBERROR;
EXEC SQL WHENEVER SQLWARNING GOTO DBERROR;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
/**********************************************************************/
/* main program routine */
/**********************************************************************/
extern main()
{
/* Open the input and output files */
cardin = fopen("DD:CARDIN","r,recfm=FB,lrecl=80,blksize=80"); /*@51*/
report = fopen("DD:REPORT","w");
/* While more input, process */
while (!feof(cardin))
{
/* Read the next request */
if (fscanf(cardin, fmt4,
ioarea.action,
ioarea.lname,
ioarea.fname,
ioarea.eno,
ioarea.newno,
trail) == 6)
{
/* Display the request */
DSN8MDG(module, "000I", outmsg);
fprintf(report, fmt5,
outmsg,
rpthdr0,
ioarea.action,
ioarea.lname,
ioarea.fname,
ioarea.eno,
ioarea.newno);
Do_req();
}
} /* endwhile */
fclose(report);
} /* end main */
/**********************************************************************/
/* Process the current request */
/**********************************************************************/
Do_req()
{
char *blankloc; /* string translation pointer */
strcpy(slname, ioarea.lname); /* save untranslated last name */
while (blankloc = strpbrk(ioarea.lname, " "))
*blankloc = '%'; /* translate blanks into % */
while (blankloc = strpbrk(ioarea.fname, " "))
*blankloc = '%'; /* translate blanks into % */
/* Determine request type */
switch (ioarea.action[0])
{
/* Process LIST request */
case 'L':
/* Print the report headings */
fprintf(report, fmt1, rpthdr1);
fprintf(report, fmt2, rpthdr2);
/* List all employees */
if (!strcmp(slname,"* ")){
EXEC SQL OPEN TELE1;
EXEC SQL FETCH TELE1 INTO :pphone;
if (sqlca.sqlcode == NOTFOUND){ /* If no employees */
DSN8MDG(module, "008I", outmsg); /* found, display */
fprintf(report, " %s\n", outmsg); /* error message */
} /* endif */
while (sqlca.sqlcode == 0){
Prt_row();
EXEC SQL FETCH TELE1 INTO :pphone;
} /* endwhile */
EXEC SQL CLOSE TELE1;
/* List generic employees */
} else {
if (strpbrk(slname, "%")) {
EXEC SQL OPEN TELE2;
EXEC SQL FETCH TELE2 INTO :pphone;
if (sqlca.sqlcode == NOTFOUND){ /* If no employees */
DSN8MDG(module, "008I", outmsg); /* found, display */
fprintf(report, " %s\n", outmsg); /* error message */
} else {
while (sqlca.sqlcode == 0){
Prt_row();
EXEC SQL FETCH TELE2 INTO :pphone;
} /* endwhile */
} /* endif */
EXEC SQL CLOSE TELE2;
/* List specific employee */
} else {
EXEC SQL OPEN TELE3;
EXEC SQL FETCH TELE3 INTO :pphone;
if (sqlca.sqlcode == NOTFOUND){ /* If no employee */
DSN8MDG(module, "008I", outmsg); /* found, display */
fprintf(report, " %s\n", outmsg); /* error message */
} else {
while (sqlca.sqlcode == 0){
Prt_row();
EXEC SQL FETCH TELE3 INTO :pphone;
} /* endwhile */
} /* endif */
EXEC SQL CLOSE TELE3;
} /* endif */
} /* endif */
break; /* end of 'L' request */
/* Update an employee phone number */
case 'U':
EXEC SQL UPDATE VEMPLP
SET PHONENUMBER = :ioarea.newno
WHERE EMPLOYEENUMBER = :ioarea.eno;
if (sqlca.sqlcode == 0){ /* If employee */
DSN8MDG(module, "004I", outmsg); /* updated, display */
fprintf(report, " %s\n", outmsg); /* confirmation msg */
} else {
DSN8MDG(module, "007E", outmsg); /* otherwise, display*/
fprintf(report, " %s\n", outmsg); /* error message */
} /* endif */
break;
/* Invalid request type */
default:
DSN8MDG(module, "068E", outmsg); /* Display error msg */
fprintf(report, " %s\n", outmsg);
} /* endswitch */
return;
DBERROR:
Sql_err();
} /* end Do_req */
/**********************************************************************/
/* Print a single employee on the report */
/**********************************************************************/
Prt_row()
{
fprintf(report, fmt3, pphone.lastname,
pphone.firstname,
pphone.middleinitial,
pphone.phonenumber,
pphone.employeenumber,
pphone.deptnumber,
pphone.deptname);
}
/**********************************************************************/
/* SQL error handler */
/**********************************************************************/
#pragma linkage(dsntiar, OS)
Sql_err() {
#define data_len 120
#define data_dim 10
struct error_struct {
short int error_len;
char error_text[data_dim][data_len];
} error_message = {data_dim * data_len};
extern short int dsntiar(struct sqlca *sqlca,
struct error_struct *msg,
int *len);
short int rc;
int i;
static int lrecl = data_len;
DSN8MDG(module, "060E", outmsg);
fprintf(report, " %s %i\n", outmsg, sqlca.sqlcode);
rc = dsntiar(&sqlca, &error_message, &lrecl); /* Format the sqlca */
if (rc == 0){ /* Print formatted */
for (i=0;i<=7;i++){ /* sqlca */
fprintf(report, "%.120s\n", error_message.error_text [i]);
} /* endfor */
} else {
DSN8MDG(module, "075E", outmsg);
fprintf(report, " %s %hi\n", outmsg, rc);
} /* endif */
/* Attempt to rollback any work already done */
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL ROLLBACK;
if (sqlca.sqlcode == 0){ /* If rollback */
DSN8MDG(module, "053I", outmsg); /* completed, display*/
fprintf(report, " %s\n", outmsg); /* confirmation msg */
} else { /* otherwise, display*/
DSN8MDG(module, "061E", outmsg); /* error message */
fprintf(report, " %s %i\n", outmsg, sqlca.sqlcode);
} /* endif */
fclose(report);
exit(0);
} /* end of Sql_err */