ODBC SQLDescribeCol function

 

 

$>:cat Makefile

ODBC = $(YOUR_UNIXODBC_INSTALLED)/vendor/unixODBC/odbc32
CC = CC

### 32bit
CFLAGS = -mt -g -I$(ODBC)/include -D_POSIX_PTHREAD_SEMANTICS
LDFLAGS = -mt
LIBS = -L $(ODBC)/lib -R $(ODBC)/lib -lodbc -lrt

PROGS = sql
OBJS = sql.o

all:$(PROGS)

clean:
        rm -f $(PROGS) *.o

sql: $(OBJS)
        $(CC) -o $@ $(OBJS) $(LIBS) $(LDFLAGS)


sql.o : sql.C
        $(CC) $(CFLAGS) -c $^

 

 

 

$:>cat sql.C
#include <sql.h>
#include <sqlext.h>
#include <unistd.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>


void CheckReturnCode(SQLRETURN rc, SQLHENV henv,SQLHDBC hdbc, SQLHSTMT hstmt,char* msg, char *filename,int lineno);
const char * getTypename(int type);

int main(int argc, char ** argv )
{

    SQLRETURN rc = SQL_SUCCESS; /* General return code for the API */
    SQLHENV hEnv = SQL_NULL_HENV; /* Environment handle */
    SQLHDBC hDbc = SQL_NULL_HDBC; /* Connection handle */
    SQLHSTMT hStmt = SQL_NULL_HSTMT; /* Statement handle */

    SQLCHAR    v2[256];
    SQLLEN     v2size;

    if (argc < 2)
    {
        printf("Usage:  %s table/nExit./n", argv[0]);
        return 0;
    }
  
    rc = SQLAllocEnv( &hEnv );
    if (rc != SQL_SUCCESS ) printf("SQLAllocEnv failed./n");

    rc = SQLAllocConnect( hEnv, &hDbc );
    if (rc != SQL_SUCCESS ) printf("SQLAllocConnect failed./n");

    rc = SQLConnect( hDbc, (UCHAR *)"PostgreSQL64", SQL_NTS, /
                        (UCHAR *)"scncraft", SQL_NTS,(UCHAR *)"scncraft91", SQL_NTS );
    CheckReturnCode(rc, hEnv, hDbc, hStmt, "Unable to SQLConnect /n", __FILE__, __LINE__);

    rc = SQLAllocStmt( hDbc, &hStmt );
    if (rc != SQL_SUCCESS ) printf("SQLAllocStmt failed./n");


/* Your application code here */
/*********************************************************************************************/

    SQLCHAR             colName[129];

    SQLSMALLINT         scale;
    SQLSMALLINT         nullable;

    union
    {
        unsigned  int   size;
        long            sizeLong;
    }colValueSizeUnion;

    union
    {
        SQLSMALLINT     size;
        long            sizeLong;
    } colNameSizeUnion;

    SQLSMALLINT       colType;
    SQLSMALLINT       numCols;

    const char * prevsql = (char *)"select * from ";
    char * sql = (char *)malloc(strlen(prevsql) + strlen(argv[1]) + 1);
    strcpy(sql,prevsql);
    strcat(sql,argv[1]);

    rc = SQLPrepare(hStmt,(UCHAR*) sql, SQL_NTS);
    CheckReturnCode(rc, hEnv, hDbc, hStmt, "Unable to SQLConnect /n", __FILE__, __LINE__);
   
    rc = SQLNumResultCols(hStmt, &numCols);
    CheckReturnCode(rc, hEnv, hDbc, hStmt, "Unable to SQLNumResultCols/n", __FILE__, __LINE__);
   
    for (int i=1;i<=numCols;i++)
    {
        rc = SQLDescribeCol(hStmt, i, (SQLCHAR *)colName,
                sizeof(colName),
                (SQLSMALLINT*)&colNameSizeUnion.size,
                &colType,
                (SQLULEN *)&colValueSizeUnion.size,
                &scale,
                &nullable);
        CheckReturnCode(rc, hEnv, hDbc, hStmt, "Unable to SQLConnect /n", __FILE__, __LINE__);

        printf("column.type=[%d/%s],/tcolumn.size=[%d],[%d],/tcolumn.name=[%s]/n",
                colType, getTypename(colType), colValueSizeUnion.size, colValueSizeUnion.sizeLong,colName);
    }


    if (hStmt != SQL_NULL_HSTMT) {
        rc = SQLFreeStmt(hStmt, SQL_DROP);
    }

    if (hDbc != SQL_NULL_HDBC) {
        rc = SQLDisconnect(hDbc);
        rc = SQLFreeConnect(hDbc);
    }

    if (hEnv != SQL_NULL_HENV) {
        rc = SQLFreeEnv(hEnv);
    }

    return 0;
}


const char * getTypename(int type)
{
    switch (type)
    {
        case SQL_UNKNOWN_TYPE:  return "SQL_UNKNOWN_TYPE";
        case SQL_CHAR:          return "SQL_CHAR";
        case SQL_NUMERIC:       return "SQL_NUMERIC";
        case SQL_INTEGER:       return "SQL_INTEGER";
        case SQL_SMALLINT:      return "SQL_SMALLINT";
        case SQL_FLOAT:         return "SQL_FLOAT";
        case SQL_DOUBLE:        return "SQL_DOUBLE";
        case SQL_VARCHAR:       return "SQL_VARCHAR";
        default:                return "SQL_SORRY";
    }
}


void CheckReturnCode(SQLRETURN rc, SQLHENV henv,SQLHDBC hdbc, SQLHSTMT hstmt,char* msg, char *filename,int lineno)
{
     #define MSG_LNG 512
     SQLCHAR szSqlState[MSG_LNG]; /* SQL state string */
     SQLINTEGER pfNativeError; /* Native error code */
     SQLCHAR szErrorMsg[MSG_LNG]; /* Error msg text buffer pointer */
     SQLSMALLINT pcbErrorMsg; /* Error msg text Available bytes */
     SQLRETURN ret = SQL_SUCCESS;
     if (rc != SQL_SUCCESS && rc != SQL_NO_DATA_FOUND && rc != SQL_SUCCESS_WITH_INFO ) {
          if (rc != SQL_SUCCESS_WITH_INFO) { /* It's not just a warning */
               fprintf(stderr, "*** ERROR in %s, line %d: %s/n",filename, lineno, msg);
          }
          /*
          * Now see why the error/warning occurred
          */
          while (ret == SQL_SUCCESS ||ret == SQL_SUCCESS_WITH_INFO) {
               ret = SQLError(henv, hdbc, hstmt,szSqlState, &pfNativeError,szErrorMsg, MSG_LNG,&pcbErrorMsg);
               switch (ret) {
               case SQL_SUCCESS:
                    fprintf(stderr, "*** %s/n*** ODBC Error/Warning = %s, TimesTen Error/Warning = %d/n",szErrorMsg, szSqlState,pfNativeError);
                    break;
               case SQL_SUCCESS_WITH_INFO:
                    fprintf(stderr, "*** Call to SQLError failed with return code of SQL_SUCCESS_WITH_INFO./n *** Need to increase size of message buffer./n");
                    break;
               case SQL_INVALID_HANDLE:
                    fprintf(stderr, "*** Call to SQLError failed with return code of SQL_INVALID_HANDLE./n");
                    break;
               case SQL_ERROR:
                    fprintf(stderr, "*** Call to SQLError failed with return code of SQL_ERROR./n");
                    break;
               case SQL_NO_DATA_FOUND:
                    break;
               default:
                    break;
               } /* switch */
          } /* while */
     }
}