Yurttas/PL/DBL/oracle/F/03/Company-B/q08.pc
Jump to navigation
Jump to search
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}