Select multiple blobs in one coloum one record

tonelm54
tonelm54 used Ask the Experts™
on
Good evening,
Ive been dealing with blobs over the past few days, and experienced several issues with my application dealing with them. I have solved it by splitting a blob file up over multiple records, so instead of having one massive blob in a record, I now have a table:-
CREATE TABLE `fileBlob` (
`fileID` BIGINT NOT NULL ,
`part` INT NOT NULL ,
`blob` TINYBLOB NOT NULL 
)

Open in new window


Which works great for uploading files into the table, but messy when downloading. Currently I call the statment and work through each row appending the blob output:-
SELECT * 
FROM `fileBlob` 
WHERE `fileID` =27
ORDER BY `part` ASC 

Open in new window


Is it possible to group all the blob data together ordered by `part` from a `fileID`, so something like:-
SELECT GROUP_CONCAT(`blob`) as blobData 
FROM fileblob
WHERE `fileID` =27

Open in new window

However the GROUP_CONCAT would obviously have to be ordered, and I dont know how to tell it to only dislay the blob data.

Any help would be appriciated.

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
> but messy when downloading
in how far? just geting the parts back to the output (appending) should do the job?
what is the issue you get?
I would not use group_concat, as it has size limit

Author

Commented:
There isnt really much of an issue, just haveing to put code in to perform the loop and append the data, would have been nice just to do it all in the SQL statment.

If group_concat has a size limit, I dont see any other way of doing apart from looping and appending!
Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
Agree with a3, but to answer your question if you ever need it, you can specify the ORDER BY in GROUP_CONCAT and suspect you want to specify separator as something different than the default comma.
SELECT GROUP_CONCAT(`blob` ORDER BY `part` SEPARATOR '') as blobData 
FROM fileblob
WHERE `fileID` =27;

Open in new window


Since your application pulls the file apart to upload into separate parts, could you not simply bring back all the data for a file:

SELECT `part`, `blob` as blobData 
FROM fileblob
WHERE `fileID` =27
ORDER BY `part`;

Open in new window


Then in the application piece the file back together and then send to client as download.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial