SQL code to insert .gif file into blob field

[dgillz]

I have a number of .gif files which are pictures of items. I need to write an update statement to stuff the .gif file into the field called 'image' when the file name (left of the .gif extension) is equal to the field 'item_no'.

Can anyone provide a small snippet of code on how to do this?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[fredericofonseca]

Are you going to use any specific programming language to do this? if so and if you are going to use a ADO object then look at the appendchunk method of the field object.

If not then please give us more details of how you intend to do that.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[dgillz]

I want to do this in SQL.

If I were to update a char field with a SQL update query, it would look like this:

UPDATE apvenfil_sql
SET vend_name='DONS VENDOR'
WHERE vend_no=12457

I am looking for similar code, but I do not know how to SET the value of a blob field. If I need to do this in another language, my first choice would be VB.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[fredericofonseca]

So you are going to do this on Query analyser manually? or using the osql command line utility?
or within a Stored Procedure?

The way you intend to do will affect how you do it, and even if you can indeed do it that way.

If using VB then the easy solution is using ADO and the previous referred method.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[dgillz]

Stored Procedure would be my 1st choice, VB my 2nd.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[fredericofonseca]

Maybe I am not explaning myself very well.

Are you going to to this insert as a ONCE off, or as part of an application you are developing.
If an application will will the users be able to select the image file to be updated? and if an application what is the language used.

If an once off and if you have access to Either VB or VBA then I would use ADO.

You can also use the TEXTCOPY utility for it, and this can be used within a SP.
Or you can use BCP.

But the best way depends on what you are trying to do. and this is not just "I need to insert a file into a field on my table".


Example using SP and textcopy (untested)

-- 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

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com