DSN8BECL
The class emp_db2 queries or updates rows in tables EMP and DEPT using views VPHONE and VEMPL .
/*********************************************************************/
/* */
/* Module name = DSN8BD3 */
/* */
/* Descriptive name = DB2 SAMPLE APPLICATION */
/* CLASS DEFINITION FOR */
/* BATCH */
/* C++ LANGUAGE */
/* */
/* Licensed Materials - Property of IBM */
/* 5635-DB2 */
/* (C) COPYRIGHT 1982, 2006 IBM Corp. All Rights Reserved. */
/* */
/* STATUS = Version 9 */
/* */
/* Function = The class emp_db2 queries or updates rows in tables */
/* EMP and DEPT using views VPHONE and VEMPL */
/* */
/* Module type = C++ class definition */
/* Processor = DB2 precompiler, C++ compiler */
/* */
/* Input = symbolic label/name = VPHONE */
/* description = VIEW OF TELEPHONE TABLE: PHONE */
/* */
/* Output = symbolic label/name = VEMPLP */
/* description = VIEW OF EMPLOYEE INFORMATION */
/* */
/* Error-messages = */
/* 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: */
/* DSN8075E - MESSAGE FORMAT ROUTINE ERROR, */
/* RETURN CODE IS: */
/* */
/* External references = */
/* Routines/services = */
/* DSN8MDG - error message formatting */
/* DSNTIAR - translate sqlca into messages */
/* */
/* Data-areas = none */
/* */
/* Control-blocks = */
/* SQLCA - sql communication area */
/* */
/* Tables = */
/* EMP, DEPT */
/* */
/* Change-activity */
/* 02/05/96 Katja KFD0024 C++ sample (D9031) */
/* Created based on C sample */
/* 10/17/05 BCM PK13582 Fix error handling bugs @01*/
/* */
/*********************************************************************/
/*********************************************************************/
/* Include C library definitions */
/*********************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h> /*@01*/
/*********************************************************************/
/* General declarations */
/*********************************************************************/
#define NOTFOUND 100
#define data_len 120
#define data_dim 10
/*********************************************************************/
/* SQL communication area */
/*********************************************************************/
EXEC SQL INCLUDE SQLCA;
/*********************************************************************/
/* Define message routine and DB2 error handler */
/*********************************************************************/
char module[8] = "DSN8BD3"; /* module name for message rtn */
char outmsg[70]; /* error/information msg buffer */
extern "C" void DSN8MDG (char *, char *, char *);
struct error_struct
{
short int error_len;
char error_text[data_dim][data_len];
} error_message = {data_dim * data_len};
extern "OS" short int dsntiar(struct sqlca *,
struct error_struct *,
int *);
/*********************************************************************/
/* Declare class to process records in a file, querying and updating */
/* employee information and phone number tables. */
/*********************************************************************/
class emp_db2 {
private:
EXEC SQL BEGIN DECLARE SECTION;
/*******************************************************************/
/* 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. */
/*******************************************************************/
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 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);
/*****************************************************************/
/* SQL declaration for view VEMPLP (used for update processing) */
/*****************************************************************/
EXEC SQL DECLARE VEMPLP TABLE
(EMPLOYEENUMBER CHAR( 6) NOT NULL,
PHONENUMBER CHAR( 4) );
/*****************************************************************/
/* SQL return code handling */
/*****************************************************************/
EXEC SQL WHENEVER SQLERROR GOTO DBERROR;
EXEC SQL WHENEVER SQLWARNING GOTO DBERROR;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
public:
void Listall(FILE *outfile);
void Listsome(FILE *outfile, char *lname);
void Listone(FILE *outfile, char *lname, char *fname);
void Empupdate(FILE *outfile, char *pno, char *eno);
private:
/*******************************************************************/
/* Member function: Print a single employee on the report */
/*******************************************************************/
void Prt_row(FILE *outfile)
{
static char fmt3[] = " %-16s%-16s%-5s%-7s%-9s%-5s%-36s\n";
fprintf(outfile, fmt3, pphone.lastname,
pphone.firstname,
pphone.middleinitial,
pphone.phonenumber,
pphone.employeenumber,
pphone.deptnumber,
pphone.deptname);
return;
} /* end Prt_row */
/*******************************************************************/
/* Member function: SQL error handler */
/*******************************************************************/
void Sql_err(FILE *outfile)
{
short int rc;
int i;
static int lrecl = data_len;
DSN8MDG(module, "060E", outmsg);
fprintf(outfile, " %s %i\n", outmsg, sqlca.sqlcode);
/* Format the sqlca */
rc = dsntiar(&sqlca, &error_message, &lrecl);
if (rc == 0) /* Print formatted sqlca */
{
for (i=0;i<=7;i++)
{
fprintf(outfile, "%.120s\n", error_message.error_text [i]);
} /* endfor */
} /* endif */
else
{
DSN8MDG(module, "075E", outmsg);
fprintf(outfile, " %s %hi\n", outmsg, rc);
} /* end else */
/* 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(outfile, " %s\n", outmsg); /* confirmation msg */
} /* endif */
else
{ /* otherwise, display*/
DSN8MDG(module, "061E", outmsg); /* error message */
fprintf(outfile, " %s %i\n", outmsg, sqlca.sqlcode);
} /* end else */
fclose(outfile);
exit(0);
} /* end Sql_err */
}; /* end emp_db2 class definition */
/****************************************************************@01*/
/* Restore SQL return code handling */
/********************************************************************/
EXEC SQL WHENEVER SQLERROR GOTO DBERROR;
EXEC SQL WHENEVER SQLWARNING GOTO DBERROR;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
/*******************************************************************/
/* Member function: List all employees in the VPHONE table */
/*******************************************************************/
void emp_db2::Listall(FILE *outfile)
{
/* cursor to list all employee names */
EXEC SQL DECLARE TELE1 CURSOR FOR
SELECT *
FROM VPHONE;
EXEC SQL OPEN TELE1;
EXEC SQL FETCH TELE1 INTO :pphone;
if (sqlca.sqlcode == NOTFOUND) /* If no employees */
{
DSN8MDG(module, "008I", outmsg); /* found, display */
fprintf(outfile, " %s\n", outmsg); /* error message */
} /* endif */
while (sqlca.sqlcode == 0)
{
Prt_row(outfile);
EXEC SQL FETCH TELE1 INTO :pphone;
} /* endwhile */
EXEC SQL CLOSE TELE1;
return;
DBERROR:
Sql_err(outfile);
} /* end Listall */
/*******************************************************************/
/* Member function: List employees selected from the VPHONE table */
/* using a pattern. */
/*******************************************************************/
void emp_db2::Listsome(FILE *outfile, char *lname)
{
EXEC SQL BEGIN DECLARE SECTION;
char lastname[16];
EXEC SQL END DECLARE SECTION;
strcpy(lastname,lname);
/* 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 :lastname;
EXEC SQL OPEN TELE2;
EXEC SQL FETCH TELE2 INTO :pphone;
if (sqlca.sqlcode == NOTFOUND) /* If no employees */
{
DSN8MDG(module, "008I", outmsg); /* found, display */
fprintf(outfile, " %s\n", outmsg); /* error message */
}
else
{
while (sqlca.sqlcode == 0)
{
Prt_row(outfile);
EXEC SQL FETCH TELE2 INTO :pphone;
} /* endwhile */
} /* endif */
EXEC SQL CLOSE TELE2;
return;
DBERROR:
Sql_err(outfile);
} /* end Listsome */
/*******************************************************************/
/* Member function: List employees selected from the VPHONE table */
/* using a specific last name and a pattern for */
/* the first name. */
/*******************************************************************/
void emp_db2::Listone(FILE *outfile, char *lname, char *fname)
{
EXEC SQL BEGIN DECLARE SECTION;
char lastname[16], firstname[13];
EXEC SQL END DECLARE SECTION;
strcpy(lastname, lname);
strcpy(firstname, fname);
/* cursor to list all employees with a specific last name */
EXEC SQL DECLARE TELE3 CURSOR FOR
SELECT *
FROM VPHONE
WHERE LASTNAME = :lastname
AND FIRSTNAME LIKE :firstname;
EXEC SQL OPEN TELE3;
EXEC SQL FETCH TELE3 INTO :pphone;
if (sqlca.sqlcode == NOTFOUND) /* If no employee */
{
DSN8MDG(module, "008I", outmsg); /* found, display */
fprintf(outfile, " %s\n", outmsg); /* error message */
}
else
{
while (sqlca.sqlcode == 0)
{
Prt_row(outfile);
EXEC SQL FETCH TELE3 INTO :pphone;
} /* endwhile */
} /* else - employee found */
EXEC SQL CLOSE TELE3;
return;
DBERROR:
Sql_err(outfile);
} /* end Listone */
/*******************************************************************/
/* Member function: Update the phone number of an employee given */
/* the employee number. */
/*******************************************************************/
void emp_db2::Empupdate(FILE *outfile, char *pno, char *eno)
{
EXEC SQL BEGIN DECLARE SECTION;
char phoneno[5], emplno[7];
EXEC SQL END DECLARE SECTION;
strcpy(phoneno, pno);
strcpy(emplno, eno);
EXEC SQL UPDATE VEMPLP
SET PHONENUMBER = :phoneno
WHERE EMPLOYEENUMBER = :emplno;
if (sqlca.sqlcode == 0) /* If employee */
{ /* updated, display */
DSN8MDG(module, "004I", outmsg); /* confirmation msg */
fprintf(outfile, " %s\n", outmsg);
} /* endif */
else
{
DSN8MDG(module, "007E", outmsg); /* otherwise, display*/
fprintf(outfile, " %s\n", outmsg); /* error message */
} /* else */
return;
DBERROR:
Sql_err(outfile);
} /* end Empupdate */