/*
 *    Program type:  API Interface
 *
 *    Description:
 *        This program executes a stored procedure and selects from
 *        a stored procedure.  First, a list of projects an employee
 *        is involved in is printed.  Then the employee is added to
 *        another project.  The new list of projects is printed again.
 */

#include <stdlib.h>
#include <string.h>
#include <ibase.h>
#include <stdio.h>
#include "example.h"

#define    PROJLEN        5
#define    BUFLEN        128

int select_projects PROTO((isc_db_handle db, int emp_no));
int add_emp_proj PROTO((isc_db_handle db, int emp_no, char ISC_FAR * proj_id));
int get_params PROTO((isc_db_handle db, int ISC_FAR * emp_no, char ISC_FAR * proj_id));

int main (ARG(int, argc), ARG(char **, argv))
ARGLIST(int argc)
ARGLIST(char **argv)
{
    int             emp_no;
    char            proj_id[PROJLEN + 2];
    isc_db_handle    db = NULL;
    long            status[20];
    char            empdb[128];

    if (argc > 1)
        strcpy(empdb, argv[1]);
    else
        strcpy(empdb, "employee.gdb");

    if (isc_attach_database(status, 0, empdb, &db, 0, NULL))
    {
        ERREXIT(status, 1)
    }

    /*
     *  Add employee with id 8 to project 'MAPDB'.
     */
    if (get_params(db, &emp_no, proj_id))
        return 1;

    /*
     *  Display employee's current projects.
     */
    printf("\nCurrent projects for employee id: %d\n\n", emp_no);
    if (select_projects(db, emp_no))
        return 1;

    /*
     *  Insert a new employee project row.
     */
    printf("\nAdd employee id: %d to project: %s\n", emp_no, proj_id);
    if (add_emp_proj(db, emp_no, proj_id))
        return 1;

    /*
     *  Display employee's new current projects.
     */
    printf("\nCurrent projects for employee id: %d\n\n", emp_no);
    if (select_projects(db, emp_no))
        return 1;
 

    if (isc_detach_database(status, &db))
    {
        ERREXIT(status, 1)
    }

    return  0 ;
}

/*
 *    Select from a stored procedure.
 *    Procedure 'get_emp_proj' gets employee's projects.
 */
int select_projects (ARG(isc_db_handle, db), ARG(int, emp_no))
ARGLIST(void    *db)
ARGLIST(int     emp_no)
{
    char            proj_id[PROJLEN + 2];
    char            selstr[BUFLEN];
    short           flag0 = 0;
    isc_tr_handle   trans = NULL;
    long            status[20];
    isc_stmt_handle stmt = NULL;
    XSQLDA  ISC_FAR *sqlda;
    long            fetch_stat;

    sprintf(selstr, "SELECT proj_id FROM get_emp_proj (%d) ORDER BY proj_id",
            emp_no);

    sqlda = (XSQLDA ISC_FAR *) malloc(XSQLDA_LENGTH(1));
    sqlda->sqln = 1;
    sqlda->version = 1;

    if (isc_start_transaction(status, &trans, 1, &db, 0, NULL))
    {
        ERREXIT(status, 1)
    }

    if (isc_dsql_allocate_statement(status, &db, &stmt))
    {
        ERREXIT(status, 1)
    }

    if (isc_dsql_prepare(status, &trans, &stmt, 0, selstr, 1, sqlda))
    {
        ERREXIT(status, 1)
    }

    sqlda->sqlvar[0].sqldata = (char ISC_FAR *) proj_id;
    sqlda->sqlvar[0].sqlind  = &flag0;
    sqlda->sqlvar[0].sqltype = SQL_TEXT + 1;

    if (isc_dsql_execute(status, &trans, &stmt, 1, NULL))
    {
        ERREXIT(status, 1)
    }

    while ((fetch_stat = isc_dsql_fetch(status, &stmt, 1, sqlda)) == 0)
    {
        proj_id[PROJLEN] = '\0';
        printf("\t%s\n", proj_id);
    }

    if (fetch_stat != 100L)
    {
        ERREXIT(status, 1)
    }

    if (isc_dsql_free_statement(status, &stmt, DSQL_close))
    {
        ERREXIT(status, 1)
    }
                              
    if (isc_commit_transaction(status, &trans))
    {
        ERREXIT(status, 1)
    }

    free(sqlda);

    return 0;
}

/*
 *    Execute a stored procedure.
 *    Procedure 'add_emp_proj' adds an employee to a project.
 */
int add_emp_proj (ARG(isc_db_handle, db), ARG(int, emp_no), ARG(char ISC_FAR *, proj_id))
ARGLIST(void    *db)
ARGLIST(int     emp_no)
ARGLIST(char    *proj_id)
{
    char            exec_str[BUFLEN];
    isc_tr_handle   trans = NULL;
    long            status[20];

    sprintf(exec_str, "EXECUTE PROCEDURE add_emp_proj %d, \"%s\"",
            emp_no, proj_id);

    if (isc_start_transaction(status, &trans, 1, &db, 0, NULL))
    {
        ERREXIT(status, 1)
    }

    if (isc_dsql_execute_immediate(status, &db, &trans, 0, exec_str, 1, NULL))
    {
        ERREXIT(status, 1)
    }
 
    if (isc_commit_transaction(status, &trans))
    {
        ERREXIT (status, 1)
    }

    return 0;
}   

/*
 *    Set-up procedure parameters and clean-up old data.
 */
int get_params (ARG(void ISC_FAR *, db),
                ARG(int ISC_FAR *, emp_no),
                ARG(char ISC_FAR *, proj_id))
ARGLIST(void    *db)
ARGLIST(int     *emp_no)
ARGLIST(char    *proj_id)
{
    isc_tr_handle   trans = NULL;
    long            status[20];

    *emp_no = 8;
    strcpy(proj_id, "MAPDB");
                     
    /* Cleanup:  delete row from the previous run. */

    if (isc_start_transaction(status, &trans, 1, &db, 0, NULL))
    {
        ERREXIT(status, 1)
    }

    if (isc_dsql_execute_immediate(status, &db, &trans, 0,
                                   "DELETE FROM employee_project \
                                   WHERE emp_no = 8 AND proj_id = 'MAPDB'", 1,
                                   NULL))
    {
        ERREXIT(status, 1)
    }

    if (isc_commit_transaction(status, &trans))
    {
        ERREXIT(status, 1)
    }

    return 0;
}
