/* NAME
     sample.pc	- Example for 459 course

   FUNCTION
     Get faculty ID in as input.  Retrieve and out faculty name.  Find and 
     output the description, credits, and number of sections of courses 
     taught by the specified faculty.  At the end, output the total load for 
     the faculty (i.e. the total credits taught by the faculty).  
   LAST MODIFIED
    Mohammadi    3/26/96
*/

#include <stdio.h>
#include <string.h>

EXEC SQL BEGIN DECLARE SECTION;
    char       auto_login='/';
    /* VARCHAR is a defined type for the precompiler. It has an arr and a len
       field */
    VARCHAR    name[30];  
    VARCHAR    desc[30];
    int        credit;
    int        count;
    int        id_num;
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE ora_proc:sqlca;

main()
{
    /* initialize accumulator for credit count */
    int total_count=0;

    /* set error trap for sql errors (i.e. login, syntax of select, etc.) */
    EXEC SQL WHENEVER SQLERROR GOTO sqlerror;

    /* login to oracle */
    EXEC SQL CONNECT :auto_login;
    printf("\nConnected to ORACLE\n");

    /* get the faculty ID */
    printf("Please Enter Faculty ID:");scanf("%d",&id_num);

    /* retrieve+output faculty name */
    EXEC SQL SELECT NAME into :name from faculty where fss#=:id_num;
    name.arr[name.len]='\0'; /* null terminate name */
    printf("Faculty Name: %s\n", name.arr);

    /* declare the cursor for get the course list */
    EXEC SQL DECLARE list_courses CURSOR FOR
        SELECT cdesc,credits,count(sec#)
          FROM course,section
         WHERE course.c#=section.c#
	   and fss#=:id_num
	 GROUP BY cdesc,credits;

    /* open cursor */
    EXEC SQL OPEN list_courses;

    /* set trap for end of data */
    EXEC SQL WHENEVER NOT FOUND GOTO end_of_fetch;

    printf("\nCourse Description              Credits   # of sections
");
    printf("__________________              _______   _____________\n");

    /* loop until fetch fails, control is then transfered to end_of_fetch. */
    for ( ; ; )
    {
        EXEC SQL FETCH list_courses INTO :desc,:credit,:count;
        desc.arr[desc.len]='\0'; /* null terminate desc */
        printf("%-30s%9d   %13d\n", desc.arr, credit,count);
        total_count = total_count + credit * count;
    }

end_of_fetch:
    EXEC SQL CLOSE list_courses;
    EXEC SQL COMMIT WORK RELEASE;  /* Log off the database. */
    printf("\nTotal Credit Count for %s is %d",name.arr,total_count);
    printf("\nHave a good day!\n\n");
    exit(0);

sqlerror:
    printf("\n\n% .70s \n\n", sqlca.sqlerrm.sqlerrmc);
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);

}