/**********************************************************************
* FILENAME : ListTables.c
*
* DESCRIPTION :
* Simple example to list the tables available on a datasource
*
* ODBC USAGE :
* SQLTables - to request TABLE names
* SQLNumResultCols - to get number of columns in the rowset
* For each table
* SQLFetch - fetches the next rowset
* SQLGetData - for each column in the rowset display
* - Catalog, Schema, Table, Type, Remarks in
* tabular form
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
int main () {
// For formatting output
char padding[] = " ";
int max=strlen(padding);
SQLHENV henv = SQL_NULL_HENV; // Environment handle
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT;// Statement handle
SQLRETURN retcode; // Return status
SQLSMALLINT columns; // Number of columns in result-set
// Allocate an environment handle
retcode=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(ENV)", henv, SQL_HANDLE_ENV);
// Set ODBC version
retcode=SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(void *) SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
// Allocate a connection handle
retcode=SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
// DSN
retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;",
SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
CHECK_ERROR(retcode, "SQLDriverConnect(DATASOURCE)",
hdbc, SQL_HANDLE_DBC);
// Allocate a statement handle
retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Retrieve a list of tables
retcode=SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
CHECK_ERROR(retcode, "SQLTables()", hstmt, SQL_HANDLE_STMT);
// How many columns are there
retcode=SQLNumResultCols(hstmt, &columns);
CHECK_ERROR(retcode, "SQLNumResultCols()",
hstmt, SQL_HANDLE_STMT);
printf ("\nCatalog Schema Table ");
printf (" Type Remarks");
printf ("\n------- ------- ------ ");
printf (" ---- -------\n");
/* Loop through the rows in the result-set */
while (SQL_SUCCEEDED(retcode = SQLFetch(hstmt))) {
SQLUSMALLINT i;
// Loop through the columns
printf ("\n");
for (i = 1; i <= columns; i++) {
SQLLEN indicator;
SQLCHAR buf[255];
// Retrieve column data as a string
retcode = SQLGetData(hstmt, i, SQL_C_CHAR,
buf, sizeof(buf), &indicator);
if (SQL_SUCCEEDED(retcode)) {
// Handle null columns
if (indicator == SQL_NULL_DATA) strcpy (buf, "NULL");
// Column 3 is the table name
if (i==3)
padOut (buf, padding, max);
else
padOut (buf, padding, 8);
printf("%s%s", buf, padding);
}
}
}
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
