/*******************************************************************************
**                                                                        
** Source File Name = compnd.c  1.4                                      
**                                                                        
** Licensed Materials - Property of IBM                                   
**                                                                        
** (C) COPYRIGHT International Business Machines Corp. 1995, 1999
** All Rights Reserved.                                                   
**                                                                        
** US Government Users Restricted Rights - Use, duplication or            
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.      
**                                                                        
**                                                                        
**    PURPOSE :                                                           
**    Demonstrates how to execute a compound SQL statement.
**    Creates an 'awards' table and executes a compound statement
**    with 4 insert sub-statemens.
**                                                                        
** For more information about these samples see the README file.
**
** For more information on programming in CLI see the:
**     - "Building CLI Applications" section of the Application Building Guide, and the
**     - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "samputil.h"          /* Header file for CLI sample code */

/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
   /* Need to include ASLM for 68K applications */
   #include <LibraryManager.h>
#endif

/*
 Global Variables for user id and password.
 To keep samples simple, not a recommended practice.
*/
extern SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
extern SQLCHAR uid[MAX_UID_LENGTH + 1] ;
extern SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;

/* main */
int main( int argc, char * argv[] ) {

    SQLHANDLE henv, hdbc, hstmt, cmhstmt[4] ;
    SQLRETURN rc ;

/*--> SQLL1X21.SCRIPT */
    SQLCHAR * stmt[] = {

        ( SQLCHAR * ) "INSERT INTO awards (id, award) "
        "SELECT id, 'Sales Merit' from staff "
        "WHERE job = 'Sales' AND (comm/100 > years)",

        ( SQLCHAR * ) "INSERT INTO awards (id, award) "
        "SELECT id, 'Clerk Merit' from staff "
        "WHERE job = 'Clerk' AND (comm/50 > years)",

        ( SQLCHAR * ) "INSERT INTO awards (id, award) "
        "SELECT id, 'Best ' concat job FROM STAFF "
        "WHERE comm = (SELECT max(comm) FROM staff WHERE job = 'Clerk')",

        ( SQLCHAR * ) "INSERT INTO awards (id, award) "
        "SELECT id, 'Best ' concat job FROM STAFF "
        "WHERE comm = (SELECT max(comm) FROM STAFF WHERE job = 'Sales')",

    } ;

    SQLINTEGER i ;

/*<-- */

/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
    /*
     Before making any API calls for 68K environment,
     need to initialize the Library Manager
    */
    InitLibraryManager(0,kCurrentZone,kNormalMemory);
    atexit(CleanupLibraryManager);
#endif

    /* macro to initalize server, uid and pwd */
    INIT_UID_PWD ;

    /* allocate an environment handle */
    rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
    if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;

    /* allocate a connect handle, and connect */
    rc = DBconnect( henv, &hdbc ) ;
    if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;

    rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

     rc = SQLExecDirect( hstmt,
                        ( SQLCHAR * ) "DROP TABLE AWARDS",
                        SQL_NTS
                      ) ;
   /* Will not check the handle, will return an error the 1st */
   /* time the sample is run because the table will not exist. */

    rc = SQLExecDirect( hstmt,
         ( SQLCHAR * ) "CREATE TABLE AWARDS (ID INTEGER, AWARD CHAR(12))" ,
                        SQL_NTS
                      ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

    rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

/*--> */
    /* Prepare 4 substatements */
    for ( i = 0; i < 4; i++ ) {
        rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &cmhstmt[i] ) ;
        CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
        rc = SQLPrepare( cmhstmt[i], stmt[i], SQL_NTS ) ;
        CHECK_HANDLE( SQL_HANDLE_STMT, cmhstmt[i], rc ) ;
    }

    rc = SQLExecDirect( hstmt,
                        ( SQLCHAR * ) "BEGIN COMPOUND NOT ATOMIC STATIC",
                        SQL_NTS
                      ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

    /* Execute 4 substatements */
    for ( i = 0; i < 4; i++ ) {
        rc = SQLExecute( cmhstmt[i] ) ;
        CHECK_HANDLE( SQL_HANDLE_STMT, cmhstmt[i], rc ) ;
    }

    /* Execute the COMPOUND statement (of 4 sub-statements) */
    printf( "Executing the COMPOUND statement (of 4 sub-statements)\n" ) ;

    rc = SQLExecDirect( hstmt,
                        ( SQLCHAR * ) "END COMPOUND COMMIT",
                        SQL_NTS
                      ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

    rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

    for ( i = 0; i < 4; i++ ) {
        rc = SQLFreeHandle( SQL_HANDLE_STMT, cmhstmt[i] ) ;
        CHECK_HANDLE( SQL_HANDLE_STMT, cmhstmt[i], rc ) ;
    }

/*<-- */

    /* COMMIT, free resources and exit */

    rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

    printf( "\n>Disconnecting .....\n" ) ;
    rc = SQLDisconnect( hdbc ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

    rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

    rc = SQLFreeHandle( SQL_HANDLE_ENV,  henv ) ;
    if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;

    return( SQL_SUCCESS ) ;

}                               /* end main */