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

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