-- Copyright © Oracle Corporation 1995.  All Rights Reserved.
--  ABSTRACT:
--
--      This program demonstrates the use of the SQL interface to 
--      Oracle Rdb and DEC Ada to load an Oracle Rdb database
--	from an RMS file using SQL module language.  The file
--	containing the module language procedures is SQL_LOAD_JOBHIST.SQLMOD.
--
--	In this sample, an existing Oracle Rdb database is attached, an RMS file
--	containing job history records is opened and the records are read,
--	formatted and inserted into the database until the end of the RMS file
--	is reached.  The transaction is then committed.
-- 

with CONDITION_HANDLING;
with SEQUENTIAL_IO;
with STARLET;
with TEXT_IO;

procedure SQL_LOAD_JOBHIST is

-- Record type declaration for the SQL_JOBHIST.DAT file
    type JH_REC is record
    	JH_ID : string(1..5);
   	F_1 : string(1..3);
    	J_CODE : string(1..4);
    	F_2 : string(1..3);
    	ASCII_START_DATE : string(1..23);
    	F_3 : string(1..3);
    	ASCII_END_DATE : string(1..23);
    	F_4 : string(1..3);
    	D_CODE : string(1..4);
    	F_5 : string(1..2);
    	SUPR_ID : string(1..5);
    end record;

-- Instantiation of SEQUENTIAL_IO for reading the SQL_JOBHIST.DAT file
-- including the file_type and record object declarations
    package JOBHIST_IO is new SEQUENTIAL_IO(JH_REC);
     JOBHIST_FILE : jobhist_io.file_type;
     JOBHIST_REC : jh_rec;

-- The following declarations are for SQL call error handling
    RETURN_STATUS : integer;
    DEADLOCK : constant integer := -913;
    INTEGRITY_FAILURE :  constant integer := -1001;
    LOCK_CONFLICT : constant integer := -1003;
    NO_DUPLICATES : constant integer := -803;
    NOT_VALID : constant integer := -1002;
    SUCCESS : constant integer := 0;
    DEADLOCK_ERROR, INTEGRITY_FAILURE_ERROR, LOCK_CONFLICT_ERROR,
     NO_DUPLICATES_ERROR, NOT_VALID_ERROR, UNEXPECTED_ERROR : exception;

-- The following declarations are for VMS system service call error handling
    VMS_RETURN_STATUS : condition_handling.cond_value_type;
    IVTIME_ERROR : exception;
    pragma IMPORT_EXCEPTION(ivtime_error, "SS$_IVTIME");

-- Variables for main program use
    NULL_DATE : constant string(1..23) := "17-NOV-1858 00:00:00.00";
    B_JOB_START : starlet.date_time_type;   -- to hold BINTIM result value
    B_JOB_END : starlet.date_time_type;     -- to hold BINTIM result value
    B_JOB_END_IND : short_integer;          -- null value indicator

-- String subtype declarations
    subtype STRING4 is string(1..4);
    subtype STRING5 is string(1..5);

-- SQL module language procedure declarations
-- including the INTERFACE pragmas
    procedure SET_TRANSACTION(SQLCODE : out integer);
    procedure STORE_JOBHIST(SQLCODE : out integer;
                            P_EMPLOYEE_ID : in string5;
                            P_JOB_CODE : in string4;
                            P_JOB_START : in starlet.date_time_type;
                            P_JOB_END : in starlet.date_time_type;
                            P_JOB_END_IND : in short_integer;
                            P_DEPARTMENT_CODE : in string4;
                            P_SUPERVISOR_ID : in string5);
    procedure COMMIT_TRANSACTION(SQLCODE : out integer);
    procedure ROLLBACK_TRANSACTION(SQLCODE: out integer);

    pragma INTERFACE(SQL, SET_TRANSACTION);
    pragma INTERFACE(SQL, STORE_JOBHIST);
    pragma INTERFACE(SQL, COMMIT_TRANSACTION);
    pragma INTERFACE(SQL, ROLLBACK_TRANSACTION);

-------------------------------------------------------------------------------
-- Main program
-------------------------------------------------------------------------------
begin

-- Operator message to the terminal
    text_io.put_line("Program: Loading JOB_HISTORY");

-- Open the sequential file containing the job history data records
    jobhist_io.open(jobhist_file,jobhist_io.in_file,
                    "SQL$SAMPLE:SQL_JOBHIST.DAT");

-- Call to SQL to start a transaction; check status on return
    set_transaction(return_status);
    case return_status is
    	when success => null;
    	when deadlock => raise deadlock_error;
    	when lock_conflict => raise lock_conflict_error;
    	when others => raise unexpected_error;
    end case;

-- Main loop until data file is empty
    while not jobhist_io.end_of_file(jobhist_file) loop

    	-- Read a job history data record
    	jobhist_io.read(jobhist_file,jobhist_rec);

    	-- Convert the start date to DATE datatype format
    	-- using BINTIM in STARLET
    	starlet.bintim(vms_return_status,jobhist_rec.ascii_start_date,
                       b_job_start);

    	-- Check for a null end date and set the null date indicator
    	-- then convert end date to DATE datatype format
    	if jobhist_rec.ascii_end_date = null_date 
    	   then b_job_end_ind := -1;   -- null date
    	   else b_job_end_ind := 0;    -- not null
    	end if;
    	starlet.bintim(vms_return_status,jobhist_rec.ascii_end_date,
                       b_job_end);

    	-- Call to SQL to perform the database INSERT; check status
    	store_jobhist(return_status,
                      jobhist_rec.jh_id,
                      jobhist_rec.j_code,
                      b_job_start,
                      b_job_end,
                      b_job_end_ind,
                      jobhist_rec.d_code,
                      jobhist_rec.supr_id);
    	case return_status is
    	    when success => null;
    	    when deadlock => raise deadlock_error;
    	    when integrity_failure => raise integrity_failure_error;
    	    when lock_conflict => raise lock_conflict_error;
    	    when no_duplicates => raise no_duplicates_error;
    	    when not_valid => raise not_valid_error;
    	    when others => raise unexpected_error;
    	end case;

    end loop;

-- Call SQL to commit the transaction; check status on return
    commit_transaction(return_status);
    case return_status is
    	when success => null;
    	when deadlock => raise deadlock_error;
    	when integrity_failure => raise integrity_failure_error;
    	when lock_conflict => raise lock_conflict_error;
    	when others => raise unexpected_error;
    end case;

-- Close the SQL_JOBHIST.DAT data file
    jobhist_io.close(jobhist_file);

-- Operator prompt message
    text_io.put_line("Program: JOB_HISTORY Loaded. Normal End-of-Job");

    exception
    	when deadlock_error =>
    	    rollback_transaction(return_status);
    	    text_io.put_line("Deadlock has occurred.  JOB_HISTORY not loaded");
    	when integrity_failure_error =>
    	    rollback_transaction(return_status);
    	    text_io.put_line("Integrity failure.  JOB_HISTORY not loaded");
    	when lock_conflict_error =>
    	    rollback_transaction(return_status);
    	    text_io.put_line("Locking conflict.  JOB_HISTORY not loaded");
    	when no_duplicates_error =>
    	    rollback_transaction(return_status);
    	    text_io.put_line("Duplicate record found.  JOB_HISTORY not loaded");
    	when not_valid_error =>
    	    rollback_transaction(return_status);
    	    text_io.put_line("Invalid data found.  JOB_HISTORY not loaded");
    	when unexpected_error =>
    	    rollback_transaction(return_status);
    	    text_io.put_line("Unexpected error.  JOB_HISTORY not loaded");
	when ivtime_error =>
    	    rollback_transaction(return_status);
	    text_io.put_line("Invalid date format. JOB_HISTORY not loaded");
	when jobhist_io.name_error =>
	    text_io.put_line("Input file not found.  JOB_HISTORY not loaded");
	when jobhist_io.use_error =>
	    text_io.put_line("Input file invalid.  JOB_HISTORY not loaded");
end SQL_LOAD_JOBHIST;