7. Embedded QUEL for Pascal : 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 will 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 retrieved values, 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 }

## program Employees;
## type
##     String2 = packed array[1..2] of Char;
##     String10 = packed array[1..10] of Char;
##     String20 = packed array[1..20] of Char;
##     String25 = packed array[1..25] of Char;
##     VString100 = varying[100] of Char;
##     Float = Real;
##     Short_Integer = [Word] -32768 .. 32767;
## var
##    empform : [External] Integer;
## declare
## procedure Employee_Query;
## var
        {Global WHERE clause qualification buffer}
##      where_clause: VString100;

        {
        | Procedure: Build_Query
        | Purpose:   Build an Ingres query from the values in the
        |           "name" and "idno" fields in "empform".
        | Parameters:
        |            None
        }
##      procedure Build_Query;
##      type
##           opers = array[1..6] of String2;
##      var
##           ename: String20;
##           eidno: Integer;

            {
            | Query operator table that maps integer values to
            | string query operators.
            }
##          operators: opers;

            {
            | Operators corresponding to the two fields,
            | that index into the "operators" table.
            }
##          name_op, id_op: Integer;
##      begin {Build_Query}
            operators := opers ('= ', '!=', '< ', '> ', '<=', '>=');
##          getform #empform 
##            (ename = name, name_op = getoper(name),
##             eidno = idno, id_op = getoper(idno))

            {Fill in the WHERE clause}
            if ((name_op = 0) and (id_op = 0)) then
            begin
                where_clause := '1=1'; {Default qualification}
            end else if ((name_op = 0) and (id_op <> 0)) then
            begin
                {Query on the "idno" field}
                WriteV( where_clause,
                        'e.idno', operators[id_op],
                        eidno);
            end else if ((name_op <> 0) and (id_op = 0)) then
            begin
                {Query on the "name" field}
                where_clause :=
                        'e.name' + operators[name_op] +
                        '"' + ename + '"';
            end else { ((name_op <> 0) and (id_op <> 0)) }
            begin
                {Query on both fields}
                WriteV( where_clause,
                        'e.name', operators[name_op],
                        '"', ename, '" and ',
                        'e.idno', operators[id_op],
                        eidno);
            end;
##      end; {Build_Query}
        {
        | Procedure: 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
        }

##      procedure Exec_Query;
##      var
##           ename:     String20; {Employee data}
##           eage:      Short_Integer;
##           eidno:     Integer;
##           ehired:    String25;
##           edept:     String10;
##           esalary:   Float;
##           rows:      Integer; {Were rows found}
##      begin {Exec_Query}
              {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
##            begin {retrieve}
                    {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
##                      begin
                                 {
                                 | Do nothing, and continue with the
                                 | retrieve loop. The last one will
                                 | drop out.
                                 }
##                      end {Next}

##                      activate menuitem 'Save', frskey8
##                     begin
                               {Save screen data in log file}
##                             printscreen (file = 'query.log')
                               {Drop through to next employee}
##                     end {Save}

##                     activate menuitem 'End', frskey3
##                     begin
                               {Terminate the RETRIEVE loop}
##                             endretrieve
##                     end {End}
##              end {retrieve}
##              inquire_equel (rows = rowcount)
                if (rows = 0) then
                begin
##                      message 'No rows found for this query'
                end else
                begin
##                      clear field all
##                      message 'No more rows. Reset for next query'
                end;
##              sleep 2
##           end; {Exec_Query}
##      begin {Employee_Query}
##      forms
##      message 'Accessing Employee Query Application . . .'
##      ingres personnel

##      range of e is employee

##      addform empform

##      display #empform query
##      initialize

##      activate menuitem 'Reset'
##      begin
##          clear field all
##      end {Reset}

##      activate menuitem 'Query'
##      begin
            {Verify validity of data}
##          validate
            Build_Query;
            Exec_Query;
##       end {Query}
##      activate menuitem 'LastQuery'
##      begin
            Exec_Query;
##      end {LastQuery}

##      activate menuitem 'End'
##      begin
##            breakdisplay
##            end {End}
##      finalize

##      clear screen
##      endforms
##      exit
##   end; {Employee_Query};

##   begin {main}
         Employee_Query;
##   end. {main}