/* 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 salary history 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_salhist.dat file record */
    char    sh_id[6];
    char    sh_amt[6];
    char    sh_start_date[24];
    char    sh_end_date[24];

/* File definitions for reading the sql_salhist.dat file */
    FILE    *salhist_file;
    
/* Declarations for error handling */
    int	    return_status;

/* Variables for main program use */
    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 SALARY_HISTORY");

/* Open the sequential file containing the salary history data records. */
#ifdef VMS
    salhist_file = fopen("sql$sample:sql_salhist.dat","r");
#endif

#if defined(__osf__) || defined (_WIN32)
    salhist_file = fopen("sql_salhist.dat","r");
#endif
		    
/* This procedure uses the executable form for starting a transaction. */
    EXEC SQL SET TRANSACTION READ WRITE RESERVING
	        SALARY_HISTORY FOR EXCLUSIVE WRITE;

/* Main loop until data file is empty */
    while (get_line(salhist_file) != NULL)
        {
        get_field(salhist_file,sh_id        ,5);
        get_field(salhist_file,NULL         ,3);
        get_field(salhist_file,sh_amt       ,5);
        get_field(salhist_file,NULL         ,3);
        get_field(salhist_file,sh_start_date,23);
        get_field(salhist_file,NULL         ,3);
        get_field(salhist_file,sh_end_date  ,23);

/* This compound statement uses the CAST and SUBSTRING functions to 
convert the dates to the DATE VMS data type 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 :start_date DATE VMS; 
   DECLARE :end_date DATE VMS; 
   SET :start_date  = CAST(SUBSTRING(:sh_start_date FROM 8 FOR 4) ||
         -- Convert the month to a number.
          (CASE SUBSTRING(:sh_start_date 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(:sh_start_date FROM 1 FOR 2)  || 
           SUBSTRING(:sh_start_date FROM 13 FOR 2) ||
           SUBSTRING(:sh_start_date FROM 16 FOR 2) || 
           SUBSTRING(:sh_start_date FROM 19 FOR 2) ||  
           SUBSTRING(:sh_start_date FROM 22 for 2)
         AS DATE VMS);

/* If the end date equals 17-NOV-1858 00:00:00.00, set :end_date 
to NULL. If it does not, convert it to DATE VMS format.*/

    IF :sh_end_date <> '17-NOV-1858 00:00:00.00'
    THEN 
       SET :end_date  = CAST(SUBSTRING(:sh_end_date FROM 8 FOR 4) ||
         -- Convert the month to a number.
               (CASE SUBSTRING(:sh_end_date 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(:sh_end_date FROM 1 FOR 2)  || 
                SUBSTRING(:sh_end_date FROM 13 FOR 2) ||
                SUBSTRING(:sh_end_date FROM 16 FOR 2) || 
                SUBSTRING(:sh_end_date FROM 19 FOR 2) ||  
                SUBSTRING(:sh_end_date FROM 22 for 2)
            AS DATE VMS);

    ELSE
         SET  :end_date = NULL;
    END IF;   

   -- Insert the row.

     INSERT INTO SALARY_HISTORY
            (EMPLOYEE_ID, SALARY_START, SALARY_END, SALARY_AMOUNT)
		    VALUES
		    (:sh_id, :start_date, :end_date, :sh_amt);
END;
	}

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

/* Close the sql_salhist.dat data file. */
    fclose(salhist_file);

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

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

}