April 9, 2009 at 4:50 am
Hi!
I must export files from database.
After some searching I stumbled upon a script to retrieve them:
DECLARE @SQLIMG VARCHAR(MAX),
@IMG_PATH VARBINARY(MAX),
@TIMESTAMP VARCHAR(MAX),
@ObjectToken INT
DECLARE IMGPATH CURSOR FAST_FORWARD FOR
SELECT FileData from DataTable
OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TIMESTAMP = 'c:\temp\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.jpg'
PRINT @TIMESTAMP
PRINT @SQLIMG
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
END
CLOSE IMGPATH
DEALLOCATE IMGPATH
My question is what to do if some of these files are partitioned in 30000 bytes large proportions?
Table looks like:
FILEID DATA BLENGTH ONO
----- ------- ------- ---
1 0x123.. 15023 1
2 0x234.. 30000 1
2 0x456.. 25678 2
How to join these 2 row witch are actually containing information about file "2"?
April 9, 2009 at 5:52 am
you could determine the max number of parts (e.g. 3) and write a left join statement to concatenate the 3 columns.
SELECT T1.FileData + coalesce(T2.FileData, '') + coalesce(T3.FileData, '')
from DataTable T1
left join DataTable T2
on T1.idcol = T2.idcol
and T1.partno = 1
and T2.partno = 2
left join DataTable T3
on T1.idcol = T2.idcol
and T1.partno = 1
and T3.partno = 3
where T1.partno = 1
I'll have to check if that works with binary data :Whistling:
-- added --
declare @t table (ColA int not null, ColB varbinary(max) not null, ColC int not null default 1)
insert @t
select 1, convert(varbinary(max),'0x123'), 1
union all select 1, convert(varbinary(max),'0x456'), 2
union all select 1, convert(varbinary(max),'0x789'), 3
union all select 2, convert(varbinary(max),'0x0D'), 1
union all select 5, convert(varbinary(max),'0x0E'), 1
union all select 6, convert(varbinary(max),'0x0F'), 1
union all select 7, convert(varbinary(max),'0x0G'), 1
union all select 7, convert(varbinary(max),'0x0H'), 2
union all select 9, convert(varbinary(max),'0x0AA'), 1
select T1.ColA
, convert(varbinary(max), (convert(varchar(max),T1.ColB)
+ coalesce( substring( convert( varchar(max),T2.ColB ) , 3, datalength( convert( varchar(max),T2.ColB ) ) ) ,'')
+ coalesce( substring( convert( varchar(max),T3.ColB ) , 3, datalength( convert( varchar(max),T3.ColB ) ) ) ,''))) as Concatenated_result
, convert(varchar(max),T1.ColB)
+ coalesce( substring( convert( varchar(max),T2.ColB ) , 3, datalength( convert( varchar(max),T2.ColB ) ) ) ,'')
+ coalesce( substring( convert( varchar(max),T3.ColB ) , 3, datalength( convert( varchar(max),T3.ColB ) ) ) ,'') as Concatenated_result_varchar
from @t T1
left join @t T2
on T2.ColA = T1.ColA
and T2.ColC = 2
and T1.ColC = 1
left join @t T3
on T3.ColA = T1.ColA
and T3.ColC = 3
and T1.ColC = 1
where T1.ColC = 1
Or use one of Jeff Modens great string functions at SSC
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 9, 2009 at 7:07 am
Thank you!
You saved my day 🙂
April 9, 2009 at 7:17 am
HTH
Just make sure you test it (so you get to see the lovely pictures as desired) 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply