gtpa3m0tApplication Requester User's Guide

Inserting a Telephone Directory Entry

The program listed in Figure 22 inserts a new employee entry into the corporate telephone directory.

The format of the parameters is:

rc /I last_name/first_name/middle_initial/country/area/phone_number

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.

I
Insert action code.

last_name
The last name of the person to add to the database.

first_name
The first name of the person to add to the database.

middle_initial
The middle initial of the person to add to the database.

country
The country code of the person to add to the database.

area
The area code of the person to add to the database.

phone_number
The phone number of the person to add to the database.

Notes:

  1. The / character separates the different parameters.

  2. The employee number is not needed for an insert because a unique number is assigned by the program.

For example, to insert an entry for Juan Martinez into the PHONE_DIRECTORY table, you would type:

     rc /I MARTINEZ/JUAN/C/52/748/2221923

Figure 22. TPF Program to Insert an Employee into the PHONE_DIRECTORY Table

#include <tpfeq.h>                /* Include the libraries           */
#include <tpfapi.h>
#include <tpfarapi.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
 
#pragma map(qxp1_insert,"QXP1")
extern void qxp1_insert();
static void Check();
 
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);
  }
}
 
 
 /********************************************************************/
 /* qxp1_insert:                                                     */
 /*    This function inserts a new record into a database with       */
 /* the information passed from the user.                            */
 /*                                                                  */
 /********************************************************************/
void qxp1_insert()
{
  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      */
 /*  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.     */
 
    short int indnull;            /* Used to indicate a null         */
                                  /* variable.                       */
 
  EXEC SQL END DECLARE SECTION;
 
 
 
 /********************************************************************/
 /* 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.  The rest of the parameters are   */
 /* all the information needed for the insert.                       */
 /* Notice that all of the parameters are separated by a /.          */
 /*                                                                  */
 /********************************************************************/
  blk = ecbptr()->ce1cr0;
  num_input = sscanf(blk->mi0acc,
"%*s /%*1c %17[^/]/%8[^/]/%1[^/]/%4[^/]/%5[^/]/%12s",
           dir_record.last_name,
                   dir_record.first_name,
                          dir_record.middle_initial,
                                 dir_record.country_code,
                                        dir_record.area_code,
                                               dir_record.phone_number);
 
 /********************************************************************/
 /* The number of conversions must be 6 which is the number of       */
 /* items needed for input.                                          */
 /*                                                                  */
 /********************************************************************/
 
  if (num_input != 6)
  {
      printf("The input is invalid.  The format is:\n\
Last name/First name/MI/Country/Area/Phone number\n");
      exit(0);
  }
 
 /********************************************************************/
 /* The new employee will automatically have the next employee       */
 /* number assigned to them.  The table must be locked first so that */
 /* anyone else making an update will not get the same employee      */
 /* number automatically assigned.                                   */
 /*                                                                  */
 /* The new employee number is one more than the current maximum     */
 /* employee number in the table.                                    */
 /*                                                                  */
 /********************************************************************/
 
  EXEC SQL
        LOCK TABLE TPFNET.PHONE_DIRECTORY IN EXCLUSIVE MODE;
  Check();
 
  EXEC SQL
        SELECT MAX(EMPLOYEE_NUMBER)
         INTO :dir_record.employee_number:indnull
         FROM TPFNET.PHONE_DIRECTORY;
  Check();
 
 /********************************************************************/
 /* Check the return to see if the answer was null.  If it is null,  */
 /* then there are no entries in the table, so set the employee      */
 /* number to zero, indicating no employee numbers yet assigned.     */
 /********************************************************************/
 
  if (indnull < 0)
  {
     dir_record.employee_number = 0;
  }
 
  dir_record.employee_number++;
 
 /********************************************************************/
 /* Insert the record into the database.                             */
 /********************************************************************/
 
  EXEC SQL
        INSERT INTO TPFNET.PHONE_DIRECTORY
        (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, COUNTRY_CODE,
         AREA_CODE, PHONE_NUMBER, EMPLOYEE_NUMBER,
         TIME_STAMP)
        VALUES
        (:dir_record.last_name,
         :dir_record.first_name,
         :dir_record.middle_initial,
         :dir_record.country_code,
         :dir_record.area_code,
         :dir_record.phone_number,
         :dir_record.employee_number,
         CURRENT TIMESTAMP);
  Check();
 
 
 /********************************************************************/
 /* The insert has completed.  We now want to commit the work so that*/
 /* others can see our updates.                                      */
 /*                                                                  */
 /********************************************************************/
 
  EXEC SQL
        COMMIT;
  Check();
 
 /********************************************************************/
 /* Let us now double check to show the user what is actually out    */
 /* on the database.                                                 */
 /*                                                                  */
 /********************************************************************/
 
  EXEC SQL
        SELECT *
        INTO
         :dir_record
        FROM TPFNET.PHONE_DIRECTORY WHERE EMPLOYEE_NUMBER =
         :dir_record.employee_number;
  Check();
 
 /********************************************************************/
 /* Show the results and exit.                                       */
 /*                                                                  */
 /********************************************************************/
 
  printf(
"Employee added to database\n\
    last name :      %s\n\
    first name :     %s\n\
    middle initial : %s\n\
    employee number: %hd\n",
           dir_record.last_name,
           dir_record.first_name,
           dir_record.middle_initial,
           dir_record.employee_number);
 
 
  exit(0);
}