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


Если вы не видите ссылки на оригинал, то этот материал позаимствован с блога http://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 (см. http://msdn.microsoft.com/en-us/library/ms188046.aspx). SQL Serverные типы nchar, nvarchar соответствуют юникодовскому представлению UCS-2, которое в известном смысле можно рассматривать как подмножество UTF-16. Просто первое было в стандарте Unicode 1.1,  а второе появилось в версии 2. Подробнеесм. http://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) – см. http://ru.wikipedia.org/wiki/UTF-16, а UTF-8 SQL Server просто так не поймет - http://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 байт (см. http://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. Это уже стало классикой жанра - http://support.microsoft.com/kb/309158. Не говоря уже про http://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. Для чего оно нужно и как с ним работать, в документации не сообщается (http://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 (http://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 (6)
  1. SQLClub says:

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

  2. SQLClub says:

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

  3. SQLClub says:

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

  4. SQLClub says:

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

  5. Сергей says:

    Огромнейшее спасибо!!! То что нужно. Класс!

Comments are closed.

Skip to main content