MFC ODBC RFX_Binary sets Pseudo Null value in bound CByteArray, which gets included in serialization to DB

Question

I have been using RFX_LongBinary together with a CLongBinary variable to move data from an SQL Server varbinary(max) field. However, now the SQL Server is upgraded to 2019 version and the newest ODBC driver is being used (MSODBCSQL17.dll, version 17.6.1.1). However, this results in an issue that max 8000 bytes can be written. Any larger and the CRecordSet::Update() call fails.

Reading a lot online convinced me that I should really be using RFX_Binary with an accompanying CByteArray variable instead. So I changed to it and got it working - I can write large amounts of data to the database. But...now, the code for RFX_Binary sets the first byte in my CByteArray to 255 (value[0] = AFX_RFX_BYTE_PSEUDO_NULL) in the CFieldExchange::SetFieldNull case. It happens when calling CRecordSet::AddNew(). After that call, my CByteArray variable is no longer empty, but instead has size = 1, and having a value of 255 on that first byte. If I overwrite this first byte in the CByteArray with anything (such as my true payload data), then CRecordSet::Update() call will fail with a Debug Assertion.

This is a problem, because previously serialized/stored data in the field of the database did not get that first byte set to 255 (when using CLongBinary).

dbrfx.cpp:

case CFieldExchange::SetFieldNull:
    if ((pFX->m_pvField == NULL &&
        pFX->m_nFieldType == CFieldExchange::outputColumn) ||
        pFX->m_pvField == &value)
    {
        if (pFX->m_bField)
        {
            // Mark fields null
            pFX->m_prs->SetNullFieldStatus(nField - 1);
            value.SetSize(1);
            value[0] = AFX_RFX_BYTE_PSEUDO_NULL;
            *plLength = SQL_NULL_DATA;
        }
        else
        {
            pFX->m_prs->ClearNullFieldStatus(nField - 1);
            *plLength = value.GetSize();
        } 
        ...
    }

How can I either:

1. Continue to use CLongBinary and write more than 8000 bytes to varbinary(max) field in SQL Server or
2. Use CByteArray without getting a different serialization data format due to the extra byte (AFX_RFX_BYTE_PSEUDO_NULL == 255) included in my data that is written to database?

Any help is highly appreciated!

Answer 1

How interesting, after trying things for many days - unable to find a working solution - 20 minutes after posting my question above, I maybe found the answer! Posting this in case it might help someone else:

I went back to RFX_LongBinary in DoFieldExchange method.

Right after calling set.Open(CRecordSet::dynaset, NULL, CRecordSet::none) I added the following:

set.m_rgODBCFieldInfos[1].m_nPrecision = INT_MAX;
set.m_rgODBCFieldInfos[1].m_nSQLType = SQL_LONGVARBINARY;

At first I had set:

set.m_rgODBCFieldInfos[1].m_nSQLType = SQL_BINARY;

which did not work (giving that 8000 byte limitation (going above 8000 bytes gave the error "string data, right truncation"), so SQL_LONGVARBINARY seems to be the way to go for this. The number "1" in the index to m_rgODBCFieldInfos, refers to the column index of the varbinary(max) field.

Also, not setting the

set.m_rgODBCFieldInfos[1].m_nSQLType

at all, also gave the "string data, right truncation" error