|
VC++
Example: ODBC Database SQL DSN- A class to dynamically read data from any ODBC data source
|
| |
Tom Archer.
Environment:
Tested in Visual C++ 5,
Visual C++ 6 and Monte
Carlo (new-unnamed version of VC++)
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.
Submitting an SQL
statement
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"));
Retrieving data from
a result set
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);
Retrieving all rows
and columns of data once ExecuteSQL or FetchData has been
called
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 FetchData, here's an example
of how easy it is to iterate through the returned 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);
}
}
Retrieving specific
columns once ExecuteSQL or FetchData has been called
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.
Download
demo project - 15 KB
Download
source - 15 KB
|
E-XD++Visual
C++/ MFC Products
