firebird/examples/api/api12.c

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