/*
 * showtab.c
 *
 * show definitions on field returned by a SELECT statement
 *
 * (c) 1996 by Dirk Ohme, all rights reserved
 */

/*---| defines |-------------------------------------------------------------*/
#define PRG_STRING      "ShowTab V "VERSION" - (c) 1996 by Dirk Ohme"
#define MAX_COLUMNS     64

/*---| includes (common) |---------------------------------------------------*/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>

/*---| includes (DB2/iODBC/mSQL/Oracle) |------------------------------------*/
#ifdef iODBC
/* #  include <isql.h>
 * #  include <isqlext.h>
 */
#  include <sqlcli_.h>
#else
#  ifdef DB2
#    include "sqlcli1.h"
#  endif
#  ifdef mSQL
#    include <sqlcli_.h>
#  endif
#  ifdef ORACLE
#    include <sqlcli_.h>
#  endif
#endif

/*---| function prototypes |-------------------------------------------------*/
int       initDB(       char     *pszDB,
                        char     *pszUser,
                        char     *pszPassword,
                        SQLHENV  *phEnv,
                        SQLHDBC  *phDbc
                );
int       exitDB(       SQLHENV  *phEnv,
                        SQLHDBC  *phDbc
                );
int       execDB(       SQLHENV   hEnv,
                        SQLHDBC   hDbc,
                        char     *pszSqlStmt
                );
void      displayError( SQLHENV   hEnv,
                        SQLHDBC   hDbc,
                        SQLHSTMT  hStmt,
                        int       iRC,
                        int       iLineNum
                      );



/*-----------------------------------------------------------------------------
 * main
 *-----------------------------------------------------------------------------
 * function: main procedure
 * input:    int argc
 *             number of command line parameters
 *           char *argv[]
 *             pointer to list of command line parameters
 * output:   int
 *              < 0 - severe error
 *             == 0 - no error
 *              > 0 - error during DB access
 *-----------------------------------------------------------------------------
 */
int main( int argc, char *argv[] )
{ /*---| variables |---*/
  int               iRC      = 0;                /* return code              */

  SQLHENV           hEnv;                        /* handle for environment   */
  SQLHDBC           hDbc;                        /* handle for DB connection */
  char              szBuffer[256] = "";          /* input line buffer        */

  /*---| program |---*/
  /*
  ** program notification
  */
  fprintf( stderr, "%s\n\n", PRG_STRING );

  /*
  ** check parameters
  */
  if( 3 > argc )
  { fprintf( stderr, "\nsyntax error!\a\n" );
    fprintf( stderr, "syntax: showtab <database> <user> [<password>]\n" );
    fprintf( stderr, "  <database> - name of database to connect to\n" );
    fprintf( stderr, "  <user>     - user name for login\n" );
    fprintf( stderr, "  <password> - optional password for login\n" );
    iRC = -1;                                    /* -1 == parameter error    */
  } /* if */

  /*
  ** login to database
  */
  if( 0 <= iRC )
  { fprintf( stderr, "..DB login\n" );
    if( 0 != initDB(argv[1], argv[2], (3 < argc) ? argv[3]
                                                 : NULL,
                    &hEnv, &hDbc)
      )
    { iRC = -2;                                  /* -2 == DB login error     */
    }
  } /* if */

  /*
  ** parse input
  */
  if( 0 <= iRC )
  {  szBuffer[0] = '\0';
    printf( "In order to terminate the program, enter an empty line\n" );
    do
    { char      *psz;                            /* local string pointer     */

      /*
      ** get a line from stdin
      */
      printf( "\nSQL> " );
      fflush( stdout );
      gets( szBuffer );

      /*
      ** filter control sequences
      */
      while( NULL != (psz=strchr(szBuffer, '\n')) ||
             NULL != (psz=strchr(szBuffer, '\r')) )
      { *psz = ' ';
      }

      /*
      ** execute line
      */
      if( '\0' != szBuffer[0] && 0 != execDB(hEnv, hDbc, &szBuffer[0]) )
        fprintf( stderr, "execution error on '%s'\n", szBuffer );

    } while( '\0' != szBuffer[0] );
  } /* if */

  /*
  ** logout from database
  */
  if( 0 <= iRC )
  { fprintf( stderr, "..DB logout\n" );
    if( 0 != exitDB(&hEnv, &hDbc) )
    { iRC = -3;                                  /* -3 == DB logout error    */
    }
  }

  /*
  ** return to system
  */
  fprintf( stderr, "\niRC = %d\n", iRC );
  return iRC;

} /* main */



/*-----------------------------------------------------------------------------
 * initDB
 *-----------------------------------------------------------------------------
 * function: initialisation of DB access
 * input:    char *pszDB                                            (not null)
 *             pointer to string containing the database name
 *           char *pszUser                                          (not null)
 *             pointer to string containing the user name
 *           char *pszPassword
 *             pointer to string containing the password
 *           SQLHENV *phEnv
 *             pointer to DB environment
 *           SQLHDBC *pdDbc
 *             pointer to DB context
 * output:   int
 *             != 0 - error
 *             == 0 - no error
 *-----------------------------------------------------------------------------
 */
int initDB( char *pszDB, char *pszUser, char *pszPassword,
            SQLHENV *phEnv, SQLHDBC *phDbc
          )
{ /*---| variables |---*/
  int     iRC           = 0;                     /* function return code     */

  /*---| program |---*/
  /*
  ** check parameter list
  */
  if( NULL == pszDB || NULL == pszUser || NULL == phEnv || NULL == phDbc )
    return -1;

  /*
  ** get DB environment
  */
  iRC = SQLAllocEnv( phEnv );
  if( SQL_SUCCESS != iRC )
  {
    displayError( *phEnv, SQL_NULL_HDBC, SQL_NULL_HENV, iRC, __LINE__ );
    return -2;
  } /* if */

  /*
  ** allocate a connection handle
  */
  iRC = SQLAllocConnect( *phEnv, phDbc );
  if( SQL_SUCCESS != iRC )
  {
    displayError( *phEnv, *phDbc, SQL_NULL_HENV, iRC, __LINE__ );
    return -3;
  } /* if */

  /*
  ** set AUTOCOMMIT off
  */
  iRC = SQLSetConnectOption( *phDbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF );
  if( SQL_SUCCESS != iRC )
  {
    displayError( *phEnv, *phDbc, SQL_NULL_HENV, iRC, __LINE__ );
    return -4;
  } /* if */

  /*
  ** connect to server
  */
  fprintf( stderr,
           "....connecting to server '%s' as user '%s' (%s)\n",
           pszDB,
           pszUser,
           (NULL != pszPassword) ? pszPassword : "<no password set>"
         );
  iRC = SQLConnect( *phDbc, pszDB,       SQL_NTS,
                            pszUser,     SQL_NTS,
                            pszPassword, SQL_NTS
                  );
  if( SQL_SUCCESS != iRC && SQL_SUCCESS_WITH_INFO != iRC )
  {
    displayError( *phEnv, *phDbc, SQL_NULL_HENV, iRC, __LINE__ );
    return -5;
  } /* if */

  /*
  ** return success
  */
  return 0;

} /* initDB */




/*-----------------------------------------------------------------------------
 * exitDB
 *-----------------------------------------------------------------------------
 * function: withdraw of DB access
 * input:    SQLHENV *phEnv
 *             pointer to DB environment
 *           SQLHDBC *phDbc
 *             pointer to DB context
 * output:   int
 *             != 0 - error
 *             == 0 - no error
 *-----------------------------------------------------------------------------
 */
int exitDB( SQLHENV *phEnv, SQLHDBC *phDbc )
{ /*---| variables |---*/
  int     iRC            = 0;                    /* function return code     */

  /*---| program |---*/
  /*
  ** check parameter list
  */
  if( NULL == phEnv || NULL == phDbc )
    return -1;

  /*
  ** commit transactions
  */
  iRC = SQLTransact( *phEnv, *phDbc, SQL_COMMIT );
  if( SQL_SUCCESS != iRC )
    displayError( *phEnv, *phDbc, SQL_NULL_HENV, iRC, __LINE__ );

  /*
  ** disconnect from DB
  */
  iRC = SQLDisconnect( *phDbc );
  if( SQL_SUCCESS != iRC )
    displayError( *phEnv, *phDbc, SQL_NULL_HENV, iRC, __LINE__ );

  /*
  ** free connection to DB
  */
  iRC = SQLFreeConnect( *phDbc );
  if( SQL_SUCCESS != iRC )
    displayError( *phEnv, *phDbc, SQL_NULL_HENV, iRC, __LINE__ );
  else
    *phDbc = SQL_NULL_HDBC;

  /*
  ** free environment
  */
  iRC = SQLFreeEnv( *phEnv );
  if( SQL_SUCCESS != iRC )
    displayError( *phEnv, SQL_NULL_HDBC, SQL_NULL_HENV, iRC, __LINE__ );
  else
    *phEnv = SQL_NULL_HENV;

  /*
  ** return
  */
  return iRC;

} /* exitDB */



/*-----------------------------------------------------------------------------
 * execDB
 *-----------------------------------------------------------------------------
 * function: execution of a command, display of results
 * input:    SQLHENV hEnv
 *             handle for DB environment
 *           SQLHDBC hDbc
 *             handle for DB context
 *           char *pszSqlStmt
 *             pointer to string containing the SQL statement
 * output:   int
 *             != 0 - error
 *             == 0 - no error
 *-----------------------------------------------------------------------------
 */
int execDB( SQLHENV hEnv, SQLHDBC hDbc, char *pszSqlStmt )
{ /*---| variables |---*/
  SQLHSTMT       hstmt;                          /* handle for statement     */
  int            ci;                             /* counter variable         */
  int            ci2;                            /* counter variable         */
  int            iRC              = 0;           /* function return code     */
  int            iResColumns;                    /* number of result cols    */
  int            iRowCount;                      /* number of rows affected  */
  void          *pData[MAX_COLUMNS];             /* pointer to results       */
  int            fColDesc         = 0;           /* column decriptor flag    */
  int            cbBuffer         = 0;           /* counter for buffer len.  */
  char           szBuffer[256]    = "";          /* string buffer            */
  SQLUSMALLINT   aColDesc[]       = {            /* list of column descript. */
                        SQL_COLUMN_AUTO_INCREMENT,
                        SQL_COLUMN_CASE_SENSITIVE,
                        SQL_COLUMN_CATALOG_NAME,
                        SQL_COLUMN_QUALIFIER_NAME,
                        SQL_COLUMN_COUNT,
                        SQL_COLUMN_DISPLAY_SIZE,
                        SQL_COLUMN_DISTINCT_TYPE,
                        SQL_COLUMN_LABEL,
                        SQL_COLUMN_LENGTH,
                        SQL_COLUMN_MONEY,
                        SQL_COLUMN_NAME,
                        SQL_COLUMN_NULLABLE,
                        SQL_COLUMN_PRECISION,
                        SQL_COLUMN_SCALE,
                        SQL_COLUMN_SCHEMA_NAME,
                        SQL_COLUMN_OWNER_NAME,
                        SQL_COLUMN_SEARCHABLE,
                        SQL_COLUMN_TABLE_NAME,
                        SQL_COLUMN_TYPE,
                        SQL_COLUMN_TYPE_NAME,
                        SQL_COLUMN_UNSIGNED,
                        SQL_COLUMN_UPDATABLE,
                        0
                 };
  char          *pszColDesc[]     = {            /* list of column descript. */
                        "SQL_COLUMN_AUTO_INCREMENT",
                        "SQL_COLUMN_CASE_SENSITIVE",
                        "SQL_COLUMN_CATALOG_NAME",
                        "SQL_COLUMN_QUALIFIER_NAME",
                        "SQL_COLUMN_COUNT",
                        "SQL_COLUMN_DISPLAY_SIZE",
                        "SQL_COLUMN_DISTINCT_TYPE",
                        "SQL_COLUMN_LABEL",
                        "SQL_COLUMN_LENGTH",
                        "SQL_COLUMN_MONEY",
                        "SQL_COLUMN_NAME",
                        "SQL_COLUMN_NULLABLE",
                        "SQL_COLUMN_PRECISION",
                        "SQL_COLUMN_SCALE",
                        "SQL_COLUMN_SCHEMA_NAME",
                        "SQL_COLUMN_OWNER_NAME",
                        "SQL_COLUMN_SEARCHABLE",
                        "SQL_COLUMN_TABLE_NAME",
                        "SQL_COLUMN_TYPE",
                        "SQL_COLUMN_TYPE_NAME",
                        "SQL_COLUMN_UNSIGNED",
                        "SQL_COLUMN_UPDATABLE",
                        NULL
                 };

  /*---| program |---*/
  /*
  ** check parameter list
  */
  if( NULL == pszSqlStmt || '\0' == *pszSqlStmt )
    return -1;

  /*
  ** get statement handle
  */
  iRC = SQLAllocStmt( hDbc, &hstmt );
  if( SQL_SUCCESS != iRC )
    displayError( hEnv, hDbc, hstmt, iRC, __LINE__ );

  /*
  ** prepare statement
  */
  if( SQL_SUCCESS != (iRC=SQLPrepare(hstmt, pszSqlStmt, SQL_NTS)) )
    displayError( hEnv, hDbc, hstmt, iRC, __LINE__ );

  /*
  ** execute statement
  */
  if( SQL_SUCCESS != (iRC=SQLExecute(hstmt)) )
    displayError( hEnv, hDbc, hstmt, iRC, __LINE__ );

  /*
  ** get number of rows affected / columns returned
  */
  iRC = SQLRowCount( hstmt, (SQLINTEGER FAR *) &iRowCount );
  printf( "number of rows affected    : %d\n",
          (SQL_SUCCESS == iRC) ? iRowCount
                               : -1
        );
  iResColumns = 0;
#ifdef DB2
  iRC = SQLNumResultCols( hstmt, (short *) &iResColumns );
#else
  iRC = SQLNumResultCols( hstmt, (SDWORD FAR *) &iResColumns );
#endif
  printf( "number of columns returned : %d\n",
          (SQL_SUCCESS == iRC) ? iResColumns
                               : -1
        );

  /*
  ** get table description and create list with pointers
  */
  if( 0 < iResColumns )
  { SQLCHAR     szColName[80];                   /* name of column           */
    SQLSMALLINT cbColName;                       /* length of column name    */
    SQLSMALLINT fColType;                        /* type of column           */
    SQLUINTEGER uiColPrecision;                  /* precision of column      */
    SQLSMALLINT iColScaling;                     /* scaling of column        */
    SQLSMALLINT fColNullable;                    /* is column nullable?      */

    printf( "result columns:\n" );
    for( ci = 1; ci <= iResColumns; ci++ )
    { /*
      ** display table info
      */
      printf( "  [%03u] ", ci ); fflush( stdout );
      if( SQL_SUCCESS != (iRC=SQLDescribeCol( hstmt, ci, &szColName[0],
                                              sizeof(szColName) - 1,
                                              &cbColName, &fColType,
#ifdef DB2
                                              &uiColPrecision,
#else
                                              (UDWORD FAR *) &uiColPrecision,
#endif
                                              &iColScaling, &fColNullable
                                            )
                          )
         )
      { displayError( hEnv, hDbc, hstmt, iRC, __LINE__ );
      }
      printf( "%s type=", szColName );
      switch( fColType )
      { case SQL_BLOB:               printf( "BLOB" );           break;
        case SQL_BLOB_LOCATOR:       printf( "BLOB_LOCATOR" );   break;
        case SQL_CHAR:               printf( "CHAR" );           break;
        case SQL_BINARY:             printf( "BINARY" );         break;
        case SQL_CLOB:               printf( "CLOB" );           break;
        case SQL_CLOB_LOCATOR:       printf( "CLOB_LOCATOR" );   break;
        case SQL_DATE:               printf( "DATE" );           break;
        case SQL_DBCLOB:             printf( "DBCLOB" );         break;
        case SQL_DBCLOB_LOCATOR:     printf( "DBCLOB_LOCATOR" ); break;
        case SQL_DECIMAL:            printf( "DECIMAL" );        break;
        case SQL_DOUBLE:             printf( "DOUBLE" );         break;
        case SQL_FLOAT:              printf( "FLOAT" );          break;
        case SQL_GRAPHIC:            printf( "GRAPHIC" );        break;
        case SQL_INTEGER:            printf( "INTEGER" );        break;
        case SQL_LONGVARCHAR:        printf( "LONGVARCHAR" );    break;
        case SQL_LONGVARBINARY:      printf( "LONGVARBINARY" );  break;
        case SQL_LONGVARGRAPHIC:     printf( "LONGVARGRAPHIC" ); break;
        case SQL_NUMERIC:            printf( "NUMERIC" );        break;
        case SQL_REAL:               printf( "REAL" );           break;
        case SQL_SMALLINT:           printf( "SMALLINT" );       break;
        case SQL_TIME:               printf( "TIME" );           break;
        case SQL_TIMESTAMP:          printf( "TIMESTAMP" );      break;
        case SQL_VARCHAR:            printf( "VARCHAR" );        break;
        case SQL_VARBINARY:          printf( "VARBINARY" );      break;
        case SQL_VARGRAPHIC:         printf( "VARGRAPHIC" );     break;
        default:                     printf( "unknown" );
      } /* switch */
      printf( " precision=%u scaling=%d nullable=%s\n",
              uiColPrecision, iColScaling,
              (SQL_NO_NULLS == fColNullable) ? "YES" : "NO"
            );
      /*
      ** create list with data entries
      */
      pData[ci] = NULL;
      if( NULL == (pData[ci]=malloc(uiColPrecision)) )
      { iRC = -1;
      }
      else
      { memset( pData[ci], 0, uiColPrecision );
        iRC = SQLBindCol( hstmt, ci, SQL_C_CHAR, (SQLPOINTER) pData[ci],
                          uiColPrecision, NULL
                        );
      }

      /*
      ** display all info available on column
      */
      ci2 = 0;
      while( NULL != pszColDesc[ci2] )
      { szBuffer[0] = '\0';
        fColDesc    = -1;
        iRC = SQLColAttributes( hstmt, ci, aColDesc[ci2], &szBuffer[0],
                                sizeof(szBuffer), (SQLSMALLINT *) &cbBuffer,
                                (SQLINTEGER *) &fColDesc );
        if( SQL_SUCCESS != iRC )
        { displayError( hEnv, hDbc, hstmt, iRC, __LINE__ );
        }
        else
        { printf( "    %30s ==> %5d '%s'\n",
                  pszColDesc[ci2], fColDesc, szBuffer );
        }
        ci2++;
      }
    }

    /*
    ** display results
    */
    printf( "\nresults:\n" );
    while( SQL_SUCCESS           == (iRC=SQLFetch(hstmt)) ||
           SQL_SUCCESS_WITH_INFO == iRC )
    { printf( "  --> " );
      for( ci = 1; ci <= iResColumns; ci++ )
      { printf( "'%s' ", (NULL == pData[ci]) ? "<ERROR>" : pData[ci] );
        fflush( stdout );
      } /* for */
      printf( " <--\n" );
    } /* while */

    /*
    ** display last return code
    */
    switch( iRC )
    { case SQL_SUCCESS:           printf( "SQL_SUCCESS?\n" );           break;
      case SQL_SUCCESS_WITH_INFO: printf( "SQL_SUCCESS_WITH_INFO?\n" ); break;
      case SQL_ERROR:             printf( "SQL_ERROR\n" );
                                  displayError( hEnv, hDbc, hstmt,
                                                iRC, __LINE__ );
                                  break;
      case SQL_INVALID_HANDLE:    printf( "SQL_INVALID_HANDLE\n" );     break;
      case SQL_NO_DATA_FOUND:     printf( "SQL_NO_DATA_FOUND\n" );      break;
      default:                    printf( "unknown iRC = %d\n", iRC );
    } /* switch */

    /*
    ** free allocated memory
    */
    for( ci = 1; ci <= iResColumns; ci++ )
    { free( pData[ci] );
      pData[ci] = NULL;
    } /* for */

  } /* if */

  /*
  ** free statement handle
  */
  iRC = SQLFreeStmt( hstmt, SQL_CLOSE );
  if( SQL_SUCCESS != iRC )
    displayError( hEnv, hDbc, hstmt, iRC, __LINE__ );

  /*
  ** return
  */
  return iRC;

} /* exitDB */



/*-----------------------------------------------------------------------------
 * displayError
 *-----------------------------------------------------------------------------
 * function: display an error message for a given error code
 * input:    SQLHENV hEnv
 *             DB environment handle
 *           SQLHDBC hDbc
 *             DB context handle
 *           SQLHSTMT hStmt
 *             statement handle
 *           int iRC
 *             error code
 *           int iLineNum
 *             line number, where error occurred
 * output:   <none>
 *-----------------------------------------------------------------------------
 */
void displayError( SQLHENV hEnv, SQLHDBC hDbc, SQLHSTMT hStmt,
                   int iRC, int iLineNum
                 )
{ /*---| variables |---*/
  SQLCHAR           szBuffer[ SQL_MAX_MESSAGE_LENGTH ];    /* msg. buffer    */
  SQLCHAR           szSqlState[ SQL_SQLSTATE_SIZE ];       /* statement buf. */
  SQLINTEGER        iSqlCode;                              /* return code    */
  SQLSMALLINT       iLength;                               /* return length  */

  /*---| program |---*/
  /*
  ** display native error code
  */
  /* fprintf( stderr, "\a-----------------------\n" ); */
  fprintf( stderr, "-----------------------\n" );
  fprintf( stderr, "SQL error              : %d\n", iRC );
  fprintf( stderr, "line number            : %d\n", iLineNum );

  /*
  ** display all error messages corresponding to this code
  */
  while( SQL_SUCCESS == SQLError( hEnv, hDbc, hStmt, szSqlState,
                                  &iSqlCode, szBuffer,
                                  SQL_MAX_MESSAGE_LENGTH - 1, &iLength
                                )
       )
  {
    fprintf( stderr, "SQL state              : %s\n", szSqlState );
    fprintf( stderr, "native error code      : %ld\n", iSqlCode );
    fprintf( stderr, "%s\n", szBuffer );
  } /* while */

  fprintf( stderr, "-----------------------\n" );
  /* fprintf( stderr, "\a-----------------------\n" ); */

} /* displayError */

/*===| end of file |=========================================================*/
