Reading Oracle BLOB columns using OLE-DB


Reading BLOB columns from a Database using OLE-DB is pretty simple, there’s an MSDN sample which shows you how to do it.

However, for Oracle BLOB columns, the Oracle OLE-DB provider doesn’t bind the length part of the output column binding in the same way that the SQL-Server OLE-DB provider does.

Oracle sets the length part to 4 bytes, indicating the length of the bind value itself, which is an ISequentialStream* pointer, rather than the size of the BLOB column contents itself.

The SQL-Server OLE-DB provider, instead, sets the length part to the size of the Image column contents. Which is convenient in this case as you can make a single call to the ISequentialStream::Read() method using the length part as the 2nd parameter.

To read the Oracle BLOB column contents, you have to make repeated calls to the ISequentialStream::Read() method using some arbitrary value as the 2nd parameter until the stream is completely read, which invariably involves multiple memory reallocations, as shown below:

  ISequentialStream* spStreamToCopyTo = reinterpret_cast<ISequentialStream*>(spSource);
  HRESULT hr = S_OK;

  ULONG ulBytesRead = 0;
  int cbBuf = 0x400000;
  BYTE* pBuf = new BYTE[cbBuf];
  do
  {
    if(SUCCEEDED(hr = spStreamToCopy->Read(pBuf, cbBuf, &ulBytesRead)))
       hr = spStreamToCopyTo->Write(pBuf, ulBytesRead, NULL);
  }
  while((cbBuf == ulBytesRead) && SUCCEEDED(hr));
  delete[] pBuf;

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

2 thoughts on “Reading Oracle BLOB columns using OLE-DB

  1. I’m looking for a good work-around. I have queries that return a lot of blobs between 1.000kb and 10.000kb. It takes up to fifteen times longer than the same query with SQL-Server Driver & Database…

    Would instead help using the LONG RAW datatype??

    thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s