/**********************************************************************
* FILENAME :        ListDataTypes.c
*
* DESCRIPTION :
*       Example allows user to select a data source and displays the
*       information available on the data types associated with it.
*
* ODBC USAGE :
*       Calls selectDSN() to ask user to select Data Source
*       Connects to Data Source selected
*       Uses SQLGetTypeInfo() with SQL_ALL_TYPES
*       Binds data for 3 columns for name, type and size
*       Uses SQLFetch() to return data types result set
*       Displays results in tabular form
*/

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "util.c"

/*
Possible Data Types

//#define SQL_UNKNOWN_TYPE        0
//#define SQL_CHAR                1
//#define SQL_NUMERIC             2
//#define SQL_DECIMAL             3
//#define SQL_INTEGER             4
//#define SQL_SMALLINT            5
//#define SQL_FLOAT               6
//#define SQL_REAL                7
//#define SQL_DOUBLE              8
//#define SQL_DATETIME            9      ODBCVER >= 0x0300
//#define SQL_DATE                9
//#define SQL_INTERVAL            10     ODBCVER >= 0x0300
//#define SQL_TIME                10
//#define SQL_TIMESTAMP           11
//#define SQL_VARCHAR             12

//#define SQL_TYPE_DATE           91     ODBCVER >= 0x0300
//#define SQL_TYPE_TIME           92     ODBCVER >= 0x0300
//#define SQL_TYPE_TIMESTAMP      93     ODBCVER >= 0x0300

//#define SQL_LONGVARCHAR         (-1)
//#define SQL_BINARY              (-2)
//#define SQL_VARBINARY           (-3)
//#define SQL_LONGVARBINARY       (-4)
//#define SQL_BIGINT              (-5)
//#define SQL_TINYINT             (-6)
//#define SQL_BIT                 (-7)
//#define SQL_WCHAR               (-8)
//#define SQL_WVARCHAR            (-9)
//#define SQL_WLONGVARCHAR        (-10)
//#define SQL_GUID		  (-11)  ODBCVER >= 0x0350
//#define SQL_SS_VARIANT          (-150) SQL Server 2008
//#define SQL_SS_UDT              (-151) SQL Server 2008
//#define SQL_SS_XML              (-152) SQL Server 2008
//#define SQL_SS_TABLE            (-153) SQL Server 2008
//#define SQL_SS_TIME2            (-154) SQL Server 2008
//#define SQL_SS_TIMESTAMPOFFSET  (-155) SQL Server 2008

*/
#define DTSIZE 35

// SQL Data Type Structure
typedef struct {
    char name [32];     // SQL data type name
    int  value;         // SQL data type numeric value
} dataTypes;

// Array of SQL Data Types
dataTypes dtList[DTSIZE] = {
    "SQL_UNKNOWN_TYPE",0,
    "SQL_CHAR",1,
    "SQL_NUMERIC",2,
    "SQL_DECIMAL",3,
    "SQL_INTEGER",4,
    "SQL_SMALLINT",5,
    "SQL_FLOAT",6,
    "SQL_REAL",7,
    "SQL_DOUBLE",8,
    "SQL_DATETIME",9,
    "SQL_DATE",9,
    "SQL_INTERVAL",10,
    "SQL_TIME",10,
    "SQL_TIMESTAMP",11,
    "SQL_VARCHAR",12,
    "SQL_TYPE_DATE",91,
    "SQL_TYPE_TIME",92,
    "SQL_TYPE_TIMESTAMP",93,
    "SQL_LONGVARCHAR",-1,
    "SQL_BINARY",-2,
    "SQL_VARBINARY",-3,
    "SQL_LONGVARBINARY",-4,
    "SQL_BIGINT",-5,
    "SQL_TINYINT",-6,
    "SQL_BIT",-7,
    "SQL_WCHAR",-8,
    "SQL_WVARCHAR",-9,
    "SQL_WLONGVARCHAR",-10,
    "SQL_GUID",-11,
    "SQL_SS_VARIANT",-150,
    "SQL_SS_UDT",-151,
    "SQL_SS_XML",-152,
    "SQL_SS_TABLE",-153,
    "SQL_SS_TIME2",-154,
    "SQL_SS_TIMESTAMPOFFSET",-155
};

// Returns name of data type, unknown if not found
char * SQLType (int dataType) {

    int i;

    for (i=0;i<DTSIZE;i++) {
        if (dataType==dtList[i].value)
        break;
    }

    if (i<DTSIZE) {
        return (dtList[i].name);
    } else {
        return (dtList[0].name);
    }
}

int main () {

    // For formatting output
    char padding[] = "                         ";
    int max=strlen(padding);

    char * val;

    SQLCHAR typeName[128];
    SQLSMALLINT dataType;
    SQLINTEGER columnSize;

    SQLLEN typeName_ind, dataType_ind, columnSize_ind;

    SQLHENV  henv  = SQL_NULL_HENV;   	// Environment
    SQLHDBC  hdbc  = SQL_NULL_HDBC;   	// Connection handle
    SQLHSTMT hstmt = SQL_NULL_HSTMT;  	// Statement handle
    SQLRETURN retcode;			// Return status

    char dsn[MAXDSNLEN];

    // Create environment handle
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    CHECK_ERROR(retcode, "SQLAllocHandle(ENV)", henv, SQL_HANDLE_ENV);

    // Set ODBC 3 behaviour
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                                        (SQLCHAR *)(void*)SQL_OV_ODBC3, -1);
    CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
                henv, SQL_HANDLE_ENV);

    // Which data source?
    retcode = selectDSN (henv, dsn, "Select DSN : ");
    if (retcode != SQL_SUCCESS) {
        printf ("DSN not selected, exiting.");
        goto exit;
    }

    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    CHECK_ERROR(retcode, "SQLAllocHandle(DBC)",
                hdbc, SQL_HANDLE_DBC);

    retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
    CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
                hdbc, SQL_HANDLE_DBC);

    retcode = SQLConnect(hdbc, (SQLCHAR*) dsn, SQL_NTS,
                                            (SQLCHAR*) NULL, 0, NULL, 0);
    CHECK_ERROR(retcode, "SQLConnect",
                hdbc, SQL_HANDLE_DBC);

    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    CHECK_ERROR(retcode, "SQLAllocHandle(STMT)",
                hstmt, SQL_HANDLE_STMT);

    retcode = SQLGetTypeInfo(hstmt, SQL_ALL_TYPES);
    CHECK_ERROR(retcode, "SQLGetTypeInfo",
                hstmt, SQL_HANDLE_STMT);

    retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR,
                         (SQLPOINTER) typeName,
                         (SQLLEN) sizeof(typeName), &typeName_ind);
    CHECK_ERROR(retcode, "SQLBindCol(1)", hstmt, SQL_HANDLE_STMT);

    retcode = SQLBindCol(hstmt, 2, SQL_C_SHORT,
                         (SQLPOINTER) &dataType,
                         (SQLLEN) sizeof(dataType), &dataType_ind);
    CHECK_ERROR(retcode, "SQLBindCol(2)", hstmt, SQL_HANDLE_STMT);

    retcode = SQLBindCol(hstmt, 3, SQL_C_ULONG,
                         (SQLPOINTER) &columnSize,
                         (SQLLEN) sizeof(columnSize), &columnSize_ind);
    CHECK_ERROR(retcode, "SQLBindCol(2)", hstmt, SQL_HANDLE_STMT);

    printf("SQL Data Type             Type Name                 Value"
                                                                "Max Size\n");
    printf("------------------------- ------------------------- -------"
                                                                "--------\n");

    // Fetch each row, and display
    while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS) {
        padOut(SQLType (dataType), padding, max);
        printf("%s,%s", SQLType (dataType), padding);

        padOut(typeName, padding, max);
        printf("%s,%s", (char *) typeName, padding);

        val = itoa(dataType);
        padOut(val, padding, max-18);
        printf("%i,%s", (int) dataType, padding);

        padOut("", padding, max);
        printf ("%i\n", (int) columnSize);
    }

exit:

    printf ("\nComplete.\n");

    // Free handles
    // Statement
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    // Connection
    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }

    // Environment
    if (henv != SQL_NULL_HENV)
        SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return 0;
}

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.