?
Solved

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

Posted on 2005-05-10
13
Medium Priority
?
347 Views
Last Modified: 2010-03-19
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,
0
Comment
Question by:sleiman
  • 6
  • 5
11 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 13966224
>>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
0
 

Author Comment

by:sleiman
ID: 13966248
Data Type is Image.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13966257
OK back in a few mins
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:sleiman
ID: 13966289
Great.  Thanks for the help.
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 13966493
Can you give it a try ?

-- IMPORTANT NOTE
-- please make sure you have a full backup of this table data before running this code
-- check the first part BEFORE running the final delete statement

-- you'll need a cursor to loop through the images
declare c1 cursor for
      select PhotoNumber, PhotoPart, TEXTPTR(Photo)
      from <YourTable> a
      where PhotoPart = (select min(PhotoPart) from YourTable where PhotoNumber = a.PhotoNumber)
-- declare variables
declare @PhotoNumber int, @PhotoPart int, @ptrFirstChunk binary(16), @ptrNewChunk binary(16)
--- here we go ...
open c1
fetch c1 into @PhotoNumber, @PhotoPart, @ptrFirstChunk
while @@fetch_status = 0  -- loop through photos
begin
      -- get pointer to next part
      select @PhotoPart = PhotoPart, @PtrNextChunk = TEXTPTR(Photo)
      from <YourTable> a
      where PhotoPart = (
            select min(PhotoPart)
            from <YourTable>
            where PhotoNumber = A.PhotoNumber and PhotoPart > @PhotoPart
      )
      while @@rowcount > 0
      begin
            -- append binary data to the image
            UPDATETEXT <YourTable>.Photo @ptrFirstChunk NULL 0 @PtrNextChunk

            -- get pointer to next part
            select @PhotoPart = PhotoPart, @PtrNextChunk = TEXTPTR(Photo)
            from <YourTable> a
            where PhotoPart = (
                  select min(PhotoPart)
                  from <YourTable>
                  where PhotoNumber = A.PhotoNumber and PhotoPart > @PhotoPart
            )
      end
      fetch c1 into @PhotoNumber, @PhotoPart, @ptrFirstChunk
end
-- cleanup
close c1
deallocate c1

-- delete records to keep only the complete images
delete a
from <YourTable> a
where PhotoPart > (select min(Photopart) from <YourTable> where PhotoNumber = a.PhotoNumber)
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13975932
Hi sleiman,

did you try my code above ?
0
 

Author Comment

by:sleiman
ID: 14001387
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.
0
 

Author Comment

by:sleiman
ID: 14272240
Sorry for the delay.  The recommendation worked.  I will send additional questions if needed.
0
 

Author Comment

by:sleiman
ID: 14909874
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?
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 14918052
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
0
 

Author Comment

by:sleiman
ID: 14926381
Hillaire, Salut.  Good to hear from you.

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question