/* 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 job 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 recieve strings read from the sql_jobhist.dat file record */
    char    jh_id[6];
    char    j_code[5];
    char    ascii_start_date[24];
    char    ascii_end_date[24];
    char    d_code[5];
    char    supr_id[6];
#if 1
    char	*foo;
#endif

/* File definitions for reading the sql_jobhist.dat file */
    FILE    *jobhist_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;  

/* Set up error handling for failures on execution of SQL statements. */
    EXEC SQL WHENEVER SQLERROR GOTO HANDLE_ERROR;

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

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

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

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

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

/* Main loop until data file is empty */
    while ((foo = get_line(jobhist_file)) != NULL)
        {
        get_field(jobhist_file,jh_id            ,5);
        get_field(jobhist_file,NULL             ,3);
        get_field(jobhist_file,j_code           ,4);
        get_field(jobhist_file,NULL             ,3);
        get_field(jobhist_file,ascii_start_date ,23);
        get_field(jobhist_file,NULL             ,3);
        get_field(jobhist_file,ascii_end_date   ,23);
        get_field(jobhist_file,NULL             ,3);
        get_field(jobhist_file,d_code           ,4);
        get_field(jobhist_file,NULL              ,2);
        get_field(jobhist_file,supr_id          ,5);


#if 0
	printf ("%s %s %s %s %s %s\n", jh_id, j_code, ascii_start_date
#endif

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

    ELSE
         SET  :end_date = NULL;
    END IF;   

   -- Insert the row.

      INSERT INTO JOB_HISTORY
		    (EMPLOYEE_ID, JOB_CODE, JOB_START, JOB_END,
		     DEPARTMENT_CODE, SUPERVISOR_ID)
		    VALUES
		    (:jh_id, :j_code, :start_date, :end_date,
                     :d_code, :supr_id);
 END;
	}

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

/* Close the sql_jobhist.dat data file. */
    fclose(jobhist_file);

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

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

}