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

  1/*  
  2REM
  3REM q08.pc
  4REM
  5REM Change the color of part 'P02' to 'Yellow',
  6REM increase its weight by 5, and
  7REM set its city to unknown('NULL').
  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 pn[4];
 25  char pname[12];
 26  char color[8];
 27  int  weight;
 28  char city[16];
 29} parts;
 30
 31void connect_to_oracle();
 32void sql_error();
 33
 34void main(int argc, char* argv[]) {
 35  connect_to_oracle();
 36
 37  EXEC SQL
 38    UPDATE Parts
 39    SET color='Yellow',
 40        weight=weight+5,
 41        city='NULL'
 42    WHERE pn = 'P02';
 43
 44  EXEC SQL DECLARE c CURSOR FOR
 45    SELECT *
 46    FROM Parts;
 47
 48  EXEC SQL OPEN c;
 49
 50  EXEC SQL WHENEVER NOT FOUND DO BREAK;
 51
 52  printf("\npn\tpname\tcolor\tweight\tcity\n");
 53  for(;;) {
 54    EXEC SQL FETCH c INTO :parts;
 55    printf("%s\t%s\t%s\t%d\t%s\n", parts.pn,
 56                                   parts.pname,
 57                                   parts.color,
 58                                   parts.weight,
 59                                   parts.city);
 60  }
 61
 62  EXEC SQL CLOSE c;
 63
 64  EXEC SQL COMMIT WORK RELEASE;
 65
 66  exit(0);
 67}
 68
 69
 70void sql_error(char* msg) {
 71  char err_msg[128];
 72  int buflen, msglen;
 73
 74  EXEC SQL WHENEVER SQLERROR CONTINUE;
 75
 76  printf("%s \n", msg);
 77  buflen = sizeof(err_msg);
 78  sqlglm(err_msg, &buflen, &msglen);
 79  printf("%.*s \n", msglen, err_msg);
 80  exit(1);
 81}
 82
 83
 84void connect_to_oracle() {
 85  /* get your username/passwd from "user_pwd.txt" file */
 86
 87  FILE *in_file;
 88  in_file = fopen("user_pwd.txt", "r");
 89  fscanf(in_file, "%s", u_name);
 90  fscanf(in_file, "%s", pwd);
 91
 92  strncpy((char *) username.arr, u_name, UNAME_LEN);
 93  username.len = strlen((char *) username.arr);
 94
 95  strncpy((char *) password.arr, pwd, PWD_LEN);
 96  password.len = strlen((char *) password.arr);
 97
 98  EXEC SQL WHENEVER SQLERROR DO sql_error ("ORACLE error-- ");
 99  EXEC SQL CONNECT :username IDENTIFIED BY :password;
100
101  printf("connected to oracle - \n");
102}