LwQuery: a MFC wrapper class for ODBC

Introduction

LwQuery is an easy-to-use ODBC wrapper class to give your MFC applications the full power of SQL and more.

Background

The MFC essentially wraps a lot of Windows API functions into an object oriented Library. While there is plenty of support for various GUI controls there are essentially only two classes for database handling.

On one hand CDatabase, a very thin wrapper for the HDBC handle to an ODBC data source.

The other one, CRecordset wraps "somehow" database table access. Unfortunately it hides away nearly all the flexibility that the ODBC-API and the SQL language provide.

I always had the impression there should be another class which is easier to use than the ODBC-API while providing the full power of SQL.

My class LwQuery is designed to fill exactly this gap.

It leaves everything concerning the connection management to the existing MFC class CDatabase and wraps most aspects of the ODBC-API around preparing a statement, parameter binding, executing, fetching and accessing the result set into one handy class.

LwQuery can execute any sql statement, regardless whether it has a result set or not. It also supports the use of stored procedures to query or modify data.

Of course it must be said, that there is not one SQL language but as many SQL dialects as there are database engines. But as long as you stick to the subset of the common standard SQL you can query and manipulate an enormous variety of database engines ranging from the widespread tiny single user file-based engine SQLite to vast clusters of server sided engines from all major manufacturers. And if you are targeting one specific engine you can use its complete variety of SQL.

Interface

Some fundamental LwQuery members:

LwQuery()	constuctor
~LwQuery()	destructor
void SetDatabase( CDatabase* pDatabase)	Set the database handle to a CDatabase object which encapsulates the ODBC connection
RETCODE ExecDirect( LPCTSTR statement)	Execute an arbitrary sql statement; on success the function returns SQL_SUCCESS, otherwise one of the return codes defined in <sql.h>. If statement contains question mark placeholders it is obligatory to bind a host variable for each placeholder (see below) before calling ExecDirect().
RETCODE Close()	Finish the statement and release all its resources.

With these members we can already create tables and insert or update data.

Example code:

    LwQuery query;

            CString statement;

        

            query.SetDatabase( &db);

            statement = _T(

            "    create table person\n"

            "    (\n"

            "       
        lfnbr                          
        int                  
        not null,\n"

            "       
        name                           
        char(32)             
        null    ,\n"

            "       
        birthdte                       
        datetime             
        null    ,\n"

            "       
        balance                        
        decimal(8,2)         
        null    ,\n"

            "        constraint
        pk_person primary key (lfnbr)\n"

            "    )\n"

            );

        

            nRetCode = query.ExecDirect( statement);

        

            nRetCode = query.ExecDirect(

                _T("insert into person( lfnbr,
        name, birthdte, balance) values( 1, 'Jack', '1964-03-24', 5432.19)"));

        

            nRetCode = query.ExecDirect(

                _T("insert into person( lfnbr,
        name, birthdte, balance) values( 2, 'Jill', '2004-05-21', 1234.56)"));

Members for fetching and accessing data from the result set:

RETCODE GetRowsAffected( SQLLEN& nRowCount)	Gets the number of affected rows. Only for such statements as delete or update which do not return a result set. For other statements which return a result set (e.g. select) the row count may be set for some drivers and for other drivers not.
short GetODBCFieldCount() const	Gets the number of columns in the result set. Can also be used to check whether there is a result set at all.
short GetFieldIndexByName(LPCTSTR lpszFieldName)	Returns the 0-based column index which corresponds to the column named lpszFieldName, -1 if no column name matches.
void GetODBCFieldInfo( short nIndex, CODBCFieldInfo& fieldinfo)	Retrieves information about column type and size. See Visual Studio / MFC help for further information on CODBCFieldInfo.
BOOL GetODBCFieldInfo( LPCTSTR lpszName, CODBCFieldInfo& fieldinfo)	Retrieves information about column type and size. See Visual Studio / MFC help for further information on CODBCFieldInfo. Returns FALSE if no column with name lpszName exists.
RETCODE Fetch()	Fetches the next row of the resultset. If successful it returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. If there are no more rows in the result (after the last row) set it returns SQL_NO_DATA_FOUND. No explicit column binding!
void GetFieldValue( short nIndex, CString& sValue)	Retrieves the value of the specified column in the current row. Most sql data types can be converted to CString.
BOOL GetFieldValue( LPCTSTR lpszName, CString& sValue )	Retrieves the value of the specified column in the current row. Most sql data types can be converted to CString. Returns FALSE if no column with name lpszName exists.
BOOL GetFieldValue( short nIndex, long& lValue)	Retrieve the value of the specified column in the current row into a variable of the respective type. Returns FALSE if the sql type of the column cannot be converted into that type. See the conversion matrix in Visual Studio help.
BOOL GetFieldValue( short nIndex, int& iValue)
BOOL GetFieldValue( short nIndex, short& siValue)
BOOL GetFieldValue( short nIndex, UINT64& ui64Value)
BOOL GetFieldValue( LPCTSTR lpszName, long& lValue)	Retrieve the value of the specified column in the current row into a variable of the respective type. Returns FALSE if no column with name lpszName exists or if the sql type of the column cannot be converted into int. See the conversion matrix in Visual Studio help.
BOOL GetFieldValue( LPCTSTR lpszName, int& iValue)
BOOL GetFieldValue( LPCTSTR lpszName, short& siValue)
BOOL GetFieldValue( LPCTSTR lpszName, UINT64& ui64Value)
BOOL GetFieldValue( short nIndex, CByteArray& ba)	Retrieve binary or varbinary column data from the current row into a CByteArray variable. The ByteArray size fits the length of the (var)binary data in the database field. Returns FALSE if no column with name lpszName exists.
BOOL GetFieldValue( LPCTSTR lpszName, CByteArray& ba)
void GetFieldValue( short nIndex, LwDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE)	Retrieve the value of the specified column in the current row into a variable of type LwDBVariant. LwDBVariant is a helper class derived from CDBVariant which supports additional data types, formatting into string, assignment and comparison operators. Returns FALSE if no column with name lpszName exists. Actually all GetFieldValue() functions, except those for CString, CstringA, CStringW, internally use this functions and cache the data in a protected member which is an array of LwDBVariants.
BOOL GetFieldValue( LPCTSTR lpszName, LwDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE)
void GetCurrentRow( LwDataRow& currentRow);	Read all fields from the current row into a LwDataRow array. LwDataRow is derived of CArray of LwDBVariants.
BOOL IsFieldInit( short nIndex)	If TRUE the data field in the current row has been read and value or null indicator is initialized.
BOOL IsFieldNull( short nIndex)	If TRUE column with nIndex has been read and holds the db null value.
RETCODE SQLMoreResults()	ODBC allows multiple result sets for one query. After reading the last row of a result set SQLMoreResults() asks whether there is another result set pending. Return value SQL_SUCCESS (or SQL_SUCCESS_WITH_INFO) means there is another result set to be read. SQL_NO_DATA_FOUND means all result sets have already been read. SQLMoreResults() also re-initializes the ODBCFieldInfos according to the new result set.

Example code:

    nRetCode = query.ExecDirect(_T("select *
          from person"));



              short colcount = query.GetODBCFieldCount();

    // if we have a result set colcount will be
          > 0 (even if the result holds 0 rows!)

              if (colcount > 0)

              {

                  // optional: retrieve
          information on the columns of the result set

                  // this is not mandatory for the
          subsequent retrieval of the column values

                  for (short col=0; col <
          colcount; col++)

                  {

                      LwFieldInfo
          fieldinfo;

                     
          query.GetODBCFieldInfo( col, fieldinfo);

                     
          printf(_T("columnn name:\t%s\nSQL
          type:\t%0d\nPrecision:\t%0d\nScale:\t%0d\nNullable:\t%0d\n\n"), 

                     
              //       
                     
                  // meta data from ODBC:

                     
              fieldinfo.m_strName,   
              //    CString m_strName;

                     
              fieldinfo.m_nSQLType,   
              //    SWORD m_nSQLType; (see "SQL
          data type codes" in <sql.h>

                     
              fieldinfo.m_nPrecision,   
              //    SQLULEN m_nPrecision;

                     
              fieldinfo.m_nScale,   
                  //    SWORD
          m_nScale;

                     
              fieldinfo.m_nNullability   
          //    SWORD m_nNullability;

                     
              );

                  }



                  // now we retrieve data by
          iterating over the rows of result set

                  for (nRetCode = query.Fetch();
          nRetCode != SQL_NO_DATA; nRetCode = query.Fetch())

                  {

                      // the
          current row is in the buffer

                      short col =
          0;

                      long lfnbr =
          0;

                     
          query.GetFieldValue( col++, lfnbr);    // adressing the
          column by its index 0

           
          CString sName;

                     
          query.GetFieldValue(_T("name"), sName);    // adressing
          the column by its column name

           
          LwDBVariant varBirthDte;

                      // variant
          data type LwDBVariant is an extension of CDBVariant

                      // and can be
          used if the column data type is unknown or no appropriate
          GetFieldValue() member available

                     
          query.GetFieldValue((short) 2, varBirthDte);

           
          printf(_T("Nr. %0d\t%s\n"), lfnbr, (LPCTSTR) sName);

                  }

              }

We can now store and retrieve any SQL data from the database.

But do not stop reading here!

Your application will have user interaction. So if you do not want somebody to put some foul data into your application by SQL injection you should use parameter binding. The LwQuery wrapper functions for parameter binding are discussed below.

Parameter binding essentially means that your SQL-Statements contain question marks as placeholders for variable data. The application provides host variables as buffers and the ODBC API reads from and writes to these buffers instead of the placeholders.

That implies that the application program, that is you, the programmer, has to guarantee the availability and sufficient size of the buffers throughout the query's execution time.

Or you leave this tedious work to LwQuery's internals and skip to the SetParamValue() member functons.

Parameter binding:

The BindParameter functions replace the question marks by host variables of various types. The binding is always done by call-by-reference. For normal sql statements these parameters are of InputOutputType SQL_PARAM_INPUT. They stay unchanged by the execution of the sql statement.
When working with stored procedure parameters may be of InputOutputType SQL_PARAM_OUTPUT or SQL_PARAM_INPUT_OUTPUT meaning the stored procedure call may use their value and assign a new value to the host variable when executed.
If a statement contains N question mark placeholders the parameters are numbered 1 to N. 0 is reserved for the RETCODE of the query.

RETCODE Prepare( LPCTSTR statement)	Set an arbitrary SQL statement which is to be executed. The statement may contain question marks as placeholders for variables which have to be bound to the statement according to their types and the sql type in the statement. For details see the conversion matrix in Visual Studio help.
RETCODE BindParameter( SQLUSMALLINT ParameterNumber, short& nParamValue, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)	Host variable is of C++ type short (16 bit integer) which corresponds to the ODBC sql type SQL_SMALLINT.
RETCODE BindParameter( SQLUSMALLINT ParameterNumber, int& nParamValue, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)	Host variable is of C++ type int (32 bit integer) which corresponds to the ODBC sql type SQL_INTEGER.
RETCODE BindParameter( SQLUSMALLINT ParameterNumber, long& nParamRef, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)	Host variable is of C++ type long (32 bit integer) which corresponds to the ODBC sql type SQL_INTEGER.
RETCODE BindParameter( SQLUSMALLINT ParameterNumber, __int64& nParamRef, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)	Host variable is of C++ type __int64 (64 bit integer) which corresponds to the ODBC sql type SQL_BIGINT.
RETCODE BindParameter( SQLUSMALLINT ParameterNumber, double& sParamRef, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)	Host variable is of C++ type double. Length in memory of double is 64 bit integer. It maybe used for ODBC sql types SQL_DOUBLE but also for SQL_DECIMAL, SQL_NUMERIC, or SQL_FLOAT.
RETCODE BindParameter( SQLUSMALLINT ParameterNumber, TIMESTAMP_STRUCT& tsParamRef, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)	Host variable is a TIMESTAMP_STRUCT. It can be used for sql types SQL_DATETIME.
RETCODE BindParameter( SQLUSMALLINT ParameterNumber, CString& sParamRef, SQLLEN strlen = 0, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)	Host variable is of MFC class CString. Length in memory depends. It maybe used for ODBC sql types SQL_CHAR, SQL_VARCHAR, SQL_WCHAR, SQL_WVARCHAR. For SQL_PARAM_INPUT_OUTPUT and SQL_PARAM_OUTPUT the CString buffer must be big enough to receive the maximal possible length to be wriiten. The buffer length has to be set in the strlen parameter.
void SetParamLenIndicator( SQLUSMALLINT ParameterNumber, SQLLEN lenInd)	Set special parameter values, e.g. SQL_NTS (Null terminated string), SQL_NULL_DATA (for null value), SQL_DEFAULT_PARAM (only for stored procedure calls).
Execute()	Execute the statement which was previously set with SetStatement(). If you want to execute the same statement with different parameter values, it is sufficient to assign the new values to the already bound host variables and call Execute() again. This is also more performant than building new query strings and calling them by ExecuteDirect().

Example code:

    long lfnbr = 3;

            CString sName = _T("Joe");

            TIMESTAMP_STRUCT tsBirthDte;

            tsBirthDte.year = 2006;

            tsBirthDte.month = 7;

            tsBirthDte.day = 12;

            tsBirthDte.hour = 0;

            tsBirthDte.minute = 0;

            tsBirthDte.second = 0;

            tsBirthDte.fraction = 0;

            double balance = 234.56;

        

            // To free old bindings, always close previous query
        when working with parameter binding.

            query.Close();

        

            query.SetStatement(

                _T("insert into person( lfnbr,
        name, birthdte, balance) values( ?, ?, ?, ?)"));

        

            // Parameter binding

            // -----------------

            short par = 1;

            query.BindParameter( par++, lfnbr);

            query.BindParameter( par++, sName);

            query.BindParameter( par++, tsBirthDte);

            query.BindParameter( par++, balance);

        

            // Remark: ExecDirect() works with parameter binding,
        too.

            nRetCode = query.Execute();

        

            ASSERT( nRetCode == SQL_SUCCESS || nRetCode ==
        SQL_SUCCESS_WITH_INFO);

            query.Close();

The BindParameter functions are only thin wrappers for the underlying API function ::SQLBindParameter(). Especially all bound host variables must stay alive and not be disposed of throughout the query's execution time until the Close() member is called. Taking into account that in most cases these variables are of InputOutputType SQL_PARAM_INPUT it seems sensible to hold the host variables within the LwQuery object and assign parameter values to them with simpler function calls.

Set parameters with call-by-value:

RETCODE SetParamValue( SQLUSMALLINT ParameterNumber, long nParamValue, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)	LwQuery creates the host variable on the heap and holds it until the Close() member function is called.
RETCODE SetParamValue( SQLUSMALLINT ParameterNumber, double dParamValue, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)
RETCODE SetParamValue( SQLUSMALLINT ParameterNumber, TIMESTAMP_STRUCT ParamValue, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)	LwQuery creates a TIMESTAMP_STRUCT host variable on the heap, assigns the dtParamValue to it and holds the host variable until the Close() member function is called.
RETCODE SetParamValue( SQLUSMALLINT ParameterNumber, LPCTSTR lpszValue, SQLSMALLINT InputOutputType = SQL_PARAM_INPUT)	LwQuery creates a CString host variable on the heap, assigns the LPCTSTR value and holds the host variable until the Close() member function is called.

Example code:

query.Prepare( _T("insert into person( lfnbr, name, birthdte, balance) values( ?, ?, ?, ?)"));

// internal parameter binding // -------------------------- par = 1; query.SetParamValue( par++, (long) 4); query.SetParamValue( par++, _T("Jim")); query.SetParamValue( par++, tsBirthDte); query.SetParamValue( par++, 3523.17); // Execute uses the above parameters. nRetCode = query.Execute();

ASSERT( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO);

// free resources // -------------- query.Close();

For stored procedures with inout parameters the parameter values can be retrieved GetParamValue(). If the stored procedure has a result set it's necessary to fetch all data rows before retrieving the output parameters.

BOOL GetParamValue( SQLUSMALLINT ParameterNumber, long& nParamValue);	If a parmeter of fitting type is bound (implicitly or explicitly) its value is copied to nParamValue and the function returns TRUE; otherwise it returns FALSE.
BOOL GetParamValue( SQLUSMALLINT ParameterNumber, double& dParamValue);
BOOL GetParamValue( SQLUSMALLINT ParameterNumber, TIMESTAMP_STRUCT& tsParamValue);
BOOL GetParamValue( SQLUSMALLINT ParameterNumber, CString& sValue);

The sample package includes a stored procedure in Microsoft's Transact-SQL. It should be no problem to use stored procedures for other database engines in a similar way.

Example code:

query.Prepare("{CALL testproc(?,?,?,?,?)}"); // internal parameter binding // -------------------------- par = 1; query.SetParamValue( par++, (long) 1); long lVal = 2; query.SetParamValue( par++, lVal, SQL_PARAM_INPUT_OUTPUT); query.SetParamValue( par++, _T("ABC")); CString sVal = _T("DEF"); query.SetParamValue( par++, sVal, SQL_PARAM_INPUT_OUTPUT); long lRetVal = 0; query.SetParamValue( par++, lRetVal, SQL_PARAM_INPUT_OUTPUT); // Execute uses the above parameters. nRetCode = query.Execute(); ASSERT( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO); query.GetParamValue( 2, lVal); // --> 3 query.GetParamValue( 4, sVal); // --> "ABCDEF" // free resources // -------------- query.Close();

Points of Interest

While this article describes the LwQuery class the code includes some more headers and source files with classes and functions used by LwQuery:

LwBase.h allows to compile a DLL (with LW_EXT_EXPORT set) bind the classes statically into another project (with LW_EXT_STATIC set)
LwString.h and LwString.cpp with a class LwString derived from and extending CString
lwdbvariant.h and lwdbvariant.cpp with several helper classes, useful when working with database queries and their result sets, especially for formatting single field values or whole data rows

Maybe I'll write someday another article on these classes.