6. Embedded SQL for BASIC : Sample Applications : The Department-Employee Master/Detail Application
 
Share this page                  
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 1985, 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. Both tables are described in declare table statements at the start of the program. The cursors retrieve all the information in the tables, some of which are 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, 1985.
Each row that is scanned from both the Department table and the Employee table is recorded in an 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.
For readability, the BASIC exclamation point (!) is used as an end-of-line comment indicator.
Sample Application
10 !
! Program: Process_Expenses
! Purpose: Main entry point to process department and employee expenses.
!
        exec sql include sqlca
        ! The department table
        exec sql declare dept table     &
            (name      char(12) not null,             &
             totsales      money not null,            &
             employees     smallint not null)
        ! The employee table
        exec sql declare employee 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)
        ! "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)
      print 'Entering application to process expenses.'
      open "expenses.log" for output as file #1
      call Init_Db
      call Process_Depts
      call End_Db
      close #1
      print 'Successful completion of application.'
end                             ! of Process_Expenses
 
!
! Subroutine: 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.
!
100 sub Init_Db
    exec sql include sqlca
    exec sql whenever sqlerror stop
    exec sql connect personnel
        print #1, 'Creating "To_Be_Resolved" table.'
        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)
    end sub ! of Init_Db
 
!
! Subroutine: End_Db
! Purpose: Commit the multi-statement transaction and disconnect
! from the database.
! Parameters: None.
!
200 sub End_Db
        exec sql include sqlca
        exec sql commit
        exec sql disconnect
    end sub                          ! of End_Db
 
!
! Subroutine: Process_Depts
! Purpose: Scan through all the departments, processing each one.
! If the department has made less than $50,000 in sales
! then the department is dissolved. For each department,
! process all the employees (they may even be moved to another table).
! If an employee was terminated, then update the department's employee
! counter.
! Parameters: None
!
300 sub Process_Depts
        exec sql include sqlca
        exec sql begin declare section
        record department
            string     dname = 12
            real     totsales
            word     employees
        end record
        declare department     dept
        declare word         emps_term ! Employees terminated
        declare string         loc_dname ! For parameter passing
    exec sql end declare section
 
! Minimum sales of department
declare real constant MIN_DEPT_SALES = 50000.00
! Was the dept deleted?
declare byte deleted_dept             ! Was the dept declared?
declare string 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 CloseDCsr                     ! Close deptcsr
    exec sql open deptcsr
    while (sqlcode = 0)
        exec sql fetch deptcsr into :dept
        ! Did the department reach minimum sales?
        if (dept::totsales \ MIN_DEPT_SALES) then
            exec sql delete from dept                 &
                where current of deptcsr
                    deleted_dept = 1
                    dept_format = ' -- DISSOLVED --'
            else
                    deleted_dept = 0
                    dept_format = ' '
            end if
        ! Log what we have just done
        print #1, 'Department: ' + (dept::dname)         &
            + ', Total Sales: ';
        print #1 using '$$####.##', dept::totsales;
        print #1, dept_format
        ! Now process each employee in the department
        loc_dname = dept::dname
        call Process_Employees(loc_dname, deleted_dept, emps_term)
        ! If some employees were terminated, record this fact
        if (emps_term > 0 and deleted_dept = 0) then
                exec sql update dept &
                    set employees = :dept::employees - :emps_term &
                    where current of deptcsr
        end if
    next
        exec sql whenever not found continue
        CloseDCsr: EXEC SQL CLOSE deptcsr
        end sub ! of Process_Depts
        !
        ! Subroutine: 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 1985 then the
        ! employee is terminated.
        ! 2. If the employee's yearly salary is more than the
        ! minimum company wage of $14,000 and the employee
        ! is not close to retirement (over 58 years of
         ! age), then 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:    loc_dname  -     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.
        !
400 sub Process_Employees(string loc_dname, byte deleted_dept, &
                     integer emps_term)
        exec sql include sqlca
        exec sql begin declare section
            record employee ! Corresponds to "employee" table
                string         ename = 20
                word         age
                long         idno
                string         hired = 25
                real         salary
                long         hired_since_85
            end record
            declare employee emp
             declare real constant SALARY_REDUC = 0.95
        exec sql end declare section
        ! Minimum employee salary
       declare real constant         MIN_EMP_SALARY = 14000.00
       declare integer constant     NEARLY_RETIRED = 58
       declare string title                 ! Formatting values
       declare string description
        ! Note the use of the Ingres function to find out who was hired
        ! since 1985.
        exec sql declare empcsr cursor for                         &
            select name, age, idno, hired, salary,                &
                int4(interval('days', hired-date('01-jan-1985'))) &
            from employee &
            where dept = :loc_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 CloseECsr ! Close empcsr
        exec sql open empcsr
        emps_term = 0
            while (sqlcode = 0)
                exec sql fetch empcsr into :emp
                if (emp::hired_since_85 > 0) then
                    exec sql delete from employee                &
                     where current of empcsr
                    title = 'Terminated: '
                    description = 'Reason: Hired since 85.'
                    emps_term = emps_term +1
            else
                if (emp::salary > MIN_EMP_SALARY) then
                    ! Reduce salary if not nearly retired
                    if (emp::age < NEARLY_RETIRED) then
                        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.'
                end if
            else
                ! Leave employee alone
                title = 'No Changes: '
                description = 'Reason: Salary.'
            end if
        end if
        ! Was employee's department dissolved?
        if (deleted_dept = 1) then
            exec sql insert into toberesolved                  &
                select *                                       &
                from employee                                  &
                where idno = :emp::idno
            exec sql delete from employee                      &
                where current of empcsr
        end if
         ! Log the employee's information
         print #1, ' ' + title;
        print #1, str$(emp::idno);
        print #1, ', ' + (emp::ename) + ', ';
        print #1, str$(emp::age) + ', ';
        print #1 using '$$####.##', emp::salary;
        print #1, '; ' + description
    next
     exec sql whenever not found continue
     CloseEcsr:     exec sql close empcsr
    end sub ! of Process_Employees
    !
    ! Subroutine: Close_Down
    ! Purpose: Error handler called any time after Init_Db was 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 too.
    ! Parameters: None
    !
500 sub Close_Down
        exec sql include sqlca
        exec sql begin declare section
            declare string errbuf
        exec sql end declare section
        exec sql whenever sqlerror continue ! Turn off error handling
        exec sql inquire_sql(:errbuf = errortext)
        print 'Closed down because of database error:'
        print errbuf
        close #1
        exec sql rollback
        exec sql disconnect
        stop
    end sub                     ! of Close_Down