Introduction

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:

Prepare the SQL statement via the SQLPrepare function.

Execute the SQL statement with the SQLExecute function.

Call SQLNumResultCols to find out how many columns were returned in the
result set.

For each column, call the SQLDescribeCol function to get the column type.

For each column, convert the SQL type returned from SQLDescribeCol to a C
type.

For each row in the result set, allocate memory for the data (depending
on the C type).

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.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

Comments and Discussions

Am using Microsoft Visual C++.NET. I downloaded the demo project but it fails to compile. I get the error message;
fatal error C1083: Cannot open include file: 'ODBCDynamic.hpp': No such file or directory

I there a way i can access this file.
This looks like some thing great if it worked. Please help

I have run into the same problem and have not found a fix yet. The problem I think stems from SQLDescribeCol() call made in FetchData which returns what appears to be the column name length rather than the actual data length of the field. I am not sure whether the error is in SQLDescribeCol() or in Tom's code.

Hi.I’m trying to develop an application that must operate on the oracle database using (ODBC) CDatabase & CRecordset. But the application can't show the record (NUMBER) type correctly and it show instead some strange numbers in the edit contrl. like -19841 instead of 50.

// here's an example of the code.char str[255];int index=0;CDBVariant var;recordset.GetFieldValue(index,var);wsprintf(str, "%d" ,var.m_iVal); // i tried to use 'var.m_dblVal' also.EditCtrl->SetWindowText(str);

may you tell me please what i have to do to convert the number and show it correctly? or how to make sure about which data type is in the CDBVariant for further proper conversion?I'm using VC++ 6.0 & Oracle 9i.

While I enjoy giving back to the community in the form of code/articles, continually updating past articles isn't something I can do and still have time to make money to put food on the table

Therefore, this is the only "free" version of the code I've made available.

If you're questions are generic ODBC-type questions, you can probably get loads of help on this site's forums as well as the MSDN Forums.

On the other hand, if you're in a pinch and want these classes customized for your company's needs, I have done that for several clients on an hourly, paid basis. If so, drop me a line and we can discuss further.

Thanks for your code!
I got a problem: when I assign the content of a field to a CString with both CODBCRecord::Lookup or CODBCRecord::GetNextAssoc, only the first N characters of the record cell are copied into the CString, where N is the length of the field name!

For ex:
If the value of the field named Name is Michelangelo, the content of the CString will be Mich (as Name is 4 character long).

The same happens with your demo project.
I used Access databases for my tests.
Do you know why this happens?
Thanks!

I think the intention was to be able to read out char fields not ended correctly by a \0. Th problem is that m_nLen does not store the size of the column, but the size of the header. This is filled in the following line

Thanks. However, I don't know if or when I'm update this code as it's something I haven't used in many years. (It was something I needed for a job iat AT&T back in '98 I believe).

Cheers,
Tom Archer, Inside C#Mainstream is just a word for the way things always have been -- just a middle-of-the-road, tow-the-line thing; a front for the Man serving up the same warmed-over slop he did yesterday and expecting you to say, "Thank you sir, may I have another?"

Your Classes are very good and straight forward, but when I implemented them into my SQL DB, it took a little too much time retrieving the rows ( 2000row ) and that was only for one column, I didn't try it for the 17 columns that Ihad.

I there a way to speed up the process, maybe using some cahing method.

Thanks man! I know it definitely saved me a lot of time in a couple of gigs once I got it finished.

Cheers,
Tom Archer, Inside C#Mainstream is just a word for the way things always have been -- just a middle-of-the-road, tow-the-line thing; a front for the Man serving up the same warmed-over slop he did yesterday and expecting you to say, "Thank you sir, may I have another?"

Chris Maunder and I originally built CodeGuru so that's where this article first appeared. When Chris started this site, I posted some of my articles over here to help him get started with some content.

This space is really intended as a means of me getting feedback on this article. You'll probably have a greater chance of getting help on specific questions regarding other issues if you ask them in the VC++ forum.