/****************************************************************/
/* Recursive Query Example (Section 3.7)                        */
/* Chapter 3; Oracle Programming -- A Primer                    */
/*            by R. Sunderraman                                 */
/****************************************************************/

#include <stdio.h>
#define TRUE 1
#define FALSE 0

EXEC SQL BEGIN DECLARE SECTION;
  int     eid, a;
  varchar userid[4],password[9];
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE sqlca;

main()					
{
  int newrowadded;

  printf("Enter your USERID: ");
  scanf("%s", userid.arr);
  userid.len = strlen(userid.arr);
  printf("Enter your PASSWORD: ");
  system("stty -echo");
  scanf("%s", password.arr);
  password.len = strlen(password.arr); 
  system("stty echo");
  printf("\n");

  EXEC SQL CONNECT :userid IDENTIFIED BY :password;

  exec sql declare c1 cursor for 
    select eid from emp where mgrid = :eid;

  exec sql declare c2 cursor for
    select eid from emp,answer where mgrid = a;

  exec sql declare c3 cursor for
    select a from answer;

  exec sql create table answer (a integer not null, primary key (a));

  printf("Type in employee id:");
  scanf("%d",&eid);

  exec sql open c1;
  exec sql fetch c1 into :a;
  while (sqlca.sqlcode == 0) {
    exec sql insert into answer values (:a);
    exec sql fetch c1 into :a;
  }
  exec sql close c1;
  exec sql commit work;

  do {
   newrowadded = FALSE;
   exec sql open c2;
   exec sql fetch c2 into :a;
   while (sqlca.sqlcode == 0) {
     exec sql insert into answer values (:a);
     if (sqlca.sqlcode == 0) 
       newrowadded = TRUE;
     exec sql fetch c2 into :a;
   }
   exec sql close c2;
  } while (newrowadded);
  exec sql commit work;  

  printf("Answer is\n");
  exec sql open c3;
  exec sql fetch c3 into :a;
  while (sqlca.sqlcode == 0) {
    printf("%d\n",a); 
    exec sql fetch c3 into :a;
  }
  exec sql close c3;
  exec sql commit work;

  exec sql drop table answer;
  exec sql commit work;
}