SQL Server BLOB

rasi · Post by **rasi** » Mon Oct 28, 2002 1:35 am

Hi,

Is is possible to user Sql Server BLOB object inside DS.

Thanks
rasi

ray.wurlod · Post by **ray.wurlod** » Mon Oct 28, 2002 9:57 pm

No.
INSIDE DataStage (which was your question) you can only work with numeric and string data types.
(What transformation would you apply to a BLOB?

)
You can use DataStage to move a BLOB, but you must encode it in some form that DataStage can use. And, of course, you must decode it once it arrives at its destination, which must be somewhere that the BLOB makes sense.

rasi · Post by **rasi** » Mon Oct 28, 2002 10:26 pm

Thanks Ray,

But how will you move the blob from SQL Server to some folder destination. Also the content which is inside is XML.

Thanks
Rasi

No.
INSIDE DataStage (which was your question) you can only work with numeric and string data types.
(What transformation would you apply to a BLOB? )
You can use DataStage to move a BLOB, but you must encode it in some form that DataStage can use. And, of course, you must decode it once it arrives at its destination, which must be somewhere that the BLOB makes sense.

csimms · Post by **csimms** » Tue Oct 29, 2002 9:43 am

Rasi,

We encountered the same problem. We needed to be able to insert and retrieve binary objects from a database. The solution that worked best for us was to write our own custom plug-in passive stage that handled the database connection via ODBC. Using a custom plug-in allowed us to implement the required input/output code needed for binary data type manipulation. Once the plug-in was completed, and since the DataStage already facilitates the movement of binary data along a link, our work was completed. The downside of writing and using a custom plug-in is that it is your responsibility to support it. I guess what I am saying here does not offer much direct help to you and your situation, but I wanted to mention to you another possible solution.

Chris Simms

Senior Systems Engineer
Extended Technologies Corporation
9708 Skillman Road
Dallas, TX 75243
csimms@xtekcorp.com
www.xtekcorp.com
214-540-4108 (Desk)
214-540-4000 (Front Desk)
214-540-4004 (Fax)

ray.wurlod · Post by **ray.wurlod** » Tue Oct 29, 2002 8:28 pm

The approach I was suggesting is, for example, to hex encode the BLOB so that each byte is represented by a pair of (printable) hex characters, and move that. This can be accomplished by a number of utilities. The same functionality is available in DataStage with the "MX0C" specification for Iconv() and Oconv() functions. Perhaps, though I have not tried this, you could CAST the BLOB as a sufficiently large VarChar when selecting it from SQL Server; this certainly works for uniqueidentifier data type in SQL Server.