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}