Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Binary Files Import and Export


Binary Files Import and Export

Author
Message
sergey.benner
sergey.benner
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 164
Comments posted to this topic are about the item Binary Files Import and Export
Bala'
Bala'
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 620
I tried the code by storing and retriving a Image File . Retival of new file by BCP command , the files are not meaning ful . Image does'nt displayed .. It seems the files are corrupted
sergey.benner
sergey.benner
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 164
What is your exact command for the retrieving of the image file and how does your data table look like?
Show me please your sp_help 'your_table' output.

thanx
JamieX
JamieX
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 437
I did something like this before. Just wanted to note how I did it as it can eliminate the use of sed grep and find (since you already are enabling xp_cmdshell). By importing the files directly using OS built in dir command, you can then find your deltas by importing again and using join to find out what has changed.

insert files
exec master..xp_cmdshell 'dir /S/b d:\temp\*.dat'

delete files where filepath is null

--then later, load up and compare again
insert #files
exec master..xp_cmdshell 'dir /S/b d:\temp\*.dat'

delete #files where filepath is null

--insert new
insert files
select fnew.filepath from #files fnew left outer join files fold on fnew.filepath = fold.filepath
where fold.filepath is null

--delete old
delete files where filepath in(
select fold.filepath from #files fnew right outer join files fold on fnew.filepath = fold.filepath
where fnew.filepath is null)
sergey.benner
sergey.benner
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 164
Thanks alot it works perfect too.
Well as I wrote there were many ways to improve. Smile
JamieX
JamieX
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 437
Yes, but please don't take this as criticism. I love these articles you guys write, and just wanted to add a note as I've done something similar before Smile
sergey.benner
sergey.benner
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 164
Comments are always welcome especially if they're the way to improve. Smile
Bala'
Bala'
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 620
CREATE TABLE files
(
--FileId INT IDENTITY(1,1),
filepath varchar(512)
)

--I had my IMAGE file path in out.txt

BULK INSERT dbo.files
FROM 'D:\SQL\SQLWorkOut\out.txt'
WITH
(BATCHSIZE = 1,
ROWTERMINATOR =''
);

select * from files

CREATE TABLE bindata
(data varbinary(max),
fileid INT IDENTITY(1,1)
)

/*
CREATE NONCLUSTERED INDEX file_id_idx ON bindata (fileid)
*/


DECLARE @filename VARCHAR(512),@sql VARCHAR(512);
DECLARE a_cur CURSOR STATIC FOR
SELECT filepath FROM files;
/*WHERE file_id >(SELECT MAX(file_id) FROM bindata);
This part is needed when you load the data NOT for the first time from the [files] table. */

OPEN a_cur
FETCH NEXT FROM a_cur INTO @filename;
WHILE (@@fetch_status=0)BEGIN
--using dynamic sql in order to use the @filename variable
SET @sql =' INSERT INTO bindata(data)
SELECT * FROM
OPENROWSET(BULK '''+ @filename+''', SINGLE_BLOB) as f;'
EXEC (@sql);
FETCH NEXT FROM a_cur INTO @filename;
END;
CLOSE a_cur;
DEALLOCATE a_cur;


SET NOCOUNT ON

DECLARE @a INT
DECLARE @i INT,
@filename VARCHAR(300),
@cmd VARCHAR(500)
SET @i = 0;
SELECT @a=max(fileid) from bindata; -- for speeding up this queryabit you may want to create and INDEX on file_id
WHILE @i != @a
BEGIN
SET @filename = 'D:\Temp\poto\'+CAST (@i AS VARCHAR)+'.jpg';
--SELECT @filename;
SET @cmd =
'bcp "SELECT data FROM test.dbo.bindata WHERE fileid='+ CAST(@i+1 AS VARCHAR)+'" queryout '+@filename+' -T -N';
EXEC master..xp_cmdshell @cmd;
SET @i=@i+1;
END;
sergey.benner
sergey.benner
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 164
Show me the contents of your bindata table
select top 1 * from bindata.


Well what I've just done for testing purposes:

CREATE TABLE data(data varbinary(max));

INSERT INTO data(data)
SELECT * FROM
OPENROWSET(BULK 'c:\tmp\pics\012.jpg', SINGLE_BLOB) as f;


declare @cmd varchar(512);
SET @cmd =
'bcp "SELECT data FROM woof.dbo.data" queryout c:\01.jpg -T -N';
EXEC master..xp_cmdshell @cmd;

and here my 01.jpg is in perfect shape no data corruption I see.
DeafEater
DeafEater
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 258
I'm also having trouble implementing this without corruption of the file after it's undergone a round trip. I am importing a text file using OPENROWSET and as far as I can see that arrives in the varbinary field perfectly. The query:

SELECT DATALENGTH(FileData) FROM TestFiles

returns a number exactly equal to the test file size.

However when I output it back to a file using the options you specify for bcp, I get a file that is 4 bytes longer than the original. The 4 extra bytes are right at the beginning of the file before the expected data starts and are as follows (in hex):

B5 42 00 00

Any ideas?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search