PC World 1999 May/PCWorld_1999-05_cd.bin/software/Vyzkuste/inprise/INTRBASE_55/EXAMPLES/API/API10.C

C/C++ Source or Header | 1998-10-18 | 6KB | 197 lines

/*
 *    Program type:  API
 *
 *    Description:
 *        This program selects and updates an array type.
 *        Projected head count is displayed and updated for
 *        a set of projects.
 */

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

char    *sel_str =
    "SELECT dept_no, quart_head_cnt FROM proj_dept_budget p \
    WHERE year = 1994 AND proj_id = 'VBASE' \
    FOR UPDATE of quart_head_cnt";

char    *upd_str =
    "UPDATE proj_dept_budget SET quart_head_cnt = ? WHERE CURRENT OF S";


int main (ARG(int, argc), ARG(char **, argv))
ARGLIST(int argc)
ARGLIST(char **argv)
{
    long            hcnt[4];
    ISC_QUAD        array_id;
    ISC_ARRAY_DESC  desc;
    long            len;
    char            dept_no[6];
    isc_db_handle   DB = NULL;
    isc_tr_handle   trans = NULL;
    long            status[20];
    short           flag0 = 0, flag1 = 0;
    isc_stmt_handle stmt = NULL;
    isc_stmt_handle ustmt = NULL;
    char ISC_FAR *  cursor = "S";
    XSQLDA ISC_FAR *osqlda, *isqlda;
    long            fetch_stat;
    short           i;
    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)
    }

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

    /*
     *    Set up the array description structure
     */


    if (isc_array_lookup_bounds(status, &DB, &trans,
                                "PROJ_DEPT_BUDGET", "QUART_HEAD_CNT", &desc))
    {
        ERREXIT(status, 1)
    }

    /*
     *    Set-up the select statement.
     */

    osqlda = (XSQLDA *) malloc(XSQLDA_LENGTH(2));
    osqlda->sqln = 2;
    osqlda->version = 1;

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

    osqlda->sqlvar[1].sqldata = (char ISC_FAR *) &array_id;
    osqlda->sqlvar[1].sqltype = SQL_ARRAY + 1;
    osqlda->sqlvar[1].sqlind  = &flag1;

    isc_dsql_allocate_statement(status, &DB, &stmt);
    isc_dsql_allocate_statement(status, &DB, &ustmt);

    /* Prepare and execute query */
    if (isc_dsql_prepare(status, &trans, &stmt, 0, sel_str, 1, osqlda))
    {
        ERREXIT(status, 1)
    }

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

    /* Needed for update current */
    isc_dsql_set_cursor_name(status, &stmt, cursor, 0);

    /*
     *    Set-up the update statement.
     */

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

    /* Use describe_bind to set up input sqlda */

    if (isc_dsql_prepare(status, &trans, &ustmt, 0, upd_str, 1, NULL))
    {
        ERREXIT(status, 1)
    }
    isc_dsql_describe_bind(status, &ustmt, 1, isqlda);

    isqlda->sqlvar[0].sqldata = (char ISC_FAR *) &array_id;
    isqlda->sqlvar[0].sqlind  = &flag1;

    /*
     *    Fetch the head count for each department's 4 quarters;
     *    increase the head count by 1 for each quarter;
     *    and save the new head count.
     */

    while ((fetch_stat = isc_dsql_fetch(status, &stmt, 1, osqlda)) == 0)
    {
        /* Get the current array values. */
        if (!flag1)
        {
            len = sizeof(hcnt);;
            if (isc_array_get_slice(status, &DB, &trans,
                                    (ISC_QUAD ISC_FAR *) &array_id,
                                    (ISC_ARRAY_DESC ISC_FAR *) &desc,
                                    (void ISC_FAR *) hcnt,
                                    (long ISC_FAR *) &len))
                                    {ERREXIT (status, 1)};

            dept_no [osqlda->sqlvar[0].sqllen] = '\0';
            printf("Department #:  %s\n\n", dept_no);

            printf("\tCurrent counts: %ld %ld %ld %ld\n",
                   hcnt[0], hcnt[1], hcnt[2], hcnt[3]);

            /* Add 1 to each count. */
            for (i = 0; i < 4; i++)
                hcnt[i] = hcnt[i] + 1;

            /* Save new array values. */
            if (isc_array_put_slice(status, &DB, &trans,
                                    (ISC_QUAD ISC_FAR *) &array_id,
                                    (ISC_ARRAY_DESC ISC_FAR *) &desc,
                                    (void ISC_FAR *) hcnt,
                                    (long ISC_FAR *) &len))
                                    {ERREXIT (status, 1)};

            /* Update the array handle. */
            if (isc_dsql_execute(status, &trans, &ustmt, 1, isqlda))
            {
                ERREXIT(status, 1)
            }

            printf("\tNew counts    : %ld %ld %ld %ld\n\n",
                   hcnt[0], hcnt[1], hcnt[2], hcnt[3]);
        }
    }

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

    isc_dsql_free_statement(status, &stmt, DSQL_close);
    isc_dsql_free_statement(status, &ustmt, DSQL_close);

    /* Do a rollback to keep from updating the sample db */

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

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

    free(osqlda);
    free(isqlda);

    return 0;
}