There are times when, as a programmer, you might be faced with scenarios where you do not know the schema of a database until runtime. Examples of this are ad-hoc query and reporting tools. In both cases, the end user is allowed to build their own SQL from a list of tables. As you may already know, it is extremely easy to pass ODBC an SQL string, have it executed, and retrieve the resulting data. But, how can you do this when you don't know what the resulting data will look like when you write your application?

Luckily ODBC provides several functions that can be used for this very purpose. After connecting to the data source, the next steps needed would be the following:

• 1. Prepare the SQL statement via the SQLPrepare function.
• 2. Execute the SQL statement with the SQLExecute function.
• 3. Call SQLNumResultCols to find out how many columns were returned in the result set.
• 4. For each column, call the SQLDescribeCol function to get the column type.
• 5. For each column, convert the SQL type returned from SQLDescribeCol to a C type.
• 6. For each row in the result set, allocate memory for the data (depending on the C type).
• 7. For each row, call SQLGetData to read the data into the allocated memory for that row/column.

Examples of how to use the CODBCDynamic class

Submitting an SQL statement

// simply specify the ODBC DSN in the c'tor 
// and pass the desired SQL to the ExecuteSQL function...
CODBCDynamic odbcDynamic(_T("YourDsn"));
odbcDynamic.ExecuteSQL(_T("SELECT * from OrderHeader"));

Retrieving data from a result set

// call a function that returns an hstmt to a result set (e.g., SQLGetTypeInfo)
odbcDynamic.FetchData(hstmt);

Retrieving all rows and columns of data once ExecuteSQL or FetchData has been called

// instantiate a CODBCDynamic object (specifying the desired DSN)
CODBCDynamic odbcDynamic(_T("Forms Express System Database"));
// execute the desired SQL 
odbcDynamic.ExecuteSQL(_T("SELECT * from UserMaster"));

// retrieve the record array
CODBCRecordArray* pODBCRecordArray = &odbcDynamic.m_ODBCRecordArray;

CString strInfo;

// for every returned record...
for (int iRecord = 0; iRecord < pODBCRecordArray->GetSize(); iRecord++)
{
 CODBCRecord* pODBCRecord = (*pODBCRecordArray)[iRecord];

 POSITION pos;
 CDBVariantEx* pvarValue;
 CString strColName;

 CString strValue;

 // for every column within the current record
 for (pos = pODBCRecord->GetStartPosition(); pos != NULL;)
 {
  pODBCRecord->GetNextAssoc(pos, strColName, pvarValue);
  pvarValue->GetStringValue(strValue);

  strInfo.Format(_T("Record: %ld, Column: %s, Value: '%s'"), iRecord, strColName, strValue);
  AfxMessageBox(strValue);
 }
}

Retrieving specific columns once ExecuteSQL or FetchData has been called

// instantiate a CODBCDynamic object (specifying the desired DSN)
CODBCDynamic odbcDynamic(_T("Forms Express System Database"));
// execute the desired SQL 
odbcDynamic.ExecuteSQL(_T("SELECT * from UserMaster"));

// retrieve the record array
CODBCRecordArray* pODBCRecordArray = &odbcDynamic.m_ODBCRecordArray;

// for every returned record...
for (int iRecord = 0; iRecord < pODBCRecordArray->GetSize(); iRecord++)
{
 CODBCRecord* pODBCRecord = (*pODBCRecordArray)[iRecord];
 CString strValue;

 // retrieve the desired column (by name)
 CDBVariantEx* pvarValue = NULL;
 if (pODBCRecord->Lookup(_T("sUserId"), pvarValue))
 {
  // As shown in the example above, you can use the 
  // CDBVariantEx::GetStringValue to have the value 
  // translated into a CString and returned...
  pvarValue->GetStringValue(strValue);
  AfxMessageBox(strValue);

  // ... or you can now use the appropriate CDBVariant member 
  // variable to access the data. For example, if the column's 
  // data type is string, or text...
  AfxMessageBox(*pvarValue->m_pstring);
 }
}

Downloads