Click here to Skip to main content
Click here to Skip to main content
Go to top

Parameterized CODBCRecordset Class

, 16 Jul 2001
Rate this:
Please Sign up or sign in to vote.
CODBCRecordset that support parameter prior to opening table(s)
<!-- Download Links -->

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sasa Kajic

Ireland Ireland
No Biography provided

Comments and Discussions

 
QuestionHow to bind variable for Oracle's DATE or TIMESTAMP? Pinmemberhou219-Feb-09 7:49 
GeneralParameterized query PinmemberScott An27-Aug-03 14:57 
GeneralRe: Parameterized query PinmemberSasa Kajic29-Aug-03 4:15 
GeneralRe: Parameterized query PinmemberScott An31-Aug-03 19:14 
GeneralCODBCRecordset and COleDateTime PinmemberThomas Geisser19-Mar-02 7:41 
GeneralDobro je, BA , Kajko... PinmemberTesic Goran9-Jan-02 10:02 
GeneralRe: Dobro je, BA , Kajko... Pinmemberkocikus20-Feb-06 5:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 17 Jul 2001
Article Copyright 2001 by Sasa Kajic
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid