Fetching column data using IRow::GetColumns (OLE DB)(MSSQL)

In my application I am trying to read data from a VARCHAR(4000) column. Since the column is 4000 bytes, I have an application buffer which is big enough to handle it. But at the moment I have only 10 bytes of data in the column. After doing IRow->GetColumns(), not sure how to copy only 10 bytes of data. I do the following, but get all 4000 bytes, so when the data is printed, it is 10 characters of actual data padded with 3990 whitespaces.

retcode = WideCharToMultiByte(CP_UTF8, 0, (WCHAR*)pDBColumnAccess[nCol].pData, -1, (char *)pReadBuf, pDBColumnAccess[nCol].cbDataLen, NULL, NULL);

I thought pDBColumnAccess.cbDataLen would have only 10, but it has the value 4000.

How to read the exact number of bytes from the column?

Thanks.

Figured out a way for this, not sure if this is the actual solution or just a workaround.

If ANSI_PADDING is set to OFF before the table is created, padding of whitespaces after the actual data will not happen. You can see the property value "TrimTrailingBlanks" in sp_help of the table.

Thanks.

You should be setting a DBBINDING that includes DBPART_LENGTH to get the length of the field. To illustrate this, I've created some VARCHAR data in SQL Server using the following DDL:

CREATE TABLE NEWS
(
    ID INT NOT NULL,
    ARTICLE NVARCHAR(4000)
);
INSERT INTO NEWS (1, 'Today is a sunny day.');

Then I use the following ATL based C++ code sample to read the ARTICLE field. If you put breakpoints in the code, you will see it will come back with

• data.nArticleLength = 21
• data.szArticle = "Today is a sunny day."

Here's the code sample sample:

#include <stdio.h>
#include <tchar.h>
#include <windows.h>
#include <oledb.h>
#include <atlbase.h>

int _tmain(int argc, _TCHAR* argv[])
{
    HRESULT hr = S_OK;
    hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);

    // Connect to SQL Server.
    CComPtr<IDBInitialize> spIDBInitialize;
    hr = spIDBInitialize.CoCreateInstance(OLESTR("SQLNCLI"));
    CComPtr<IDBProperties> spIDBProperties;
    hr = spIDBInitialize->QueryInterface(IID_IDBProperties, (void**) &spIDBProperties);
    CComVariant varDataSource(OLESTR("InsertYourSQLServer"));
    CComVariant varCatalog(_T("InsertYourDatabase"));
    CComVariant varUserID(_T("InsertYourUserName"));
    CComVariant varPassword(_T("InsertYourPassword"));
    DBPROP rgProps[4] =
    {
        { DBPROP_INIT_DATASOURCE, DBPROPOPTIONS_REQUIRED, 0, DB_NULLID, varDataSource },
        { DBPROP_INIT_CATALOG, DBPROPOPTIONS_REQUIRED, 0, DB_NULLID, varCatalog },
        { DBPROP_AUTH_USERID, DBPROPOPTIONS_REQUIRED, 0, DB_NULLID, varUserID },
        { DBPROP_AUTH_PASSWORD, DBPROPOPTIONS_REQUIRED, 0, DB_NULLID, varPassword }
    };
    DBPROPSET propSet = {rgProps, 4, DBPROPSET_DBINIT};
    hr = spIDBProperties->SetProperties(1, &propSet);
    spIDBProperties = NULL;
    hr = spIDBInitialize->Initialize();

    // Execute the query.
    CComPtr<IDBCreateSession> spIDBCreateSession;
    hr = spIDBInitialize->QueryInterface(IID_IDBCreateSession, (void**) &spIDBCreateSession);
    CComPtr<IDBCreateCommand> spIDBCreateCommand;
    hr = spIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand, (IUnknown**) &spIDBCreateCommand);
    spIDBCreateSession = NULL;
    CComPtr<ICommandText> spICommandText;
    hr = spIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, (IUnknown**) &spICommandText);
    spIDBCreateCommand = NULL;
    hr = spICommandText->SetCommandText(DBGUID_SQL, OLESTR("SELECT ID, ARTICLE FROM NEWS"));
    DBROWCOUNT cRowsAffected = 0;
    CComPtr<IRowset> spIRowset;
    hr = spICommandText->Execute(NULL, IID_IRowset, NULL, &cRowsAffected, (IUnknown**) &spIRowset);
    spICommandText = NULL;

    // Retrieve records.
    HROW hRow = NULL;
    HROW *rghRow = &hRow;
    DBCOUNTITEM cRowsObtained = 0;
    hr = spIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &rghRow);
    while (hr == S_OK && cRowsObtained == 1)
    {
        // Fetch the ARTICLE field.
        struct
        {
            DBSTATUS dbArticleStatus;
            ULONG nArticleLength;
            char szArticle[4000 + 4];
        } data = {0};
        DBBINDING Binding = {0};
        Binding.iOrdinal    = 2;
        Binding.obValue     = sizeof(DBSTATUS) + sizeof(ULONG);
        Binding.obLength    = sizeof(DBSTATUS);
        Binding.obStatus    = 0;
        Binding.pTypeInfo   = NULL;
        Binding.pObject     = NULL;
        Binding.pBindExt    = NULL;
        Binding.dwPart      = DBPART_STATUS | DBPART_LENGTH | DBPART_VALUE;
        Binding.dwMemOwner  = DBMEMOWNER_CLIENTOWNED;
        Binding.eParamIO    = DBPARAMIO_NOTPARAM;
        Binding.cbMaxLen    = 4000 + 1;
        Binding.wType       = DBTYPE_STR;
        Binding.dwFlags     = 0;
        Binding.bPrecision  = 0;
        Binding.bScale      = 0;
        CComPtr<IAccessor> spIAccessor;
        hr = spIRowset->QueryInterface(IID_IAccessor, (void**) &spIAccessor);
        HACCESSOR hAccessor = NULL;
        hr = spIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 1, &Binding, 0, &hAccessor, NULL);
        hr = spIRowset->GetData(hRow, hAccessor, &data);
        DBREFCOUNT cRefCount = 0;
        hr = spIAccessor->ReleaseAccessor(hAccessor, &cRefCount);
        spIAccessor = NULL;

        // @@TODO: Do something with data.szArticle and data.nArticleLength
        // ...

        // Fetch next row of data.
        hr = spIRowset->ReleaseRows(1, rghRow, NULL, NULL, NULL);
        cRowsObtained = 0;
        hr = spIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &rghRow);
    }

    // Release everything
    spIRowset = NULL;
    spIDBInitialize = NULL;

    CoUninitialize();
    return 0;
}