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