Постановка задачи
Необходимо загрузить все файлы, определенного формата из заданной директории в таблицу базы. Рекурсивный обход директории делать не нужно. Вместе с файлом необходимо сохранить некоторые метаданные файла из файловой системы.Загрузка файла в БД
Для того, чтобы загрузить файл в БД необходимо воспользоваться следующей командойOPENROWSET(BULK 'data_file' , { SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } )
В завиcимости от параметра, данная конструкция возвращает следующие типы данных:- varbinary(max) - SINGLE_BLOB (рекомендуется для чтения XML-файлов)
- varchar(max) - SINGLE_CLOB (используется для считывания ASCII файлов. Использует кодировку текущей базы данных)
- nvarchar(max) - SINGLE_NCLOB (используется для считывания данных в кодировке юникод)
-- Загрузка изображения select bulkcolumn from openrowset(bulk '\\share\icon.png', single_blob) as img
Загрузим теперь текстовый файл. Я скачал первый том произведения "Война и мир".
select bulkcolumn from openrowset(bulk '\\share\Vojna i mir. Tom 1.txt', single_clob) as img select bulkcolumn from openrowset(bulk '\\share\Vojna i mir. Tom 1.txt', single_nclob) as imgРезультат первого запроса некорректно разобрал русские символы, а второй вообще свалился с ошибкой ("SINGLE_NCLOB requires a UNICODE (widechar) input file. The file specified is not Unicode.").
Откроем блокнот и поменяем кодировку файла. Один файл сохраним в кодировки ANSI, второй сохраним в кодировке "Юникод Big Endian" -- UTF16-BE, именно такую кодировку поддерживает MS SQL Server. После загрузки файлов, получим корректные результаты.
Получение информации о файлах в директории
Получить всё содержимое файловой директории можно используя недокументированную команду master.sys.xp_dirtree, которая принимает следующие параметры
- directory - имя исследуемой директории;
- depth - определяет сколько уровней вложенных директорий необходимо отобразить. Параметр по умолчанию 0 отображает все директории;
- file - определяет отображать ли файлы в каждой директории. По умолчанию 0 не отображает файлы.
Следующая команда возвращает содержимое директории \\Share как файлы, так и вложенные директории. Значение 1 в колонке depth значит, что файл находится в указанной директории
exec master.sys.xp_dirtree '\\Share\', 0, 1;
Ниже приводится код загрузки содержимого директории, отфильтрованного по расширению png во временную таблицу userLogo
declare @folder nvarchar(255) = '\\Share\Files\'
declare @sql nvarchar(max);
declare @fileName nvarchar(255);
declare @filePath nvarchar(500);
declare @files table (
fileName nvarchar(255),
depth tinyint,
isFile bit
);
if object_id('tempdb..#usersLogo') is not null
drop table #usersLogo;
create table #usersLogo (
id int identity,
fileName nvarchar(255),
fileContent varbinary(max)
)
set @sql = 'exec master.sys.xp_dirtree ''' + @folder + ''' , 0, 1;';
insert into @files
exec sp_executesql @sql;
select * from @files
declare fileCursor cursor
for select fileName from @files
where isFile = 1 and depth = 1 and fileName like '%.png'
open fileCursor
fetch next from fileCursor into @fileName
while (@@fetch_status = 0)
begin
set @sql = '
insert into #usersLogo(fileName, fileContent)
select
$fileName,
(select bulkcolumn from openrowset(bulk $filepath, single_blob) t )';
set @filePath = @folder + @fileName
set @sql = replace(@sql, '$fileName', '''' + @fileName + '''' );
set @sql = replace(@sql, '$filePath', '''' + @filePath + '''' );
print @sql
exec sp_executesql @sql;
fetch next from fileCursor into @fileName
end
select * from #usersLogo
Результаты выполнения приведенного выше скрипта
Права доступа
Для операции загрузки файла в базу данных, необходимо обладать правами на уровне сервера bulkadmin, а также иметь доступ на чтение к директории из которой производится чтение данных.
