Yurttas/PL/DBL/oracle/F/03/Bank/o02.pc

 1/*  
 2REM
 3REM o02.pc
 4REM
 5REM List the entire borrow relation in descending order of amount,
 6REM if several loans have the same amount, order them
 7REM in ascending order by loannumber.
 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 branchname[16];
25  int  loannumber;
26  char customername[16];
27  int  amount;
28} borrower;
29
30void connect_to_oracle();
31void sql_error();
32
33void main(int argc, char* argv[]) {
34  connect_to_oracle();
35
36  EXEC SQL DECLARE c CURSOR FOR
37    SELECT *
38    FROM Borrower
39    ORDER BY amount DESC,
40	     loannumber ASC;
41
42  EXEC SQL OPEN c;
43
44  EXEC SQL WHENEVER NOT FOUND DO BREAK;
45
46  printf("\nbranchname\tloannumber\tcustomername\tamount\n");
47  for(;;) {
48    EXEC SQL FETCH c INTO :borrower;
49    printf("%s\t%d\t\t%s\t%d\n", borrower.branchname,
50                                 borrower.loannumber,  
51                                 borrower.customername,
52                                 borrower.amount);
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}