Recently a reader asked that how he can read his image files names, which are placed in a folder of operating system and how can he insert these images from this path, directly into a database table.
Following is the script, which will help you to read a file name from operating system, and later on insert these files one by one in a database table. For following script I have placed my target picture files at D:\ SamplePictures.
(Note: How to enable xp_cmdshell CLICK HERE)
Extended stored procedure xp_cmdshell will enable us to execute execute a shell command like DIR
Following is the script, which will help you to read a file name from operating system, and later on insert these files one by one in a database table. For following script I have placed my target picture files at D:\ SamplePictures.
(Note: How to enable xp_cmdshell CLICK HERE)
Extended stored procedure xp_cmdshell will enable us to execute execute a shell command like DIR
USE AdventureWorksGOIF OBJECT_ID('MyPictures', 'U') IS NOT NULLBEGINDROP TABLE dbo.MyPicturesENDCREATE TABLE dbo.MyPictures(PicId INT IDENTITY(1, 1),PicName VARCHAR(50),OrignalFileName VARCHAR(50),CreationDate DATE,Picture VARBINARY(MAX))DECLARE @PathName VARCHAR(256), -- to hold folder path where pictures are placed@CMD VARCHAR(512), -- to hold command, we will use DIR (Directory) as command@SQLstr VARCHAR(1000)-- to hold generic query text-- Temp table to hold intermediate results
CREATE TABLE #CmdShell ( Title VARCHAR(512) )SET @PathName = 'D:\SamplePictures\*.*'SET @CMD = 'DIR ' + @PathName + ' /TC'-- Populate temp table by reading file names,through xp_cmdshell, from given folder
INSERT INTO #CmdShellEXEC MASTER..xp_cmdshell @CMD-- Delete records other then file names
DELETE FROM #CmdShellWHERE Title NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'OR Title LIKE '%<DIR>%'OR Title IS NULL-- Cursor is used to insert records, with picture file
DECLARE CUR_IMG CURSOR FAST_FORWARDFOR SELECT Title AS OrignalFileName,REVERSE(LEFT(REVERSE(Title),CHARINDEX(' ', REVERSE(Title)) - 1)) AS FileName,LEFT(Title, 10) AS CreationDateFROM #CmdShellOPEN CUR_IMGDECLARE @OrignalFileName VARCHAR(50),@FileName VARCHAR(50),@CreationDate DATETIMEFETCH NEXT FROM CUR_IMG INTO @OrignalFileName, @FileName, @CreationDateWHILE ( @@FETCH_STATUS <> -1 )BEGINSELECT @SQLstr = 'INSERT INTO dbo.MyPictures (PicName,OrignalFileName,CreationDate,Picture)SELECT ''' + @OrignalFileName + ''',''' + @FileName + ''',
'''
+ CAST(@CreationDate AS VARCHAR(50))+ ''',
*FROM OPENROWSET(BULK ''D:\SamplePictures\'+ @FileName + ''', SINGLE_BLOB) AS imagesource'
EXEC ( @SQLstr)FETCH NEXT FROM CUR_IMG INTO @OrignalFileName, @FileName,@CreationDateENDCLOSE CUR_IMGDEALLOCATE CUR_IMGGO-- drop temporary table when not required
DROP TABLE #CmdShell
Hi,
ReplyDeleteThis is an excellent article on storing images in the database. I have a couple of questions.
1) Is it possible in SQL server to create CRC values for these files using SHA256 algorithm?
2) Also is there a way to resize the images in SQL server itself and store them as thumbnails in the same stored proc that you explained?
Your help will be greatly appreciated.
Thanks