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

  1/*  
  2REM
  3REM u07.pc
  4REM
  5REM Insert that `Smith' has $1200 in account 9732 at the `Perryridge'
  6REM   branch.
  7REM
  8
  9REM or
 10REM INSERT INTO Depositor
 11REM     (branchname, accountnumber, customername, balance)
 12REM   VALUES ('Perryridge', 9989, 'Smith', 1200);
 13
 14REM or
 15REM INSERT INTO Depositor
 16REM     (accountnumber, customername, branchname, balance)
 17REM   VALUES (9990, 'Smith', 'Perryridge', 1200);
 18*/
 19
 20#include <stdio.h>
 21#include <sqlca.h>
 22
 23#define UNAME_LEN 32
 24#define PWD_LEN   16
 25
 26VARCHAR username[UNAME_LEN]; /* VARCHAR is an Oracle-supplied struct */
 27VARCHAR password[PWD_LEN];   /* varchar can be in lower case also. */
 28
 29char u_name[UNAME_LEN];
 30char pwd[PWD_LEN];
 31
 32struct {
 33  char branchname[16];
 34  int  accountnumber;
 35  char customername[16];
 36  int  balance;
 37} depositor;
 38
 39void connect_to_oracle();
 40void sql_error();
 41
 42void main(int argc, char* argv[]) {
 43  connect_to_oracle();
 44
 45  EXEC SQL
 46    INSERT INTO Depositor
 47      VALUES ('Perryridge', 9732, 'Smith', 1200);
 48
 49  EXEC SQL DECLARE c CURSOR FOR
 50    SELECT *
 51    FROM Depositor;
 52
 53  EXEC SQL OPEN c;
 54
 55  EXEC SQL WHENEVER NOT FOUND DO BREAK;
 56
 57  printf("\nbranchname\taccountnumber\tcustomername\tbalance\n");
 58  for(;;) {
 59    EXEC SQL FETCH c INTO :depositor;
 60    printf("%s\t%d\t\t%s\t%d\n", depositor.branchname,
 61                                 depositor.accountnumber,  
 62                                 depositor.customername,
 63                                 depositor.balance);
 64  }
 65
 66  EXEC SQL CLOSE c;
 67
 68  EXEC SQL COMMIT WORK RELEASE;
 69
 70  exit(0);
 71}
 72
 73
 74void sql_error(char* msg) {
 75  char err_msg[128];
 76  int buflen, msglen;
 77
 78  EXEC SQL WHENEVER SQLERROR CONTINUE;
 79
 80  printf("%s \n", msg);
 81  buflen = sizeof(err_msg);
 82  sqlglm(err_msg, &buflen, &msglen);
 83  printf("%.*s \n", msglen, err_msg);
 84  exit(1);
 85}
 86
 87
 88void connect_to_oracle() {
 89  /* get your username/passwd from "user_pwd.txt" file */
 90
 91  FILE *in_file;
 92  in_file = fopen("user_pwd.txt", "r");
 93  fscanf(in_file, "%s", u_name);
 94  fscanf(in_file, "%s", pwd);
 95
 96  strncpy((char *) username.arr, u_name, UNAME_LEN);
 97  username.len = strlen((char *) username.arr);
 98
 99  strncpy((char *) password.arr, pwd, PWD_LEN);
100  password.len = strlen((char *) password.arr);
101
102  EXEC SQL WHENEVER SQLERROR DO sql_error ("ORACLE error-- ");
103  EXEC SQL CONNECT :username IDENTIFIED BY :password;
104
105  printf("connected to oracle - \n");
106}