Parameterized CODBCRecordset Class

• Download source files - 8.4 Kb

This article is just an extension to the CODBCRecordset class article by Stefan Chekanov. It will describe how to add a support for parameterization in that class. For example:

CODBCRecordset tDyn( m_database );
CString cSQL("SELECT * FROM some_table WHERE number_field > ? AND date_field = ?");
tDyn[ "number_field" ] = "12345";
tDyn[ "date_field" ] = "30.03.2001";
tDyn.Open(cSQL, CRecordset::forwardOnly, CRecordset::readOnly);

First problem is that we don’t know what field types are, how many parameters we need (dynamically creating SELECT statement on user request for specific values in some or all fields…). That will be solved if we say that all parameters will be accepted as LPCTSTR type, and in the moment of binding parameters (function DoFieldExchange( CFieldExchange* pFX )) we will know what appropriate types are, and than we can do the right conversion.

We need first to override operator [] in header file

CDBField&	operator[]( LPCTSTR szField );

and to add pointer variable that will store the parameter fields

CPtrArray	m_ParamFields;

implementation (still in header file)

inline
CDBField& CODBCRecordset::operator[]( LPCTSTR szField ) 
{
    CDBField *fl = new CDBField;
    fl->m_cName = szField;
    fl->m_pstring = new CString;
    fl->m_dwType = DBVT_STRING;
    m_ParamFields.Add( fl );

    m_nParams++;

    return *fl;
}

Very important is that we don't forget to increment CRecordset::m_params because it will tell the CRecordset how many parameters to expect.

In .cpp file we need to add the following code to the CODBCRecordset::Clear() function

if( m_ParamFields.GetSize() > 0 )
{
    for( int i = 0; i < m_ParamFields.GetSize(); i++ )
    {
        CDBField *fl;
        fl = (CDBField*) m_ParamFields.GetAt(i);
        delete fl;
        m_ParamFields.SetAt( i, NULL );
    }
    m_ParamFields.RemoveAll();
}

and to adopt DoFieldExchange( CFieldExchange* pFX ) function

void CODBCRecordset::DoFieldExchange( CFieldExchange* pFX )
{
    CString cFieldName;
    if( pFX->m_nOperation != CFieldExchange::BindParam )       // Fields or params
    {
        pFX->SetFieldType( CFieldExchange::outputColumn );
        for( UINT i = 0; i < m_nFields; i++ )
        {
            cFieldName.Format( "[%s]", GetFieldName(i) );
            switch( m_fields[i].m_dwType )
            {
                case	DBVT_NULL:
                    break;

                case	DBVT_BOOL:
                    RFX_Bool( pFX, cFieldName, m_fields[i].m_boolVal );
                    break;

                case	DBVT_UCHAR:
                    RFX_Byte( pFX, cFieldName, m_fields[i].m_chVal );
                    break;

                case	DBVT_SHORT:
                    //	CDBVariant::m_iVal is of type short
                    //	RFX_Int() requires parameter of type int.
                    //	Class wizard maps int variable in this case
                    //	but CDBVariand does not have int member.
                    m_fields[i].m_dwType = DBVT_LONG;
                    RFX_Long( pFX, cFieldName, m_fields[i].m_lVal );
                    break;

                case	DBVT_LONG:
                    RFX_Long( pFX, cFieldName, m_fields[i].m_lVal );
                    break;

                case	DBVT_SINGLE:
                    RFX_Single( pFX, cFieldName, m_fields[i].m_fltVal );
                    break;

                case	DBVT_DOUBLE:
                    RFX_Double( pFX, cFieldName, m_fields[i].m_dblVal );
                    break;

                case	DBVT_DATE:
                    RFX_Date( pFX, cFieldName, *m_fields[i].m_pdate );
                    break;

                case	DBVT_STRING:
                    {
                        CODBCFieldInfo	fi;
                        GetODBCFieldInfo( (short)i, fi );
                        RFX_Text( pFX, cFieldName, *m_fields[i].m_pstring, fi.m_nPrecision );
                        break;
                    }

                case	DBVT_BINARY:
                    RFX_LongBinary( pFX, cFieldName, *(m_fields[i].m_pbinary) );
                    break;

                default:
                    //	Unknown datatype
                    ASSERT( FALSE );
            }
            m_fields[i].SetNull( FALSE != IsFieldStatusNull( i ) );
        }
    }
    else
    {

        if( m_ParamFields.GetSize() == 0 ) return;

        // Parameters...
        pFX->SetFieldType( CFieldExchange::param );

        for( int i = 0; i < m_ParamFields.GetSize(); i++ )
        {
            CDBField *fl = (CDBField*) m_ParamFields.GetAt(i);

            unsigned char cValue = (unsigned char)atoi((const char*)fl->m_pstring);
            BOOL bValue = atoi((const char*)fl->m_pstring);
            long lValue = atol((const char*)fl->m_pstring);
            float fValue = (float)atof((const char*)fl->m_pstring);
            double dValue = atof((const char*)fl->m_pstring);

            cFieldName.Format( "[%s]", fl->m_cName );

            switch( fl->m_dwType )
            {
                case	DBVT_NULL:
                    break;

                case	DBVT_BOOL:
                    RFX_Bool( pFX, cFieldName, bValue );
                    break;

                case	DBVT_UCHAR:
                    RFX_Byte( pFX, cFieldName, cValue );
                    break;

                case	DBVT_SHORT:
                    fl->m_dwType = DBVT_LONG;
                    RFX_Long( pFX, cFieldName, lValue );
                    break;

                case	DBVT_LONG:
                    RFX_Long( pFX, cFieldName, lValue );
                    break;

                case	DBVT_SINGLE:
                    RFX_Single( pFX, cFieldName, fValue );
                    break;

                case	DBVT_DOUBLE:
                    RFX_Double( pFX, cFieldName, dValue );
                    break;

                case	DBVT_DATE:
                    RFX_Date( pFX, cFieldName, *fl->m_pdate);
                    break;

                case	DBVT_STRING:
                    RFX_Text( pFX, cFieldName, *fl->m_pstring );
                    break;

                case	DBVT_BINARY:
                    RFX_LongBinary( pFX, cFieldName, *(fl->m_pbinary) );
                    break;

                default:
                    //	Unknown datatype
                    ASSERT( FALSE );
            }
        }
    }
}

The important thing here is that we distinguish when we need to bind parameters and when to bind fields.

This is it (hopefully).

History

5 Jul 2001: The CODBCRecordset class now supports all SQL types, not just strings.