Re: Insert into Image field

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: oj (nospam_ojngo_at_home.com)
Date: 02/05/05 

Date: Sat, 5 Feb 2005 00:09:49 -0800

You can use TextCopy.exe in the \bin folder.

-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder 

--
------------------
--TEXTCOPY IN
------------------
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @sql varchar(255),
 @fname varchar(100),
 @path varchar(50),
 @user sysname,
 @pass sysname
set @user='myuser'
set @pass='mypass'
--specify desired folder
set @path='c:\winnt\'
set @sql='dir ' + @path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @fname
while @@fetch_status=0
begin
 set @sql='textcopy  /s"'+@@servername+'" /u"'+@user+'" /p"'+@pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @fname + '''"'
 set @sql=@sql + ' /f"' + @path + @fname + '" /i' + ' /z'
 print @sql
 exec master..xp_cmdshell @sql ,no_output
 fetch next from cc into @fname
end
close cc
deallocate cc
go
select * from tmp
go
-------------------
--TEXTCOPY OUT
-------------------
declare @sql varchar(255),
 @fname varchar(100),
 @path varchar(50),
 @user sysname,
 @pass sysname
set @user='myuser'
set @pass='mypass,'
--specify desired output folder
set @path='c:\tmp\'
set @sql='md ' + @path
--create output folder
exec master..xp_cmdshell @sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @fname
while @@fetch_status=0
begin
 set @sql='textcopy  /s"'+@@servername+'" /u"'+@user+'" /p"'+@pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @fname + '''"'
 set @sql=@sql + ' /f"' + @path + @fname + '" /o' + ' /z'
 print @sql
 exec master..xp_cmdshell @sql ,no_output
 fetch next from cc into @fname
end
close cc
deallocate cc
set @sql='dir ' + @path + '*.bmp /c /b'
exec master..xp_cmdshell @sql
go
drop table tmp
go
-- 
-oj
"Emma" <Emma@discussions.microsoft.com> wrote in message 
news:B9F0A281-E9E1-47C4-A045-901156484760@microsoft.com...
>I am reading a binary data from file and I want to insert it into an image
> field as follows:
> Insert INTO TableName (IDField, Imagefield). I get an incorrect syntax 
> when
> I do this. How do I resolve it?
>
> Thanks

Relevant Pages

  • Re: T-SQL to write a BLOB to disk
    ... You can use TextCopy.exe in the \bin folder. ... exec master..xp_cmdshell @sql ... for select fname from tmp ... fetch next from cc into @fname ...
    (microsoft.public.sqlserver.programming)
  • Re: How to store/retrieve image/file in sql db
    ... You can use TextCopy.exe in the \bin folder. ... exec master..xp_cmdshell @sql ... for select fname from tmp ... fetch next from cc into @fname ...
    (microsoft.public.sqlserver.programming)
  • Re: t-sql to insert/update image from file
    ... -- exporting files out to folder ... exec master..xp_cmdshell @sql ... for select fname from tmp ... fetch next from cc into @fname ...
    (microsoft.public.sqlserver.programming)
  • Re: BLOB
    ... -- exporting files out to folder ... exec master..xp_cmdshell @sql ... for select fname from tmp ... fetch next from cc into @fname ...
    (microsoft.public.sqlserver.programming)
  • Re: How to create file using field from a table.
    ... -- exporting files out to folder ... exec master..xp_cmdshell @sql ... for select fname from tmp ... fetch next from cc into @fname ...
    (microsoft.public.sqlserver.server)