Yurttas/PL/DBL/oracle/F/03/SPJ/q14.pc

  1/*  
  2REM
  3REM q14.pc
  4REM
  5REM Construct a table containing a list of project numbers
  6REM for projects that are either located in 'London' or are
  7REM supplied by a 'London' supplier.
  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 jn[4];
 25} londonprojects;
 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 TABLE LondonProjects (JN VARCHAR2(4));
 35    
 36  EXEC SQL
 37    INSERT INTO LondonProjects
 38    (SELECT T.JN
 39     FROM Shipments T, Suppliers S
 40     WHERE T.SN = S.SN
 41       AND S.CITY = 'London')
 42    UNION
 43    (SELECT JN
 44     FROM Projects
 45     WHERE CITY = 'London');
 46
 47  EXEC SQL DECLARE c CURSOR FOR
 48    SELECT *
 49    FROM LondonProjects;
 50
 51  EXEC SQL OPEN c;
 52
 53  EXEC SQL WHENEVER NOT FOUND DO BREAK;
 54
 55  printf("\njn\n");
 56  for(;;) {
 57    EXEC SQL FETCH c INTO :londonprojects;
 58    printf("%s\n", londonprojects.jn);
 59  }
 60
 61  EXEC SQL CLOSE c;
 62
 63  EXEC SQL COMMIT WORK RELEASE;
 64
 65  exit(0);
 66}
 67
 68
 69void sql_error(char* msg) {
 70  char err_msg[128];
 71  int buflen, msglen;
 72
 73  EXEC SQL WHENEVER SQLERROR CONTINUE;
 74
 75  printf("%s \n", msg);
 76  buflen = sizeof(err_msg);
 77  sqlglm(err_msg, &buflen, &msglen);
 78  printf("%.*s \n", msglen, err_msg);
 79  exit(1);
 80}
 81
 82
 83void connect_to_oracle() {
 84  /* get your username/passwd from "user_pwd.txt" file */
 85
 86  FILE *in_file;
 87  in_file = fopen("user_pwd.txt", "r");
 88  fscanf(in_file, "%s", u_name);
 89  fscanf(in_file, "%s", pwd);
 90
 91  strncpy((char *) username.arr, u_name, UNAME_LEN);
 92  username.len = strlen((char *) username.arr);
 93
 94  strncpy((char *) password.arr, pwd, PWD_LEN);
 95  password.len = strlen((char *) password.arr);
 96
 97  EXEC SQL WHENEVER SQLERROR DO sql_error ("ORACLE error-- ");
 98  EXEC SQL CONNECT :username IDENTIFIED BY :password;
 99
100  printf("connected to oracle - \n");
101}