Binary Files Import and Export

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sergey Benner » Binary Files Import and Export

29 posts, Page 1 of 3

1 2 3 »»»

Binary Files Import and Export

Rate Topic

Display Mode

Topic Options

Author

Message

sergey.benner

Posted Thursday, August 28, 2008 12:31 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:28 PM
Points: 210, Visits: 158

Comments posted to this topic are about the item Binary Files Import and Export

Post #560151

Bala'

Posted Thursday, August 28, 2008 5:13 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:43 PM
Points: 326, Visits: 413

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

Post #560295

sergey.benner

Posted Thursday, August 28, 2008 5:18 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:28 PM
Points: 210, Visits: 158

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

Post #560297

JamieX

Posted Thursday, August 28, 2008 6:43 AM

Old Hand

Group: General Forum Members
Last Login: Friday, October 15, 2010 8:23 AM
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)

Post #560346

sergey.benner

Posted Thursday, August 28, 2008 6:58 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:28 PM
Points: 210, Visits: 158

Thanks alot it works perfect too.
Well as I wrote there were many ways to improve. :)

Post #560373

JamieX

Posted Thursday, August 28, 2008 7:20 AM

Old Hand

Group: General Forum Members
Last Login: Friday, October 15, 2010 8:23 AM
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 :)

Post #560403

sergey.benner

Posted Thursday, August 28, 2008 7:24 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:28 PM
Points: 210, Visits: 158

Comments are always welcome especially if they're the way to improve. :)

Post #560408

Bala'

Posted Thursday, August 28, 2008 7:43 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:43 PM
Points: 326, Visits: 413

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;

Post #560441

sergey.benner

Posted Thursday, August 28, 2008 7:59 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:28 PM
Points: 210, Visits: 158

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.

Post #560464

DeafEater

Posted Thursday, August 28, 2008 9:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 2:15 AM
Points: 123, Visits: 227

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?

Post #560581

« Prev Topic | Next Topic »

29 posts, Page 1 of 3

1 2 3 »»»

Permissions