/**********************************************************************
* FILENAME : ListSpecialColumns.c
*
* DESCRIPTION :
* Example finds all tables starting with 'TestTBL' and for each
* one outputs the optimal set of columns that uniquely identifies
* a row in the table.
*
* NOTE: Doesn't return information where table records are uniquely
* identified by identity fields
*
* ODBC USAGE :
* SQLGetInfo() to get the DB and USER names
* SQLBindCol and SQLTables to obtain list of tables like 'TestTBL'
* For each table
* SQLSpecialColumns () to get column info
* SQLBindCol() to bind the 8 columns of the result set (those being
* Catalog, Schema, Table, Column, Grantor, Grantee and Is Grantable)
* SQLFetch() to get the values
* Display results
*/
#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
//Number of columns in SQLTables
#define NUMCOLS 5
// Declare buffers for result set data
SQLSMALLINT Scope;
SQLCHAR strColumnName[STR_LEN];
SQLSMALLINT DataType;
SQLCHAR strTypeName[STR_LEN];
SQLLEN ColumnSize;
SQLLEN BufferLength;
SQLSMALLINT DecimalDigits;
SQLSMALLINT PseudoColumn;
SQLLEN lenColumnName, lenTypeName;
SQLLEN lenScope, lenDataType, lenColumnSize;
SQLLEN lenBufferLength, lenDecimalDigits, lenPseudoColumn;
struct DataBinding {
SQLSMALLINT TargetType;
SQLPOINTER TargetValuePtr;
SQLINTEGER BufferLength;
SQLLEN StrLen_or_Ind;
};
//
// Function to free handles
//
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);
}
//
//gets list of tables based on iTableStrPtr
//
int getTheseTables (char *pTableName, SQLCHAR *tableNames[]) {
int bufferSize = 1024, i, j, count = 1;
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 handle
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, "SQLBindCol(SQLUSMALLINT)",
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++] );
}
}
for ( j = 0 ; j < NUMCOLS ; j++ ) {
free (catalogResult[j].TargetValuePtr);
}
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;
char padding[] = " ";
int header;
SQLCHAR *tableNames[MAX_TABLES];
SQLINTEGER tableCount, i, max=strlen(padding);
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++) {
// allocate statement handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
printf ("\nTable a : %s", tableNames[i]);
// Permutations:-
// Param 2 Identifier Type - SQL_BEST_ROWID or SQL_ROWVER
// Param 9 Scope - SQL_SCOPE_CURROW,
// SQL_SCOPE_TRANSACTION or
// SQL_SCOPE_SESSION
// Param 10 Nullable - SQL_NO_NULLS or SQL_NULLABLE
retcode = SQLSpecialColumns (hstmt,
SQL_BEST_ROWID,
NULL, 0,
NULL, 0,
(SQLCHAR *)tableNames[i], SQL_NTS,
SQL_SCOPE_CURROW,
SQL_NULLABLE);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
// Bind columns in result set to buffers
SQLBindCol(hstmt, 1, SQL_C_SHORT, &Scope,
sizeof(Scope), &lenScope);
SQLBindCol(hstmt, 2, SQL_C_CHAR, strColumnName,
sizeof(strColumnName), &lenColumnName);
SQLBindCol(hstmt, 3, SQL_C_SHORT, &DataType,
sizeof(DataType), &lenDataType);
SQLBindCol(hstmt, 4, SQL_C_CHAR,
strTypeName, STR_LEN, &lenTypeName);
SQLBindCol(hstmt, 5, SQL_C_SHORT, &ColumnSize,
sizeof(ColumnSize), &lenColumnSize);
SQLBindCol(hstmt, 6, SQL_C_SHORT, &BufferLength,
sizeof(BufferLength), &lenBufferLength);
SQLBindCol(hstmt, 7, SQL_C_SHORT, &DecimalDigits,
sizeof(DecimalDigits), &lenDecimalDigits);
SQLBindCol(hstmt, 8, SQL_C_SHORT, &PseudoColumn,
sizeof(PseudoColumn), &lenPseudoColumn);
max=strlen(padding);
header=0;
while (SQL_SUCCESS == retcode) {
retcode = SQLFetch(hstmt);
CHECK_ERROR(retcode, "SQLFetch(SpecialColumns)",
hstmt, SQL_HANDLE_STMT);
if (header++==0) {
printf ("\n%s\n",tableNames[i]);
}
printf ("\nScope : %i\n", (int) Scope);
printf ("Column : %s\n", strColumnName);
printf ("Data Type : %i\n", (int) DataType);
printf ("Type Name : %s\n", strTypeName);
printf ("Col Size : %i\n", (int) ColumnSize);
printf ("Buff Len : %i\n", (int) BufferLength);
printf ("Digits : %i\n", (int) DecimalDigits);
printf ("Pseudo Col : %i\n", (int) PseudoColumn);
if (retcode==SQL_NO_DATA && header==1) {
printf ("(NO DATA)\n");
}
}
} else {
CHECK_ERROR(retcode, "SQLSpecialColumns()",
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;
}
Further information
