gtpa3m0vApplication Requester User's Guide

Updating a Telephone Directory Entry

The program listed in Figure 24 updates an employee's entry in the corporate telephone directory.

The format of the parameters is:

rc /U field_to_update/employee_number/new_value

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.

U
Update action code.

field_to_update
One of the following employee entry fields to be updated:

L
Last name

F
First name

M
Middle initial

C
Country code

A
Area code

P
Phone number

employee_number
The employee number of the entry to be updated.

new_value
The new value for the field indicated in the field_to_update parameter.
Note:
The / character separates the different parameters.

For example, to update the country in the PHONE_DIRECTORY table entry for Mary Stewart, you would type:

     rc /U C/3/1

Figure 24. TPF Program to Update 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(qxp3_update,"QXP3")
extern void qxp3_update();
 
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);
  }
}
 
 
/********************************************************************/
/* This routine updates the employee record requested.              */
/********************************************************************/
void qxp3_update()
{
  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   */
  char field;                      /* The field in the record that   */
                                   /* is being updated.              */
  char change[18];                /* The new value needed for the   */
                                   /* field.  The largest value is   */
                                   /* 18 for the last name.          */
 
 /********************************************************************/
 /* 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.  Two copies of this     */
 /*  structure are needed, one for what the new record looks like,   */
 /*  and one for what the old record looked like.                    */
 /********************************************************************/
    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;
 
   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_up_record;
 
    char buf[16];                 /* This will be used to point to   */
                                  /* the database to connect to.     */
    char column[10];
  EXEC SQL END DECLARE SECTION;
 
  EXEC SQL DECLARE U1 CURSOR FOR
        SELECT *
         FROM TPFNET.PHONE_DIRECTORY
         WHERE EMPLOYEE_NUMBER = :dir_record.employee_number
         FOR UPDATE OF LAST_NAME, FIRST_NAME, MIDDLE_INITIAL,
         COUNTRY_CODE, AREA_CODE, PHONE_NUMBER, TIME_STAMP;
 
 
 
 /********************************************************************/
 /* Issue a connect with the name of the database to connect         */
 /* to.  When done, the check function will check the return code,   */
 /* and if invalid, exit.                                            */
 /********************************************************************/
 
  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.  The third parameter is the field */
 /* to be updated, the fourth, the employee number to be updated,    */
 /* and the last, the new value of the field.                        */
 /*                                                                  */
 /********************************************************************/
  blk = ecbptr()->ce1cr0;
  num_input = sscanf(blk->mi0acc,
   "%*s /%*1c %1c/%hd/%s",
                &field,
                   &dir_record.employee_number,
                      change);
 
 /********************************************************************/
 /* All parameters are required.  If any are missing, then tell      */
 /* the user and exit.                                               */
 /*                                                                  */
 /********************************************************************/
 
  if (num_input != 3)
  {
      printf("The input is invalid.  The format is:\n\
Field to change/employee number/new field");
      exit(0);
  }
 
  /***************************************************************/
  /* Open the cursor for input.                                  */
  /***************************************************************/
  EXEC SQL OPEN U1;
  Check();
 
 
 /*********************************************************************/
 /* Find the record that matches this request.                        */
 /*                                                                   */
 /*********************************************************************/
 
 
  EXEC SQL
        FETCH U1
         INTO :dir_record;
 
  if (memcmp(sqlca.sqlstate,"02000",sizeof(sqlca.sqlstate)) == 0)
  {
     printf("Employee Number %d not found.",dir_record.employee_number);
     exit(0);
  }
  Check();
 
 
 /*********************************************************************/
 /* Copy the request to the update record.                            */
 /*                                                                   */
 /*********************************************************************/
 
  memcpy(dir_up_record.last_name,
         dir_record.last_name, sizeof(dir_record));
 
  /*****************************************************************/
  /*                                                               */
  /*  Set up the update field based on what needs to be updated.   */
  /*  The first bytes of the string that are needed are used.      */
  /*                                                               */
  /*                                                               */
  /*****************************************************************/
 
 
  switch (toupper(field))
  {
      case 'L':
      {
        memcpy(dir_up_record.last_name, change,
                 sizeof(dir_up_record.last_name)-1);
 
        /*************************************************************/
        /*  The last character must be the null.  If the input       */
        /*  was less than the length, then the null is already       */
        /*  in place.  This is only for the case when the change     */
        /*  string is greater than or equal to the length of the     */
        /*  record.                                                  */
        /*                                                           */
        /*************************************************************/
 
        dir_up_record.last_name[sizeof(dir_up_record.last_name)-1]
           = '\0';
        break;
      }
 
     case 'F':
      {
        memcpy(dir_up_record.first_name, change,
                 sizeof(dir_up_record.first_name)-1);
 
        /*************************************************************/
        /*  The last character must be the null.  If the input       */
        /*  was less than the length, then the null is already       */
        /*  in place.  This is only for the case when the change     */
        /*  string is greater than or equal to the length of the     */
        /*  record.                                                  */
        /*                                                           */
        /*************************************************************/
 
        dir_up_record.first_name[sizeof(dir_up_record.first_name)-1]
          = '\0';
        break;
      }
      case 'M':
      {
        memcpy(dir_up_record.middle_initial, change,
                 sizeof(dir_up_record.middle_initial)-1);
 
        /*************************************************************/
        /*  The last character must be the null.  If the input       */
        /*  was less than the length, then the null is already       */
        /*  in place.  This is only for the case when the change     */
        /*  string is greater than or equal to the length of the     */
        /*  record.                                                  */
        /*                                                           */
        /*************************************************************/
 
    dir_up_record.middle_initial
          [sizeof(dir_up_record.middle_initial)-1]
          = '\0';
        break;
      }
 
     case 'C':
      {
        memcpy(dir_up_record.country_code, change,
                 sizeof(dir_up_record.country_code)-1);
 
        /*************************************************************/
        /*  The last character must be the null.  If the input       */
        /*  was less than the length, then the null is already       */
        /*  in place.  This is only for the case when the change     */
        /*  string is greater than or equal to the length of the     */
        /*  record.                                                  */
        /*                                                           */
        /*************************************************************/
 
        dir_up_record.country_code[sizeof(dir_up_record.country_code)-1]
          = '\0';
        break;
      }
      case 'A':
      {
        memcpy(dir_up_record.area_code, change,
                 sizeof(dir_up_record.area_code)-1);
 
        /*************************************************************/
        /*  The last character must be the null.  If the input       */
        /*  was less than the length, then the null is already       */
        /*  in place.  This is only for the case when the change     */
        /*  string is greater than or equal to the length of the     */
        /*  record.                                                  */
        /*                                                           */
        /*************************************************************/
 
        dir_up_record.area_code[sizeof(dir_up_record.area_code)-1]
          = '\0';
        break;
      }
 
      case 'P':
      {
        memcpy(dir_up_record.phone_number, change,
                 sizeof(dir_up_record.phone_number)-1);
 
        /*************************************************************/
        /*  The last character must be the null.  If the input       */
        /*  was less than the length, then the null is already       */
        /*  in place.  This is only for the case when the change     */
        /*  string is greater than or equal to the length of the     */
        /*  record.                                                  */
        /*                                                           */
        /*************************************************************/
 
       dir_up_record.phone_number
            [sizeof(dir_up_record.phone_number)-1]
          = '\0';
        break;
      }
 
      default:
        printf("Invalid type to update.");
        printf("Valid types are L, F, M, C, A, or P");
        exit(0);
 
  }
 
  EXEC SQL
        UPDATE TPFNET.PHONE_DIRECTORY
        SET LAST_NAME    = :dir_up_record.last_name,
         FIRST_NAME      = :dir_up_record.first_name,
         MIDDLE_INITIAL  = :dir_up_record.middle_initial,
         COUNTRY_CODE    = :dir_up_record.country_code,
         AREA_CODE       = :dir_up_record.area_code,
         PHONE_NUMBER    = :dir_up_record.phone_number,
         TIME_STAMP      = CURRENT TIMESTAMP
         WHERE CURRENT OF U1;
 
  Check();
 
  EXEC SQL
        COMMIT;
  Check();
 
 
  EXEC SQL
        SELECT *
        INTO
         :dir_up_record
        FROM TPFNET.PHONE_DIRECTORY WHERE EMPLOYEE_NUMBER =
         :dir_record.employee_number;
 
  Check();
 
   printf("Employee record updated");
   printf(
     "OLD %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);
   printf(
     "NEW %s %s %s %s %s %s %hd\n",
          dir_up_record.last_name,
             dir_up_record.first_name,
                dir_up_record.middle_initial,
                   dir_up_record.phone_number,
                      dir_up_record.country_code,
                         dir_up_record.area_code,
                            dir_up_record.employee_number);
 
  exit(0);
}