Yurttas/PL/DBL/oracle/F/03/Movie/q08.pc

 1/*  
 2REM
 3REM q08.pc
 4REM
 5REM Modify the relation MovieExec(name, address, 
 6REM cn, networth) by prepending the title Pres.  
 7REM in front of every movie executive who is 
 8REM the president of a studio.
 9REM
10*/
11
12#include <stdio.h>
13#include <sqlca.h>
14
15#define UNAME_LEN 32
16#define PWD_LEN   16
17
18VARCHAR username[UNAME_LEN]; /* VARCHAR is an Oracle-supplied struct */
19VARCHAR password[PWD_LEN];   /* varchar can be in lower case also. */
20
21char u_name[UNAME_LEN];
22char pwd[PWD_LEN];
23
24struct {
25  char name[18];
26  int address;
27  int cn;
28  int networth;
29} movieexec;
30
31void connect_to_oracle();
32void sql_error();
33
34void main(int argc, char* argv[]) {
35  connect_to_oracle();
36
37  EXEC SQL
38    UPDATE MovieExec
39    SET name = 'Pres. ' || name
40    WHERE cn IN (SELECT presCN
41                 FROM Studio);
42
43  EXEC SQL DECLARE c CURSOR FOR
44    SELECT *
45    FROM MovieExec;
46
47  EXEC SQL OPEN c;
48
49  EXEC SQL WHENEVER NOT FOUND DO BREAK;
50
51  printf("\nname\taddress\tcn\tnetworth\n");
52  for(;;) {
53    EXEC SQL FETCH c INTO :movieexec;
54    printf("%s\t%s\t%d\t%d\n", movieexec.name,
55                               movieexec.address,
56                               movieexec.cn,
57  }
58
59  EXEC SQL CLOSE c;
60
61  EXEC SQL COMMIT WORK RELEASE;
62
63  exit(0);
64}
65
66
67void sql_error(char* msg) {
68  char err_msg[128];
69  int buflen, msglen;
70
71  EXEC SQL WHENEVER SQLERROR CONTINUE;
72
73  printf("%s \n", msg);
74  buflen = sizeof(err_msg);
75  sqlglm(err_msg, &buflen, &msglen);
76  printf("%.*s \n", msglen, err_msg);
77  exit(1);
78}
79
80
81void connect_to_oracle() {
82  /* get your username/passwd from "user_pwd.txt" file */
83
84  FILE *in_file;
85  in_file = fopen("user_pwd.txt", "r");
86  fscanf(in_file, "%s", u_name);
87  fscanf(in_file, "%s", pwd);
88
89  strncpy((char *) username.arr, u_name, UNAME_LEN);
90  username.len = strlen((char *) username.arr);
91
92  strncpy((char *) password.arr, pwd, PWD_LEN);
93  password.len = strlen((char *) password.arr);
94
95  EXEC SQL WHENEVER SQLERROR DO sql_error ("ORACLE error-- ");
96  EXEC SQL CONNECT :username IDENTIFIED BY :password;
97
98  printf("connected to oracle - \n");
99}