/* Copyright © , 2004, Oracle Corporation. All Rights Reserved. */ /* * This program calls C subroutines that display and store resumes. * Because resumes require the special LIST OF BYTE VARYING data type, to * modify a resume you must first delete the resume, then substitute a new * file to replace the old resume. * * A personnel database must exist in the directory where you precompile * this program. Before you run this program, you also must create the * resume file(s) that you will store in personnel. * */ #include #ifdef VMS #include #endif #include #include #include /* Declare the SQLCA. */ EXEC SQL INCLUDE SQLCA; /* Attach to the database. */ EXEC SQL DECLARE ALIAS FILENAME personnel; /* Variables for main program use. */ char employee_id[6]; char option; char confirm_flag; char input_buf[256]; /* Variables for SQL list data type. */ char resume_seg_id[8]; /* Segment id */ char resume_segment[80]; /* List segment */ /* Variables for inserting the resume from a file to the database. */ FILE *res_file_ptr; /* File pointer */ char resume_file[30]; /* Name of resume file */ char resume_line[80]; /* One line from file */ /* Functions and procedures used by the main program. */ /* This procedure demonstrates how to retrieve a column of * the data type LIST OF BYTE VARYING. */ display_resume() { printf("\n\n\n\n"); printf("Display a Resume \n\n"); /* Prompt the user to enter the ID of the employee * resume that he or she wants to view. If the user * enters 'exit' then exit the procedure. */ printf("Please enter the ID of the employee whose resume\n"); printf("you want to display or type exit:\n"); gets(input_buf); sscanf(input_buf, " %s", employee_id); while (((strcmp(employee_id,"exit")) != 0) && ((strcmp(employee_id,"EXIT")) != 0)) { /* Start a transaction. */ EXEC SQL SET TRANSACTION READ ONLY RESERVING RESUMES FOR SHARED READ; /* Declare and open a table cursor. */ EXEC SQL DECLARE ACURSOR CURSOR FOR SELECT EMPLOYEE_ID, RESUME FROM RESUMES WHERE EMPLOYEE_ID = :employee_id; EXEC SQL OPEN ACURSOR; if (SQLCA.SQLCODE != 0) sql_signal(); /* Fetch the row containing the specified ID and resume. */ EXEC SQL FETCH ACURSOR INTO :employee_id, :resume_seg_id; /* If a record with the specified ID was not found then inform * the user. */ if (SQLCA.SQLCODE == 100) { printf("\n\nEmployee %s", employee_id); printf(" has no resume on file\n\n\n"); } /* Declare and open a list cursor. */ else if (SQLCA.SQLCODE == 0) { EXEC SQL DECLARE BCURSOR LIST CURSOR FOR SELECT RESUME WHERE CURRENT OF ACURSOR; EXEC SQL OPEN BCURSOR; if (SQLCA.SQLCODE != 0) sql_signal(); } while (SQLCA.SQLCODE == 0) { /* Fetch each segment of the resume. */ EXEC SQL FETCH BCURSOR INTO :resume_segment; /* Display each segment of the resume as it is retrieved from the database. */ if (SQLCA.SQLCODE == 0) printf (" %s\n", resume_segment); else if (SQLCA.SQLCODE != 100) sql_signal(); } /* Close the list cursor. */ EXEC SQL CLOSE BCURSOR; /* Close the table cursor. */ EXEC SQL CLOSE ACURSOR; if (SQLCA.SQLCODE != 0) sql_signal(); EXEC SQL COMMIT; if (SQLCA.SQLCODE != 0) sql_signal(); /* Prompt for another employee's ID. */ printf("\n\n"); printf("Please enter the ID of the employee whose resume\n"); printf("you want to display or type exit:\n"); gets(input_buf); sscanf(input_buf, " %s", employee_id); } return; } /* This procedure demonstrates how to store a row with a * column of data type LIST OF BYTE VARYING. */ store_resume() { printf("\n\n\n\n"); printf(" Store a Resume \n\n"); /* Prompt the user for the employee ID of the employee whose resume * he or she wants to store. If the user enters 'exit' then exit the * procedure. */ printf("Please enter the ID of the new employee whose resume\n"); printf("you want to store or type exit:\n"); gets(input_buf); sscanf(input_buf, " %s", employee_id); while (((strcmp(employee_id,"exit")) != 0) && ((strcmp(employee_id,"EXIT")) != 0)) { /* Prompt the user for the file name of the resume to be stored. */ printf("Please enter file name of new resume: "); gets(input_buf); sscanf(input_buf, "%s", resume_file); /* Start a transaction reserving the RESUMES table for SHARED WRITE. */ EXEC SQL SET TRANSACTION READ WRITE RESERVING RESUMES FOR SHARED WRITE; if (SQLCA.SQLCODE != 0) sql_signal(); /* Declare a table cursor for insert only. */ EXEC SQL DECLARE CCURSOR INSERT ONLY TABLE CURSOR FOR SELECT RESUME,EMPLOYEE_ID FROM RESUMES; /* Open the table cursor and insert an ID number. */ EXEC SQL OPEN CCURSOR; if (SQLCA.SQLCODE != 0) sql_signal(); EXEC SQL INSERT INTO CURSOR CCURSOR (employee_id) values (:employee_id); if (SQLCA.SQLCODE != 0) sql_signal(); /* Declare a list cursor and open it. */ EXEC SQL DECLARE DCURSOR INSERT ONLY LIST CURSOR FOR SELECT RESUME WHERE CURRENT OF CCURSOR; EXEC SQL OPEN DCURSOR; if (SQLCA.SQLCODE != 0) sql_signal(); /* Open the resume file. */ res_file_ptr = fopen(resume_file, "r"); /* Insert the resume file for the employee whose ID you just entered. */ while (fgets (resume_line, 80, res_file_ptr) != NULL) { /* Strip off any newline character before you insert it into the database. */ if (strchr(resume_line,'\n') != 0) memset(strchr(resume_line,'\n'),'\0',1); EXEC SQL INSERT INTO CURSOR DCURSOR VALUES (:resume_line); if (SQLCA.SQLCODE != 0) sql_signal(); } fclose (res_file_ptr); /* Close the list cursor. */ EXEC SQL CLOSE DCURSOR; if (SQLCA.SQLCODE != 0) sql_signal(); /* Close the table cursor. */ EXEC SQL CLOSE CCURSOR; if (SQLCA.SQLCODE != 0) sql_signal(); /* Commit the changes. */ EXEC SQL COMMIT; if (SQLCA.SQLCODE == -1001) { printf("\n\nEmployee %s", employee_id); printf(" already has a resume on file or\n"); printf("is not entered in the EMPLOYEES table.\n\n\n"); EXEC SQL ROLLBACK; } else if (SQLCA.SQLCODE != 0) { sql_signal(); EXEC SQL ROLLBACK; } /* Prompt for another employee's ID. */ printf("\n\n"); printf("Please enter the ID of the new employee whose resume\n"); printf("you want to store or type exit:\n"); gets(input_buf); sscanf(input_buf, " %s", employee_id); } } /* Main program. */ main() { int retries = 0; /* Display menu of choices to the user. */ printf(" Main Menu\n"); printf(" Sample Application\n"); printf("\n\n"); printf("1 : Display a RESUME\n"); printf("2 : Store a RESUME\n"); printf("9 : Exit the Program\n\n"); while (option != '9') { printf("Please enter an option number and press RETURN: "); gets(input_buf); sscanf(input_buf, " %c", &option); /* The switch statement directs the program to perform appropriate procedures * based on the user's reponse to the preceding menu. */ switch (option) { case '1': retries = 0; display_resume(); break; case '2': retries = 0; store_resume(); break; case '9': exit (1); break; default: if (retries > 10) { printf("Too many retries"); exit (1); } printf("Please enter 1, 2 or 9. \n"); retries++; } printf("\n\n\n"); printf("1 : Display a RESUME\n"); printf("2 : Store a RESUME\n"); printf("9 : Exit the Program\n\n"); } EXEC SQL DISCONNECT DEFAULT; }