/**********************************************************************
* FILENAME : ListDBTables.c
*
* DESCRIPTION :
* Example illustrates use of SQLTables to show Catalogs, Schemas,
* and Tables
*
* ODBC USAGE :
* SQLGetInfo to get SQL_DATABASE_NAME e.g msdb
* SQL_DBMS_NAME e.g Microsoft SQL Server
* SQL_DBMS_VER e.g 12.00.2000
* SQL_USER_NAME e.g dbo
* SQLTables to get Catalogs
* SQLTables to get Schemas
* SQLTables to get Tables
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define NUMCOLS 5
#define BUFFERSIZE 1024
struct DataBinding {
SQLSMALLINT TargetType;
SQLPOINTER TargetValuePtr;
SQLINTEGER BufferLength;
SQLLEN StrLen_or_Ind;
};
int MySQLSuccess(SQLRETURN rc) {
return (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO);
}
void printCatalog(const struct DataBinding* catalogResult) {
// index 0 - Catalog e.g. master, msdb, etc
// index 1 - empty
// index 2 - empty
// index 3 - empty
if (catalogResult[0].StrLen_or_Ind != SQL_NULL_DATA)
printf("Catalog = %s\n", (char *)catalogResult[0].TargetValuePtr);
}
void printSchema(const struct DataBinding* schemaResult) {
// index 0 - empty
// index 1 - Schema Name e.g dbo, sys
// index 2 - empty
// index 3 - empty
if (schemaResult[1].StrLen_or_Ind != SQL_NULL_DATA)
printf("Schema = %s\n", (char *)schemaResult[1].TargetValuePtr);
}
void printTable(const struct DataBinding* tableResult) {
// index 0 - catalog e.q. msdb
// index 1 - schema e.g dbo
// index 2 - table name e.g. TestTBL1
// index 3 - type - e.g. TABLE
if (tableResult[2].StrLen_or_Ind != SQL_NULL_DATA)
printf("Table (%s) = %s\n", (char *)tableResult[1].TargetValuePtr,
(char *)tableResult[2].TargetValuePtr);
}
int main () {
struct DataBinding* catalogResult = (struct DataBinding*)
malloc( NUMCOLS * sizeof(struct DataBinding) );
struct DataBinding* schemaResult = (struct DataBinding*)
malloc( NUMCOLS * sizeof(struct DataBinding) );
struct DataBinding* tableResult = (struct DataBinding*)
malloc( NUMCOLS * sizeof(struct DataBinding) );
SQLCHAR* dbName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*BUFFERSIZE );
SQLCHAR* dbmsName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*BUFFERSIZE );
SQLCHAR* dbmsVerName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*BUFFERSIZE );
SQLCHAR* userName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*BUFFERSIZE );
// Declare and initialise the environment, connection, statement handles
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
SQLCHAR connStrbuffer[1024];
SQLSMALLINT connStrBufferLen, buffSize;
int 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,
(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);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
// Get database name and user name
retcode = SQLGetInfo(hdbc, SQL_DATABASE_NAME,
dbName, (SQLSMALLINT)1024,
(SQLSMALLINT *)&buffSize);
CHECK_ERROR(retcode, "SQLGetInfo(SQL_DATABASE_NAME)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLGetInfo(hdbc, SQL_DBMS_NAME,
dbmsName, (SQLSMALLINT)1024,
(SQLSMALLINT *)&buffSize);
CHECK_ERROR(retcode, "SQLGetInfo(SQL_DBMS_NAME)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLGetInfo(hdbc, SQL_DBMS_VER,
dbmsVerName, (SQLSMALLINT)1024,
(SQLSMALLINT *)&buffSize);
CHECK_ERROR(retcode, "SQLGetInfo(SQL_DBMS_VER)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLGetInfo(hdbc, SQL_USER_NAME,
userName, (SQLSMALLINT)1024,
(SQLSMALLINT *)&buffSize);
CHECK_ERROR(retcode, "SQLGetInfo(SQL_USER_NAME)",
hstmt, SQL_HANDLE_STMT);
printf ("\nSQL_DATABASE_NAME : %s\n", dbName);
printf ("SQL_DBMS_NAME : %s\n", dbmsName);
printf ("SQL_DBMS_VER : %s\n", dbmsVerName);
printf ("SQL_USER_NAME : %s\n\n", userName);
// Allocate memory for the binding
// Catalogues
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 );
}
// Schemas
for ( i = 0 ; i < NUMCOLS ; i++ ) {
schemaResult[i].TargetType = SQL_C_CHAR;
schemaResult[i].BufferLength = (BUFFERSIZE + 1);
schemaResult[i].TargetValuePtr =
malloc( sizeof(unsigned char)*schemaResult[i].BufferLength );
}
// Tables
for ( i = 0 ; i < NUMCOLS ; i++ ) {
tableResult[i].TargetType = SQL_C_CHAR;
tableResult[i].BufferLength = (BUFFERSIZE + 1);
tableResult[i].TargetValuePtr =
malloc( sizeof(unsigned char)*tableResult[i].BufferLength );
}
// Setup the binding for catalog
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));
// All catalogs query
printf( "A list Catalogs : \n" );
retcode = SQLTables( hstmt, (SQLCHAR*)SQL_ALL_CATALOGS,
SQL_NTS, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"",
SQL_NTS, (SQLCHAR*)"", SQL_NTS );
for ( retcode = SQLFetch(hstmt) ;
MySQLSuccess(retcode) ;
retcode = SQLFetch(hstmt) ) {
printCatalog( catalogResult );
}
// Set up the binding for schemas
for ( i = 0 ; i < NUMCOLS ; i++ )
retcode = SQLBindCol(hstmt, (SQLUSMALLINT)i + 1,
schemaResult[i].TargetType,
schemaResult[i].TargetValuePtr,
schemaResult[i].BufferLength,
&(schemaResult[i].StrLen_or_Ind));
printf( "A list of Schemas : \n" );
retcode = SQLTables( hstmt, (SQLCHAR*)"", SQL_NTS,
(SQLCHAR*)SQL_ALL_SCHEMAS, SQL_NTS, (SQLCHAR*)"",
SQL_NTS, (SQLCHAR*)"", SQL_NTS );
for ( retcode = SQLFetch(hstmt) ;
MySQLSuccess(retcode) ;
retcode = SQLFetch(hstmt) ) {
printSchema( schemaResult );
}
// Setup the binding for tables
for ( i = 0 ; i < NUMCOLS ; i++ )
retcode = SQLBindCol(hstmt, (SQLUSMALLINT)i + 1,
tableResult[i].TargetType,
tableResult[i].TargetValuePtr,
tableResult[i].BufferLength,
&(tableResult[i].StrLen_or_Ind));
// Ideally, we could loop around to get tables within schemas
// within catalogs, but uses msdb and dbo to illustrate the point...
printf( "A list of Tables : \n" );
retcode = SQLTables( hstmt, (SQLCHAR*)"msdb",
SQL_NTS, (SQLCHAR*)"dbo",
SQL_NTS, (SQLCHAR*)SQL_ALL_TABLE_TYPES,
SQL_NTS, (SQLCHAR*)"'TABLE'", SQL_NTS );
for ( retcode = SQLFetch(hstmt) ;
MySQLSuccess(retcode) ;
retcode = SQLFetch(hstmt) ) {
printTable( tableResult );
}
}
printf ("\nThe End.\n");
exit:
// Free memory
for ( i = 0 ; i < NUMCOLS ; i++ ) {
free (catalogResult[i].TargetValuePtr); // Catalogues
free (schemaResult[i].TargetValuePtr); // Schemas
free (tableResult[i].TargetValuePtr); // Tables
}
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
