Yurttas/PL/DBL/oracle/F/03/PC/q07.pc

  1/*  
  2REM
  3REM q07.pc
  4REM
  5REM Find the manufacturer(s) of the computer(PC or laptop)
  6REM with the highest available speed.
  7REM
  8*/
  9
 10#include <stdio.h>
 11#include <sqlca.h>
 12
 13#define UNAME_LEN 32
 14#define PWD_LEN   16
 15
 16VARCHAR username[UNAME_LEN]; /* VARCHAR is an Oracle-supplied struct */
 17VARCHAR password[PWD_LEN];   /* varchar can be in lower case also. */
 18
 19char u_name[UNAME_LEN];
 20char pwd[PWD_LEN];
 21
 22struct {
 23  char maker[6];
 24  int speed;
 25} makerspeed;
 26
 27void connect_to_oracle();
 28void sql_error();
 29
 30void main(int argc, char* argv[]) {
 31  connect_to_oracle();
 32
 33  EXEC SQL
 34    CREATE VIEW ProdSpeed(model, speed)
 35      AS (SELECT model, speed
 36          FROM PC
 37          WHERE speed IN (SELECT MAX(speed)
 38                          FROM  PC))
 39         UNION
 40         (SELECT model, speed
 41          FROM Laptop
 42          WHERE speed IN (SELECT MAX(speed)
 43                          FROM  Laptop));
 44
 45  EXEC SQL DECLARE c CURSOR FOR
 46    SELECT P.maker, S.speed
 47    FROM Product P, ProdSpeed S
 48    WHERE P.model=S.model
 49      AND S.speed IN (SELECT MAX(speed)
 50                      FROM ProdSpeed);
 51
 52  EXEC SQL OPEN c;
 53
 54  EXEC SQL WHENEVER NOT FOUND DO BREAK;
 55
 56  printf("\nmaker\tspeed\n");
 57  for(;;) {
 58    EXEC SQL FETCH c INTO :makerspeed;
 59    printf("%s\t%d\n", makerspeed.maker,
 60                       makerspeed.speed);
 61  }
 62
 63  EXEC SQL CLOSE c;
 64
 65  EXEC SQL COMMIT WORK RELEASE;
 66
 67  exit(0);
 68}
 69
 70
 71void sql_error(char* msg) {
 72  char err_msg[128];
 73  int buflen, msglen;
 74
 75  EXEC SQL WHENEVER SQLERROR CONTINUE;
 76
 77  printf("%s \n", msg);
 78  buflen = sizeof(err_msg);
 79  sqlglm(err_msg, &buflen, &msglen);
 80  printf("%.*s \n", msglen, err_msg);
 81  exit(1);
 82}
 83
 84
 85void connect_to_oracle() {
 86  /* get your username/passwd from "user_pwd.txt" file */
 87
 88  FILE *in_file;
 89  in_file = fopen("user_pwd.txt", "r");
 90  fscanf(in_file, "%s", u_name);
 91  fscanf(in_file, "%s", pwd);
 92
 93  strncpy((char *) username.arr, u_name, UNAME_LEN);
 94  username.len = strlen((char *) username.arr);
 95
 96  strncpy((char *) password.arr, pwd, PWD_LEN);
 97  password.len = strlen((char *) password.arr);
 98
 99  EXEC SQL WHENEVER SQLERROR DO sql_error ("ORACLE error-- ");
100  EXEC SQL CONNECT :username IDENTIFIED BY :password;
101
102  printf("connected to oracle - \n");
103}