firebird/examples/api/api6.c

/*
 *    Program type:  API Interface
 *
 *    Description:
 *        This program performs a positioned update.
 *        Department budgets are examined and updated using some
 *        percent increase factor, determined at run-time.
 *
 *        The update statement is constructed using a dynamic cursor
 *        name.  The statement handle is freed and re-used by the
 *        update cursor, after being used by another statement.
 * 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    DEPTLEN        3
#define    PROJLEN        5
#define    BUFLEN        256

float increase_factor (double budget);

/*
 *  A cursor is declared on this select statement, allowing for
 *  the update of projected_budget field.
 */
char *sel_str =
    "SELECT proj_id, dept_no, projected_budget \
     FROM proj_dept_budget WHERE fiscal_year = 1994 \
     FOR UPDATE OF projected_budget";

/* This query is executed prior to the positioned update. */
char *tot_str =
    "SELECT SUM(projected_budget) FROM proj_dept_budget WHERE fiscal_year = 1994";



int main (int argc, char** argv)
{
    char                dept_no[DEPTLEN + 2];
    char                proj_id[PROJLEN + 2];
    char                upd_str[BUFLEN];
    double              budget;
    double              tot_budget;
    short               flag0 = 0,
                        flag1 = 0,
                        flag2 = 0,
                        flag3 = 0;
    isc_db_handle       DB = NULL;              /* Database handle */
    isc_tr_handle       trans = NULL;           /* transaction handle */
    ISC_STATUS_ARRAY    status;                 /* status vector */
    char                *cursor = "budget";     /* dynamic cursor name */
    isc_stmt_handle     stmt = NULL;            /* statement handle */
    XSQLDA  *           osqlda;                 /* output SQLDA */
    XSQLDA  *           isqlda;                 /* input SQLDA */
    long                fetch_stat;
    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)
    }

    /*
     *    Prepare and execute the first select statement.
     *    Free the statement handle, when done.
     */

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

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

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

    if (isc_dsql_prepare(status, &trans, &stmt, 0, tot_str, 1, osqlda))
        isc_print_status(status);

    osqlda->sqlvar[0].sqldata = (char *) &tot_budget;
    osqlda->sqlvar[0].sqltype = SQL_DOUBLE + 1;
    osqlda->sqlvar[0].sqllen  = sizeof(budget);
    osqlda->sqlvar[0].sqlind  = &flag3;

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

    fetch_stat = isc_dsql_fetch(status, &stmt, 1, osqlda);

    printf("\nTotal budget:  %16.2f\n\n", tot_budget);

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

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

    /*
     *    Prepare and execute the positioned update.
     *    Re-use the statement handle as the select cursor.
     */
 
    sprintf(upd_str, "UPDATE proj_dept_budget SET projected_budget = ? \
            WHERE CURRENT OF %s", cursor);

    /* Allocate an input SQLDA for the update statement. */
    isqlda = (XSQLDA *) malloc(XSQLDA_LENGTH(1));
    isqlda->sqln = isqlda->sqld = 1;
    isqlda->version = 1;

    isqlda->sqlvar[0].sqldata = (char *) &budget;
    isqlda->sqlvar[0].sqltype = SQL_DOUBLE + 1;
    isqlda->sqlvar[0].sqllen  = sizeof(budget);
    isqlda->sqlvar[0].sqlind  = &flag3;
                              
    /* Free the output SQLDA, which was used previously. */
    free(osqlda);

    /* Re-allocate the output SQLDA. */
    osqlda = (XSQLDA *) malloc(XSQLDA_LENGTH(3));
    osqlda->sqln = 3;
    osqlda->sqld = 3;
    osqlda->version = 1;

    osqlda->sqlvar[0].sqldata = proj_id;
    osqlda->sqlvar[0].sqlind  = &flag0;

    osqlda->sqlvar[1].sqldata = dept_no;
    osqlda->sqlvar[1].sqlind  = &flag1;

    osqlda->sqlvar[2].sqldata = (char *) &budget;
    osqlda->sqlvar[2].sqlind  = &flag2;
                              
    if (isc_start_transaction(status, &trans, 1, &DB, 0, NULL))
    {
        ERREXIT(status, 1)
    }

    /* Zero the statement handle. */
    stmt = NULL;

    if (isc_dsql_allocate_statement(status, &DB, &stmt))
        isc_print_status(status);
    
    if (isc_dsql_prepare(status, &trans, &stmt, 0, sel_str, 1, osqlda))
        isc_print_status(status);
               
    /* Declare the cursor. */
    isc_dsql_set_cursor_name(status, &stmt, cursor, 0);

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

    printf("\n%-15s%-10s%-18s%-18s\n\n",
           "PROJ", "DEPT", " CURRENT BUDGET",  "  CHANGED TO");

    /*
     *    Fetch and update department budgets.
     */

    while ((fetch_stat = isc_dsql_fetch(status, &stmt, 1, osqlda)) == 0)
    {
        /* Determine the increase percentage. */
        proj_id[PROJLEN] = '\0';
        dept_no[DEPTLEN] = '\0';
        printf("%-15s%-10s%15.2f", proj_id, dept_no, budget);
        budget = budget + budget * increase_factor(budget);
        printf("%15.2f\n", budget);

        /* Increase the budget. */
        isc_dsql_exec_immed2(status, &DB, &trans, 0, upd_str, 1, isqlda, NULL);

        if (isc_sqlcode(status) == -625)
        {
            printf("\tExceeded budget limit -- not updated.\n");
            continue;
        }
        else
            isc_print_status(status);
    }

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

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

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

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

    free(osqlda);
    free(isqlda);

    return 0;
}
    
/*
 *    Determine a percent increase for the department's budget.
 */
float increase_factor (double budget)
{
    if (budget < 100000L)
        return (float)0.15;
    else if (budget < 500000L)
        return (float)0.10;
    else 
        return (float)0.5;
}