/**********************************************************************
* FILENAME : ListStatistics.c
*
* DESCRIPTION :
* Example finds tables starting with 'TestTBL' and for each one
* outputs table statistics.
*
* 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 IndexName (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
* SQLStatistics - called for each table which returns two types of
* information:
*
* Statistics information for the table (if it is available):
* when the TYPE column of the result set described below is set
* to SQL_TABLE_STAT, we get the number of rows in the table and
* the number of pages used to store the table. When the TYPE column
* of the result set indicates an index, we get the number of unique
* values in the index, and the number of pages used to store the
* indexes.
*
* Information about each index, where each index column is represented
* by one row of the result set. The result set columns are given
* in Columns returned by SQLStatistics in the order shown; the
* rows in the result set are ordered by NON_UNIQUE, TYPE,
* INDEX_QUALIFIER, INDEX_NAME and KEY_SEQ.
*/
#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
// Declare buffers for result set data
SQLCHAR strCatalog[STR_LEN];
SQLCHAR strSchema[STR_LEN];
SQLCHAR strTableName[STR_LEN];
SQLCHAR strIndexQualifier[STR_LEN];
SQLCHAR strIndexName[REM_LEN];
SQLCHAR strColumnName[STR_LEN];
SQLCHAR strAscDesc[STR_LEN];
SQLCHAR strFilterCond[STR_LEN];
SQLINTEGER Cardinality;
SQLINTEGER Pages;
SQLSMALLINT NonUnique;
SQLSMALLINT Type;
SQLSMALLINT OrdinalPosn;
SQLHSTMT hstmt = NULL;
// Declare buffers for bytes available to return
SQLLEN lenCatalog;
SQLLEN lenSchema;
SQLLEN lenTableName;
SQLLEN lenIndexQualifier;
SQLLEN lenIndexName;
SQLLEN lenColumnName;
SQLLEN lenAscDesc;
SQLLEN lenFilterCond;
SQLLEN lenCardinality;
SQLLEN lenPages;
SQLLEN lenNonUnique;
SQLLEN lenType;
SQLLEN lenOrdinalPosn;
struct DataBinding {
SQLSMALLINT TargetType;
SQLPOINTER TargetValuePtr;
SQLINTEGER BufferLength;
SQLLEN StrLen_or_Ind;
};
void Cleanup(SQLHSTMT henv, SQLHSTMT hdbc, SQLHSTMT hstmt) {
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;
}
// Gets list of tables based on iTableStrPtr
int getTheseTables (char *pTableName, SQLCHAR *tableNames[]) {
int bufferSize = 1024, 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 );
// Connect to database
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
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);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_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*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)",
hdbc, SQL_HANDLE_DBC);
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);
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 );
}
// Set up the binding.
for ( i = 0 ; i < numCols ; i++ ) {
//printf ("Binding Column %i\n", i+1);
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_ENV)",
hstmt, SQL_HANDLE_STMT);
}
retcode = SQLTables( hstmt, dbName, SQL_NTS, userName, SQL_NTS, "%",
SQL_NTS, "TABLE", SQL_NTS );
CHECK_ERROR(retcode, "SQLTables(dbName)", 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, pTableName) != 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:
Cleanup(henv, hdbc, hstmt);
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;
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE",
SQL_NTS, (SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)",
hdbc, SQL_HANDLE_DBC);
tableCount=getTheseTables ("TestTBL", tableNames);
for (i=0; i<tableCount; i++) {
printf ("\nTable : %s\n", tableNames[i]);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLStatistics(hstmt, NULL, 0, NULL, 0,
(SQLCHAR*)tableNames[i], SQL_NTS,
SQL_INDEX_ALL, SQL_QUICK);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
// Clean buffers
memset (strCatalog, ' ', STR_LEN);
memset (strSchema, ' ', STR_LEN);
memset (strTableName, ' ', STR_LEN);
NonUnique=0;
memset (strIndexQualifier, ' ', STR_LEN);
memset (strIndexName, ' ', STR_LEN);
Type=0;
OrdinalPosn=0;
memset (strColumnName, ' ', STR_LEN);
memset (strAscDesc, ' ', STR_LEN);
Cardinality=0;
Pages=0;
memset (strFilterCond, ' ', STR_LEN);
// Bind columns in result set to buffers
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_SSHORT,
&NonUnique, 0, &lenNonUnique);
SQLBindCol(hstmt, 5, SQL_C_CHAR,
strIndexQualifier, STR_LEN, &lenIndexQualifier);
SQLBindCol(hstmt, 6, SQL_C_CHAR,
strIndexName, REM_LEN, &lenIndexName);
SQLBindCol(hstmt, 7, SQL_C_SSHORT,
&Type, 0, &lenType);
SQLBindCol(hstmt, 8, SQL_C_SSHORT,
&OrdinalPosn, 0, &lenOrdinalPosn);
SQLBindCol(hstmt, 9, SQL_C_CHAR,
strColumnName, STR_LEN, &lenColumnName);
SQLBindCol(hstmt, 10, SQL_C_CHAR,
strAscDesc, STR_LEN, &lenAscDesc);
SQLBindCol(hstmt, 11, SQL_C_SLONG,
&Cardinality, 0, &lenCardinality);
SQLBindCol(hstmt, 12, SQL_C_SLONG,
&Pages, 0, &lenPages);
SQLBindCol(hstmt, 13, SQL_C_CHAR,
strFilterCond, STR_LEN, &lenFilterCond);
while (retcode == SQL_SUCCESS) {
retcode = SQLFetch(hstmt);
CHECK_ERROR(retcode, "SQLFetch(SQLStatistics)",
hstmt, SQL_HANDLE_STMT);
printf ("\nCatalog %s\n", rtrim(strCatalog, ' '));
printf ("Schema %s\n", rtrim(strSchema, ' '));
printf ("TableName %s\n", rtrim(strTableName, ' '));
printf ("NonUnique %i\n", NonUnique);
printf ("IndexQualifier %s\n",rtrim(strIndexQualifier, ' '));
printf ("IndexName %s\n", rtrim(strIndexName, ' '));
printf ("Type %i\n", Type);
printf ("OrdinalPosn %i\n", OrdinalPosn);
printf ("ColumnName %s\n", rtrim(strColumnName, ' '));
printf ("AscDesc %s\n", rtrim(strAscDesc, ' '));
printf ("Cardinality %i\n", Cardinality);
printf ("Pages %i\n", Pages);
printf ("FilterCond %s\n", rtrim(strFilterCond, ' '));
}
} else {
CHECK_ERROR(retcode, "SQLStatistics()", hstmt, SQL_HANDLE_STMT);
}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
exit:
printf ("\nComplete.\n");
// Free statement, connection and environment handle
Cleanup(henv, hdbc, hstmt);
return 0;
}
See Also
