/*
* Program type: API Interface
*
* Description:
* This program has several active transactions:
*
* Sales order records are entered continuously (transaction 1).
*
* If it is discovered during transaction 1, that the customer
* placing the order is new, then a new customer record must be
* added (transaction 2).
*
* If the customer record uses a country that does not exist
* in the 'country' table, a new country record is added (transaction 3).
*
* Transaction 2 can be committed after the country is added.
* Transaction 1 can be committed after the customer record is added.
*
* Transactions 1, 2, and 3 can be undone individually, if the user
* decides not to save the sales, customer, or country changes.
* If transaction 3 is undone, transactions 1 and 2 must be undone.
* If transaction 2 is undone, transaction 1 must be undone.
*
* In addition, several independent transactions, selecting the
* customer number and the country records, take place during
* the three update transactions.
* 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 BUFLEN 512
char* more_orders (void);
int do_trans (void);
int cleanup (void);
char *customer = "Maritime Museum";
char *country = "Cayman Islands";
char *currency = "CmnDlr";
isc_db_handle db = NULL;
isc_tr_handle sales_trans = NULL,
cust_trans = NULL,
cntry_trans = NULL,
trans = NULL;
ISC_STATUS_ARRAY status;
static char *Sales[] = {"V88005", 0};
int Inp_ptr = 0;
char *trans_str = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
int main (int argc, char** argv)
{
char empdb[128];
if (argc > 1)
strcpy(empdb, argv[1]);
else
strcpy(empdb, "employee.fdb");
/* Zero the transaction handles. */
if (isc_attach_database(status, 0, empdb, &db, 0, NULL))
{
ERREXIT(status, 1)
}
/* Do the updates */
do_trans();
if (trans)
isc_rollback_transaction(status, &trans);
if (cust_trans)
isc_rollback_transaction(status, &cust_trans);
if (cntry_trans)
isc_rollback_transaction(status, &cntry_trans);
if (sales_trans)
isc_rollback_transaction(status, &sales_trans);
/* Remove them again */
cleanup();
if (trans)
isc_rollback_transaction(status, &trans);
isc_detach_database(status, &db);
return 0;
}
/*
* Function does all the work.
*/
int do_trans (void)
{
long cust_no;
char sales_str[BUFLEN + 1];
char cust_str[BUFLEN + 1];
char cntry_str[BUFLEN + 1];
char sel_str[BUFLEN + 1];
XSQLDA *sqlda1, *sqlda2, *sqlda3;
isc_stmt_handle stmt0 = NULL,
stmt1 = NULL,
stmt2 = NULL;
short flag0 = 0, flag1 = 0;
long fetch_stat;
long sqlcode;
/* Prepare a query for fetching data. Make it read committed, so you
* can see your own updates.
*/
if (isc_dsql_execute_immediate(status, &db, &trans, 0, trans_str,
1, NULL))
{
ERREXIT(status, 1)
}
sprintf(sel_str, "SELECT cust_no FROM customer WHERE customer = '%s'",
customer);
if (isc_dsql_allocate_statement(status, &db, &stmt0))
{
ERREXIT(status, 1)
}
sqlda1 = (XSQLDA *) malloc(XSQLDA_LENGTH(1));
sqlda1->sqln = 1;
sqlda1->version = 1;
if (isc_dsql_prepare(status, &trans, &stmt0, 0, sel_str, 1, sqlda1))
{
ERREXIT(status, 1)
}
sqlda1->sqlvar[0].sqldata = (char *) &cust_no;
sqlda1->sqlvar[0].sqltype = SQL_LONG + 1;
sqlda1->sqlvar[0].sqlind = &flag0;
/* Prepare a query for checking if a country exists. */
sprintf(sel_str, "SELECT country FROM country WHERE country = '%s'",
country);
sqlda2 = (XSQLDA *) malloc(XSQLDA_LENGTH(1));
sqlda2->sqln = 1;
sqlda2->version = 1;
if (isc_dsql_allocate_statement(status, &db, &stmt2))
{
ERREXIT(status, 1)
}
if (isc_dsql_prepare(status, &trans, &stmt2, 0, sel_str, 1, sqlda2))
{
ERREXIT(status, 1)
}
sqlda2->sqlvar[0].sqldata = (char *) country;
sqlda2->sqlvar[0].sqltype = SQL_TEXT + 1;
sqlda2->sqlvar[0].sqlind = &flag1;
/*
* Start transaction 1 -- add a sales order.
* for a customer.
*/
cust_no = 9999;
/* This transaction is also read committed so it can see the results of
* other transactions
*/
if (isc_dsql_execute_immediate(status, &db, &sales_trans, 0, trans_str,
1, NULL))
{
ERREXIT(status, 1)
}
sprintf(sales_str, "INSERT INTO sales (po_number, cust_no, \
order_status, total_value) VALUES ('V88005', ?, \
'new', 2000)");
if (isc_dsql_allocate_statement(status, &db, &stmt1))
{
ERREXIT(status, 1)
}
if (isc_dsql_prepare(status, &trans, &stmt1, 0, sales_str, 1, NULL))
{
ERREXIT(status, 1)
}
/* Insert parameter (cust_no) used for sales insert */
sqlda3 = (XSQLDA *) malloc(XSQLDA_LENGTH(1));
sqlda3->sqln = 1;
sqlda3->version = 1;
isc_dsql_describe_bind(status, &stmt1, 1, sqlda3);
sqlda3->sqlvar[0].sqldata = (char *) &cust_no;;
sqlda3->sqlvar[0].sqlind = &flag0;
isc_dsql_execute(status, &sales_trans, &stmt1, 1, sqlda3);
sqlcode = isc_sqlcode(status);
if (sqlcode == -530)
{
/* Integrity constraint indicates missing primary key*/
printf ("No customer number %ld -- adding new customer \n", cust_no);
/*
* This a new customer.
* Start transaction 2 -- add a customer record.
*/
if (isc_start_transaction(status, &cust_trans, 1, &db, 0, NULL))
{
ERREXIT(status, 1)
}
sprintf(cust_str, "INSERT INTO customer (customer, country) \
VALUES ('%s', '%s')", customer, country);
printf("Adding a customer record for %s\n", customer );
/* Does the customer country exist in the validation table?
* Do a lookup this time instead of waiting for the constraint
* violation. Because trans is read committed, it will see
* updates on other transactions.
*/
if (isc_dsql_execute(status, &trans, &stmt2, 1, NULL))
{
ERREXIT(status, 1)
}
fetch_stat = isc_dsql_fetch(status, &stmt2, 1, sqlda2);
/*
* Country was not found in the validation table.
* Start transaction 3 -- add a country record.
*/
if (fetch_stat == 100L)
{
printf("Missing country record, adding %s\n", country);
if (isc_start_transaction(status, &cntry_trans, 1, &db, 0, NULL))
{
ERREXIT (status, 1)
}
sprintf(cntry_str, "INSERT INTO country VALUES ('%s', '%s')",
country, currency);
if (isc_dsql_execute_immediate(status, &db, &cntry_trans, 0,
cntry_str, 1, NULL))
{
ERREXIT(status, 1)
}
/* This must be committed to be visible */
isc_commit_transaction(status, &cntry_trans);
}
/*
* End transaction 2.
* Add the customer record, now with a reference.
*/
if (isc_dsql_execute_immediate(status, &db, &cust_trans, 0, cust_str,
1, NULL))
{
ERREXIT(status, 1)
}
/* Commit to make this reference visible */
if (isc_commit_transaction(status, &cust_trans))
{
ERREXIT(status, 1)
}
/* Lookup the new cust_no for this record */
if (isc_dsql_execute(status, &trans, &stmt0, 1, NULL))
{
ERREXIT(status, 1)
}
if (!isc_dsql_fetch(status, &stmt0, 1, sqlda1))
printf("New customer number: %ld\n", cust_no);
/* Then try to add the sales record again */
if (isc_dsql_execute(status, &sales_trans, &stmt1, 1, sqlda3))
{
ERREXIT(status, 1)
}
}
if (isc_commit_transaction(status, &sales_trans))
{
ERREXIT (status, 1)
}
printf("Added sales record for V88055\n");
isc_commit_transaction(status, &trans);
isc_dsql_free_statement(status, &stmt0, DSQL_close);
isc_dsql_free_statement(status, &stmt1, DSQL_close);
isc_dsql_free_statement(status, &stmt2, DSQL_close);
free(sqlda1);
free(sqlda2);
free(sqlda3);
return 0;
}
/* Cleanup removes all updates that might have been done
* Make sure to cleanup in reverse order to avoid primary
* key violations
*/
int cleanup (void)
{
char del_str[100];
printf ("Cleaning up...\n");
if (isc_start_transaction(status, &trans, 1, &db, 0, NULL))
{
ERREXIT(status, 1)
}
strcpy(del_str, "DELETE FROM SALES WHERE PO_NUMBER = \"V88005\"");
if (isc_dsql_execute_immediate(status, &db, &trans, 0, del_str, 1, NULL))
{
ERREXIT(status, 1)
}
strcpy (del_str, "DELETE FROM CUSTOMER WHERE COUNTRY LIKE \"Cayman%\"");
if (isc_dsql_execute_immediate(status, &db, &trans, 0, del_str, 1, NULL))
{
ERREXIT (status, 1)
}
strcpy (del_str, "DELETE FROM COUNTRY WHERE COUNTRY LIKE \"Cayman%\"");
if (isc_dsql_execute_immediate(status, &db, &trans, 0, del_str, 1, NULL))
{
ERREXIT(status, 1)
}
if (isc_commit_transaction(status, &trans))
{
ERREXIT(status, 1)
}
return 0;
}
/*
* Return the order number for the next sales order to be entered.
*/
char* more_orders (void)
{
return Sales[Inp_ptr++];
}