/**********************************************************************
* FILENAME : ListTableColumns.c
*
* DESCRIPTION :
* Example finds tables starting with 'TestTBL' and for each one
* outputs the column names, size and data type.
*
* ODBC USAGE :
* SQLGetInfo - with SQL_DATABASE_NAME info type - to get name
* of the current database in use. e.g msdb
* SQLGetInfo - with SQL_USER_NAME info type - to get the name
* used in a particular database. e.g. dbo
* Creates a catalogue table with 5 elements table details, namely
* TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE and
* REMARKS (see MSDN SQLTables).
* SQLBindCol - to bind the columns from the catalogue table before
* calling SQLTables
* SQLTables - with database, user and TABLE to initiate retrieval
* of tables from msdb/dbo
* SQLFetch - loops until all all tables returned, saviing ones
* starting with 'TestTBL' for later
* SQLColumns - called for each table saves, displaying Column Name,
* Column Size and Data Type
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define STR_LEN 128 + 1
#define REM_LEN 254 + 1
// Arbitary sizes for number of tables and
#define MAX_TABLES 100
#define BUFFERSIZE 1024
// Declare buffers for result set data
SQLCHAR strSchema[STR_LEN];
SQLCHAR strCatalog[STR_LEN];
SQLCHAR strColumnName[STR_LEN];
SQLCHAR strTableName[STR_LEN];
SQLCHAR strTypeName[STR_LEN];
SQLCHAR strRemarks[REM_LEN];
SQLCHAR strColumnDefault[STR_LEN];
SQLCHAR strIsNullable[STR_LEN];
SQLINTEGER ColumnSize;
SQLINTEGER BufferLength;
SQLINTEGER CharOctetLength;
SQLINTEGER OrdinalPosition;
SQLSMALLINT DataType;
SQLSMALLINT DecimalDigits;
SQLSMALLINT NumPrecRadix;
SQLSMALLINT Nullable;
SQLSMALLINT SQLDataType;
SQLSMALLINT DatetimeSubtypeCode;
SQLHSTMT hstmt = NULL;
// Declare buffers for bytes available to return
SQLLEN lenCatalog;
SQLLEN lenSchema;
SQLLEN lenTableName;
SQLLEN lenColumnName;
SQLLEN lenDataType;
SQLLEN lenTypeName;
SQLLEN lenColumnSize;
SQLLEN lenBufferLength;
SQLLEN lenDecimalDigits;
SQLLEN lenNumPrecRadix;
SQLLEN lenNullable;
SQLLEN lenRemarks;
SQLLEN lenColumnDefault;
SQLLEN lenSQLDataType;
SQLLEN lenDatetimeSubtypeCode;
SQLLEN lenCharOctetLength;
SQLLEN lenOrdinalPosition;
SQLLEN lenIsNullable;
struct DataBinding {
SQLSMALLINT TargetType;
SQLPOINTER TargetValuePtr;
SQLINTEGER BufferLength;
SQLLEN StrLen_or_Ind;
};
//
// Retrieves list of tables based on iTableStrPtr
// Example is self contained, using own handles
//
int getTheseTables (char *iTableName, SQLCHAR *tableNames[]) {
int i, j, count = 1, numCols = 5;
SQLCHAR * dbName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*BUFFERSIZE );
SQLCHAR * userName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*BUFFERSIZE );
SQLCHAR connStrbuffer[1024];
SQLSMALLINT connStrBufferLen, bufferLen;
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
struct DataBinding* catalogResult = (struct DataBinding*)
malloc( numCols * sizeof(struct DataBinding) );
SQLCHAR* selectAllQuery = (SQLCHAR *)
malloc( sizeof(SQLCHAR) * BUFFERSIZE );
// Create Environment Handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Set ODBC version
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);
// Create Connection Handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
// Set Login Timeout
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
// Connect to data source
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)",
hdbc, SQL_HANDLE_DBC);
// Allocate Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Display the database information
retcode = SQLGetInfo(hdbc, SQL_DATABASE_NAME, dbName,
(SQLSMALLINT)BUFFERSIZE,
(SQLSMALLINT *)&bufferLen);
CHECK_ERROR(retcode, "SQLGetInfo(SQL_DATABASE_NAME)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLGetInfo(hdbc, SQL_USER_NAME, userName,
(SQLSMALLINT)BUFFERSIZE, &bufferLen);
CHECK_ERROR(retcode, "SQLGetInfo(SQL_USER_NAME)",
hdbc, SQL_HANDLE_DBC);
printf ("Current DB Name : %s\n", dbName);
printf ("Current User Name : %s\n", userName);
// Buffers for table names results
for ( i = 0 ; i < numCols ; i++ ) {
catalogResult[i].TargetType = SQL_C_CHAR;
catalogResult[i].BufferLength = (BUFFERSIZE + 1);
catalogResult[i].TargetValuePtr =
malloc( sizeof(unsigned char)*catalogResult[i].BufferLength );
}
// Bind results set columns to table names
for ( i = 0 ; i < numCols ; i++ ) {
retcode = SQLBindCol(hstmt, (SQLUSMALLINT)i+1,
catalogResult[i].TargetType,
catalogResult[i].TargetValuePtr,
catalogResult[i].BufferLength,
&(catalogResult[i].StrLen_or_Ind));
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
}
retcode = SQLTables( hstmt, dbName, SQL_NTS, userName, SQL_NTS, "%",
SQL_NTS, "TABLE", SQL_NTS );
CHECK_ERROR(retcode, "SQLTables()", hstmt, SQL_HANDLE_STMT);
// Create array of my tables starting with 'TestTBL'
i=0;
for ( retcode = SQLFetch(hstmt) ;
retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO ;
retcode = SQLFetch(hstmt), ++count ) {
if ( strstr (catalogResult[2].TargetValuePtr, "TestTBL") != 0 ) {
tableNames[i]=(char *)
malloc((strlen(catalogResult[2].TargetValuePtr)+1)*sizeof(char));
strcpy (tableNames[i], catalogResult[2].TargetValuePtr);
printf( " Found Table %s\n", tableNames[i++] );
}
}
exit:
if (hstmt!=SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc!=SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (henv!=SQL_NULL_HENV) SQLFreeHandle(SQL_HANDLE_ENV, henv);
return i;
}
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
SQLCHAR *tableNames[MAX_TABLES];
SQLINTEGER tableCount, i;
// Create Environment Handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Set ODBC Version
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
// Create Connection Handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
// Set Login Timeout
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
// Connect to data source
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)",
hdbc, SQL_HANDLE_DBC);
// Create Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Get list of tables and columns in each table
tableCount=getTheseTables ("TestTBL", tableNames);
for (i=0; i<tableCount; i++) {
printf ("\nTable : %s\n", tableNames[i]);
retcode = SQLColumns(hstmt, NULL, 0, NULL, 0,
(SQLCHAR*)tableNames[i], SQL_NTS, NULL, 0);
CHECK_ERROR(retcode, "SQLColumns(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Bind columns in result set to buffers
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
SQLBindCol(hstmt, 1, SQL_C_CHAR, strCatalog,
STR_LEN, &lenCatalog);
SQLBindCol(hstmt, 2, SQL_C_CHAR, strSchema,
STR_LEN, &lenSchema);
SQLBindCol(hstmt, 3, SQL_C_CHAR, strTableName,
STR_LEN,&lenTableName);
SQLBindCol(hstmt, 4, SQL_C_CHAR, strColumnName,
STR_LEN, &lenColumnName);
SQLBindCol(hstmt, 5, SQL_C_SSHORT, &DataType,
0, &lenDataType);
SQLBindCol(hstmt, 6, SQL_C_CHAR, strTypeName,
STR_LEN, &lenTypeName);
SQLBindCol(hstmt, 7, SQL_C_SLONG, &ColumnSize,
0, &lenColumnSize);
SQLBindCol(hstmt, 8, SQL_C_SLONG, &BufferLength,
0, &lenBufferLength);
SQLBindCol(hstmt, 9, SQL_C_SSHORT, &DecimalDigits,
0, &lenDecimalDigits);
SQLBindCol(hstmt, 10, SQL_C_SSHORT, &NumPrecRadix,
0, &lenNumPrecRadix);
SQLBindCol(hstmt, 11, SQL_C_SSHORT, &Nullable,
0, &lenNullable);
SQLBindCol(hstmt, 12, SQL_C_CHAR, strRemarks,
REM_LEN, &lenRemarks);
SQLBindCol(hstmt, 13, SQL_C_CHAR, strColumnDefault,
STR_LEN, &lenColumnDefault);
SQLBindCol(hstmt, 14, SQL_C_SSHORT, &SQLDataType,
0, &lenSQLDataType);
SQLBindCol(hstmt, 15, SQL_C_SSHORT, &DatetimeSubtypeCode,
0, &lenDatetimeSubtypeCode);
SQLBindCol(hstmt, 16, SQL_C_SLONG, &CharOctetLength,
0, &lenCharOctetLength);
SQLBindCol(hstmt, 17, SQL_C_SLONG, &OrdinalPosition,
0, &lenOrdinalPosition);
SQLBindCol(hstmt, 18, SQL_C_CHAR, strIsNullable,
STR_LEN, &lenIsNullable);
// retrieve column data
while (SQL_SUCCESS == retcode) {
retcode = SQLFetch(hstmt);
CHECK_ERROR(retcode, "SQLFetch(SQLColumns)",
hstmt, SQL_HANDLE_STMT);
// Display column name, size and type
printf (" Column Name : %s, ", strColumnName);
printf ("Column Size : %i, ", ColumnSize);
printf ("Data Type : %i\n", SQLDataType);
}
}
}
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
