/******
First Sample program for SQL embedded in C.
This program logs on to Oracle, prompts the user for a supplier number,
queries the database for the supplier's name and city, and displays the
result. It continues until the user enters a carriage return for the
employee number, or until an error is encountered.
******/

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;
  char oracleid = '/';
  VARCHAR supp_num[6], supp_name[16], supp_city[16];
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE sqlca;

void sql_error();
int get_user_input(char *input);
int total_queried = 0;

main()
{
  EXEC SQL WHENEVER SQLERROR DO sql_error();
  EXEC SQL CONNECT :oracleid;
  printf ("\nConnected to Oracle!\n");
  
  while (get_user_input(supp_num.arr))
  {
    supp_num.len = strlen(supp_num.arr);
    
 EXEC SQL WHENEVER NOT FOUND GOTO notfound;
    EXEC SQL SELECT SNAME, CITY
      INTO :supp_name, :supp_city
      FROM SUPPLIERS
      WHERE S# = :supp_num;
    
    printf("\nSupplier      \tCity\n");
    printf("--------------\t-----------\n");
    supp_name.arr[supp_name.len] = '\0';
    supp_city.arr[supp_city.len] = '\0';
    printf("%s\t\t%s\n", supp_name.arr, supp_city.arr);
    
    total_queried++;
    continue;
    
   notfound:
   
    printf("\nThere is no supplier with the requested number - try again.\n");
  }
  
  printf("\nTotal number of successfully queried suppliers is %d.\n", total_queried);
  
  EXEC SQL COMMIT WORK RELEASE;
  
  return(0);
}

void sql_error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  
  fprintf(stderr, "\nOracle error occured:\n");
  fprintf(stderr, "%.70s\n", sqlca.sqlerrm.sqlerrmc);
  
  EXEC SQL ROLLBACK RELEASE;
  exit(1);
}

int get_user_input(char *input)
{
  char line[1024], token[1024];
  int count = -1;
  
  printf("Enter supplier number: ");
  fgets(line, 1024, stdin);
  count = sscanf(line,"%s",token);
  if (count <= 0 || count >= 1024)
    return 0;
  strcpy(input, token);
  return 1;
}