gtpa3m0v | Application Requester User's Guide |
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:
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); }