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

 1/*  
 2REM
 3REM u08.pc
 4REM
 5REM Suppose that we want to provide all loan customers in the
 6REM `Perryridge' branch with a $200 savings account.(Let the
 7REM loannumber serve as the accountnumber for the new savings account.)
 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  accountnumber;
26  char customername[16];
27  int  balance;
28} depositor;
29
30void connect_to_oracle();
31void sql_error();
32
33void main(int argc, char* argv[]) {
34  connect_to_oracle();
35
36  EXEC SQL
37    INSERT INTO Depositor
38      SELECT branchname, loannumber, customername, 200
39      FROM Borrower
40      WHERE branchname = 'Perryridge';
41
42  EXEC SQL DECLARE c CURSOR FOR
43    SELECT *
44    FROM Depositor;
45
46  EXEC SQL OPEN c;
47
48  EXEC SQL WHENEVER NOT FOUND DO BREAK;
49
50  printf("\nbranchname\taccountnumber\tcustomername\tbalance\n");
51  for(;;) {
52    EXEC SQL FETCH c INTO :depositor;
53    printf("%s\t%d\t\t%s\t%d\n", depositor.branchname,
54                                 depositor.accountnumber,  
55                                 depositor.customername,
56                                 depositor.balance);
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}