/*******************************************************************************
**                                                                        
** Source File Name = tbdefine.c                                        
**                                                                        
** Licensed Materials - Property of IBM                                   
**                                                                        
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.                                                   
**                                                                        
** US Government Users Restricted Rights - Use, duplication or            
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.      
**                                                                        
**                                                                        
**    PURPOSE :                                                           
**    Shows how to create/alter/drop tables.
**                                                                        
** 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 "utilcli.h"          /* Header file for CLI sample code */

int TbCreate( SQLHANDLE) ;
int TbAlter( SQLHANDLE) ;
int TbDrop( SQLHANDLE) ;

/*******************************************************************
** main
*******************************************************************/
int main( int argc, char * argv[] )
{   SQLRETURN   sqlrc = SQL_SUCCESS;
    int         rc = 0; 
    SQLHANDLE   henv;  /* environment handle */
    SQLHANDLE   hdbc;  /* connection handles */

    char       dbAlias[SQL_MAX_DSN_LENGTH + 1] ;
    char       user[MAX_UID_LENGTH + 1] ;
    char       pswd[MAX_PWD_LENGTH + 1] ;

    /* checks the command line arguments */
    rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd );
    if ( rc != 0 ) return( rc ) ;

    printf("\n\nTABLES: HOW TO CREATE/ALTER/DROP TABLES.\n");

    /* initialize the CLI application */
    rc = CLIAppInit( dbAlias, user, pswd, &henv, &hdbc, 
                     (SQLPOINTER)SQL_AUTOCOMMIT_ON);
    if ( rc != 0 ) return( rc ) ;

    rc = TbCreate( hdbc) ;
/*    rc = TbAlter( hdbc) ;  */
    rc = TbDrop( hdbc) ;    
    
    /* terminate the CLI application */
    rc = CLIAppTerm( &henv, &hdbc, dbAlias);
    return( rc ) ;
}                                  /* end main */
    


/******************************************************************************
**    TbCreate
******************************************************************************/
int TbCreate( SQLHANDLE hdbc)
{   SQLRETURN   sqlrc = SQL_SUCCESS;
    int         rc = 0; 
    SQLHANDLE   hstmt ;  /* statement handle */

    SQLCHAR *   stmt = ( SQLCHAR *) "CREATE TABLE TBDEFINE ( "
        "Col1     SMALLINT, "
        "Col2     CHAR(7), "
        "Col3     VARCHAR(7) , "
        "Col4     DEC(9,2), "
        "Col5     DATE, "
        "Col6     BLOB(5000), "	
        "Col7     CLOB(5000)      )" ;	


    
    printf("\nUSE THE CLI FUNCTIONS\n");
    
    printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
    printf("-SQLExecDirect\n-SQLFreeHandle\n");

    printf("TO SHOW HOW TO CREATE A TABLE:\n");   

    /* set AUTOCOMMIT on */
    sqlrc = SQLSetConnectAttr( hdbc,
                               SQL_ATTR_AUTOCOMMIT,
                               (SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
    DBC_HANDLE_CHECK( hdbc, sqlrc);
    
    /* allocate a statement handle */
    sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
    DBC_HANDLE_CHECK( hdbc, sqlrc);

    /* create the table */ 
    printf("\n    Execute directly the statement\n");
    printf("        CREATE TABLE TBDEFINE ( \n");    
    printf("            Col1     SMALLINT, \n"); 
    printf("            Col2     CHAR(7), \n"); 
    printf("            Col3     VARCHAR(7) , \n"); 
    printf("            Col4     DEC(9,2), \n"); 
    printf("            Col5     DATE, \n"); 
    printf("            Col6     BLOB(5000), \n"); 
    printf("            Col7     CLOB(5000)      )\n"); 
    sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
    STMT_HANDLE_CHECK( hstmt, sqlrc);

    /* free the statement handle */
    sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
    STMT_HANDLE_CHECK( hstmt, sqlrc);    
    
    return(rc);  
}    


/******************************************************************************
**    TbDrop
******************************************************************************/
int TbDrop( SQLHANDLE hdbc)
{   SQLRETURN   sqlrc = SQL_SUCCESS;
    int         rc = 0; 
    SQLHANDLE   hstmt ;  /* statement handle */

    SQLCHAR *   stmt = ( SQLCHAR *) "DROP TABLE TBDEFINE" ;	


    
    printf("\nUSE THE CLI FUNCTIONS\n");
    
    printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
    printf("-SQLExecDirect\n-SQLFreeHandle\n");

    printf("TO SHOW HOW TO DROP A TABLE:\n");   

    /* set AUTOCOMMIT on */
    sqlrc = SQLSetConnectAttr( hdbc,
                               SQL_ATTR_AUTOCOMMIT,
                               (SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
    DBC_HANDLE_CHECK( hdbc, sqlrc);
    
    /* allocate a statement handle */
    sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
    DBC_HANDLE_CHECK( hdbc, sqlrc);

    /* drop the table */ 
    printf("\n    Execute directly the statement\n");
    printf("        %s\n", stmt);    
    sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
    STMT_HANDLE_CHECK( hstmt, sqlrc);

    /* free the statement handle */
    sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
    STMT_HANDLE_CHECK( hstmt, sqlrc);    
    
    return(rc);  
}