Yurttas/PL/DBL/oracle/F/03/SPJ/q07.pc

 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}