Yurttas/PL/DBL/oracle/F/03/Company-B/q08.pc

 1/*  
 2REM
 3REM q08.pc
 4REM
 5REM Assume the companies may be located in several cities.
 6REM Find all companies located in every city in which
 7REM 'Small Bank Corporation' is located.
 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 companyname[24];
25} company;
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 C1.companyname 
35    FROM Company C1
36    WHERE NOT EXISTS
37                (SELECT city 
38                 FROM Company 
39                 WHERE companyname='Small Bank Corporation' 
40                   AND city NOT IN (SELECT C2.city 
41                                    FROM Company C2 
42                                    WHERE C1.companyname = C2.companyname)) 
43      AND C1.companyname <> 'Small Bank Corporation';
44
45  EXEC SQL OPEN c;
46
47  EXEC SQL WHENEVER NOT FOUND DO BREAK;
48
49  printf("\ncompanyname\n");
50  for(;;) {
51    EXEC SQL FETCH c INTO :company;
52    printf("%s\n", company.companyname);
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}