/* Copyright © Oracle Corporation 2007.  All Rights Reserved. */
/*
 *  Modification History:
 *
 *	27-AUG-2007 05305 Need to handle null parms in CONNECT better
 *
 * This program demonstrates how to use a PRO*C program to provide a set of 
 * external functions and procedures which can be called as Rdb SQL external
 * routines. The program must be built into a shared run-time library which can
 * be done using the accompanying PRO_C_EXT_FUNC.COM command file. There is
 * also a sample SQL script named PRO_C_EXT_FUNC.SQL which will define the
 * functions in this module as external routines.
 *
 * This module has the following routines which are intended to be used as Rdb
 * external functions:
 *
 * ROIF_CONNECT - connects to an Oracle RDBMS instance
 * ROIF_DISCONNECT - Disconnects from the Oracle instance
 * ROIF_COMMIT - Commits the current transaction
 * ROIF_ROLLBACK - Rolls back the current transaction
 * ROIF_GET_ERRMSG - Gets the text associated with the latest ORA status code
 * ROIF_GET_EMPLOYEE - retrieves employee data for a give employee number
 * ROIF_OPEN_EMP_CURSOR - opens a cursor to retrieve the whole emp table
 * ROIF_FETCH_EMP_CURSOR - fetches the next row from the emp table
 * ROIF_CLOSE_EMP_CURSOR - closes the cursor opened by ROIF_OPEN_EMP_CURSOR
 * ROIF_UPDATE_EMPLOYEE - updates employee data for a give employee number
 * ROIF_INSERT_EMPLOYEE - inserts a new employee row in the emp table
 *
 * This module knows how to handle the following data types:
 *
 *    Rdb type       Interface type        Oracle type
 *    -----------  ----------------------  -----------
 *    NUMBER(n)    int                     NUMBER(n)
 *    NUMBER(n,m)  double                  NUMBER(n,m)
 *    DATE_VMS     unsigned long long int  DATE
 *    VARCHAR2(n)  char *                  VARCHAR2(n)
 *
 */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#ifdef __ia64
#include <cvtdef.h>
#include <cvt$routines.h>
#endif
#include <libdtdef.h>
#include <lib$routines.h>
#include <descrip.h>

#define DATE_VMS unsigned long long int

EXEC SQL BEGIN DECLARE SECTION;
    int		empno_p;
    int		mgr_p;
    int		deptno_p;
    float	sal_p;
    float	comm_p;
    VARCHAR	ename_p[11]; 
    VARCHAR	job_p[10];
    VARCHAR	hiredate_p[24];
    short int	ind1_p, ind2_p, ind3_p, ind4_p, ind5_p, ind6_p, ind7_p, ind8_p;
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE SQLCA;
EXEC SQL DECLARE emp_cursor CURSOR FOR
    SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
    FROM EMP;

int		connected = FALSE;
unsigned int	dt_context = 0;
struct dsc$descriptor_s dt_str_dsc;

/* Utility functions for assigning values to output parameters
*/
void assign_float_parm(double *target, float source)
{
#ifdef __ia64
    cvt$convert_float(&source, CVT$K_IEEE_S, target, CVT$K_VAX_G,
		      CVT$M_VAX_ROUNDING);
#else
    *target = source;
#endif
}

void assign_double_parm(double *target, double source)
{
#ifdef __ia64
    cvt$convert_float(&source, CVT$K_IEEE_T, target, CVT$K_VAX_G,
		      CVT$M_VAX_ROUNDING);
#else
    *target = source;
#endif
}

void assign_date_parm(DATE_VMS *target, VARCHAR *source)
{
    dt_str_dsc.dsc$b_class = DSC$K_CLASS_S;
    dt_str_dsc.dsc$b_dtype = DSC$K_DTYPE_T;
    dt_str_dsc.dsc$w_length = source->len;
    dt_str_dsc.dsc$a_pointer = (char *)source->arr;
    lib$convert_date_string(&dt_str_dsc, target, &dt_context);
}

/* Utility functions for assigning input values in a SQL statement
*/
void assign_float_value(float *target, double source)
{
#ifdef __ia64
    cvt$convert_float(&source, CVT$K_VAX_G, target, CVT$K_IEEE_S,
		      CVT$M_ROUND_TO_NEAREST);
#else
    *target = source;
#endif
}

void assign_double_value(double *target, double source)
{
#ifdef __ia64
    cvt$convert_float(&source, CVT$K_VAX_G, target, CVT$K_IEEE_T,
		      CVT$M_ROUND_TO_NEAREST);
#else
    *target = source;
#endif
}

void assign_date_value(VARCHAR *target, DATE_VMS source)
{
    dt_str_dsc.dsc$b_class = DSC$K_CLASS_S;
    dt_str_dsc.dsc$b_dtype = DSC$K_DTYPE_T;
    dt_str_dsc.dsc$w_length = target->len;
    dt_str_dsc.dsc$a_pointer = (char *)target->arr;
    lib$format_date_time(&dt_str_dsc, &source, &dt_context);
}

void convert_emp_values(
int *empno,
char *ename,		short ind2,
char *job,		short ind3,
int  *mgr,		short ind4,
DATE_VMS *hiredate,	short ind5, /* "quadword" */
double *sal,		short ind6,
double *comm,		short ind7,
int  *deptno,		short ind8)
{
    empno_p = *empno;

    if (ind2 != -1)
    {
	strcpy((char *)ename_p.arr, ename);
	ename_p.len = strlen(ename);
    }

    if (ind3 != -1)
    {
	strcpy((char *)job_p.arr, job);
	job_p.len = strlen(job);
    }

    if (ind4 != -1)
    {
	
	mgr_p = *mgr;
    }

    if (ind5 != -1)
    {
	assign_date_value((VARCHAR *)&hiredate_p, *hiredate);
    }

    if (ind6 != -1)
    {
	assign_float_value(&sal_p, *sal);
    }

    if (ind7 != -1)
    {
	assign_float_value(&comm_p, *comm);
    }

    if (ind8 != -1)
    {
	
	deptno_p = *deptno;
    }

}

/* A SQL Function */
extern int ROIF_CONNECT (
char *username,
char *password,
char *sid )
{
    int                 rc, libstat;
    int                 errcode;
    int			dt_component;
    char		oracleid[2] = "/";
    char		init_string[100];
    unsigned char       errbuf[100];

    if (strlen(username) == 0)
    {
	if (strlen(sid) == 0 || sid[0] == ' ')
	    EXEC SQL CONNECT :oracleid ;
	else
	    EXEC SQL CONNECT :oracleid USING :sid ;
    }
    else if (strlen(password) == 0)
    {
	if (strlen(sid) == 0 || sid[0] == ' ')
	    EXEC SQL CONNECT :username;
	else
	    EXEC SQL CONNECT :username USING :sid;
    }
    else
    {
	if  (strlen(sid) == 0 || sid[0] == ' ')
	    EXEC SQL CONNECT :username IDENTIFIED BY :password;
	else
	    EXEC SQL CONNECT :username IDENTIFIED BY :password USING :sid;
    }

    rc = sqlca.sqlcode;

    if (sqlca.sqlcode == 0)
    {
	/*
	 * set up the context for LIB$CONVERT_DATE_STRING so that it always
	 * expects DD-MON-YYYY HH:MM:SS format and uses English month 
	 * abbreviations.
	 */
	dt_component = LIB$K_OUTPUT_FORMAT;
	strcpy(&init_string[0], "|!DB-!MAAU-!Y4|!H04:!M0:!S0.!C2|");
	dt_str_dsc.dsc$b_class = DSC$K_CLASS_S;
	dt_str_dsc.dsc$b_dtype = DSC$K_DTYPE_T;
	dt_str_dsc.dsc$w_length = strlen(init_string);
	dt_str_dsc.dsc$a_pointer = &init_string[0];
	libstat = lib$init_date_time_context(&dt_context, &dt_component, &dt_str_dsc);
	dt_component = LIB$K_MONTH_NAME_ABB;
	strcpy(&init_string[0], 
	    "|JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC|");
	dt_str_dsc.dsc$w_length = strlen(init_string);
	libstat = lib$init_date_time_context(&dt_context, &dt_component, &dt_str_dsc);

	/*
	 * set the Oracle session to produce dates compatible with the
	 * LIB$CONVERT_DATE_STRING utility. These settings correspond to the
	 * two calls above. Note that it is not really necessary to append ".00"
	 * on the end of what comes back from the Oracle server because
	 * LIB$CONVERT_DATE_STRING will still now how to convert even without
	 * the hundreths of seconds.
	 */
	EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
	EXEC SQL ALTER SESSION SET NLS_DATE_LANGUAGE = AMERICAN;

	/*
	 * initialize the length of the conversion string for dates
	 */
	hiredate_p.len = 20;

	connected = TRUE;
    }

    return (rc);
}

/* A SQL Function */
/* Note: there is an implicit commit on disconnect */
extern int ROIF_DISCONNECT ()
{
    EXEC SQL COMMIT WORK RELEASE;
    return (0);
}

/* A SQL Function */
extern int ROIF_COMMIT ()
{
    EXEC SQL COMMIT;
    return (sqlca.sqlcode);
}

/* A SQL Function */
extern int ROIF_ROLLBACK ()
{
    EXEC SQL ROLLBACK;
    return (sqlca.sqlcode);
}

/* As SQL procedure */
extern void ROIF_GET_ERRMSG (
char *errmsg )
{
    if (sqlca.sqlcode == 0)
    {
	errmsg[0] = '\0';
    }
    else
    {
	strncpy(errmsg, sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrm.sqlerrml);
    }
}

/* A SQL procedure */
extern void ROIF_GET_EMPLOYEE (
int  empno,
char *ename,
char *job,
int  *mgr,
DATE_VMS *hiredate, /* "quadword" */
double *sal,
double *comm,
int  *deptno,
int  *status )
{
    int		rc = 1;

    if (!connected)
    {
	*status = ROIF_CONNECT("scott","tiger","");
	if (*status < 0)
	    return;
    }

    EXEC SQL SELECT ENAME, JOB, NVL(MGR, 0), HIREDATE, SAL, 
                    NVL(COMM, 0), DEPTNO
	INTO :ename_p, :job_p, :mgr_p, :hiredate_p, :sal_p, :comm_p, :deptno_p
	FROM EMP WHERE EMPNO = :empno;

    *status = sqlca.sqlcode;

    if (*status == 0)
    {
	strncpy(ename, (char *)ename_p.arr, ename_p.len);
	ename[ename_p.len] = '\0';
	strncpy(job, (char *)job_p.arr, job_p.len);
	job[job_p.len] = '\0';
	*mgr = mgr_p;
	assign_date_parm(hiredate, (VARCHAR *)&hiredate_p);
	assign_float_parm(sal, sal_p);
	assign_float_parm(comm, comm_p);
	*deptno = deptno_p;
    }

    return;
}

/* A SQL Function */
extern int ROIF_OPEN_EMP_CURSOR()
{
    EXEC SQL OPEN emp_cursor;

    return (sqlca.sqlcode);
}

/* A SQL procedure */
extern void ROIF_FETCH_EMP_CURSOR (
int  *empno, short *ind1,
char *ename, short *ind2,
char *job, short *ind3,
int  *mgr, short *ind4,
DATE_VMS *hiredate, short *ind5, /* "quadword" */
double *sal, short *ind6,
double *comm, short *ind7,
int  *deptno, short *ind8,
int  *status )
{
    if (!connected)
    {
	*status = ROIF_CONNECT("scott","tiger","");
	if (*status < 0)
	    return;
    }

    EXEC SQL FETCH emp_cursor INTO 
	:empno_p:ind1_p, :ename_p:ind2_p, :job_p:ind2_p, :mgr_p:ind4_p, 
	:hiredate_p:ind5_p, :sal_p:ind6_p, :comm_p:ind7_p,:deptno_p:ind8_p;

    *status = sqlca.sqlcode;

    if (*status == 0)
    {
	*empno = empno_p;					*ind1 = ind1_p;
	strncpy(ename, (char *)ename_p.arr, ename_p.len);	*ind2 = ind2_p;
	ename[ename_p.len] = '\0';
	strncpy(job, (char *)job_p.arr, job_p.len);		*ind3 = ind3_p;
	job[job_p.len] = '\0';
	*mgr = mgr_p;						*ind4 = ind4_p;
	assign_date_parm(hiredate, (VARCHAR *)&hiredate_p);	*ind5 = ind5_p;
	assign_float_parm(sal, sal_p);				*ind6 = ind6_p;
	assign_float_parm(comm, comm_p);			*ind7 = ind7_p;
	*deptno = deptno_p;					*ind8 = ind8_p;
    }

    return;
}

/* A SQL Function */
extern int ROIF_CLOSE_EMP_CURSOR()
{
    EXEC SQL CLOSE emp_cursor;

    return (sqlca.sqlcode);
}

/* A SQL procedure */
extern void ROIF_UPDATE_EMPLOYEE (
int  *empno,
char *ename,		short ind2,
char *job,		short ind3,
int  *mgr,		short ind4,
DATE_VMS *hiredate,	short ind5, /* "quadword" */
double *sal,		short ind6,
double *comm,		short ind7,
int  *deptno,		short ind8,
int  *status )
{
    if (!connected)
    {
	*status = ROIF_CONNECT("scott","tiger","");
	if (*status < 0)
	    return;
    }

    convert_emp_values(empno, ename, ind2, job, ind3, mgr, ind4,  
	hiredate, ind5, sal, ind6, comm, ind7, deptno, ind8);

    EXEC SQL UPDATE emp SET
	ename = :ename_p:ind2_p, job = :job_p:ind2, mgr = :mgr_p:ind4, 
	hiredate = :hiredate_p:ind5, sal = :sal_p:ind6, comm = :comm_p:ind7, 
	deptno = :deptno_p:ind8 
	WHERE empno = :empno_p;

    *status = sqlca.sqlcode;

    return;
}

/* A SQL procedure */
extern void ROIF_INSERT_EMPLOYEE (
int  *empno,
char *ename,		short ind2,
char *job,		short ind3,
int  *mgr,		short ind4,
DATE_VMS *hiredate,	short ind5, /* "quadword" */
double *sal,		short ind6,
double *comm,		short ind7,
int  *deptno,		short ind8,
int  *status )
{
    if (!connected)
    {
	*status = ROIF_CONNECT("scott","tiger","");
	if (*status < 0)
	    return;
    }

    convert_emp_values(empno, ename, ind2, job, ind3, mgr, ind4,  
	hiredate, ind5, sal, ind6, comm, ind7, deptno, ind8);

    EXEC SQL INSERT INTO emp VALUES (
	:empno_p, :ename_p:ind2_p, :job_p:ind2, :mgr_p:ind4, :hiredate_p:ind5,
	:sal_p:ind6, :comm_p:ind7, :deptno_p:ind8 );

    *status = sqlca.sqlcode;

    return;
}