I am using DB2 UDB 8.1 Express Edition on Windows 2000 and I am trying to update a CLOB column using IBM OLE DB provider and ATL OLE DB templates in C++ on Windows 2000.

I am encountering a problem during select or during update.

The table was created using:

Code:
CREATE TABLE MySchema.MyTable(UserID VARCHAR(128) NOT NULL, ObjectID VARCHAR(255) NOT NULL, Data CLOB, PRIMARY KEY(UserID, ObjectID));
Following is part of the code I use:

Code:
CDataConnection dbConn;
// Using a standard IBM OLE DB2 connection string
dbConn.Open(...);

CCommand<CDynamicAccessor, CRowset, CNoMultipleResults> dbSelect;

CDBPropSet propSet(DBPROPSET_ROWSET);
propSet.AddProperty(DBPROP_IRowsetChange, true);
propSet.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE);
propSet.AddProperty(DBPROP_ISequentialStream, true);

// ASSUME the default schema is already set to 'MySchema'

CString strCommand;
strCommand = "INSERT INTO MyTable (UserID, ObjectID, Data) VALUES ('User Name', 'Object Name', NULL)";

// Insert the data (SUCCEEDS)
dbSelect.Open(dbConn, strCommand);
dbSelect.Close();

CString strSelect;
strSelect = "SELECT Data FROM  MyTable WHERE UserID = 'User Name' AND ObjectID = 'Object Name'"; 

// Select the data (FAILS)
dbSelect.Open(dbConn, strSelect);
Why would the above select fail? When I tried to get extended information it told me no additional information is available.

NOTE: This same code works with Microsoft SQL Server and Oracle OLE DB providers.

If I add

Code:
propSet.AddProperty(DBPROP_IRowsetUpdate, true);
Then IBM DB2 provider selects the data, however then it fails during SetData code.

Code:
// SUCCEEDS
dbSelect.MoveFirst();

// NOTE: column 1 is our selected Data colum (CLOB)

DBSTATUS dbStatus = DBSTATUS_E_BADSTATUS;
dbSelect.GetStatus(1, &dbStatus);
if (dbStatus == DBSTATUS_S_OK)
{
    IUnknown *pUnk;
    dbSelect.GetValue(nDataColumn, &pUnk);
    pUnk->Release();
}

// CISSHelper is an implementation of ISequentialStream
CComObject<CISSHelper> *pISSHelper;
// this call SUCCEEDS
CComObject<CISSHelper>::CreateInstance(&pISSHelper);
pISSHelper->AddRef();

pISSHelper->Write("MyData", 6, NULL);

dbSelect.SetValue(1, (IUnknown *)(ISequentialStream *)pISSHelper);
dbSelect.SetLength(1, pISSHelper->m_ulLength);
dbSelect.SetStatus(1, DBSTATUS_S_OK);

// The SetData call fails for IBM DB2 provider.  Works for SQL Server and Oracle.
if (SUCCEEDED(dbSelect.SetData()))
    dbSelect.Update();

...
All error checking and other irrelevant code was ommitted.

I tried to find OLE DB provider information for 8.1 database but was unable to locate any documentation. The best I could find was version 7 documentation that says that BLOB and CLOB data is not supported. Does version 8 not suport BLOB/CLOB types?

Let me know if I should post this question elsewhere.

Thank you.
Michael.