/* NAME
     sample2.pc	- Example for 459 course

   FUNCTION
     For All faculty, retrieve and output their ID and name.  For
     each, output their course number, section number, description, and 
     credits of the courses they are teaching.
   LAST MODIFIED
    Mohammadi    3/28/96
*/

#include <stdio.h>


EXEC SQL BEGIN DECLARE SECTION;
    char       auto_login='/';
    VARCHAR    name [100][30];/* array of faculty names */  
    int        id [100];/* array of faculty IDs */  
    VARCHAR    desc[30];
    int        credit;
    int        cnum;
    int	       secnum;
    int	       idnum;
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE ora_proc:sqlca;
        /* declare the cursor for get the course list */
        EXEC SQL DECLARE list_courses CURSOR FOR
            SELECT section.c#,section.sec#,cdesc,credits
              FROM course,section
             WHERE course.c#=section.c#
	       and fss#=:idnum
	     ORDER BY section.c#,section.sec#;
main()
{
    int i;
    /* 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");

    /* init the arrays */
    for (i=0;i<100;i++)
    { 
	name[i].arr[0]='\0';name[i].len=0;
        id[i]=0;
    }

    /* retrieve faculty id&name */
    EXEC SQL SELECT FSS#,NAME into :id,:name from faculty;

    for (i=0;name[i].arr[0]!='\0';i++)
    {
	name[i].arr[name[i].len]='\0';
	printf("\nFaculty ID: %d    Name: %s\n", id[i],name[i].arr);
        idnum=id[i];  /* idnum is used in the cursor */

        /* open cursor */
        EXEC SQL OPEN list_courses;

        /* get first record */
        EXEC SQL FETCH list_courses INTO :cnum,:secnum,:desc,:credit;
        if (sqlca.sqlcode != 0) 
	   printf("\n NO TEACHING ASSIGNMENT THIS TERM \n");
        else
        {
           printf("\nC#        Sec#      Description               Credits
");
           printf( "___       ____      ___________               _______\n");
           do
           {
              desc.arr[desc.len]='\0'; /* null terminate desc */
              printf("%-4d      %-4d      %-30s%3d\n", cnum,secnum,desc.arr, 
                      credit);
              EXEC SQL FETCH list_courses INTO :cnum,:secnum,:desc,:credit;
           }  while (sqlca.sqlcode == 0);
        }
        EXEC SQL CLOSE list_courses;
    }
    EXEC SQL COMMIT WORK RELEASE;  /* Log off the database. */
    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);

}