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