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

WINDOWS        | WINDOWS NT

The information in this article applies to:

   The Microsoft Foundation Classes (MFC), included with:
    - Microsoft Visual C++ for Windows, versions 1.5, 1.51 and 1.52
    - Microsoft Visual C++ 32-bit Edition, versions 2.0, 2.1

SYMPTOMS

The following exception is thrown in a MFC database application:

   "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;
        }

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.

RESOLUTION

In Visual C++ version 2.2 or greater, you will not get the exception discussed in this article. Type checking has been relaxed and now you will only see a message in the Output window:

   "Warning: CString converted from SQL type <SOME_SQL_TYPE>"

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;
            }

Another alternative is to create a new RFX function that either handles the BindFieldToColumn case or calls the standard RFX function. For example, you could re-write your RFX_Text() function to be something like this:

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);

REFERENCES

For more information about Record Field Exchange (RFX) functions, please see the following references:

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