다음을 통해 공유


Импорт/экспорт блобов в файлы

Если вы не видите ссылки на оригинал, то этот материал позаимствован с блога 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

image001

рис.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 байт в начале файла.

image003

рис.2

Дальнейшее зависит от того, насколько это критично по отношению к данному формату файла и от приложения, которое обучено с этим типом файлов управляться. Например, mp3 оно по барабану, а aviшнику нет:

image005

, да и в случае jpg вместо картинки мы увидим крестик. Pdf хавает без вопросов, а Word, хоть и кочевряжится, что дескать битый формат, но если сказать Repair, тоже открывает. И т.д. Как повезет. Это не дело. На данный момент мы умеем класть файл в блоб с точностью до битика и можем убедиться, что он там так и хранится, но доставать его в столь же первозданном виде при помощи bcp не получается. Bcp порет отсебятину не со зла, а потому что пытается отформатировать результат при экспорте. Посмотреть, какой форматный файл у нее подкладывается к нашей таблице Media по умолчанию, можно так:

exec xp_cmdshell 'bcp TestFS..Media format nul -n -x -f c:\Demo\BcpFormat.xml -T'

Скрипт 4

image007

рис.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, а непосредственно в окне командной строки:

image009

рис.4

Там, где мы соглашаемся с ее предложением, просто жмем Enter, а там, где наше мнение с ней не совпадает, например, в длине префикса, настаиваем на своем. Результат образовавшегося консенсуса она предлагает сохранить в форматном файле, который не XML, а исконный. Сохраняем и смотрим:

image011

рис.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

 

image013

рис.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

Comments

  • Anonymous
    June 09, 2009
    В предыдущей серии нашей картины рассматривались варианты импорта / экспорта файлов в BLOB-поля SQL Server

  • Anonymous
    June 12, 2009
    Настоящим постом предполагается завершить тему "Введение в FILESTREAM" – см. " Введение в FILESTREAM

  • Anonymous
    June 12, 2009
    Как известно, SQL Server еще с семерки умеет заниматься полнотекстовым поиском по своим строковым и текстовым

  • Anonymous
    June 14, 2009
    Когда-то в стародавние времена, когда выходили технологии OLE, OLE2, все думали, что в лучших традициях

  • Anonymous
    June 18, 2009
    PingBack from http://thestoragebench.info/story.php?id=9269

  • Anonymous
    August 29, 2013
    Огромнейшее спасибо!!! То что нужно. Класс!