1/*
2REM
3REM q07.pc
4REM
5REM Get project numbers for projects supplied with part 'P01'
6REM in an average quantity greater than the greatest quantity
7REM in which any part is supplied to project 'J01'.
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 jn[4];
25} shipments;
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 jn
35 FROM Shipments
36 WHERE pn = 'P1'
37 GROUP BY jn
38 HAVING AVG(quantity) > (SELECT MAX(quantity)
39 FROM Shipments
40 WHERE jn='J01');
41
42 EXEC SQL OPEN c;
43
44 EXEC SQL WHENEVER NOT FOUND DO BREAK;
45
46 printf("\njn\n");
47 for(;;) {
48 EXEC SQL FETCH c INTO :shipments;
49 printf("%s\n", shipments.jn);
50 }
51
52 EXEC SQL CLOSE c;
53
54 EXEC SQL COMMIT WORK RELEASE;
55
56 exit(0);
57}
58
59
60void sql_error(char* msg) {
61 char err_msg[128];
62 int buflen, msglen;
63
64 EXEC SQL WHENEVER SQLERROR CONTINUE;
65
66 printf("%s \n", msg);
67 buflen = sizeof(err_msg);
68 sqlglm(err_msg, &buflen, &msglen);
69 printf("%.*s \n", msglen, err_msg);
70 exit(1);
71}
72
73
74void connect_to_oracle() {
75 /* get your username/passwd from "user_pwd.txt" file */
76
77 FILE *in_file;
78 in_file = fopen("user_pwd.txt", "r");
79 fscanf(in_file, "%s", u_name);
80 fscanf(in_file, "%s", pwd);
81
82 strncpy((char *) username.arr, u_name, UNAME_LEN);
83 username.len = strlen((char *) username.arr);
84
85 strncpy((char *) password.arr, pwd, PWD_LEN);
86 password.len = strlen((char *) password.arr);
87
88 EXEC SQL WHENEVER SQLERROR DO sql_error ("ORACLE error-- ");
89 EXEC SQL CONNECT :username IDENTIFIED BY :password;
90
91 printf("connected to oracle - \n");
92}