PRB: "Unexpected column data types were returned from query" (123977)

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.

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

If you do not have Visual C++ 2.2 or greater and want to remove the type checking that the RFX functions provide, copy the existing RFX function into a new function and remove the the code that throws the exception. Replace the call to RFX_Text() with your new RFX_MyText() function in the DoFieldExchange() function. For example, to change the RFX_Text() function so that it doesn't do the type checking, change the code segment shown near the beginning of this article to this:

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; } By removing the strict type checking of the RFX functions, you can use functions like RFX_Text() to store SQL_DATE or SQL_TIMESTAMP column data in strings rather than structures.

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

PRB: "Unexpected column data types were returned from query" (123977)

SYMPTOMS

CAUSE

RESOLUTION

REFERENCES