6. Embedded QUEL for BASIC : Sample Applications : The Employee Query Interactive Forms Application
 
Share this page                  
The Employee Query Interactive Forms Application
This EQUEL/FORMS application uses a form in query mode to view a subset of the Employee table in the Personnel database. An Ingres query qualification is built at runtime using values entered in fields of the form "empform."
The objects used in this application are:
Object
Description
personnel
The program's database environment.
employee
A table in the database, with six columns:
name (c20)
age (i1)
idno (i4)
hired (date)
dept (c10)
salary (money)
empform
A VIFRED form with fields corresponding in name and type to the columns in the Employee database table. The Name and Idno fields are used to build the query and are the only updatable fields. "Empform" is a compiled form.
The application is driven by a display statement that allows the runtime user to enter values in the two fields that build the query. The Build_Query and Exec_Query procedures make up the core of the query that is run as a result. Note the way the values of the query operators determine the logic used to build the where clause in Build_Query. The retrieve statement encloses a submenu block that allows the user to step through the results of the query.
No updates are performed on the values retrieved, but any particular employee screen may be saved in a log file through the printscreen statement.
The following create statement describes the format of the Employee database table:
     ##  create employee
     ##      (name        = c20,         ! Employee name
     ##       age         = i1,          ! Employee age 
     ##       idno        = i4,          ! Unique employee id
     ##       hired       = date,        ! Date of hire 
     ##       dept        = c10,         ! Employee department
     ##       salary      = money)       ! Annual salary
10   ##   declare ingres
     ##   external integer empform

          ! Program: Employee_Query

          ! Initialize global WHERE clause qualification buffer
          ! to be an Ingres default qualification that is always true.
     ##    common(globs) string where_clause = 100
           where_clause = '1=1'

     ##   forms
     ##   message "Accessing Employee Query Application . . ."
     ##   ingres personnel

     ##   range of e is employee

     ##       addform empform

     ##       display #empform query
     ##       initialize

     ##       activate menuitem "Reset"
     ##       {
     ##                 clear field all
     ##       }

     ##       activate menuitem "Query"
     ##       {
                 ! Verify validity of data
     ##            validate

                   call Build_Query
                   call Exec_Query
     ##       }

     ##       activate menuitem "LastQuery"
     ##       {
                     call Exec_Query
     ##       }

     ##       activate menuitem "End", frskey3
     ##       {
     ##                 breakdisplay
     ##       }
     ##       finalize

     ##       clear screen
     ##       endforms
     ##       exit

     ##       end                     ! main program

     ! Procedure: Build_Query
     ! Purpose:   Build an Ingres query from the values in the
     !            "name" and "idno" fields in "empform".
     ! Parameters:
     !            None
100  ##    sub Build_Query

               ! Global WHERE clause qualification buffer 
     ##        common(globs) string where_clause = 100

     ##        declare string ename, integer eidno

               ! Query operator table that maps integer values to
               ! string query operators.
     ##        dim string operators(6) 
               mat read operators
               data "= ", "!=", "< ", "> ", "<=", ">="
110            ! Operators corresponding to the two fields,
               ! that index into the "operators" table.
     ##        declare integer opername, operidno
     ##        getform #empform 
     ##            (ename = name, opername = getoper(name),
     ##             eidno = idno, operidno = getoper(idno))

               ! Fill in the WHERE clause
               if (opername = 0% and operidno = 0%) then

                    ! Default qualification
                    where_clause = '1=1'

               else 
                    if (opername = 0% and operidno <> 0%) then

                         ! Query on the "idno" field
                         where_clause = 'e.idno' +              &
                         operators(operidno) + str$(eidno)

                    else 
                         if (opername <> 0% and operidno = 0%) then

                              ! Query on the "name" field
                              where_clause = 'e.name' +         &
                              operators(opername) +             &
                              '"' + ename + '"'

                         else ! (opername <> 0% and operidno <> 0%)

                              ! Query on both fields
                              where_clause = 'e.name' +         &
                              operators(opername) +             &
                              '"' + ename + '" and '            &
                              + 'e.idno' + &
                              operators(operidno) +             &
                              str$(eidno)
                          end if

                    end if

               end if

     ##      end sub ! Build_Query
          ! Subroutine:     Exec_Query
          ! Purpose:      Given a query buffer, defining a WHERE clause
          !          issue a RETRIEVE to allow the runtime use to
          !           browse the employees found with the given
          !           qualification.
          ! Parameters:
           !          None

200  ##      sub Exec_Query

                    ! Global WHERE clause qualification buffer 
     ##             common(globs) string where_clause = 100

     ##             declare string       ename, ! Employee data
     ##             word                 eage,
     ##             integer              eidno,
     ##             string               ehired,
     ##             string               edept,
     ##             real                 esalary

                    declare byte rows ! Were rows found
                    rows = 0%

                    ! Issue query using WHERE clause
     ##             retrieve (
     ##                     ename = e.name, eage = e.age,
     ##                     eidno = e.idno, ehired = e.hired,
     ##                     edept = e.dept, esalary = e.salary)
     ##             where where_clause
     ## {
                         rows = 1%
                         ! Put values up and display them
     ##                  putform #empform (
     ##                  name = ename, age = eage,
     ##                  idno = eidno, hired = ehired,
     ##                  dept = edept, salary = esalary)
     ##                  redisplay

     ##                  submenu
     ##                  activate menuitem "Next" , frskey4
     ##                  {
                             ! Do nothing, and continue with the
                             ! RETRIEVE loop. The last one will
                             ! drop out.
     ##                  }
     ##                  activate menuitem "Save" , frskey8
     ##                  {
                             ! Save screen data in log file
     ##                      printscreen (file = "query.log")
                               ! Drop through to next employee
     ##                     }
     ##                     activate menuitem "End" , frskey3
     ##                     {
                               ! Terminate the RETRIEVE loop
     ##                        endretrieve
     ##                     }
     ##             }
                    if (rows = 0%) then
     ##             message "No rows found for this query"
               else
     ##             clear field all
     ##             message "No more rows. Reset for next query"
               end if

     ##        sleep 2

     ## end sub                     ! Exec_Query