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

#define MAX_UID_LENGTH 18
#define MAX_PWD_LENGTH 30

/* Macro for common Error Checking using check_error from samputil.c */
#define CHECK_HANDLE( htype, hndl, RC ) if ( RC != SQL_SUCCESS ) \
   { check_error( htype, hndl, RC, __LINE__, __FILE__ ) ; }

/* Forward declarations */
SQLRETURN check_error( SQLSMALLINT, SQLHANDLE, SQLRETURN, int, char * ) ;
SQLRETURN DBconnect( SQLHANDLE, SQLHANDLE * ) ;
SQLRETURN print_error( SQLSMALLINT, SQLHANDLE, SQLRETURN, int, char * ) ;
SQLRETURN print_results( SQLHANDLE, SQLSMALLINT * ) ;
SQLRETURN terminate( SQLHANDLE, SQLRETURN ) ;

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

#define  MAX_STMT_LEN 2048
#define  MAXCOLS   255

#ifndef max
#define  max(a,b) (a > b ? a : b)
#endif

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

/* Function declarations: */
int process_stmt( SQLHANDLE, SQLSMALLINT * ) ;

/*******************************************************************
** main
** - initialize
** - start a transaction
** - terminate
*******************************************************************/
int
main( int argc, char * argv[] )
{
    SQLHANDLE henv, hdbc, hstmt ;
    SQLCHAR         sqlstmt[MAX_STMT_LEN + 1] = "";
    SQLCHAR         product_id[20];
    SQLRETURN       rc;
    SQLSMALLINT     rowcount = 0;

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

    /* This little application takes a product OID. */
    strncpy (( char * ) server, ( const char * ) (( argc > 2 ) ? argv[2] : "mfg"     ), SQL_MAX_DSN_LENGTH );
    strncpy (( char * ) uid,    ( const char * ) (( argc > 3 ) ? argv[3] : "db2inst1"  ), MAX_UID_LENGTH );
    strncpy (( char * ) pwd,    ( const char * ) (( argc > 4 ) ? argv[4] : "ER5nbc8" ), MAX_PWD_LENGTH );
    
    /* 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 ) ;
    
    if ( rc = process_stmt( hstmt, &rowcount ) == SQL_ERROR )
      return( rc ) ;
    
    rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
    
    rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
    
    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( rowcount ? 0 : 1 ) ;
}                               /* end main */

/*******************************************************************
** process_stmt
** - allocates a statement resources
** - executes the statement
** - display results
** - frees the statement resources
*******************************************************************/

int process_stmt( SQLHANDLE hstmt, SQLSMALLINT * rowcount ) {

    SQLRETURN       rc;

    /* execute the SQL statement in "sqlstr"    */

    rc = SQLGetTypeInfo(hstmt, SQL_ALL_TYPES);
    if (rc != SQL_SUCCESS)
      if (rc == SQL_NO_DATA_FOUND) {
        fprintf(stderr, "\nStatement executed without error, however,\n");
        fprintf(stderr, "no data was found or modified\n");
        return (SQL_SUCCESS);
      }
      else CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

    print_results( hstmt, rowcount ) ; /* display the result set */

    /* free statement resources */

    rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

    rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

    rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

    return( rc ) ;
} /* end process_stmt */


/* check_error - calls print_error(), checks severity of return code */

SQLRETURN check_error( SQLSMALLINT htype, /* A handle type identifier */
                       SQLHANDLE   hndl,  /* A handle */
                       SQLRETURN   frc,   /* Return code to be included with error msg  */
                       int         line,  /* Used for output message, indcate where     */
                       char *      file   /* the error was reported from  */
                     ) {

  if ( frc != SQL_SUCCESS && frc != SQL_SUCCESS_WITH_INFO ) {
    print_error( htype, hndl, frc, line, file ) ;

    switch ( frc ) {
      case SQL_SUCCESS:
        break ;
      case SQL_INVALID_HANDLE:
        fprintf( stderr, "\n>------ ERROR Invalid Handle --------------------------\n" ) ;
      case SQL_ERROR:
        fprintf( stderr, "\n>--- FATAL ERROR, Attempting to rollback transaction --\n" ) ;
        if ( SQLEndTran( htype, hndl, SQL_ROLLBACK ) != SQL_SUCCESS )
           fprintf( stderr, ">Rollback Failed, Exiting application\n" ) ;
        else
           fprintf( stderr, ">Rollback Successful, Exiting application\n" ) ;
        return( terminate( hndl, frc ) ) ;
      case SQL_SUCCESS_WITH_INFO:
        fprintf( stderr, "\n> ----- Warning Message , application continuing ------- \n" ) ;
        break ;
      case SQL_NO_DATA_FOUND:
        fprintf( stderr, "\n> ----- No Data Found, application continuing --------- \n" ) ;
        break ;
      default:
        fprintf( stderr, "\n> ----------- Invalid Return Code --------------------- \n" ) ;
        fprintf( stderr, "> --------- Attempting to rollback transaction ---------- \n" ) ;
        if ( SQLEndTran( htype, hndl, SQL_ROLLBACK ) != SQL_SUCCESS )
           fprintf( stderr, ">Rollback Failed, Exiting application\n" ) ;
        else
           fprintf( stderr, ">Rollback Successful, Exiting application\n" ) ;
        return( terminate( hndl, frc ) ) ;
    }
  }
  return ( frc ) ;
}

/* connect without prompt */

SQLRETURN DBconnect( SQLHANDLE henv,
                     SQLHANDLE * hdbc
                   ) {

    /* allocate a connection handle */
    if ( SQLAllocHandle( SQL_HANDLE_DBC,
                         henv,
                         hdbc
                       ) != SQL_SUCCESS ) {
        fprintf( stderr, ">---ERROR while allocating a connection handle-----\n" ) ;
        return( SQL_ERROR ) ;
    }

    /* Set AUTOCOMMIT OFF */
    if ( SQLSetConnectAttr( * hdbc,
                            SQL_ATTR_AUTOCOMMIT,
                            ( void * ) SQL_AUTOCOMMIT_OFF, SQL_NTS
                          ) != SQL_SUCCESS ) {
        fprintf( stderr, ">---ERROR while setting AUTOCOMMIT OFF ------------\n" ) ;
        return( SQL_ERROR ) ;
    }

    if ( SQLConnect( * hdbc,
                     server, SQL_NTS,
                     uid,    SQL_NTS,
                     pwd,    SQL_NTS
                   ) != SQL_SUCCESS ) {
        fprintf( stderr, ">--- Error while connecting to database: %s -------\n",
                server
              ) ;
        SQLDisconnect( * hdbc ) ;
        SQLFreeHandle( SQL_HANDLE_DBC, * hdbc ) ;
        return( SQL_ERROR ) ;
    }

    return( SQL_SUCCESS ) ;
}

/* print_error - calls SQLGetDiagRec(), displays SQLSTATE and message **
**             - called by check_error                                */

SQLRETURN print_error( SQLSMALLINT htype, /* A handle type identifier */
                       SQLHANDLE   hndl,  /* A handle */
                       SQLRETURN   frc,   /* Return code to be included with error msg  */
                       int         line,  /* Used for output message, indcate where     */
                       char *      file   /* the error was reported from  */
                     ) {

    SQLCHAR     buffer[SQL_MAX_MESSAGE_LENGTH + 1] ;
    SQLCHAR     sqlstate[SQL_SQLSTATE_SIZE + 1] ;
    SQLINTEGER  sqlcode ;
    SQLSMALLINT length, i ;

    fprintf( stderr, ">--- ERROR -- RC = %d Reported from %s, line %d ------------\n",
            frc,
            file,
            line
          ) ;

    i = 1 ;
    while ( SQLGetDiagRec( htype,
                           hndl,
                           i,
                           sqlstate,
                           &sqlcode,
                           buffer,
                           SQL_MAX_MESSAGE_LENGTH + 1,
                           &length
                         ) == SQL_SUCCESS ) {
       fprintf( stderr, "         SQLSTATE: %s\n", sqlstate ) ;
       fprintf( stderr, "Native Error Code: %ld\n", sqlcode ) ;
       fprintf( stderr, "%s \n", buffer ) ;
       i++ ;
    }

    fprintf( stderr, ">--------------------------------------------------\n" ) ;

    return( SQL_ERROR ) ;

}

SQLRETURN print_results( SQLHANDLE hstmt, SQLSMALLINT * rowcount ) {

  SQLRETURN   sqlrc = SQL_SUCCESS;
  int         rc = 0; 

  SQLSMALLINT i ; /* index */   
  SQLSMALLINT nResultCols ; /* variable for SQLNumResultCols */     
    
  SQLCHAR     colName[32] ;    /* variables for SQLDescribeCol  */
  SQLSMALLINT colNameLen ;    
  SQLSMALLINT colType ;
  SQLUINTEGER colSize ;
  SQLSMALLINT colScale ;
      
  SQLINTEGER colDataDisplaySize ; /* the max. size of the data */
    
  SQLINTEGER colDisplaySize[MAXCOLS] ; /* the max. size of the column */
  
  struct
  {   SQLCHAR    * buff;
    SQLINTEGER len;
    SQLINTEGER buffLen;	
  } outData[MAXCOLS];  /* var. to read the results */    

  char sep = ' ';
  
  /* identify the output columns */    
  sqlrc = SQLNumResultCols( hstmt, &nResultCols ) ;
  CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, sqlrc ) ;

  printf("    sub type_info_all {\n");
  printf("        my ($dbh) = @_;\n");
  printf("\n");
  printf("        # Vales in $names and $ti are based on results of call\n");
  printf("        # to DB2 SQLGetTypeInfo CLI function:\n");
  printf("\n");
  printf("        my $names = {");

  for ( i = 0; i < nResultCols; i++ ) {
    sqlrc = SQLDescribeCol( hstmt, ( SQLSMALLINT ) ( i + 1 ), colName, sizeof(colName), &colNameLen, &colType, &colSize, &colScale, NULL ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, sqlrc ) ;
    
    /* get display size for column */
    sqlrc = SQLColAttribute( hstmt, ( SQLSMALLINT ) ( i + 1 ), SQL_DESC_DISPLAY_SIZE, NULL, 0, NULL, &colDataDisplaySize ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, sqlrc ) ;
    
    /* set "column display size" to max of "column data display size",
       and "column name length". Plus at least one space between 
       columns.
    */	
    colDisplaySize[i] = max( colDataDisplaySize, colNameLen ) + 1 ;
    
    /* print the column name */	
    printf ("%c\n          %s => %d", sep, colName, i ) ;
    sep = ',';
    /* set "output data buffer length" to "column data display size".
       Plus one byte for null terminator.
    */
    outData[i].buffLen = colDataDisplaySize + 1;        	
    
    /* allocate memory to bind column */
    outData[i].buff = ( SQLCHAR * ) malloc( (int) outData[i].buffLen ) ;
    
    /* bind columns to program vars, converting all types to CHAR */
    sqlrc = SQLBindCol( hstmt, ( SQLSMALLINT ) ( i + 1 ), SQL_C_CHAR, outData[i].buff, outData[i].buffLen, &outData[i].len ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, sqlrc ) ;
  }
       
  /* fetch each row and display */
  sqlrc = SQLFetch( hstmt ) ;

  printf("\n        };\n") ;
  printf("        my $ti = \n");
  printf("            [ $names,\n");

  while ( sqlrc == SQL_SUCCESS || sqlrc == SQL_SUCCESS_WITH_INFO ) {
    char sep = '[';
    printf ("              ");
    for ( i = 0; i < nResultCols; i++ ) {
      if ( i == 9 ) {
        printf ("%c\n                  ", sep);
      }
      else {
        printf ("%c ", sep);
      }
      /* check for NULL data */
      if ( outData[i].len == SQL_NULL_DATA ) {
        printf ("%s", "undef");
      }
      else {
        /* print outData for this column */
        if ( 0 == i || 3 == i || 4 == i || 5 == i ) {
          /* always quoted fields */
          if ( strcmp ("\'", outData[i].buff )) {
            printf ("'%s'", outData[i].buff );
          }
          else {
            printf ("'\\%s'", outData[i].buff);
          }
        }
        else {
          /* numeric fields, quoted if 0 or length >= 10 */
          if ( ! strcmp ("0", outData[i].buff) || 9 < outData[i].len ) {
            printf ("'%s'", outData[i].buff );
          }
          else {
            printf ("%s", outData[i].buff );
          }
        }
      }
      sep = ',';
    } /* for all columns in this row  */
    
    printf(" ],\n");	
    sqlrc = SQLFetch( hstmt ) ;  
    if (rc != SQL_SUCCESS)
      if (rc == SQL_NO_DATA_FOUND) {
        return (SQL_SUCCESS);
      }
      else
        CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
  } /* while rows to fetch */

  printf ("            ];\n");
  printf ("        return $ti;\n");
  printf ("    }\n");
  
  /* free data buffers */
  for ( i = 0; i < nResultCols; i++ ) {
    free( outData[i].buff ) ;
  }
    
  return(rc);    
}

/* terminate and free environment handle */

SQLRETURN terminate( SQLHANDLE henv,
                     SQLRETURN rc
                   ) {

    SQLRETURN lrc ;

    fprintf ( stderr, ">Terminating ....\n" ) ;
    print_error( SQL_HANDLE_ENV,
                 henv,
                 rc,
                 __LINE__,
                 __FILE__
               ) ;

    /* Free environment handle */
    if ( ( lrc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ) != SQL_SUCCESS )
       print_error( SQL_HANDLE_ENV,
                    henv,
                    lrc,
                    __LINE__,
                    __FILE__
                  ) ;

    return( rc ) ;
}