Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Binary Files Import and Export Expand / Collapse
Author
Message
Posted Thursday, August 28, 2008 12:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:07 AM
Points: 210, Visits: 160
Comments posted to this topic are about the item Binary Files Import and Export
Post #560151
Posted Thursday, August 28, 2008 5:13 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:04 AM
Points: 403, Visits: 559
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
Posted Thursday, August 28, 2008 5:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:07 AM
Points: 210, Visits: 160
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
Posted Thursday, August 28, 2008 6:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Thursday, August 28, 2008 6:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:07 AM
Points: 210, Visits: 160
Thanks alot it works perfect too.
Well as I wrote there were many ways to improve. :)
Post #560373
Posted Thursday, August 28, 2008 7:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Thursday, August 28, 2008 7:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:07 AM
Points: 210, Visits: 160
Comments are always welcome especially if they're the way to improve. :)
Post #560408
Posted Thursday, August 28, 2008 7:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:04 AM
Points: 403, Visits: 559
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
Posted Thursday, August 28, 2008 7:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:07 AM
Points: 210, Visits: 160
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
Posted Thursday, August 28, 2008 9:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:15 AM
Points: 133, Visits: 235
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse