asked on

How do I combine BLOB object from two different rows into one?

I have SQL table with a BLOB object. The BLOB contains an 8K image. This is due to a limitation in getting a larger chunk generated from the source. The table also contains an identifier for each BLOB. So it is possible that an image could spread into 2 or three different rows. These are small images.

I need to find a way to merge these object into a single row. For example:

I have row1 and row2 each with an 8k image.

PhotoNumber PhotoPart Photo
Row1 1000 0 Binary
Row2 1000 1 Binary

I need to find a way to locate these two rows based on the PhotoNumber and then merge the two photos together into one row with the same PhotoNumber (1000) in this case. Row2 will also need to be deleted.

Can this be setup in a trigger so that anytime a multi-part photo hits the system, it gets merged into one row?

Thanks,

Hilaire

>>I have SQL table with a BLOB object. <<
What is the datatype currently used ?
if it's varbinary, then you are bound by the limits of this datatype, ie 8000 bytes

If it's image datatype, then you can concatenate the chunks using the TEXTPTR and UPDATETEXT functions

This should give you a good start.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_6i2c.asp

I can give sample code if you need it, but please check the datatypes first

sleiman

ASKER

Data Type is Image.

Hilaire

OK back in a few mins

sleiman

ASKER

Great. Thanks for the help.

ASKER CERTIFIED SOLUTION

Hilaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hilaire

Hi sleiman,

did you try my code above ?

sleiman

ASKER

Yes I did. It seems to have merged the rows as expected. I am evaluating the results which is to view the images as a whole. It is taking me some time. I can't figure out how to view image in SQL. I tried Access and it does not show the image.

In any case. I have a follow up question: based on your code, I should be able to run this script on a scheduled basis. Right? These rows would be building up throughout the day and I need to keep the merge process as frequent as possible. The user will ultimatly be viewing them after upload.

Any ideas? Recommendations?

I will continue to work on a way to validate the image result and let you know.

Thanks for your help.

sleiman

ASKER

Sorry for the delay. The recommendation worked. I will send additional questions if needed.

sleiman

ASKER

Hilaire, I have a follow up question:

It seems that after testing the script, the result of all images is the same. What I mean is that the scripts seems to be doing things right as far as identifying the unique images and the photoparts assocaited with them. However,when I export the images to .jpg, they all seem to have the same image. Although the .jpg files were generated from the different records in the table.

Any ideas?

Hilaire

Sleiman,
for the moment I can't think of any reason why the code above would fail.
The loop logic seems ok and works for me (tested on a small set of images).

Would you mind giving more details on how you export the images back to file system ?

I'll be glad to help more if I can.

Hilaire

sleiman

ASKER

Hillaire, Salut. Good to hear from you.

Is there a way to send you sample images? email?