firebird/examples/api/api11.c

/*
 *    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.
 * The contents of this file are subject to the Interbase Public
 * License Version 1.0 (the "License"); you may not use this file
 * except in compliance with the License. You may obtain a copy
 * of the License at http://www.Inprise.com/IPL.html
 *
 * Software distributed under the License is distributed on an
 * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express
 * or implied. See the License for the specific language governing
 * rights and limitations under the License.
 *
 * The Original Code was created by Inprise Corporation
 * and its predecessors. Portions created by Inprise Corporation are
 * Copyright (C) Inprise Corporation.
 *
 * All Rights Reserved.
 * Contributor(s): ______________________________________.
 */

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

#define    PROJLEN        5
#define    BUFLEN        128

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

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

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

    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 (isc_db_handle db, int emp_no)
{
    char            proj_id[PROJLEN + 2];
    char            selstr[BUFLEN];
    short           flag0 = 0;
    isc_tr_handle   trans = NULL;
    ISC_STATUS_ARRAY    status;
    isc_stmt_handle stmt = NULL;
    XSQLDA          *sqlda;
    long            fetch_stat;

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

    sqlda = (XSQLDA *) 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 *) 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 (isc_db_handle db, int emp_no, char *proj_id)
{
    char            exec_str[BUFLEN];
    isc_tr_handle   trans = NULL;
    ISC_STATUS_ARRAY    status;

    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 (isc_db_handle db, int *emp_no, char *proj_id)
{
    isc_tr_handle   trans = NULL;
    ISC_STATUS_ARRAY    status;

    *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;
}