Site navigation
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
/**********************************************************************
* FILENAME : ColumnAttributes.c
*
* DESCRIPTION :
* Example extends ListColumns.c example to get list of tables
* starting with 'TestTBL' but then uses the column details (from
* SQLColAttribute) to form a SELECT statement to read the data
*
* ODBC USAGE :
* Gets list of tables in same manner as SQLColumns example
* For each table name
* SQLFreeStmt - to close cursor used in statement to retrieve
* table names
* SQLExecDirect - with SELECT * FROM <tablename>
* SQLNumResultCols - to get number of columns in
* <tablename>
* SQLColAttribute - for each column uses SQL_DESC_NAME to get
* column name/label
* Allocate memory for column retrieval
* SQLBindCol - to bind memory to columns
* SQLFetch - to retrieve full records from table
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define BUFFERSIZE 1024
#define NUMCOLS 5
struct DataBinding {
SQLSMALLINT TargetType;
SQLPOINTER TargetValuePtr;
SQLINTEGER BufferLength;
SQLLEN StrLen_or_Ind;
};
SQLRETURN printStatementResult(SQLHSTMT hstmt) {
int i;
SQLRETURN retcode = SQL_SUCCESS;
SQLSMALLINT numColumns = 0, bufferLenUsed;
SQLPOINTER* columnLabels = NULL;
struct DataBinding* columnData = NULL;
retcode = SQLNumResultCols(hstmt, &numColumns);
CHECK_ERROR(retcode, "SQLNumResultCols()",
hstmt, SQL_HANDLE_STMT);
printf ("DataBinding Size : %i\n",
(int)(numColumns * sizeof(struct DataBinding)));
columnData = (struct DataBinding*)
malloc ( numColumns * sizeof(struct DataBinding) );
columnLabels = (SQLPOINTER *)malloc( numColumns * sizeof(SQLPOINTER*) );
for ( i = 0 ; i < numColumns ; i++ ) {
columnData[i].TargetValuePtr = NULL;
columnLabels[i] = NULL;
}
printf( "No of columns : %i\n", numColumns );
for ( i = 0 ; i < numColumns ; i++ ) {
columnLabels[i] = (SQLPOINTER)malloc( BUFFERSIZE*sizeof(char) );
// Get Field names from Table
retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)i + 1, SQL_DESC_NAME,
columnLabels[i], (SQLSMALLINT)BUFFERSIZE,
&bufferLenUsed, NULL);
CHECK_ERROR(retcode, "SQLColAttribute()",
hstmt, SQL_HANDLE_STMT);
printf( "Column %d: %s\n", i+1, (SQLCHAR*)columnLabels[i] );
}
// Allocate memory for the binding
for ( i = 0 ; i < numColumns ; i++ ) {
columnData[i].TargetType = SQL_C_CHAR;
columnData[i].BufferLength = (BUFFERSIZE+1);
columnData[i].TargetValuePtr =
malloc( sizeof(unsigned char)*columnData[i].BufferLength );
}
// Set up the binding
for ( i = 0 ; i < numColumns ; i++ ) {
printf ("Binding Column %i\n", i+1);
printf (" TargetType : %i\n", columnData[i].TargetType);
printf (" ValuePtr : %p\n", columnData[i].TargetValuePtr);
printf (" ColumnDataLen %i\n", columnData[i].BufferLength);
printf (" StrLen_or_Ind %p\n", &(columnData[i].StrLen_or_Ind));
retcode = SQLBindCol(hstmt, (SQLUSMALLINT)i+1,
columnData[i].TargetType, columnData[i].TargetValuePtr,
columnData[i].BufferLength, &(columnData[i].StrLen_or_Ind));
CHECK_ERROR(retcode, "SQLBindCol(1)",
hstmt, SQL_HANDLE_STMT);
}
printf( "Data :\n" );
// Fetch the data and print out the data
for ( retcode = SQLFetch(hstmt) ;
retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO ;
retcode = SQLFetch(hstmt) ) {
int j;
printf ("\n");
for ( j = 0 ; j < numColumns ; j++ ) {
printf( "%s: %.12s\n", (char *) columnLabels[j],
(char *) columnData[j].TargetValuePtr );
memset (columnData[j].TargetValuePtr, ' ', BUFFERSIZE+1);
}
}
// If we've just read all the data return success
if (retcode==SQL_NO_DATA) retcode=SQL_SUCCESS;
printf( "\n" );
exit:
// Free buffers
for ( i = 0 ; i < numColumns ; i++ ) {
if (columnLabels[i] != NULL) free (columnLabels[i]);
}
for ( i = 0 ; i < numColumns ; i++ ) {
if (columnData[i].TargetValuePtr != NULL)
free (columnData[i].TargetValuePtr);
}
if (columnLabels!=NULL) free (columnLabels);
if (columnData!=NULL) free (columnData);
return retcode;
}
int main () {
int i, j, count = 1;
SQLCHAR tableNames[100][BUFFERSIZE];
SQLCHAR *dbName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*BUFFERSIZE );
SQLCHAR *userName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*BUFFERSIZE );
SQLCHAR connStrbuffer[BUFFERSIZE];
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; // Return status
struct DataBinding* catalogResult = (struct DataBinding*)
malloc( NUMCOLS * sizeof(struct DataBinding) );
SQLCHAR* selectAllQuery = (SQLCHAR *)malloc(sizeof(SQLCHAR) * BUFFERSIZE);
for ( i = 0 ; i < NUMCOLS ; i++ ) {
catalogResult[i].TargetValuePtr = NULL;
}
// 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)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLGetInfo(hdbc, SQL_USER_NAME, userName,
(SQLSMALLINT)BUFFERSIZE, &bufferLen);
CHECK_ERROR(retcode, "SQLGetInfo(SQL_USER_NAME)",
hstmt, SQL_HANDLE_STMT);
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. This can be used even if the statement is
// closed by closeStatementHandle
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, "SQLBindCol(2)",
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 ) {
strcpy (tableNames[i++], catalogResult[2].TargetValuePtr);
printf( "Saving Table %i, Name %s\n", count,
(char *) catalogResult[2].TargetValuePtr );
}
}
// Output details of all my tables.
// The SQLFreeStmt with SQL_CLOSE closes the cursor associated with
// Statement Handle and discards all pending results.
for (j=0; j<i; j++) {
retcode = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_ERROR(retcode, "SQLFreeStmt()",
hstmt, SQL_HANDLE_STMT);
printf( "Select all data from table : (%s)\n", tableNames[j] );
sprintf( selectAllQuery, "SELECT * FROM %s", tableNames[j] );
printf( "Query : %s\n", selectAllQuery);
retcode = SQLExecDirect(hstmt, selectAllQuery, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect()",
hstmt, SQL_HANDLE_STMT);
retcode = printStatementResult(hstmt);
CHECK_ERROR(retcode, "printStatementResult()",
hstmt, SQL_HANDLE_STMT);
}
exit:
// Free memory
free (dbName);
free (userName);
free (selectAllQuery);
for ( i = 0 ; i < NUMCOLS ; i++ ) {
if (catalogResult[i].TargetValuePtr == NULL)
free(catalogResult[i].TargetValuePtr);
}
free (catalogResult);
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;
}
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.