/* Copyright © Oracle Corporation 1995. All Rights Reserved. */ /*-----------------------------------------------------------------------*/ /* This is the sample C program that is referred to in the VAX Rdb/VMS */ /* Guide to RDO, RDBPRE, and RDML. To create an executable program you */ /* must preprocess,compile, link, and run this program and the modules */ /* that it calls. */ /* */ /* Enter the following commands to create an executable image (first */ /* create the necessary options file as described in the VAX Rdb/VMS */ /* Guide to RDO, RDBPRE, and RDML; in this example, this options file */ /* is called RDMLOPT.OPT): */ /* */ /* $ RDML/C C_SAMPLE.RC */ /* $ RDML/C C_CALL_OTHER.RC */ /* $ RDML/C C_ERROR.RC */ /* $ CC C_CIO.C */ /* $ CC C_SAMPLE.C */ /* $ CC C_CALL_OTHER.C */ /* $ CC C_ERROR.C */ /* $ LINK C_SAMPLE, C_CALL_OTHER, C_ERROR, C_CIO, RDMLOPT/OPT */ /* $ RUN C_SAMPLE */ /*-----------------------------------------------------------------------*/ #include #include #include #include DATABASE PERS = [GLOBAL] FILENAME "MF_PERSONNEL" DBKEY SCOPE IS FINISH; /* Declarations */ #define EOS '\0' /* Null terminator */ #define SECONDS_TO_WAIT 5 int exit; /* Exit flag */ int user_input; /* User's response from read_int() */ extern read_int(); /* Reads a decimal integer from stdin */ extern void read_string(); /* Reads a string from stdin */ extern check_response(); /* Compares a user's response with a */ /* Predefined string */ extern callable_error(); /* Error handler for Callable RDO */ extern LIB$SIGNAL(); globaldef int trans_1 = 0; /* Transaction handle for START_TRANS */ globaldef int retry = 0; /* Number of retries when handling */ /* Errors in Callable RDO */ /* Declarations for the function named pair */ /* Declare two streams: one for the CANDIDATES relation and the other */ /* for the EMPLOYEES relation. */ DECLARE_STREAM emps USING EM IN EMPLOYEES SORTED BY EM.FIRST_NAME; DECLARE_STREAM cands USING CA IN CANDIDATES SORTED BY CA.LAST_NAME; /* Flags for end-of-stream condition */ globaldef int end_of_emps = FALSE; globaldef int end_of_cands = FALSE; /* End of declarations for the function named pair */ globalvalue RDB$_DEADLOCK; /* Rdb/VMS symbolic error codes */ globalvalue RDB$_LOCK_CONFLICT; globalvalue RDB$_INTEG_FAIL; globalvalue RDB$_NO_DUP; globalvalue RDB$_NOT_VALID; globalvalue RDB$_STREAM_EOF; globalvalue RDB$_NO_RECORD; main() { READY PERS; exit = FALSE; /* Loop directs program to call appropriate functions */ /* based on users response to the following menu. */ while (!exit) { /* Display main menu */ printf (" \n\n"); printf (" Main Menu\n"); printf (" Sample Application\n\n"); printf (" 1. Add one or more records to EMPLOYEES\n"); printf (" 2. Modify the address of one or more records from EMPLOYEES\n"); printf (" 3. Delete one or more records from EMPLOYEES\n"); printf (" 4. List all the employees and the college(s) attended\n"); printf (" 5. List employees in order of seniority\n"); printf (" 6. Pair an EMPLOYEES record with a CANDIDATES record\n"); printf (" 7. Calculate the total number of employees in the Company\n"); printf (" 8. Store one or more records in the CANDIDATES relation\n"); printf (" 9. Display one or more records from CANDIDATES\n"); printf ("10. Display employee IDs of employees in DEGREES with an\n"); printf (" unknown area of study\n"); printf ("11. Store a record in DEGREES\n"); printf ("12. Modify a resume in RESUMES\n"); printf ("13. Display a RESUMES record\n"); printf ("14. Add or delete a temporary index\n"); printf ("15. Retrieve colleges information using Callable RDO\n"); printf ("16. Store a RESUME\n"); printf ("99. Exit the program\n\n"); user_input = read_int ("Please enter an option number and press Return: "); /* Determine what was entered by user. */ switch (user_input) { case 1: add_employees(); break; case 2: modify_address(); break; case 3: delete_record(); break; case 4: list_record(); break; case 5: seniority(); break; case 6: pair(); break; case 7: stats(); break; case 8: store_cand(); break; case 9: display_cand(); break; case 10: find_missing(); break; case 11: rdbdollarmissing(); break; case 12: mod_resume(); break; case 13: display_resume(); break; case 14: FINISH; ddl_stmnt(); READY PERS; break; case 15: callable(); break; case 16: store_res(); break; case 99: exit = TRUE; break; default: ; } } /* End while (!exit) */ FINISH; } /* End main() */ handle_error() /* -----------------------------------------------------------------*/ /* This function handles run-time errors trapped by the ON ERROR */ /* clause in preprocessed RDML/C programs. */ /* -----------------------------------------------------------------*/ { char msg_string[256]; char string[133]; int error; FILE open(), fp; $DESCRIPTOR (msgstr, msg_string); /* Use LIB$MATCH_COND to determine which of a series of errors */ /* might have occurred. */ error = LIB$MATCH_COND (&RDB$MESSAGE_VECTOR[1], &RDB$_DEADLOCK, &RDB$_LOCK_CONFLICT, &RDB$_NO_DUP, &RDB$_NOT_VALID, &RDB$_INTEG_FAIL, &RDB$_STREAM_EOF, &RDB$_NO_RECORD); printf (" \n"); /* The switch statement directs program logic to appropriate */ /* statements to execute depending on the error that was */ /* trapped. */ switch (error) { case 0: printf("Unexpected error - terminating program\n"); fp = fopen("error_log", "w"); error = SYS$GETMSG(RDB$MESSAGE_VECTOR[1], &msgstr.dsc$w_length, &msgstr, 0, 0); msg_string[msgstr.dsc$w_length] = EOS; fputs(msg_string, fp); fclose (fp); error = LIB$CALLG (&RDB$MESSAGE_VECTOR, LIB$SIGNAL); break; case 1: case 2: if (retry <= 4) { printf("Deadlock or Lock conflict error"); printf("Others are using the data that you want to access\n"); error = LIB$WAIT(SECONDS_TO_WAIT); } else printf("Sorry resources are not available, please retry later\n"); break; case 3: printf("Duplicates are not allowed\n"); SYS$PUTMSG(RDB$MESSAGE_VECTOR); break; case 4: printf("Invalid data\n"); SYS$PUTMSG(RDB$MESSAGE_VECTOR); break; case 5: printf("Integrity failure"); SYS$PUTMSG(RDB$MESSAGE_VECTOR); break; case 6: printf("There are no colleges with that code\n"); break; case 7: printf("A record entered during this session has been deleted\n"); break; } } /* End handle_error */ add_employees() /* ------------------------------------------------------------------------*/ /* This function adds a new EMPLOYEES record to the EMPLOYEES relation. */ /* ------------------------------------------------------------------------*/ { DECLARE_VARIABLE OF rdb_key_array[100] SAME AS EMPLOYEES.RDB$DB_KEY; static struct { DECLARE_VARIABLE OF employee_id SAME AS PERS.EMPLOYEES.EMPLOYEE_ID; DECLARE_VARIABLE OF last_name SAME AS PERS.EMPLOYEES.LAST_NAME; DECLARE_VARIABLE OF first_name SAME AS PERS.EMPLOYEES.FIRST_NAME; DECLARE_VARIABLE OF middle_initial SAME AS PERS.EMPLOYEES.MIDDLE_INITIAL; DECLARE_VARIABLE OF address_data_1 SAME AS PERS.EMPLOYEES.ADDRESS_DATA_1; DECLARE_VARIABLE OF address_data_2 SAME AS PERS.EMPLOYEES.ADDRESS_DATA_2; DECLARE_VARIABLE OF city SAME AS PERS.EMPLOYEES.CITY; DECLARE_VARIABLE OF state SAME AS PERS.EMPLOYEES.STATE; DECLARE_VARIABLE OF postal_code SAME AS PERS.EMPLOYEES.POSTAL_CODE; DECLARE_VARIABLE OF sex SAME AS PERS.EMPLOYEES.SEX; DECLARE_VARIABLE OF status_code SAME AS PERS.EMPLOYEES.STATUS_CODE; DECLARE_VARIABLE OF birthday SAME AS PERS.EMPLOYEES.BIRTHDAY; } employee_record; static $DESCRIPTOR(ascii_birthday, "dd-mmm-yyy "); long stat; char response[80]; /* User's response from read_string() */ int succeed; /* DML success flag */ int i, x; /* Loop variables */ int transaction_started; /* Transaction started flag */ i = x = 0; employee_record.employee_id[0] = EOS; /* Prompt user for input, until user confirms that */ /* input is valid or enters 'exit'. */ while (TRUE) { succeed = TRUE; response[0] = EOS; while (check_response (response, "Y") != 0) { printf (" \n"); printf ("Please enter the ID of the new Employee or type \n"); read_string ("exit to return to the Main Menu: ", employee_record.employee_id, sizeof (employee_record.employee_id)-1); if (check_response (employee_record.employee_id, "EXIT") == 0 ) return; read_string ("Please enter the Employee's last name: ", employee_record.last_name, sizeof (employee_record.last_name)-1); read_string ("Please enter the Employee's first name: ", employee_record.first_name, sizeof (employee_record.first_name)-1); read_string ("Please enter the Employee's middle initial: ", employee_record.middle_initial, sizeof (employee_record.middle_initial)-1); read_string ("Please enter Employee's sex: ", employee_record.sex, sizeof (employee_record.sex)-1); while (1) { printf ("Please enter the Employee's birthday\n"); read_string ("In the format: dd-MMM-yyyy :", ascii_birthday.dsc$a_pointer, strlen(ascii_birthday.dsc$a_pointer) ); /* Convert ASCII date to binary date. */ stat = SYS$BINTIM(&ascii_birthday, employee_record.birthday); if ( (stat & 1) == 1 ) break; else { ascii_birthday.dsc$a_pointer[ascii_birthday.dsc$w_length] = '\0'; printf ("***Invalid date '%s' ****\n", ascii_birthday.dsc$a_pointer); } } read_string ("Please enter the Employee's street address: ", employee_record.address_data_1, sizeof (employee_record.address_data_1)-1); read_string ("Please enter Apartment number, if any: ", employee_record.address_data_2, sizeof (employee_record.address_data_2)-1); read_string ("Please enter Employee's City: ", employee_record.city, sizeof (employee_record.city)-1); read_string ("Please enter Employee's State: ", employee_record.state, sizeof (employee_record.state)-1); read_string ("Please enter Employee's Postal Code: ", employee_record.postal_code, sizeof (employee_record.postal_code)-1); read_string ("Please enter Employee's status code: ", employee_record.status_code, sizeof (employee_record.status_code)-1); printf (" \n"); read_string ("Have you entered all the data correctly? (Y,N): ", response, sizeof (response)-1); printf (" \n"); } /* End while (response != "Y") */ /* Start Transaction */ transaction_started = FALSE; retry = 0; while (!transaction_started && retry <= 5) { transaction_started = TRUE; START_TRANSACTION READ_WRITE RESERVING EMPLOYEES FOR SHARED WRITE NOWAIT ON ERROR handle_error(); transaction_started = FALSE; retry++; END_ERROR; } if (!transaction_started) break; /* Store the values that the user entered into an */ /* record in the EMPLOYEES relation. */ STORE E IN EMPLOYEES USING ON ERROR succeed = FALSE; handle_error(); END_ERROR; E.* = employee_record; /* Get the dbkey of the newly stored EMPLOYEES record. */ GET rdb_key_array[i] = (E.RDB$DB_KEY); END_GET; END_STORE; /* If the STORE operation succeeded, increment a counter by one */ /* and add the dbkey to an array of dbkeys. */ if (succeed == TRUE) { i = i+1; printf (" \n"); printf ("Successfully added employee: %s\n", employee_record.last_name); printf (" with employee id: %s\n\n", employee_record.employee_id); /* If the user wants to see all the EMPLOYEES records added during */ /* this session, step through the array of dbkeys to find and */ /* print each new EMPLOYEES record. */ printf (" Do you want to see the names of all the employees\n"); read_string (" entered during this session? (Y,N): ", response, sizeof (response)-1); if (check_response (response, "Y") == 0) for (x=0; x != i; x++) { FOR E IN EMPLOYEES WITH E.RDB$DB_KEY = rdb_key_array[x] ON ERROR handle_error(); END_ERROR printf ("%s %s. ", E.FIRST_NAME, E.MIDDLE_INITIAL); printf ("%s\n", E.LAST_NAME); printf ("%s", E.ADDRESS_DATA_1); printf ("%s\n", E.ADDRESS_DATA_2); printf ("%s %s\n", E.CITY, E.STATE); printf ("%s\n", E.POSTAL_CODE); /* Convert binary date to ascii date */ stat = SYS$ASCTIM( &ascii_birthday.dsc$w_length, &ascii_birthday, employee_record.birthday, 0); if ( (stat & 1) != 1 ) printf ("Data conversion failed"); else { ascii_birthday.dsc$a_pointer[ascii_birthday. dsc$w_length] ='\0'; puts (ascii_birthday.dsc$a_pointer); } END_FOR; } COMMIT; } else /* succeed = FALSE */ /* If the field values were a success */ { printf ("Update operation failed, %s\n", employee_record.last_name); printf (" with employee id: %s\n", employee_record.employee_id); printf ("has not been stored in the database\n\n"); ROLLBACK; } response[0] = EOS; } } /* End add_employees */ modify_address() /* -------------------------------------------------------------*/ /* This function modifies the address of an EMPLOYEES record. */ /* -------------------------------------------------------------*/ { DECLARE_VARIABLE OF employee_id SAME AS PERS.EMPLOYEES.EMPLOYEE_ID; DECLARE_VARIABLE OF street SAME AS PERS.EMPLOYEES.ADDRESS_DATA_1; DECLARE_VARIABLE OF address_data SAME AS PERS.EMPLOYEES.ADDRESS_DATA_2; DECLARE_VARIABLE OF town SAME AS PERS.EMPLOYEES.CITY; DECLARE_VARIABLE OF state SAME AS PERS.EMPLOYEES.STATE; DECLARE_VARIABLE OF postal_code SAME AS PERS.EMPLOYEES.POSTAL_CODE; char response[80]; /* User's response from read_string() */ int succeed; /* Success flag */ employee_id[0]= EOS; /* EOS is the null terminator */ /* Prompt user for the ID of the employee whose record he or she */ /* wants to modify. */ while (TRUE) { succeed = TRUE; response[0] = EOS; while (check_response (response, "Y") != 0) { printf (" \n"); printf ("Please enter the ID number of the Employee\n"); read_string (" whose address you want to change or type exit: ", employee_id, sizeof (employee_id)-1); if (check_response (employee_id, "EXIT") == 0) return; /* Retrieve and display the record specified by the employee ID and */ /* confirm with the user that he or she wants to modify this record. */ START_TRANSACTION READ_WRITE RESERVING EMPLOYEES FOR SHARED READ; FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = employee_id printf ("Street: %s\n", E.ADDRESS_DATA_1); printf ("Apartment: %s\n", E.ADDRESS_DATA_2); printf ("Town: %s\n", E.CITY); printf ("State: %s\n", E.STATE); printf ("Postal Code: %s\n", E.POSTAL_CODE); END_FOR; COMMIT; printf (" \n"); read_string ("Do you want to change this address? (Y,N): ", response, sizeof (response)-1); } /* End while response != "Y" */ response[0] = EOS; /* Prompt the user for a new address. */ while (check_response (response, "Y") != 0) { printf (" \n"); read_string ("Please enter the Employee's street address: ", street, sizeof (street)-1); read_string ("Please enter Apartment number, if any: ", address_data, sizeof (address_data)-1); read_string ("Please enter Employee's Town: ", town, sizeof (town)-1); read_string ("Please enter Employee's State: ", state, sizeof (state)-1); read_string ("Please enter Employee's Postal Code: ", postal_code, sizeof (postal_code)-1); printf (" \n"); read_string ("Have you entered the address correctly? (Y,N): ", response, sizeof (response)-1); } /* End while response != "Y" */ START_TRANSACTION READ_WRITE RESERVING EMPLOYEES FOR SHARED WRITE; printf (" \n"); printf ("Proceeding to store operation\n"); /* Modify the address fields for the specified EMPLOYEES record. */ FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = employee_id MODIFY E USING ON ERROR succeed = FALSE; handle_error(); END_ERROR strcpy (E.ADDRESS_DATA_1, street); strcpy (E.ADDRESS_DATA_2, address_data); strcpy (E.CITY, town); strcpy (E.STATE, state); strcpy (E.POSTAL_CODE, postal_code); END_MODIFY; END_FOR; /* Notify the user of the success or failure of the modify */ /* operation. */ if (succeed == TRUE) { printf ("Update operation succeeded\n\n"); COMMIT; } else { printf ("Update operation failed\n\n"); ROLLBACK; } } printf (" \n"); } /* End modify_address */ delete_record() /* ----------------------------------------------------------- */ /* This function deletes a record from the database. */ /* ----------------------------------------------------------- */ { typedef BASED ON EMPLOYEES.RDB$DB_KEY db_key_type; DECLARE_VARIABLE employee_id SAME AS PERS.EMPLOYEES.EMPLOYEE_ID; char response[80]; /* User's response from read_string() */ int exit; /* Exit flag */ int req_1; /* Request handle */ int found_emp; /* Found Employee flag */ db_key_type db_key; /* Employee key */ employee_id[0]= EOS; /* EOS is the null terminator */ exit = FALSE; /* Prompt user for the ID of the EMPLOYEE record that he or she */ /* wants to delete from the database. */ while (exit != TRUE) { response[0] = EOS; found_emp = FALSE; req_1 = 0; while ((check_response (response, "Y") != 0) && (found_emp == FALSE)) { printf (" \n"); printf ("Please enter the ID number of the Employee\n"); read_string (" you want to delete or type exit: ", employee_id, sizeof (employee_id)-1); if (check_response (employee_id, "EXIT") == 0) { exit = TRUE; break; } START_TRANSACTION (TRANSACTION_HANDLE trans_1 ) READ_WRITE; /* Find the record of the employee that the user wants to delete. If */ /* an error occurs during the FOR operation call an error handler. */ FOR (TRANSACTION_HANDLE trans_1) E IN EMPLOYEES WITH E.EMPLOYEE_ID = employee_id ON ERROR handle_error(); END_ERROR /* Get the dbkey of the EMPLOYEES record */ /* that the user wants to delete. */ found_emp = TRUE; db_key = E.RDB$DB_KEY; /* Pass the db key to an external routine "CALL_OTHER" to */ /* print out the record to which the dbkey points. Note */ /* that using an external routine is neither necessary nor */ /* recommended for performing this task. It is done in this */ /* example only to show how values are passed between routines */ /* in an RDML/C program. */ call_other (db_key, req_1); END_FOR; if (found_emp != TRUE) printf (" No employee with %s on file\n", employee_id); else /* Ask user for confirmation that this is the */ /* EMPLOYEES record he or she wants to delete. */ read_string ("Is this the employee you want to delete? (Y,N): ", response, sizeof (response)-1); COMMIT (TRANSACTION_HANDLE trans_1); } /* If the user wants to delete the employee, then start a READ_WRITE */ /* transaction and delete the EMPLOYEES record from all relations */ /* in which employee_id appears. Note that this is all done in one */ /* transaction. You would not want to split this task across */ /* transactions. If one of many transactions failed, you would not be */ /* certain that the EMPLOYEES record was deleted from all the */ /* relations. */ if (exit == TRUE) break; START_TRANSACTION READ_WRITE RESERVING EMPLOYEES, SALARY_HISTORY, JOB_HISTORY, DEGREES, RESUMES FOR SHARED WRITE; FOR E IN EMPLOYEES WITH E.RDB$DB_KEY = db_key ERASE E; END_FOR; FOR JH IN JOB_HISTORY WITH JH.EMPLOYEE_ID = employee_id ERASE JH; END_FOR; FOR SH IN SALARY_HISTORY WITH SH.EMPLOYEE_ID = employee_id ERASE SH; END_FOR; FOR D IN DEGREES WITH D.EMPLOYEE_ID = employee_id ERASE D; END_FOR; FOR R IN RESUMES WITH R.EMPLOYEE_ID = employee_id ERASE R; END_FOR; COMMIT; response[0] = EOS; } } /* End delete_record */ list_record() /* ------------------------------------------------------------------------*/ /* This function lists all the employees and the colleges they attended. */ /* ------------------------------------------------------------------------*/ { START_TRANSACTION READ_ONLY; /* For each EMPLOYEES record that has a corresponding record in */ /* DEGREES, print the DEGREES record. */ FOR E IN EMPLOYEES SORTED BY E.LAST_NAME FOR D IN DEGREES WITH D.EMPLOYEE_ID = E.EMPLOYEE_ID printf ("Name is: %s %s\n", E.FIRST_NAME, E.LAST_NAME); printf ("Degree is: %s\n", D.DEGREE); printf ("Degree field is: %s\n\n", D.DEGREE_FIELD); END_FOR; /* Use the NOT ANY clause to create a stream of all the records in the */ /* EMPLOYEES relation that do not have an associated record in the */ /* DEGREES relation. Then use the FIRST clause to step through this */ /* stream. The FOR statement previously created for the EMPLOYEES */ /* relation is still active; this will cause the FIRST clause to step */ /* through the stream created by the NOT ANY clause. */ FOR FIRST 1 D IN DEGREES WITH NOT ANY D1 IN DEGREES WITH D1.EMPLOYEE_ID = E.EMPLOYEE_ID /* Print the names from the EMPLOYEES records */ /* that do not have an associated record stored */ /* in DEGREES. */ printf ("%s %s\n", E.FIRST_NAME, E.LAST_NAME); printf ("Does not have this information stored in the\n"); printf ("database\n\n"); END_FOR; END_FOR; COMMIT; } /* End list_record */ seniority() /* ----------------------------------------------------------*/ /* This function lists employees in order of seniority. */ /* ----------------------------------------------------------*/ { extern int SYS$ASCTIM( ); static $DESCRIPTOR (sal_date, "dd-mmm-yyyy hh:mm:ss.cc "); /* sys$asctim returns "len" in a 16-bit word. */ int len; long status; START_TRANSACTION READ_ONLY; /* Create a stream of records by crossing the EMPLOYEES relation */ /* with a stream of records from the SALARY_HISTORY relation that */ /* have the SALARY_END field flagged as missing. The assumption */ /* is that if the SALARY_END field is missing this record is the */ /* current record. Sort the records in ascending order of the */ /* salary start date. */ FOR SH IN SALARY_HISTORY CROSS E IN EMPLOYEES OVER EMPLOYEE_ID SORTED BY DESCENDING SH.SALARY_START /* Display the retrieved records use sys$asctim to convert the */ /* date, which is stored in binary format. */ printf (" \n"); printf ("%s %s\n", E.FIRST_NAME, E.LAST_NAME); status = SYS$ASCTIM (&len, &sal_date, SH.SALARY_START, 0); if (status != SS$_NORMAL) printf ("Date conversion failed\n"); else { sal_date.dsc$a_pointer[len-1] = EOS; printf ("started work on: %.11s\n", sal_date.dsc$a_pointer); } END_FOR; COMMIT; } /* End seniority */ pair() /* ---------------------------------------------------------------------*/ /* This function demonstrates the use of the declared START_STREAM */ /* statement. The output of this program is merely a random matching */ /* of each CANDIDATES record with an EMPLOYEES record. The functions */ /* called in this function appear just after this one. */ /* ---------------------------------------------------------------------*/ { char response[80]; /* User's Response from read_string() */ START_TRANSACTION READ_ONLY; /* Open both streams and set flags for the end-of-stream condition */ /* to false. */ open_candidates(); open_employees(); end_of_emps = FALSE; end_of_cands = FALSE; /* Fetch a record from the CANDIDATES and EMPLOYEES relations. */ read_a_candidate(); read_an_employee(); /* Print the employee and candidate names until the end-of-stream */ /* condition is met for the stream of CANDIDATES records. */ while (!end_of_cands) { printf ("%s %s %s %s\n", EM.LAST_NAME, EM.FIRST_NAME, CA.LAST_NAME, CA.FIRST_NAME ); read_a_candidate(); if (!end_of_cands) { read_an_employee(); } } printf (" \n"); read_string("Press RETURN to continue", response, sizeof (response)-1); /* Close both streams. */ close_employees(); close_candidates(); COMMIT; } /* End pair */ /* The functions control streams in the pair function. */ /* Of course, a simple program such as this does not require the use */ /* of functions to separate the RDML statements. It is done here to */ /* demonstrate what you can do. Note that the statements do */ /* not appear in the order that they will be executed. This is a */ /* functionality that declared streams have and undeclared streams */ /* do not have. */ read_a_candidate( ) { FETCH cands AT END end_of_cands = TRUE; END_FETCH; } open_candidates( ) { START_STREAM cands; } open_employees( ) { START_STREAM emps; } read_an_employee( ) { FETCH emps AT END end_of_emps = TRUE; END_FETCH; } close_employees( ) { END_STREAM emps; } close_candidates( ) { END_STREAM cands; } stats() /* ----------------------------------------------------------- */ /* This function displays the total number of records stored */ /* in the EMPLOYEES relation. */ /* ----------------------------------------------------------- */ { char response[80]; /* User's response from read_string() */ int atotal; /* Total */ START_TRANSACTION READ_ONLY; /* Use the GET statement with a statistical expression to calculate */ /* the total number of records in the EMPLOYEES relation. */ printf ("\n\n"); printf ("The number of employees in the Corporation is: "); GET atotal = (COUNT OF E IN EMPLOYEES); END_GET; printf ("%d\n",atotal); read_string ("Press RETURN to continue", response, sizeof (response)-1); COMMIT; } /* End stats */ store_cand() /* ----------------------------------------------------------- */ /* This function stores a record in the CANDIDATES relation. */ /* It shows how to store a value in a field of VARYING STRING */ /* data type. */ /* ----------------------------------------------------------- */ { DECLARE_VARIABLE first_name SAME AS PERS.CANDIDATES.FIRST_NAME; DECLARE_VARIABLE last_name SAME AS PERS.CANDIDATES.LAST_NAME; DECLARE_VARIABLE middle_init SAME AS PERS.CANDIDATES.MIDDLE_INITIAL; char status_info[255]; char response[80]; /* User's response from read_string() */ int succeed; /* Success flag */ int transaction_started; /* Transaction started flag */ first_name[0] = EOS; /* EOS is the null terminator */ /* Prompt user for data to store in the CANDIDATES relation */ while (TRUE) { succeed = TRUE; response[0] = EOS; while (check_response (response, "Y") != 0) { printf (" \n"); printf ("Please enter the first name of the candidate or\n"); read_string (" type exit: ", first_name, sizeof (first_name)-1); if (check_response (first_name, "EXIT") == 0) return; read_string ("Please enter the middle initial of the candidate: ", middle_init, sizeof (middle_init)-1); read_string ("Please enter the last name of the candidate: ", last_name, sizeof (last_name)-1); read_string ("Please enter the candidate status information: ", status_info, 254); printf ("Have you entered the Candidate information \n"); read_string ("correctly? (Y,N): ", response, sizeof (response)-1); } /* Start transaction */ transaction_started = FALSE; retry = 0; while (!transaction_started && retry <= 5) { transaction_started = TRUE; START_TRANSACTION READ_WRITE RESERVING CANDIDATES FOR SHARED WRITE NOWAIT ON ERROR handle_error(); transaction_started = FALSE; retry++; END_ERROR; } if (!transaction_started) break; /* Store the values specified by the user in the CANDIDATES */ /* relation. Trap for errors and inform the user of the success */ /* or failure of the STORE operation. */ STORE C IN CANDIDATES USING strcpy (C.FIRST_NAME, first_name); strcpy (C.LAST_NAME, last_name); strcpy (C.MIDDLE_INITIAL, middle_init); RDB$CSTRING_TO_VARYING (status_info, C.CANDIDATE_STATUS); END_STORE; if (succeed == TRUE) { printf (" \n"); printf ("Update operation succeeded\n\n"); COMMIT; } else { printf ("Update operation failed\n\n"); ROLLBACK; } response[0] = EOS; } } /* End store_cand */ display_cand() /* --------------------------------------------------------------------*/ /* This function displays a record from the CANDIDATES relation. */ /* It shows how to display a field stored as VARYING STRING data type. */ /* --------------------------------------------------------------------*/ { DECLARE_VARIABLE first_name SAME AS PERS.CANDIDATES.FIRST_NAME; DECLARE_VARIABLE last_name SAME AS PERS.CANDIDATES.LAST_NAME; DECLARE_VARIABLE middle_init SAME AS PERS.CANDIDATES.MIDDLE_INITIAL; char status_info[255]; int succeed; /* Success flag */ char response[80]; /* User's response from read_atring() */ first_name[0] = EOS; /* EOS is the null terminator */ /* Prompt user for information needed to identify a record in the */ /* CANDIDATES relation. */ while (TRUE) { response[0] = EOS; while (check_response (response, "Y") != 0) { printf (" \n"); printf ("Please enter the first name of the candidate or\n"); read_string (" type exit: ", first_name, sizeof (first_name)-1); if (check_response (first_name, "EXIT") == 0) return; read_string ("Please enter the middle initial of the candidate: ", middle_init, sizeof (middle_init)-1); read_string ("Please enter the last name of the candidate: ", last_name, sizeof (last_name)-1); printf ("Have you entered the Candidate information \n"); read_string ("correctly? (Y,N): ", response, sizeof (response)-1); } START_TRANSACTION READ_ONLY; /* Retrieve and display the VARYING STRING field if a record exists */ /* for the specified candidate. If no record exists for this */ /* person, inform the user. */ succeed = FALSE; FOR C IN CANDIDATES WITH C.FIRST_NAME = first_name AND C.MIDDLE_INITIAL = middle_init AND C.LAST_NAME = last_name RDB$VARYING_TO_CSTRING (C.CANDIDATE_STATUS, status_info); printf ("%s %s\n", C.FIRST_NAME, C.LAST_NAME); printf ("has the following status: %s\n\n", status_info); succeed = TRUE; END_FOR; if (succeed == FALSE) { printf("No such candidate on file"); } COMMIT; response[0] = EOS; } } /* End display_cand */ find_missing() /* -----------------------------------------------------------------*/ /* This function prints the employee ID of all EMPLOYEES records */ /* in the DEGREES relation that do not have a value stored in the */ /* DEGREE_FIELD field. */ /* -----------------------------------------------------------------*/ { char response[80]; /* User's response from read_string() */ printf (" \n"); printf ("IDs of Employees in relation DEGREES with DEGREE_FIELD\n"); printf ("missing are:\n\n"); START_TRANSACTION READ_ONLY; /* Use the MISSING value expression to find all records in the */ /* DEGREES relation that have the DEGREE_FIELD flagged as missing */ /* Print the employee ID of all the records id the DEGREES relation */ /* that do not have a value stored in the DEGREES_FIELD field. */ FOR D IN DEGREES WITH D.DEGREE_FIELD MISSING printf ("%s\n", D.EMPLOYEE_ID); END_FOR; COMMIT; printf (" \n"); read_string ("Press RETURN to continue: ", response, sizeof (response)-1); } /* End of find_missing */ rdbdollarmissing() /* --------------------------------------------------------------*/ /* This function demonstrates how to use the RDB$MISSING value */ /* clause to mark a field as missing. */ /* --------------------------------------------------------------*/ { DECLARE_VARIABLE employee_id SAME AS PERS.DEGREES.EMPLOYEE_ID; DECLARE_VARIABLE coll_code SAME AS PERS.DEGREES.COLLEGE_CODE; DECLARE_VARIABLE year SAME AS PERS.DEGREES.YEAR_GIVEN; DECLARE_VARIABLE degree SAME AS PERS.DEGREES.DEGREE; DECLARE_VARIABLE field SAME AS PERS.DEGREES.DEGREE_FIELD; char response[80]; /* User's response from read_string() */ employee_id[0] = EOS; /* EOS is the null terminator */ /* Prompt the user for values to store in the COLLEGES relation. */ while (TRUE) { response[0] = EOS; while (check_response (response, "Y") != 0) { printf (" \n"); printf ("Please enter the ID number of the Employee\n"); read_string (" or type exit: ", employee_id, sizeof (employee_id)-1); if (check_response (employee_id, "EXIT") == 0) return; read_string ("Please enter College Code: ", coll_code, sizeof (coll_code)-1); year = read_int ("Please enter Year degree was granted: "); read_string ("Please enter Degree: ", degree, sizeof (degree)-1); /* Direct user to enter a question mark if he or she is */ /* uncertain of the DEGREE_FIELD for the record being stored. */ printf ("Please enter field in which\n"); read_string ("degree was granted. If unknown enter ?: ", field, sizeof (field)-1); read_string ("Have you entered all the data correctly? (Y,N): ", response, sizeof (response)-1); } START_TRANSACTION READ_WRITE RESERVING DEGREES FOR SHARED WRITE; /* Store the user-specified values in the DEGREES relation. If he or she */ /* entered a question mark for the DEGREE_FIELD, the missing value */ /* defined for DEGREE_FIELD will be stored; otherwise the value */ /* specified by the user will be stored. */ STORE D IN DEGREES USING ON ERROR handle_error(); END_ERROR strcpy (D.EMPLOYEE_ID, employee_id); strcpy (D.COLLEGE_CODE, coll_code); D.YEAR_GIVEN = year; strcpy (D.DEGREE, degree); /* If the user entered a question mark for the DEGREE_FIELD then, */ /* retrieve the missing value that is defined for the DEGREE_FIELD. */ if (check_response (field, "?") == 0) pad_string (RDB$MISSING (D.DEGREE_FIELD), D.DEGREE_FIELD, sizeof (D.DEGREE_FIELD)); else strcpy (D.DEGREE_FIELD, field); END_STORE; COMMIT; response[0] = EOS; } } /* End rdbdollarmissing */ mod_resume() /* ------------------------------------------------------------*/ /* This function demonstrates how to modify a field of data */ /* type SEGMENTED STRING */ /* ------------------------------------------------------------*/ { DECLARE_VARIABLE employee_id SAME AS RESUMES.EMPLOYEE_ID; char response[80]; /* User's response from read_string() */ char my_file[21]; /* Resume file */ char buffer[80]; /* Temporary buffer for fgets */ FILE *fopen(), *fp; /* File pointer */ employee_id[0] = EOS; /* EOS is the null terminator */ /* Prompt user for the employee ID of the RESUMES record he */ /* or she wants to modify. */ while (TRUE) { response[0] = EOS; while (check_response (response, "Y") != 0) { printf (" \n"); printf ("Please enter the ID number of the Employee\n"); read_string (" or type exit: ", employee_id, sizeof (employee_id)-1); if (check_response (employee_id, "EXIT") == 0) return; /* Prompt user for the file name of the resume that will replace the */ /* old resume. */ printf ("To modify a resume, you must supply a new file"); printf ("name that contains the new resume.\n"); printf (" resume and replace it with a new resume\n"); read_string ("Please enter file name of new resume: ", my_file, sizeof (my_file)-1); read_string ("Have you entered all the data correctly? (Y,N) ", response, sizeof (response)-1); } fp = fopen(my_file, "r"); START_TRANSACTION READ_WRITE RESERVING RESUMES FOR SHARED WRITE; /* Start an outer for loop to retrieve the employee record(s) */ /* with the specified ID. */ FOR R IN RESUMES WITH R.EMPLOYEE_ID = employee_id /* Use a MODIFY statement to change the value of the */ /* segmented string field. */ MODIFY R USING ON ERROR handle_error(); END_ERROR; /* Read in the new resume and use a store operation to store a new */ /* segmented string handle in the RESUMES relation. */ while (fgets (buffer, 132, fp) != NULL) { /* fgets reads a carriage return if it exists in the */ /* file into the buffer - therefore subtract 1 */ STORE SEG IN R.RESUME strcpy(SEG.VALUE, buffer); SEG.LENGTH = strlen(buffer) - 1; END_STORE; } END_MODIFY; END_FOR; fclose (fp); COMMIT; response[0] = EOS; } } /* End mod_resume */ display_resume() /* -------------------------------------------------------------*/ /* This function demonstrates how to retrieve a field of data */ /* type SEGMENTED STRING. */ /* -------------------------------------------------------------*/ { DECLARE_VARIABLE employee_id SAME AS RESUMES.EMPLOYEE_ID; char response[80]; /* User's response from read_string() */ int succeed; employee_id[0]= EOS; /* EOS is the null terminator */ /* Prompt the user to enter the ID of the employee resume that he or */ /* she wants to view. If user enters 'exit' then exit function. */ while (TRUE) { response[0] = EOS; while (check_response (response, "Y") != 0) { printf (" \n"); printf ("Please enter the ID number of the Employee whose resume\n"); read_string (" you want to display or type exit: ", employee_id, sizeof (employee_id)-1); if (check_response (employee_id, "EXIT") == 0) return; read_string ("Have you entered all the data correctly? (Y,N) ", response, sizeof (response)-1); } START_TRANSACTION READ_ONLY RESERVING RESUMES FOR SHARED READ; /* Start an outer FOR loop to retrieve the employee record(s) */ /* with the specified ID. */ succeed = FALSE; FOR R2 IN RESUMES WITH R2.EMPLOYEE_ID = employee_id succeed = TRUE; /* Start an inner FOR loop to retrieve the segments of the */ /* segmented string that comprise the employee's resume. */ /* Display each segment as it is retrieved from the database. */ FOR TEXT IN R2.RESUME printf ("%.*s\n", TEXT.LENGTH,TEXT.VALUE); END_FOR; END_FOR; /* If a record with the specified ID was not found then */ /* inform the user. */ if (succeed == FALSE) { printf("Employee: %s%s",employee_id,"has no resume on file"); } COMMIT; employee_id[0] = EOS; } } /* End display_resume */ ddl_stmnt () /* ----------------------------------------------------------- */ /* This function demonstrates how to perform data definition */ /* tasks from an RDML/C program. You must use the Callable */ /* RDO interface, RDB$INTERPRET, to perform data definition */ /* tasks in preprocessed programs. */ /* ----------------------------------------------------------- */ { char literal [255]; /* RDO command buffer */ char literal1 [255]; /* RDO command buffer1 */ char response[80]; /* User's response from read_string() */ long status; /* Status returned from RDB$INTERPRET */ long db_handle; /* Database handle */ int succeed; /* Declare descriptors. */ struct dsc$descriptor pers; $DESCRIPTOR (command_buf, literal); $DESCRIPTOR (command_buf1, literal1); /* Set up database handle. */ pers.dsc$a_pointer = &db_handle; pers.dsc$b_dtype = DSC$K_DTYPE_L; pers.dsc$b_class = DSC$K_CLASS_S; status = 0; /* Prompt user for input. Ordinarily, it would not be likely that */ /* you would ask a user to define an index for the database. This */ /* example serves only to show you how this type of task can be done */ /* from within an RDML/C environment. */ while (TRUE) { response[0] = EOS; while (check_response (response, "Y") != 0) { printf(" \n"); printf("Please enter the data definition statement to define\n"); printf(" or delete a temporary index, or type 'exit'\n"); printf(" on EMPLOYEE_ID, you might enter:\n"); printf("Define index emp_employee_id for employees. employee_id.\n"); printf(" end index.\n"); printf("To delete this index, you might enter:\n"); read_string(" Delete index emp_employee_id. :", literal, sizeof (literal)-1); if (check_response (literal, "EXIT") == 0) return; read_string("Did you enter the definition correctly? (Y,N): ", response, sizeof (response)-1); } /* Invoke the database to make it known to Callable RDO. */ strcpy(literal1, "invoke database !val = filename 'mf_personnel'"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1, &pers); if ((status & 1) == 0) callable_error(status); /* Start a READ_WRITE transaction. */ strcpy(literal1, "START_TRANSACTION READ_WRITE"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret (&command_buf1); if ((status & 1) == 0) callable_error(&status); /* Pass the data definition statement specified by the user */ /* to RDB$INTERPRET. */ command_buf.dsc$w_length = strlen(literal); status = rdb$interpret (&command_buf); succeed = TRUE; if ((status & 1) == 0) { callable_error(&status); succeed = FALSE; } if (succeed == TRUE) { printf ("Transaction successful"); /* Commit */ strcpy(literal1, "COMMIT"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret (&command_buf1); if ((status & 1) == 0) callable_error(&status); } else { printf ("Transaction failed"); /* Rollback */ strcpy(literal1, "ROLLBACK"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret (&command_buf1); if ((status & 1) == 0) callable_error(&status); } /* Finish database. */ strcpy(literal1, "FINISH"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1, &pers); response[0] = EOS; } }/* End ddl_stmnt */ callable () /* -------------------------------------------------------------------*/ /* This function demonstrates how to embed Callable RDO statements */ /* that perform data manipulation tasks in an RDML/C program. Note */ /* that you should always use RDML/C DML to perform data manipulation */ /* tasks in C, unless special circumstances require that you use */ /* Callable RDO. Callable RDO uses more resources and is slower */ /* than using RDML/C. */ /* -------------------------------------------------------------------*/ { DECLARE_VARIABLE coll_code SAME AS COLLEGES.COLLEGE_CODE; DECLARE_VARIABLE coll_name SAME AS COLLEGES.COLLEGE_NAME; DECLARE_VARIABLE coll_city SAME AS COLLEGES.CITY; DECLARE_VARIABLE coll_state SAME AS COLLEGES.STATE; DECLARE_VARIABLE coll_postal SAME AS COLLEGES.POSTAL_CODE; int exit; int succeed; int found_rec; char literal1 [255]; /* RDO command buffer */ char response[80]; /* User's response from read_string() */ long status; /* Status returned from RDB$INTERPRET */ long db_handle; /* Database handle */ /* Declare database handle. */ struct dsc$descriptor pers; /* Declare string descriptors. */ $DESCRIPTOR (command_buf1, literal1); $DESCRIPTOR (colcode, coll_code); $DESCRIPTOR (colcity, coll_city); $DESCRIPTOR (colname, coll_name); $DESCRIPTOR (colstate, coll_state); $DESCRIPTOR (poscode, coll_postal); /* Set up database handle. */ pers.dsc$a_pointer = &db_handle; pers.dsc$b_dtype = DSC$K_DTYPE_L; pers.dsc$b_class = DSC$K_CLASS_S; exit = FALSE; status = 0; /* Invoke the database in Callable RDO. The DATABASE statement */ /* issued at the beginning of the program (using RDML) is unknown to */ /* Callable RDO. If an error occurs during the invoke, call an error */ /* handler. */ strcpy(literal1, "invoke database !val = filename 'mf_personnel'"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1, &pers); if ((status & 1) == 0) callable_error(&status); /* Prompt user for the college code of the COLLEGES record he */ /* or she wants to view. */ while (exit != TRUE) { /* Initialize loop variables. */ retry = 0; found_rec = TRUE; succeed = FALSE; response[0] = EOS; /* EOS is the null terminator */ strcpy (coll_city, "Unknown"); strcpy (coll_name, "Unknown"); coll_state[0] = coll_postal[0] = coll_code[0] = EOS; while (check_response (response, "Y") != 0) { retry = 0; printf(" \n"); printf("Please enter the college code of the college"); read_string(" or type exit: ", coll_code, sizeof(coll_code)-1); if (check_response (coll_code, "EXIT") == 0) { exit = TRUE; break; } read_string("Did you enter the code correctly? (Y,N): ", response, sizeof (response)-1); } while ((!succeed) && (retry < 5) && (exit != TRUE)) { succeed = TRUE; /* Place the RDO START_TRANSACTION statement in a C variable. */ /* Pass this variable to RDB$INTERPRET. */ strcpy(literal1, "START_TRANSACTION READ_WRITE RESERVING COLLEGES "); strcat(literal1, "FOR EXCLUSIVE WRITE NOWAIT"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1); if ((status & 1) == 0) { succeed = FALSE; retry++; callable_error(&status); } } if (succeed == TRUE) { /* Start a stream of COLLEGES records. */ strcpy (literal1, "START_STREAM coll_info USING C IN COLLEGES "); strcat (literal1, "WITH C.COLLEGE_CODE = !val"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1, &colcode); if ((status & 1) == 0) callable_error(&status); /* Fetch a record from stream coll_info. */ strcpy (literal1, "FETCH coll_info"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1); if ((status & 1) == 0) { callable_error(&status); found_rec = FALSE; } if (found_rec) { /* Get name of college and city. */ strcpy (literal1, "GET !val = C.COLLEGE_NAME; !val = C.CITY "); strcat (literal1, "END_GET"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1, &colname, &colcity); if ((status & 1) == 0) callable_error(&status); /* Null terminate coll_name and coll_city. */ coll_name [colname.dsc$w_length] = EOS; coll_city [colcity.dsc$w_length] = EOS; /* Get state and postal code. */ strcpy (literal1, "GET !val = C.STATE; !val = C.POSTAL_CODE "); strcat (literal1, "END_GET"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1, &colstate, &poscode); if ((status & 1) == 0) callable_error(&status); /* Null terminate coll_state and coll_postal. */ coll_state [sizeof(coll_state)-1] = EOS; coll_postal [sizeof(coll_postal)-1] = EOS; /* Print college information. */ printf (" \n"); printf ("College is: %s\n", coll_name); printf ("College city is: %s\n", coll_city); printf ("College state is: %s\n", coll_state); printf ("Postal code is: %s\n\n", coll_postal); /* End stream */ strcpy (literal1, "END_STREAM coll_info"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1); if ((status & 1) == 0) callable_error(&status); } /* Commit transaction */ strcpy (literal1, "COMMIT"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1); if ((status & 1) == 0) callable_error(&status); } } /* End while (Exit != TRUE). */ /* Finish database. */ strcpy (literal1, "FINISH"); command_buf1.dsc$w_length = strlen(literal1); status = rdb$interpret(&command_buf1); if ((status & 1) == 0) callable_error(&status); } /* End callable */ store_res() /************************************************************/ /* This function demonstrates how to store a record with */ /* a field of data type SEGMENTED STRING */ /************************************************************/ { DECLARE_VARIABLE employee_id SAME AS RESUMES.EMPLOYEE_ID; char response[80]; /* User's response from read_string() */ char my_file[21]; /* Resume file */ char buffer[80]; /* Temporary Buffer for fgets */ FILE *fopen(), *fp; /* File pointer */ employee_id[0] = EOS; /* EOS is the null terminator */ while (TRUE) { response[0] = EOS; while (check_response (response, "Y") != 0) { printf (" \n"); /* Prompt user for employee ID of the EMPLOYEES */ /* record that he or she wants to store. */ printf ("Please enter the ID number of the Employee\n"); read_string (" or type exit: ", employee_id, sizeof (employee_id)-1); if (check_response (employee_id, "EXIT") == 0) return; /* Prompt user for the file name of the resume to be stored. */ read_string ("Please enter file name of the resume: ", my_file, sizeof (my_file)-1); read_string ("Have you entered all the data correctly? (Y,N) ", response, sizeof (response)-1); } fp = fopen(my_file, "r"); START_TRANSACTION READ_WRITE RESERVING RESUMES FOR SHARED WRITE; /* Use the STORE statement with segmented strings to store the */ /* record. The outer STORE statement creates the new RESUMES */ /* record. The inner STORE stores the individual segements of */ /* the SEGMENTED STRING field. */ STORE R IN RESUMES USING ON ERROR handle_error(); END_ERROR strcpy (R.EMPLOYEE_ID, employee_id); while (fgets (buffer, 132, fp) != NULL) { STORE LINE IN R.RESUME strcpy (LINE.VALUE, buffer); LINE.LENGTH = strlen (buffer)-1; END_STORE; } END_STORE; fclose (fp); COMMIT; response[0] = EOS; } } /* End store_res */