On one of the projects we are working on, there was a need to integrate with Vantive (aka Peoplesoft), which was to be done by using a connector to access the CRM's Oracle database. All fine and good.
However, we did ran into a nasty issue: Attachments. As it turns out, Vantive allows the users to attach documents (say, a word document or an excel spreadsheet) to things like service orders. As part of the integration process we were building, we needed to grab the file location (path) of the file that had been attached and send it as part of a message to another application. So we had to dive into Vantive, only to find that the SW_ATTACHMENT table contained the links and attachments, allright, but not in the way we expected it.
The problem is, what Vantive stores in the table is not just the original path to the file, since it also allows the user to embed the actual document besides just linking to it. As it turns out, what Vantive actually stores is a BLOB containing an OLE Structured Storage file (and using one of the older OLE formats as a Compound File I believe).
We spent some days breaking up the BLOBS to see how we could manipulate them, and finally ended up doing something like this yesterday, which works, sort of:
- Extract the BLOB from the database and save it to a temporary file
- Use StgOpenStorage() to crack it open and get a reference to IStorage to manipulate it
- Look for a stream called "\x1Ole10Native" within the storage, and then open it using IStorage::OpenStream()
- Read the contents of the stream into a buffer, and then look for the byte sequence {0x01 0x00 0x01 0x00}. If we find it, then just after it we can read the rest of the stream as an UTF-7 encoded string. This is the path to the linked file.
In all honesty, this sounds like a fairly brutal hack, but it does the trick for now and only took a few hours to discover :). The problem mostly is that we are not very familiar with structured storage of exactly how Vantive packed the data in it, so, if anyone has any tips that could helps us clean up the code (like what the significance of the "\x1Ole10Native" stream is, or what the format of it is), I'd appreciate it.
