Yurttas/PL/DBL/oracle/F/03/Company-A/q05.pc

 1/*  
 2REM
 3REM q05.pc
 4REM
 5REM Retrieve the names of employees who work on every project.
 6REM
 7*/
 8
 9#include <stdio.h>
10#include <sqlca.h>
11
12#define UNAME_LEN 32
13#define PWD_LEN   16
14
15VARCHAR username[UNAME_LEN]; /* VARCHAR is an Oracle-supplied struct */
16VARCHAR password[PWD_LEN];   /* varchar can be in lower case also. */
17
18char u_name[UNAME_LEN];
19char pwd[PWD_LEN];
20
21struct {
22  char fname[12];
23  char minit;
24  char lname[16];
25} employee;
26
27void connect_to_oracle();
28void sql_error();
29
30void main(int argc, char* argv[]) {
31  connect_to_oracle();
32
33  EXEC SQL DECLARE c CURSOR FOR
34    SELECT DISTINCT E1.fname, E1.minit, E1.lname
35    FROM Employee E1
36    WHERE NOT EXISTS (SELECT pno
37                      FROM WorksOn
38                      WHERE pno NOT IN (SELECT pno
39                                        FROM Employee E2, WorksOn
40                                        WHERE essn = E2.ssn
41                                          AND E2.ssn = E1.ssn));
42
43  EXEC SQL OPEN c;
44
45  EXEC SQL WHENEVER NOT FOUND DO BREAK;
46
47  printf("\nfname\tminit\tlname\n");
48  for(;;) {
49    EXEC SQL FETCH c INTO :employee;
50    printf("%s\t%s\t%s\n", employee.fname,
51                           employee.minit,
52                           employee.lname);
53  }
54
55  EXEC SQL CLOSE c;
56
57  EXEC SQL COMMIT WORK RELEASE;
58
59  exit(0);
60}
61
62
63void sql_error(char* msg) {
64  char err_msg[128];
65  int buflen, msglen;
66
67  EXEC SQL WHENEVER SQLERROR CONTINUE;
68
69  printf("%s \n", msg);
70  buflen = sizeof(err_msg);
71  sqlglm(err_msg, &buflen, &msglen);
72  printf("%.*s \n", msglen, err_msg);
73  exit(1);
74}
75
76
77void connect_to_oracle() {
78  /* get your username/passwd from "user_pwd.txt" file */
79
80  FILE *in_file;
81  in_file = fopen("user_pwd.txt", "r");
82  fscanf(in_file, "%s", u_name);
83  fscanf(in_file, "%s", pwd);
84
85  strncpy((char *) username.arr, u_name, UNAME_LEN);
86  username.len = strlen((char *) username.arr);
87
88  strncpy((char *) password.arr, pwd, PWD_LEN);
89  password.len = strlen((char *) password.arr);
90
91  EXEC SQL WHENEVER SQLERROR DO sql_error ("ORACLE error-- ");
92  EXEC SQL CONNECT :username IDENTIFIED BY :password;
93
94  printf("connected to oracle - \n");
95}