gtpa3m0wApplication Requester User's Guide

Displaying Entries in the Telephone Directory

The program listed in Figure 25 displays an employee entry in the corporate telephone directory.

The format of the parameter is:

rc /D last_name[%][/first_name]

where:

rc
A routing code. This code is used to route messages to the application and is not used in the example of parsing the entry.

D
Display action code.

last_name
The last name of the employee entry to be displayed.

%
The % is an optional parameter used to retrieve information sharing common characteristics, such as similar names. See the examples that follow.

first_name
The first name of the employee entry to be displayed.

Notes:

  1. The / character separates the different parameters.

  2. The % can only be used when the first_name parameter has been omitted.

  3. The [ and ] characters denote an optional parameter.

For example, to display the entry for Takao Chiba, you would type:

     rc /D CHIBA/TAKAO

To display the entries for everyone who has a last name that begins with S, you would type:

     rc /D S%

To display the entries for everyone in the database, you would type:

     rc /D %

Figure 25. TPF Program to Display a Specific Entry in the PHONE_DIRECTORY Table

#include <tpfeq.h>                /* Include libraries                */
#include <tpfapi.h>
#include <tpfarapi.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
 
/**********************************************************************/
/*                                                                    */
/*  Declare the internal and external functions.                      */
/*                                                                    */
/**********************************************************************/
static void Check();
#pragma map(qxp4_display,"QXP4")
extern void qxp4_display();
 
EXEC SQL INCLUDE SQLCA;           /* Include the SQLCA                */
 
 
  /********************************************************************/
  /* Check:                                                           */
  /*    This function verifies the SQLSTATE returned.  If it is not 0,*/
  /* a message is printed indicating what the SQLSTATE returned was.  */
  /*                                                                  */
  /* A customer implementing a function like this may want to include */
  /* a much more robust error handling and recovery.                  */
  /********************************************************************/
 
static void Check()
{
  if (memcmp(sqlca.sqlstate,"00000",sizeof(sqlca.sqlstate))!= 0) {
    printf("FAILED\n %d %.5s\n",sqlca.sqlcode,sqlca.sqlstate);
    exit(5);
  }
}
 
 
 /********************************************************************/
 /* qxp4_display:                                                    */
 /* This routine will display the data asked for from DB2. It will   */
 /* parse the rest of the message and issue a query depending on     */
 /* what information was passed.                                     */
 /********************************************************************/
void qxp4_display()
{
  short int num_input;           /* The number of variables sscanf   */
                                 /* has correctly set up.  This is   */
                                 /* used to check the validity of    */
                                 /* the parameters.                  */
  struct mi0mi *blk;             /* Pointer to the input message     */
 
 
 /********************************************************************/
 /* Declare all the variables that SQL needs to know about.          */
 /********************************************************************/
 
 
  EXEC SQL BEGIN DECLARE SECTION;
 
 /********************************************************************/
 /*  Set up a structure for the directory record.  This structure    */
 /*  is set up in the same order as the CREATE TABLE parameters      */
 /*  were when the table was created on DB2.                         */
 /********************************************************************/
    struct {
      char last_name[18];
      char first_name[9];
      char middle_initial[2];
      char country_code[5];
      char area_code[6];
      char phone_number[13];
      short int employee_number;
      char timestamp[27];
    } dir_record;
 
    char buf[16];                /* This will be used to point to   */
                                  /* the database to connect to.     */
  EXEC SQL END DECLARE SECTION;
 
/********************************************************************/
/* There are two possible cursors needed.  The first one is used    */
/* when only the last name has been given.  The second one is       */
/* used when the last name and first name are given.                */
/*                                                                  */
/********************************************************************/
 
 
/********************************************************************/
/* Declare a cursor D1 for use when only the last name is given.    */
/********************************************************************/
 
  EXEC SQL DECLARE D1 CURSOR FOR
       SELECT LAST_NAME,FIRST_NAME,
        PHONE_NUMBER, EMPLOYEE_NUMBER FROM TPFNET.PHONE_DIRECTORY
        WHERE LAST_NAME LIKE :dir_record.last_name
        ORDER BY LAST_NAME,FIRST_NAME;
 
/********************************************************************/
/* Declare a cursor D2 for use when the last name and the first     */
/* name are given.                                                  */
/********************************************************************/
 
  EXEC SQL DECLARE D2 CURSOR FOR
       SELECT *
        FROM TPFNET.PHONE_DIRECTORY
        WHERE LAST_NAME = :dir_record.last_name AND
              FIRST_NAME = :dir_record.first_name;
 
 
 /********************************************************************/
 /* Issue the connect with the name of the database to connect       */
 /* to.  When done, the check function will check the return code,   */
 /* and if invalid, exit.  Ebx000 was set up in the root segment with*/
 /* the name of the database to connect to.                          */
 /********************************************************************/
 
  strcpy(buf, &ecbptr()->ebx000);
  EXEC SQL
    CONNECT TO :buf;
  Check();
 
 
 /********************************************************************/
 /* Parse the message block.  The first parameter is unused, and     */
 /* the second was already parsed.  If there is a first name, it is  */
 /* separated from the last name by a /.                             */
 /*                                                                  */
 /********************************************************************/
  blk = ecbptr()->ce1cr0;
  num_input = sscanf(blk->mi0acc,
              "%*s /%*1c %17[^/]/%8s",
                         dir_record.last_name,
                                 dir_record.first_name);
 
 
 /********************************************************************/
 /*  Based on the number of successful conversions, we can tell      */
 /*  which query is requested.                                       */
 /********************************************************************/
 
  switch (num_input)
  {
     case 1:
     {
      /***************************************************************/
      /*                                                             */
      /*  Since we are using the LIKE attribute on the WHERE clause, */
      /*  we must append a "%" at the end of the last_name.          */
      /*                                                             */
      /***************************************************************/
 
      strcpy((strchr (dir_record.last_name,'\0')),"%");
 
      /***************************************************************/
      /* Only the last name is given.  Give back all the names for   */
      /* all the people with a last name like the one entered.       */
      /* The first thing that needs to be done is the cursor opened. */
      /***************************************************************/
       EXEC SQL OPEN D1;
       Check();
 
      /***************************************************************/
      /* Now that the cursor is opened, get the first row of data.   */
      /***************************************************************/
 
       EXEC SQL FETCH D1 INTO
          :dir_record.last_name,
          :dir_record.first_name,
          :dir_record.phone_number,
          :dir_record.employee_number;
 
 
      /***************************************************************/
      /* Before we call check, check for end of table.  If it is,    */
      /* there were no entries for this query, so respond back with  */
      /* this information.                                           */
      /***************************************************************/
 
       if (memcmp(sqlca.sqlstate,"02000",sizeof(sqlca.sqlstate))== 0)
       {
          printf("No entries found for name %s",dir_record.last_name);
       }
       else
       {
         short int count;         /* Count has the number of rows    */
                                  /* returned from the query.        */
 
         count = 0;               /* No rows printed yet.            */
         Check();                 /* Check the results of the first  */
                                  /* fetch.                          */
 
         printf(
         "Last Name         Fst Name Phone        ENum");
 
         /************************************************************/
         /* While there is more information, send it all back to the */
         /* person who issued the query.                             */
         /************************************************************/
 
         while (memcmp(sqlca.sqlstate,"00000",sizeof(sqlca.sqlstate))
               ==0)
         {
          printf("%s %s %s %hd\n",
                  dir_record.last_name,
                     dir_record.first_name,
                        dir_record.phone_number,
                           dir_record.employee_number);
         count++;
 
         /************************************************************/
         /* Get the next row of data from the query.                 */
         /************************************************************/
 
          EXEC SQL FETCH D1 INTO
          :dir_record.last_name,
          :dir_record.first_name,
          :dir_record.phone_number,
          :dir_record.employee_number;
 
         /************************************************************/
         /* Again, before we call check, we must see if this is the  */
         /* last record.                                             */
         /************************************************************/
 
          if (memcmp(sqlca.sqlstate,"02000",sizeof(sqlca.sqlstate))==0)
          {
            printf("%1.0d rows found.",
                    count);
          }
          else
          {
            Check();
          }
        }
       }
       break;
     }
     case 2:
     {
      /***************************************************************/
      /* The last name and the first name were given.   Return       */
      /* the entire record(s) of the person with this name.          */
      /* The first thing that needs to be done is the cursor opened. */
      /***************************************************************/
       EXEC SQL OPEN D2;
       Check();
 
      /***************************************************************/
      /* Now that the cursor is opened, get the first row of data.   */
      /***************************************************************/
 
       EXEC SQL FETCH D2 INTO
          :dir_record;
 
      /***************************************************************/
      /* Before we call check, check for end of table.  If it is,    */
      /* there were no entries for this query, so respond back with  */
      /* this information.                                           */
      /***************************************************************/
 
       if (memcmp(sqlca.sqlstate,"02000",sizeof(sqlca.sqlstate))== 0)
       {
          printf("No entries found for name %s",dir_record.last_name);
       }
       else
       {
         short int count;         /* Count has the number of rows    */
                                  /* returned from the query.        */
 
         count = 0;               /* No rows printed yet.            */
         Check();                 /* Check the results of the first  */
                                  /* fetch.                          */
 
         printf(
"Last Name         Fst Name MI Phone        Country Area  ENum");
 
         /************************************************************/
         /* While there is more information, send it all back to the */
         /* person who issued the query.                             */
         /************************************************************/
 
         while (memcmp(sqlca.sqlstate,"00000",sizeof(sqlca.sqlstate))
            ==0)
         {
          printf(
          "%s %s %s  %s %s    %s %hd\n",
           dir_record.last_name,
              dir_record.first_name,
                 dir_record.middle_initial,
                     dir_record.phone_number,
                        dir_record.country_code,
                              dir_record.area_code,
                                 dir_record.employee_number);
         count++;
 
         /************************************************************/
         /* Get the next row of data from the query.                 */
         /************************************************************/
 
          EXEC SQL FETCH D2 INTO
          :dir_record;
 
 
         /************************************************************/
         /* Again, before we call check, we must see if this is the  */
         /* last record.                                             */
         /************************************************************/
 
          if (memcmp(sqlca.sqlstate,"02000",sizeof(sqlca.sqlstate))==0)
          {
            printf("%1.0d rows found.",
                    count);
          }
          else
          {
            Check();
          }
        }
       }
       break;
     }
     default:
       printf("Invalid input for display option.  Please re-enter\n");
  }
  exit(0);
}