This article was contributed by Tom Archer.
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.
Did I say “luckily”? Actually, I said it with tongue firmly planted in cheek. Therefore, in this article
I submit to you a class (CODBCDynamic
) that reduces the 400+ lines of code required to
fully implement the functionality listed above to 2 lines of code! Here are some examples of how to
use the CODBCDynamic
class.
Examples of how to use the CODBCDynamic class
While this article also includes a full-fledged test application, it’s always nice to be
able to see what you’re getting before you invest the time in downloading, unzipping and
running someone else’s code. Therefore, here are some code snippets that show how easy the
CODBCDynamic
class is to use.
To submit an SQL statement, you simply instantiate a CODBCDynamic
object
(passing a valid DSN) and then call the CODBCDynamic::ExecuteSQL
member function
(passing the SQL string to execute). That’s it!
// 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”));
In the first example above, I showed you how the CODBCDynamic
class allows
you to submit an SQL statement using the ExecuteSQL
member function. However, there
are times, when your application will only have the HSTMT
to a result set. For example,
if you call the ODBC SDK function SQLGetTypeInfo
, you will receive a result set
with the returned data. Using the CODBCDynamic
class, you can read the data into its
member variables with the following two lines of code.
// call a function that returns an hstmt to a result set (e.g., SQLGetTypeInfo)
odbcDynamic.FetchData(hstmt);
Once either the ExecuteSQL
or FetchData
member functions
have been called, the resulting data can be retrieved from the
CODBCDynamic
object in a very generic manner. The CODBCDynamic
class has a templatized array (m_ODBCRecordArray
) that represents each of
the records that were read. Each entry in the m_ODBCRecordArray
is a templatized
CMapStringToPtr
map of columns and their respective values for that record.
The map is keyed by the column name (retrieved automatically) and the data is in the form of
a CDBVariantEx
object. However, you never have to worry about such
technicalities. Assuming that you’ve already called ExecuteSQL
or
, here’s an example of how easy it is to iterate through the returned
FetchData
records of an SQL statement.
// 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);
}
}
As mentioned above, once the ExecuteSQL or FetchData function has returned, each
returned record is stored in an array and each record is a basically a map of
column names to CDBVariant values. Therefore, as easy as it is to iterate through
all the returned the data, you can just as easily request specific columns by
name. Here’s an example of how you would do that.
// 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);
}
}
That’s it! That’s how easy it is to interrogate any ODBC data source.
The last thing that I will point out is that in the example above,
I used my CDBVariantEx’s GetStringValue
member function to retrieve the data in as a CString. However, because I chose to
store the data in CDBVariant objects, you can also easily query that object as to
the data’s exact type by inspecting the CDBVariant::m_dwType member variable. For more
documentation on this small, but useful class, please refer to the Visual C++
documentation.
Downloads
Download demo project – 15 KB
Download source – 15 KB
Date Last Updated: October 22, 2000