Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 22, 2006

Upload multiple files to VarBinary column in SQL Server 2005

By Muthusamy Anantha Kumar aka The MAK

This article discusses how to upload multiple image or text files to the SQL Server database varbinary column.

Pre-requisite

a.  Make sure xp_cmdshell is enabled. If it is not enabled, execute the following command.

use master
go
sp_configure 'xp_cmdshell',1
go
reconfigure with override
go

b.  Make sure to create a table with a varbinary(max) column, since the image column is not going to be supported in future versions. Also, make sure at least one column in the table can hold the value of the filename.

set quoted_identifier off
go
use [master]
go
/****** object:  database [test]    script date: 09/10/2006 22:07:03 ******/
if  exists (select name from sys.databases where name = N'test')
drop database [test]
go
create database [test]
go
use [test]
/****** object:  table [dbo].[myimage]    script date: 09/10/2006 21:55:46 ******/
if  exists (select * from sys.objects 
   where object_id = object_id(N'[myblob]') and type in (N'u'))
drop table [myblob]
go
create table [myblob] (
[id] int identity(1,1),
[image name] varchar(100),
[blob] varbinary(max))
Go

Let us assume that we want to upload all the .bmp files from the C:\Windows folder to the table “myblob” in the schema “dbo” on the “test” database.

Step 1

Execute the following script [using copy and paste or download the usp_uploadfiles.sql file]. This creates a stored procedure, usp_uploadfiles, on the master database so that it can be executed and called for any database.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[usp_uploadfiles]    
   Script Date: 09/10/2006 23:33:34 ******/
IF  EXISTS (SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'[dbo].[usp_uploadfiles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_uploadfiles]
go
set quoted_identifier off
go
create procedure usp_uploadfiles 
@databasename varchar(128),
@schemaname varchar(128),
@tablename varchar(128),
@FileNameColumn varchar(128),
@blobcolumn varchar(128),
@path varchar(500), 
@filetype varchar(10),
@printorexec varchar(5) = 'print'
as
set nocount on
declare @dircommand varchar(1500)
declare @insertquery varchar(2000)
declare @updatequery varchar(2000)
declare @count int
declare @maxcount int
declare @filename varchar(500)
set @count=1
set @dircommand = 'dir /b '+@path+@filetype
create table #dir (name varchar(1500))
insert #dir(name) exec master..xp_cmdshell @dircommand
delete from #dir where name is NULL
create table #dir2 (id int identity(1,1),name varchar(1500))
insert into #dir2 select name from #dir
--select * from #dir2
set @maxcount = ident_current('#dir2')
while @count <=@maxcount
begin
set @filename =(select name from #dir2 where id = @count)
set @insertquery = 'Insert into ['+@databasename+'].['+@schemaname+'].['+@tablename+'] 
   ([' +@filenamecolumn +']) values ("'+@filename+'")'
set @updatequery = 'update ['+@databasename+'].['+@schemaname+'].['+@tablename+'] 
   set ['+@blobcolumn+'] = 
   (SELECT *    FROM OPENROWSET(BULK "'+@path+@filename+'", SINGLE_BLOB)AS x ) 
   WHERE ['+@filenamecolumn +']="'+@filename+'"'
if @printorexec ='print'
begin
print @insertquery
print @updatequery
end
if @printorexec ='exec'
begin
exec (@insertquery)
exec (@updatequery)
end
set @count = @count +1
end
go

This procedure accepts the following parameters:

@databasename = Name of the database where the schema and table

exist.

@schemaname = Schema of the database where the table exists

@tablename = Name of the table where files are going to be

uploaded

@FileNameColumn = Name of the column in the table where the file

name is going to be stored

@blobcolumn = The actual varbinary(max) column where the file is going to be

stored as blob data

@path = Path of all the files that are suppose to be

uploaded. Example “C:\Windows\”

@filetype = Type of file you want to upload. Example

“*.jpeg”

@printorexec = if “Print” is passed as a parameter it will

generate and display the commands. If “Exec” is

passed as a parameter it will execute the

command directly--meaning upload all the files.

Step 2

Let us execute this stored procedure by passing the following parameters as shown below.

Exec master..usp_uploadfiles 
@databasename ='test',
@schemaname ='dbo',
@tablename ='myblob',
@FileNameColumn ='Image Name',
@blobcolumn = 'blob',
@path = 'c:\windows\',
@filetype ='*.bmp',
@printorexec ='print'

This will generate all the commands needed for creating a row for each file and updating the row with the proper file as shown below.

Insert into [test].[dbo].[myblob] ([Image Name]) values ("Blue Lace 16.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\Blue Lace 16.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Blue Lace 16.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Coffee Bean.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\Coffee Bean.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Coffee Bean.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("FeatherTexture.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\FeatherTexture.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="FeatherTexture.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Gone Fishing.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\Gone Fishing.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Gone Fishing.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Greenstone.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\Greenstone.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Greenstone.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Prairie Wind.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\Prairie Wind.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Prairie Wind.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Rhododendron.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\Rhododendron.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Rhododendron.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("River Sumida.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\River Sumida.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="River Sumida.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Santa Fe Stucco.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\Santa Fe Stucco.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Santa Fe Stucco.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Soap Bubbles.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\Soap Bubbles.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Soap Bubbles.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Zapotec.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    
 FROM OPENROWSET(BULK "c:\windows\Zapotec.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Zapotec.bmp"

Step 3

Let us execute this stored procedure by passing the following parameters as shown below.

Exec master..usp_uploadfiles 
@databasename ='test',
@schemaname ='dbo',
@tablename ='myblob',
@FileNameColumn ='Image Name',
@blobcolumn = 'blob',
@path = 'c:\windows\',
@filetype ='*.bmp',
@printorexec ='exec'

This will create one row for each file in the folder based on the file type specified and upload the corresponding files.

A successful upload can be verified by executing the following query:

Select * from [test].[dbo].[myblob]

The result of the above query is displayed in Fig 1.0.


Fig 1.0

Conclusion

This article has examined how to upload multiple image or text files to SQL server database varbinary column.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date