/* Copyright © 1995, 1997, Oracle Corporation.  All Rights Reserved. */
/*  ABSTRACT:
*
*      This program demonstrates the use of the SQL precompiler for the C 
*      language to load an Oracle Rdb database from a stream (flat) file.
*
*      This program attaches to an existing Oracle Rdb database, opens a data
*      file containing employee records, and reads the records,
*      formatting and inserting them into the database until the end of 
*      the data file is reached.  Then the program commits the transaction.
*/ 

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sql_rdb_headers.h>

#ifdef VMS
#include "sql$sample:sql_load_rtl.sc"
#endif

#if defined(__osf__) || defined (_WIN32)
#include <sql_load_rtl.sc>
#endif

main( )
{


/* Fields to receive strings read from the sql_employees.dat file record. */
    char    emp_id[6];
    char    last[15];
    char    first[11];
    char    middle[2];
    char    address[26];
    char    city[21];
    char    state[3];
    char    zip[6];
    char    birthday[24];
    char    sex[2];
    char    status[2];

char out_text[23];
/* File definitions for reading the sql_employees.dat file */
    FILE    *emp_file;
    

/* Declarations for error handling */
    int	    return_status;

/* Variables for main program use */
    char	null_middle[2] = {' ','\0'};
				/* to hold null value for middle initial */
    short int	middle_ind;	/* null value indicator */
    int		i;		/* loop counter */

/* Define the SQLCA. */
    EXEC SQL INCLUDE SQLCA;

/* Declare the database. */
    EXEC SQL DECLARE ALIAS FILENAME personnel;

    EXEC SQL WHENEVER SQLERROR GOTO HANDLE_ERROR;

/* Operator message to the terminal */
    printf("\nProgram: Loading EMPLOYEES");

/* Open the sequential file containing the job history data records. */

#ifdef VMS
    emp_file = fopen("sql$sample:sql_employees.dat","r");
#endif

#if defined(__osf__) || defined (_WIN32)
 emp_file = fopen("sql_employees.dat","r");
#endif

		    
/* This procedure uses the executable form for starting a transaction. */
    EXEC SQL SET TRANSACTION READ WRITE RESERVING
	        EMPLOYEES FOR EXCLUSIVE WRITE;

/* Main loop until data file is empty */
    while (get_line(emp_file) != NULL)
        {
        get_field(emp_file,emp_id  ,5);
        get_field(emp_file,NULL    ,3);
        get_field(emp_file,last    ,14);
        get_field(emp_file,NULL    ,3);
        get_field(emp_file,first   ,10);
        get_field(emp_file,NULL    ,3);
        get_field(emp_file,middle  ,1);
        get_field(emp_file,NULL    ,3);
        get_field(emp_file,address ,25);
        get_field(emp_file,NULL    ,3);
        get_field(emp_file,city    ,20);
        get_field(emp_file,NULL    ,3);
        get_field(emp_file,state   ,2);
        get_field(emp_file,NULL    ,3);
        get_field(emp_file,zip     ,5);
        get_field(emp_file,NULL    ,3);
        get_field(emp_file,birthday,23);
        get_field(emp_file,NULL    ,3);
        get_field(emp_file,sex     ,1);
        get_field(emp_file,NULL    ,3);
        get_field(emp_file,status  ,1);

	/* Check for a null middle initial and set the null date indicator */
	if (strcmp(middle,null_middle) == 0) 
            {
	    middle_ind = -1;   /* null middle initial */
            middle[0] = '\0';
            }
	else
            {
	    middle_ind = 0;    /* not null */
            }

/* This compound statement uses the CAST and SUBSTRING functions to 
convert the birthdate to the DATE VMS datatype format and then inserts 
the row into the table.

In the INSERT statement, the list of names in the VALUES clause corresponds 
to the host variables containing the values.  The list of names that follows 
the INSERT clause names the columns in the table that are to be inserted.
*/

EXEC SQL 
BEGIN
   declare :date_out DATE VMS; 
   set :date_out  = CAST(substring(:birthday FROM 8 FOR 4) ||
         -- Convert the month to a number.
	   (CASE SUBSTRING(:birthday FROM 4 FOR 3)
                   WHEN 'JAN' THEN '01'
                   WHEN 'FEB' THEN '02'
                   WHEN 'MAR' THEN '03'
                   WHEN 'APR' THEN '04'
                   WHEN 'MAY' THEN '05'
                   WHEN 'JUN' THEN '06'
                   WHEN 'JUL' THEN '07'
                   WHEN 'AUG' THEN '08'
                   WHEN 'SEP' THEN '09'
                   WHEN 'OCT' THEN '10'
                   WHEN 'NOV' THEN '11'
                   WHEN 'DEC' THEN '12'
                END) ||
      -- Parse the day, hour, minutes, seconds.
	SUBSTRING(:birthday FROM 1 FOR 2)  || 
	SUBSTRING(:birthday FROM 13 FOR 2) ||
	SUBSTRING(:birthday FROM 16 FOR 2) ||
	SUBSTRING(:birthday FROM 19 FOR 2) ||
	SUBSTRING(:birthday FROM 22 for 2)
        AS DATE VMS);

   -- Insert the row.
   INSERT INTO EMPLOYEES
              (EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL,
              ADDRESS_DATA_1, CITY, STATE, POSTAL_CODE, SEX, BIRTHDAY,
              STATUS_CODE)
	  VALUES
	      (:emp_id, :last, :first, :middle:middle_ind,
               :address, :city, :state, :zip, :sex, 
               :date_out, :status);
END;

	}


/* Commit the transaction. */
    EXEC SQL COMMIT;

/* Close the sql_employees.dat data file. */
    fclose(emp_file);

/* Operator prompt message */
    printf("\nProgram: EMPLOYEES Loaded. Normal End-of-Job");
    exit(1);

/* Error handler for SQL errors */
HANDLE_ERROR:
    sql_signal();
    exit(0);

}