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.