/**********************************************************************
* 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;
}
Further information
