Yurttas/PL/DBL/oracle/F/03/Company-A/q05.pc
Revision as of 23:41, 4 November 2013 by MassBot1 (talk | contribs) (Created page with "<syntaxhighlight lang="text" line start="1" enclose="div">→REM REM q05.pc REM REM Retrieve the names of employees who work on every project. REM: #include <stdio.h> #in...")
1/*
2REM
3REM q05.pc
4REM
5REM Retrieve the names of employees who work on every project.
6REM
7*/
8
9#include <stdio.h>
10#include <sqlca.h>
11
12#define UNAME_LEN 32
13#define PWD_LEN 16
14
15VARCHAR username[UNAME_LEN]; /* VARCHAR is an Oracle-supplied struct */
16VARCHAR password[PWD_LEN]; /* varchar can be in lower case also. */
17
18char u_name[UNAME_LEN];
19char pwd[PWD_LEN];
20
21struct {
22 char fname[12];
23 char minit;
24 char lname[16];
25} employee;
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 E1.fname, E1.minit, E1.lname
35 FROM Employee E1
36 WHERE NOT EXISTS (SELECT pno
37 FROM WorksOn
38 WHERE pno NOT IN (SELECT pno
39 FROM Employee E2, WorksOn
40 WHERE essn = E2.ssn
41 AND E2.ssn = E1.ssn));
42
43 EXEC SQL OPEN c;
44
45 EXEC SQL WHENEVER NOT FOUND DO BREAK;
46
47 printf("\nfname\tminit\tlname\n");
48 for(;;) {
49 EXEC SQL FETCH c INTO :employee;
50 printf("%s\t%s\t%s\n", employee.fname,
51 employee.minit,
52 employee.lname);
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}