Импорт/экспорт блобов в файлы
Если вы не видите ссылки на оригинал, то этот материал позаимствован с блога https://blogs.msdn.com/alexejs/
А.Ш.
Задача. Лежит файл в файловой системе. Требуется закачать его в SQL Server и выпихнуть обратно в файл. Желательно, чтобы результат несильно отличался от оригинала.
В целях экономии в качестве примера будет использоваться таблица Media из поста "Введение в FILESTREAM". То, что у нее поле stream помечено как filestream, в данном случае дело десятое. Главное, что varbinary(max).
Способ 1. T-SQL.
Предлагается использовать функцию openrowset(bulk...) для импорта файла в блоб. Пример:
update media set stream = (select * from openrowset(bulk 'c:\Demo\FILESTREAM_ импорт-экспорт файла.txt', single_blob) as stream)
Скрипт 1
Функция openrowset может использоваться для загрузки данных не только в виде бинарщины, но и в виде текста. При этом вместо single_blob следует использовать single_clob или single_nclob. В данном случае нас интересует бинарный контент загружаемого файла, поскольку атрибут filestream могут иметь только varbinary(max) поля. Проверить, что контент загрузился нормально, можно, найдя и посмотрев файл, соответствующий данной ячейке filestream
рис.1
либо последующим преобразованием
select cast(stream as varchar(max)) from media where id = 1
При этом предполагается, что текстовый файл находится в той же кодировке, что и является дефолтной для неюникодовских символов в SQL Server. Например, кодовой странице ANSI 1251 соответствует Windows-коллация Cyrillic General (см. https://msdn.microsoft.com/en-us/library/ms188046.aspx). SQL Serverные типы nchar, nvarchar соответствуют юникодовскому представлению UCS-2, которое в известном смысле можно рассматривать как подмножество UTF-16. Просто первое было в стандарте Unicode 1.1, а второе появилось в версии 2. Подробнее – см. https://www.unicode.org/faq/basic_q.html#14, "Q: What is the difference between UCS-2 and UTF-16?" Поэтому если текст рис.1 был сохранен как Unicode и сохранен в своем бинарном выражении в SQL Server, там его надо конвертить как
select cast(stream as nvarchar(max)) from media where id = 1
До кучи стоит блеснуть, что порядок байт UTF-16, совместимый с архитектурой х86, является little endian в отличие, например, от SPARCов (big endian) – см. https://ru.wikipedia.org/wiki/UTF-16, а UTF-8 SQL Server просто так не поймет - https://support.microsoft.com/kb/q232580/.
Как известно, функции openrowset/openquery воспринимают в качестве строкового параметра константную строку и не позволяют передать запрос к прилинкованному серверу, или в данном случае имя импортируемого файла, как строковую переменную. Делать нечего, придется переписать Скрипт 1 в виде динамического запроса.
declare @fileName nvarchar(1000) = 'c:\Demo\FILESTREAM_импорт-экспорт файла.txt', @i int = 1
declare @cmd nvarchar(1000) = 'update media set fileName = ''' + @fileName +
''', stream = (select * from openrowset(bulk ''' + @fileName +
''', single_blob) as stream) where id = ' + cast(@i as varchar(10))
exec sp_executesql @stmt = @cmd
Скрипт 2
Теперь рассмотрим обратный процесс – данные из блоба требуется экспортнуть в файл. В SQL Server известно по меньшей мере три инструмента, чтобы забрать данные извне в SQL Server: это только что проиллюстрированная функция openrowset(bulk ...), T-SQLный оператор BULK INSERT и тула командной строки bcp. Из них симметричным в том плане, что им можно не только импортировать, но и экспортировать из SQL Server наружу является последний инструмент. Выглядит это так:
exec xp_cmdshell 'bcp "select stream from TestFS..Media where id = 1" queryout "c:\Demo\FILESTREAM_импорт-экспорт файла1.txt" -T -N'
Или то же самое в виде динамического SQL:
declare @fileName nvarchar(1000) = 'c:\Demo\FILESTREAM_импорт-экспорт файла1.txt', @i int = 1
declare @cmd nvarchar(1000) = 'bcp "select stream from TestFS..Media where id = ' + cast(@i as varchar(10)) + '" queryout "' + @fileName + '" -T -N'
exec xp_cmdshell @cmd
Скрипт 3
Имейте в виду, что при этом в результат экспорта bcp без спроса впихнет некоторую отсебятину в начало результата экспорта. Отсебятина называется префиксом. В префиксе хранится длина поля, которая нам сейчас, скажем прямо, без надобности. Для поля типа varbinary(max) под нее отводится 8 байт (см. https://msdn.microsoft.com/ru-ru/library/ms190779.aspx). Т.е. если вы сначала закачали файл в блог, тьфу, в блоб (Скрипт 1 или 2), а потом, не меняя, вытолкнули его обратно (Скрипт 3), результат будет отличаться от оригинала на 8 байт в начале файла.
рис.2
Дальнейшее зависит от того, насколько это критично по отношению к данному формату файла и от приложения, которое обучено с этим типом файлов управляться. Например, mp3 оно по барабану, а aviшнику нет:
, да и в случае jpg вместо картинки мы увидим крестик. Pdf хавает без вопросов, а Word, хоть и кочевряжится, что дескать битый формат, но если сказать Repair, тоже открывает. И т.д. Как повезет. Это не дело. На данный момент мы умеем класть файл в блоб с точностью до битика и можем убедиться, что он там так и хранится, но доставать его в столь же первозданном виде при помощи bcp не получается. Bcp порет отсебятину не со зла, а потому что пытается отформатировать результат при экспорте. Посмотреть, какой форматный файл у нее подкладывается к нашей таблице Media по умолчанию, можно так:
exec xp_cmdshell 'bcp TestFS..Media format nul -n -x -f c:\Demo\BcpFormat.xml -T'
Скрипт 4
рис.3
Возникает желание поставить PREFIX_LENGTH = 0, но нет:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]The value of attribute PREFIX_LENGTH is incorrect in xml format file for the field...
Можно, например, поиграться с атрибутом LENGTH или MAX_LENGTH, задрав их побольше. Гриву вытащили – хвост увяз: префикс уберется, но взамен появится суффикс. Все, что не будет хватать до размера файла, прописанного в LENGTH, она упорно добьет нулевыми символами (символами с кодом 0) до заданной длины. Нам оно надо? Кроме того, bcp отказывается воспринимать MAX_LENGTH > 8000:
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid field size for datatype
Получается, что либо придется ограничиться длиной файла в блобе в 8000 байт (что несерьезно), либо после экспорта производить доработку напильником на предмет обрезания префиксов/хвостов. Беда bcp в том, что она рвется разделять колонки, форматировать их по максимальной ширине значения и не может взять в толк, что иногда от нее требуется вытолкнуть наружу всего-то одно значение (что-то вроде ExecuteScalar), незатейливо 1 в 1, без привносимых форматирований и украшательств.
Теперь, когда всем стало совсем грустно, самое время вспомнить, что, кроме сравнительно недавнего xml, bcp воспринимает второй, а точнее сказать, первый формат форматного файла, который повелся еще исстари. Он представляет собой текстовый файл, где каждому экспортируемому полю соответствует строчка вида порядковый номер колонки в результате экспорта \ t тип данных \t длина префикса \t ширина колонки \t строка-терминатор (признак окончания) колонки \t порядковый номер поля в таблице SQL Server \t название поля в таблице \t коллация (в случае строковой колонки) . Когда команде bcp что-либо неясно, она начинает задавать наводящие вопросы. Например, опустим свитч –N в Скрипте 3 и выполним приведенную там команду bcp не из SQL Server через xp_cmdshell, а непосредственно в окне командной строки:
рис.4
Там, где мы соглашаемся с ее предложением, просто жмем Enter, а там, где наше мнение с ней не совпадает, например, в длине префикса, настаиваем на своем. Результат образовавшегося консенсуса она предлагает сохранить в форматном файле, который не XML, а исконный. Сохраняем и смотрим:
рис.5
Вот этот контент
10.0
1
1 SQLBINARY 0 0 "" 1 stream ""
Скрипт 5
обеспечит нам 1:1 экспорт бинарника из блоба в файл без bcpшной отсебятины:
exec xp_cmdshell 'bcp "select stream from TestFS..Media where id = 1" queryout "c:\Demo\FILESTREAM_импорт-экспорт файла1.txt" -T -f c:\Demo\BcpFormat.fmt'
или в динамическом исполнении:
declare @fileName nvarchar(1000) = 'c:\Demo\FILESTREAM_импорт-экспорт файла1.txt', @i int = 1
declare @cmd nvarchar(1000) = 'bcp "select stream from TestFS..Media where id = ' + cast(@i as varchar) +
'" queryout "' + @fileName + '" -T -f c:\Demo\BcpFormat.fmt'
select @cmd
exec xp_cmdshell @cmd
Скрипт 6
рис.6
Способ 2. CLR .
Всем давно известно, как читать/писать SQL Serverный блоб средствами ADO.NET. Это уже стало классикой жанра - https://support.microsoft.com/kb/309158. Не говоря уже про https://msdn.microsoft.com/en-us/library/a1904w6t.aspx. Нам остается взять классику, завернуть ее в хранимую процедуру и обломаться:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ReadBlob(out SqlBytes blob, SqlString fileName)
{
FileStream fs = new FileStream(fileName.ToString(), FileMode.Open);
blob = new SqlBytes(fs);
fs.Close();
SqlContext.Pipe.Send(Encoding.GetEncoding(1251).GetString(blob.Buffer).Substring(0, 4000));
}
A first chance exception of type 'System.ObjectDisposedException' occurred in mscorlib.dll
A .NET Framework error occurred during execution of user-defined routine or aggregate "ReadBlob":
System.ObjectDisposedException: Cannot access a closed file.
System.ObjectDisposedException:
at System.IO.__Error.FileNotOpen()
at System.IO.FileStream.get_Length()
at System.Data.SqlTypes.SqlBytes.CopyStreamToBuffer()
at System.Data.SqlTypes.SqlBytes.get_Buffer()
at ReadWriteBlob.ReadBlob(SqlBytes& blob, SqlString fileName)
Сначала я думал, что это происходит от того, что конструктор класса SqlBytes(Stream) просто копирует в свойство Stream указатель на fs, и если fs закрыть, он, понятно, выражает недовольство. Ладно, подумал я и скопировал по-быстрому содержание потока fs в blob.Stream. Фигушки, ошибка никуда не делась. Flush() не помогло. Думаю, это происходит потому, что он где-то внутри открывает еще какой-нибудь BinaryReader на FileStream и пытается закрыть его на этапе финализации, когда FileStream уже закрыт. Самое интересное, что из обычного консольного приложения все работает, а в SQL CLR не хочет. Забудем про FileStream при инициализации SqlBytes. К сожалению, пустой конструктор тоже не проходит:
FileStream fs = new FileStream(fileName.ToString(), FileMode.Open);
blob = new SqlBytes();
<Копирование с потока на поток>
fs.Close();
System.Data.SqlTypes.SqlTypeException: There is no buffer. Read or write operation failed.
System.Data.SqlTypes.SqlTypeException:
at System.Data.SqlTypes.SqlBytes.Write(Int64 offset, Byte[] buffer, Int32 offsetInBuffer, Int32 count)
at System.Data.SqlTypes.StreamOnSqlBytes.Write(Byte[] buffer, Int32 offset, Int32 count)
at ReadWriteBlob.CopyBytesBetweenStreams(Stream sourceStream, Stream destStream)
at ReadWriteBlob.ReadBlob(SqlBytes& blob, SqlString fileName)
Действительно, внутри SqlBytes, помимо Stream, есть еще свойство Buffer. Для чего оно нужно и как с ним работать, в документации не сообщается (https://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqlbytes.buffer.aspx), вероятно, предлагается развивать интуицию. Свойство Buffer представляет собой байтовый массив. Инициализировать его напрямую нельзя, поскольку readonly. Можно через конструктор. Методом научного тыка можно придти к выводу, что Buffer должен быть размером со Stream:
FileStream fs = new FileStream(fileName.ToString(), FileMode.Open);
byte[] b = new byte[1024];
blob = new SqlBytes(b);
fs.Read(b, 0, b.Length); blob.Write(0, b, 0, b.Length);
SqlContext.Pipe.Send("ля-ля-ля");
fs.Read(b, 0, b.Length); blob.Write(1024, b, 0, b.Length);
SqlContext.Pipe.Send("жу-жу-жу");
fs.Close();
ля-ля-ля
A .NET Framework error occurred during execution of user-defined routine or aggregate "ReadBlob":
System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed.
System.Data.SqlTypes.SqlTypeException:
at System.Data.SqlTypes.SqlBytes.Write(Int64 offset, Byte[] buffer, Int32 offsetInBuffer, Int32 count)
at ReadWriteBlob.ReadBlob(SqlBytes& blob, SqlString fileName)
Это непонятно. Если в стриме лежит многогиговый файл, зачем его весь тащить в byte[]? Память, чай, не резиновая. В принципе, в SqlBytes есть еще любопытное свойство Storage, которое может принимать enum значения StorageState: Buffer, Stream и UnmanagedBuffer. В процессе научного тыка оно всегда наблюдалось как Buffer, даже когда SqlBytes инициализировался конструктором от FileStream. Повлиять на него непосредственно нельзя, потому что оно тоже readonly. Поиск возвращает в основном древний пример на компрессию/декомпрессию блобов из книжки "Pro SQL Server 2005" (Thomas Rizzo и др.), разновидность которого встречалась в SQL Server Magazine (https://www.sqlmag.com/Article/ArticleID/95185/sql_server_95185.html), и который широко разошелся по Интернету под разными соусами. Но там блоб копируется в блоб, и с FileStreamом они благоразумно не связываются. Беда какая-то с материалами. Короче, вот нулевая итерация для преодоления информационного вакуума.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text;
public class ReadWriteBlob
{
/// <summary>
/// Функция читает содержимое файла в блоб. Пример вызова:
/// select cast(dbo.ReadBlob('c:\Demo\FILESTREAM_импорт-экспорт файла.txt') as varchar(max))
/// </summary>
/// <param name="fileName">Полное имя файла</param>
/// <returns>Блоб</returns>
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlBytes ReadBlob(SqlString fileName)
{
FileStream fs = new FileStream(fileName.ToString(), FileMode.Open);
SqlBytes blob = new SqlBytes(new byte[fs.Length]);
CopyBytesBetweenStreams(fs, blob.Stream);
fs.Close(); return blob;
}
/// <summary>
/// Процедура пишет в файл содержимое блоба. Пример вызова:
/// declare @x varbinary(max)
/// set @x = dbo.ReadBlob('c:\Demo\FILESTREAM_импорт-экспорт файла.txt')
/// exec WriteBlob @x, 'c:\Demo\FILESTREAM_импорт-экспорт файла1.txt'
/// </summary>
/// <param name="blob">Блоб</param>
/// <param name="fileName">Полное имя файла</param>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WriteBlob(SqlBytes blob, SqlString fileName)
{
FileStream fs = new FileStream(fileName.ToString(), FileMode.OpenOrCreate);
CopyBytesBetweenStreams(blob.Stream, fs);
fs.Close();
}
private static void CopyBytesBetweenStreams(Stream sourceStream, Stream destStream)
{
int bufLen = 100000; SqlBytes blob = new SqlBytes();
byte[] buffer = new byte[bufLen]; int count;
for (; ; )
{
count = sourceStream.Read(buffer, 0, bufLen);
if (count == 0) break;
destStream.Write(buffer, 0, count);
}
destStream.Flush();
}
}
Скрипт 7
Как отмечалось выше, слабое место – это строка SqlBytes blob = new SqlBytes(new byte[fs.Length]) в функции ReadBlob. Например, при размере файла 150 МБ байтовый массив такой длины он создать не может, а по-другому класс инициализировать не получается. Какие будут мысли?
select dbo.ReadBlob('c:\Temp\Book1.csv')
Msg 6532, Level 16, State 49, Line 1
.NET Framework execution was aborted by escalation policy because of out of memory.
System.Threading.ThreadAbortException: Thread was being aborted.
System.Threading.ThreadAbortException:
at ReadWriteBlob.ReadBlob(SqlString fileName)
Еще я взял в качестве эксперимента 15-килобайтный файл и попробовал его ввести/вывести в/из SQL Server 100 тыс.раз подряд. Эксперимент длился 3 мин. и благополучно завершился, что радует. Значит, по крайней мере нет утечки памяти.
use TestFS
declare @x varbinary(max)
declare @i int = 1, @n int = 100000
while @i < @n begin
set @x = dbo.ReadBlob('c:\Demo\FILESTREAM_импорт-экспорт файла.txt')
--select cast(@x as varchar(max))
exec WriteBlob @x, 'c:\Demo\FILESTREAM_импорт-экспорт файла1.txt'
print @i
set @i += 1
end