MS SQL Server. Загрузка файлов в БД

Постановка задачи

Необходимо загрузить все файлы, определенного формата из заданной директории в таблицу базы. Рекурсивный обход директории делать не нужно. Вместе с файлом необходимо сохранить некоторые метаданные файла из файловой системы.

Загрузка файла в БД

Для того, чтобы загрузить файл в БД необходимо воспользоваться следующей командой

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 (используется для считывания данных в кодировке юникод)

Обратиться к считываемой информации можно используя конструкцию bulkcolumn. Все содержимое файла будет содержаться в одной ячейке.

-- Загрузка изображения
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, а также иметь доступ на чтение к директории из которой производится чтение данных.

Ресурсы

1. MSDN. OPENROWSET
2. Why is SQL Server Big Endian?