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