PRB: "Unexpected column data types were returned from query"

CAUSE

This exception is thrown by a record field exchange RFX function if the SQL type of the column returned in the recordset doesn't match the C type used to stored the retrieved data for that column. For example, in the MFC RFX_Text() function for Visual C++ version 1.51, you'll see the following code segment:

case CFieldExchange::BindFieldToColumn:
        {
        UINT cbColumn;
        int nSqlType = pFX->GetColumnType(nField, &cbColumn);
        switch (nSqlType)
        {
         default:
   pFX->m_prs->ThrowDBException(AFX_SQL_ERROR_FIELD_SCHEMA_MISMATCH);
         case SQL_LONGVARCHAR:
         case SQL_CHAR:
         case SQL_VARCHAR:
            break;
         case SQL_DECIMAL:
         case SQL_NUMERIC:
             // Add room for sign and decimal point
             cbColumn += 2;
             break;
        }
				

NOTE: The RFX_Text() function supports converting SQL_LONGVARCHAR,SQL_CHAR, SQL_VARCHAR, SQL_DECIMAL, and SQL_NUMERIC fields to a CString. If the column that the RFX_Text() function represents has any other SQL type, you will get the exception.

There are two typical reasons why developers get the exception. First, a developer has specified a SQL statement as the second argument for the CRecordset::Open() function. In this case, the columns listed in the SQL SELECT statement must match the order by which the RFX functions are called in the CRecordset's DoFieldExchange(). The first RFX function maps to the first column returned in the recordset, the second RFX function maps to the second column, and so on. The exception is thrown when the RFX functions are out of order. Paying attention to the exception can save developers a lot of debugging time.

Second, a developer intentionally changes one of the RFX functions to map the recordset column to a different C type because he or she knows the ODBC driver is capable of converting the column to any number of C types. For example, a common scenario is to have the driver return a date in a character string rather than a TIMESTAMP_STRUCT. Therefore, the developer will change the RFX_Date() function in the CRecordset's DoFieldExchange() function to a RFX_Text() function. In this scenario, the MFC RFX functions are being type strict and the exception shown above will occur.

case CFieldExchange::BindFieldToColumn:
        {
            UINT cbColumn;
            int nSqlType = pFX->GetColumnType(nField, &cbColumn);
            switch (nSqlType)
            {
            default:
                break;
            case SQL_DECIMAL:
            case SQL_NUMERIC:
                // Add room for sign and decimal point
                cbColumn += 2;
                break;
            }
					

void RFX_MyText(CFieldExchange* pFX, const char *szName,
    CString& value, int nMaxLength, int nColumnType)
{
    ASSERT(AfxIsValidAddress(pFX, sizeof(CFieldExchange)));
    ASSERT(AfxIsValidString(szName));
    ASSERT(AfxIsValidAddress(&value, sizeof(CString)));
    RETCODE nRetCode;
    UINT nField;
    if (!pFX->IsFieldType(&nField))
        return;
    if (pFX->m_nOperation==CFieldExchange::BindFieldToColumn)
    {
            UINT cbColumn;
            int nSqlType = pFX->GetColumnType(nField, &cbColumn);
            switch (nSqlType)
            {
            default:
                break;
            case SQL_DECIMAL:
            case SQL_NUMERIC:
                // Add room for sign and decimal point
                cbColumn += 2;
                break;
            }
            // Constrain to user specified max length
            if (cbColumn > (UINT)nMaxLength)
                cbColumn = nMaxLength;
            AFX_SQL_SYNC(::SQLBindCol(pFX->m_prs->m_hstmt,
                (unsigned short int)nField, SQL_C_CHAR,
                value.GetBufferSetLength(cbColumn+1), cbColumn+1,
                pFX->m_prs->GetFieldLength(pFX)));
            value.ReleaseBuffer();
            if (!pFX->m_prs->Check(nRetCode))
                pFX->m_prs->ThrowDBException(nRetCode);
            return;
    }
    // Undo the increments done in the IsFieldType call above
    if (pFX->m_nFieldType == CFieldExchange::outputColumn)
          --pFX->m_nFields;
    else
          --pFX->m_nParams;
    RFX_Text(pFX, szName, value, nMaxLength, nColumnType);
}
				

   MFC Technote #43 - "RFX Routines"
   MFC Encyclopedia articles on RFX in the online books