The Department-Employee Master/Detail Application
This application uses two database tables joined on a specific column. This typical example of a department and its employees demonstrates how to process two tables as a master and a detail.
The program scans through all the departments in a database table, in order to reduce expenses. Based on certain criteria, the program updates department and employee records. The conditions for updating the data are the following:
Departments
• If a department has made less than $50,000 in sales, the department is dissolved.
Employees
• If an employee was hired since the start of 1998, the employee is terminated.
• If the employee's yearly salary is more than the minimum company wage of $14,000 and the employee is not nearing retirement (over 58 years of age), the employee takes a 5% pay cut.
• If the employee's department is dissolved and the employee is not terminated, the employee is moved into a state of limbo to be resolved by a supervisor.
This program uses two cursors in a master/detail fashion. The first cursor is for the Department table, and the second cursor is for the Employee table. The declare table statements at the beginning of the program describe both tables. The cursors retrieve all the information in the tables, some of which is updated. The cursor for the Employee table also retrieves an integer date interval whose value is positive if the employee was hired after January 1, 1998. The tables contain no null values.
Each row that is scanned, from both the Department table and the Employee table, is recorded into the system output file. This file serves both as a log of the session and as a simplified report of the updates that were made.
Each section of code is commented for the purpose of the application and also to clarify some of the uses of the embedded SQL statements. The program illustrates table creation, multi-statement transactions, all cursor statements, direct updates, and error handling.
Note: The application uses function prototypes and ifdef statements to enable you to build it using either the ESQL/C or ESQL/C++ precompiler.
Sample Application
# include <stdio.h>
EXEC SQL INCLUDE SQLCA;
/* The department table */
EXEC SQL DECLARE dept TABLE
(name char(12) NOT NULL, /* Department name */
totsales money NOT NULL, /* Total sales */
employees smallint NOT NULL); /* Number of employees */
/* The employee table */
EXEC SQL DECLARE employee TABLE
(name char(20) NOT NULL, /* Employee name */
age integer1 NOT NULL, /* Employee age */
idno integer NOT NULL, /* Unique employee id */
hired date NOT NULL, /* Date of hire */
dept char(12) NOT NULL, /* Department of work */
salary money NOT NULL); /* Yearly salary */
/* "State-of-Limbo" for employees who lose their department */
EXEC SQL DECLARE toberesolved TABLE
(name char(20) NOT NULL,
age integer1 NOT NULL,
idno integer NOT NULL,
hired date NOT NULL,
dept char(12) NOT NULL,
salary money NOT NULL);
EXEC SQL BEGIN DECLARE SECTION;
# define MIN_DEPT_SALES 50000.00 /* Minimum sales of department */
# define MIN_EMP_SALARY 14000.00 /* Minimum employee salary */
# define NEARLY_RETIRED 58
# define SALARY_REDUC 0.95
EXEC SQL END DECLARE SECTION;
/*
** Function prototypes for C++ only so that this is compatible
** with old-style C compilers
*/
# ifdef __cplusplus
void Init_Db(void);
void End_Db(void);
void Process_Depts(void);
void Process_Employees( char *dept_name, short deleted_dept, short *emps_term );
void Close_Down(void);
# endif /* __cplusplus */
/*
** Procedure: MAIN
** Purpose: Main body of the application. Initialize the database,
** process each department and terminate the session.
** Parameters:
** None
*/main()
{
printf( "Entering application to process expenses.\n" );
Init_Db();
Process_Depts();
End_Db();
printf( "Successful completion of application.\n" );
}
/*
** Procedure: Init_Db
** Purpose: Initialize the database.
** Connect to the database, and abort if an error. Before
** processing employees, create the table for employees
** who lose their department, "toberesolved".
** Parameters:
** None
*/
# ifdef __cplusplus
void
Init_Db(void)
# else
Init_Db()
# endif /* __cplusplus */
{
EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL CONNECT personnel;
printf( "Creating \"To_Be_Resolved\" table.\n" );
EXEC SQL CREATE TABLE toberesolved
(name char(20) NOT NULL,
age integer1 NOT NULL,
idno integer NOT NULL,
hired date NOT NULL,
dept char(12) NOT NULL,
salary money NOT NULL);
}
/*
** Procedure: End_Db
** Purpose: Commit the multi-statement transaction and access
** to the database.
** Parameters:
** None
*/
# ifdef __cplusplus
void
End_Db(void)
# else
End_Db()
# endif /* __cplusplus */
{
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
}
/*
** Procedure: Process_Depts
** Purpose: Scan through all the departments, processing each one.
** If the department has made less than $50,000 in sales,
** the department is dissolved. For each department, process
** all employees (they may even be moved to another table).
** If an employee was terminated, then update the department's
** employee counter.
** Parameters:
** None
*/
# ifdef __cplusplus
void
Process_Depts(void)
# else
Process_Depts()
# endif /* __cplusplus */
{
EXEC SQL BEGIN DECLARE SECTION;
struct dept_ { /* Corresponds to the "dept" table */
char name[13];
double totsales;
short employees;
} dept;
short emps_term = 0; /* Employees terminated */
EXEC SQL END DECLARE SECTION;
short deleted_dept; /* Was the dept deleted? */
char *dept_format; /* Formatting value */
EXEC SQL DECLARE deptcsr CURSOR FOR
SELECT name, totsales, employees
FROM dept
FOR DIRECT UPDATE OF name, employees;
/* All errors from this point on close down the application */
EXEC SQL WHENEVER SQLERROR CALL Close_Down;
EXEC SQL WHENEVER NOT FOUND GOTO Close_Dept_Csr;
EXEC SQL OPEN deptcsr;
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH deptcsr INTO :dept;
/* Did the department reach minimum sales? */
if (dept.totsales < MIN_DEPT_SALES)
{
EXEC SQL DELETE FROM dept
WHERE CURRENT OF deptcsr;
deleted_dept = 1;
dept_format = " -- DISSOLVED --";
}
else
{
deleted_dept = 0;
dept_format = "";
}
/* Log what we have just done */
printf( "Department: %14s, Total Sales: %12.3f %s\n",
dept.name, dept.totsales, dept_format );
/* Now process each employee in the department */
Process_Employees( dept.name, deleted_dept, &emps_term );
/* If employees were terminated, record this fact */
if (emps_term > 0 && !deleted_dept)
{
EXEC SQL UPDATE dept
SET employees = :dept.employees - :emps_term
WHERE CURRENT OF deptcsr;
}
}
Close_Dept_Csr:
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL CLOSE deptcsr;
}
/*
** Procedure: Process_Employees
** Purpose: Scan through all the employees for a particular department.
** Based on given conditions the employee may be terminated or
** given a salary reduction:
** 1. If an employee was hired since 1998, the employee is
** terminated.
** 2. If the employee's yearly salary is more than minimum
** company wage of $14,000 and the employee is not close to
** retirement (over 58 years of age), the employee
** takes a 5% salary reduction.
** 3. If the employee's department is dissolved and the employee
** is not terminated, then the employee is moved into the
** "toberesolved" table.
** Parameters:
** dept_name - Name of current department.
** deleted_dept - Is current department being dissolved?
** emps_term - Set locally to record how many employees
** were terminated for the current department.
*/
# ifdef __cplusplus
void
Process_Employees( char *dept_name, short deleted_dept, short *emps_term )
# else
Process_Employees( dept_name, deleted_dept, emps_term
)
char *dept_name;
short deleted_dept;
short *emps_term;
# endif /* __cplusplus */
{
EXEC SQL BEGIN DECLARE SECTION;
struct emp_ { /* Corresponds to "employee" table */
char name[21];
short age;
int idno;
char hired[26];
float salary;
int hired_since_98;
} emp;
char *dname = dept_name;
EXEC SQL END DECLARE SECTION;
char *title; /* Formatting values */
char *description;
/*
** Note the use of the INGRES function to find out who has been
** hired since 1998.
*/
EXEC SQL DECLARE empcsr CURSOR FOR
SELECT name, age, idno, hired, salary,
int4(interval('days', hired-date('01-jan-1998')))
FROM employee
WHERE dept = :dname
FOR DIRECT UPDATE OF name, salary;
/* All errors from this point on close down the application */
EXEC SQL WHENEVER SQLERROR CALL Close_Down;
EXEC SQL WHENEVER NOT FOUND GOTO Close_Emp_Csr;
EXEC SQL OPEN empcsr;
*emps_term = 0; /* Record how many */
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH empcsr INTO :emp;
if (emp.hired_since_98 > 0)
{
EXEC SQL DELETE FROM employee
WHERE CURRENT OF empcsr;
title = "Terminated:";
description = "Reason: Hired since 1998.";
(*emps_term)++;
}
else
{
/* Reduce salary if not nearly retired */
if (emp.salary > MIN_EMP_SALARY)
{
if (emp.age < NEARLY_RETIRED)
{
EXEC SQL UPDATE employee
SET salary = salary * :SALARY_REDUC
WHERE CURRENT OF empcsr;
title = "Reduction: ";
description = "Reason: Salary.";
}
else
{
/* Do not reduce salary */
title = "No Changes:";
description = "Reason: Retiring.";
}
}
else /* Leave employee alone */
{
title = "No Changes:";
description = "Reason: Salary.";
}
/* Was employee's department dissolved? */
if (deleted_dept)
{
EXEC SQL INSERT INTO toberesolved
SELECT *
FROM employee
WHERE idno = :emp.idno;
EXEC SQL DELETE FROM employee
WHERE CURRENT OF empcsr;
}
}
/* Log the employee's information */
printf( " %s %6d, %20s, %2d, %8.2f; %s\n",
title, emp.idno, emp.name, emp.age, emp.salary,
description );
}
Close_Emp_Csr:
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL CLOSE empcsr;
}
/*
** Procedure: Close_Down
** Purpose: Error handler called any time after Init_Db has been
** successfully completed. In all cases, print the cause of
** the error and abort the transaction, backing out changes.
** Note that disconnecting from the database will implicitly
** close any open cursors.
** Parameters:
** None
*/
# ifdef __cplusplus
void
Close_Down(void)
# else
Close_Down()
# endif /* __cplusplus */
{
EXEC SQL BEGIN DECLARE SECTION;
char errbuf[101];
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR CONTINUE; /* Turn off error handling */
EXEC SQL INQUIRE_INGRES (:errbuf = ERRORTEXT);
printf( "Closing Down because of database error:\n" );
printf( "%s\n", errbuf );
EXEC SQL ROLLBACK;
EXEC SQL DISCONNECT;
exit( -1 );
}