среда, 27 июня 2012 г.

MSSQL : Преобразование varbinary в hex строку и обратно

На днях столкнулся с интересной особенностью 1С.
Нужно было передать в 1С двоичные данные (varbinary), но как говорится все проблемы на стыке систем. 1С восприняла данные как объект, каковыми они не являлись. Памятуя о том что 1С любит строковые данные, я начал пытаться преобразовать их в строку. И вот что у меня получилось на выходе:
  1. Использование не документированных функций [sys].[fn_varbintohexstr] и [sys].[fn_varbintohexsubstring] Если стоит простое преобразование, то можно воспользоваться [sys].[fn_varbintohexstr] , она принимает только один параметр: бинарную переменную.
    Вот ее полное описание.

    1. CREATE FUNCTION [sys].[fn_varbintohexstr]
    2. (
    3.   @pbinin varbinary(max)
    4. )
    5. returns nvarchar(max)
    6. as
    7. begin
    8.   return sys.fn_varbintohexsubstring(1,@pbinin,1,0)
    9. end
    * This source code was highlighted with Source Code Highlighter.
    Как вы видете функция [sys].[fn_varbintohexstr] является оберткой для [sys].[fn_varbintohexsubstring], и вызывает ее всегда с одними и темиже параметрами.
    [sys].[fn_varbintohexsubstring], в свою очередь имеет более продвинутый функционал. Например, позволяет преобразовывать в строку не всю последовательность, а некую "подстроку". К таким выводам я пришел после просмотра заголовочной части:

    1. CREATE FUNCTION [sys].[fn_varbintohexsubstring] (
    2.    @fsetprefix bit = 1   -- добавлять '0x' в вывод
    3.   ,@pbinin varbinary(max-- входной binary поток
    4.   ,@startoffset int = 1  -- начальное смещение
    5.   ,@cbytesin int = 0    -- Длина входной части, 0 означает до конца строки
    6. )
    * This source code was highlighted with Source Code Highlighter.
    Остается только проблема обратного преобразования, дело в том что в SQL нет встроенной функции для такого действия. Точнее не было, в MS SQL 2008 есть, но об этом чуть позже.
    Со своими коллегами я договорился что им будут отдавать строку вида 0x0123456789, соответственно и мне они будут отдавать уже не в виде строки, а в виде binary, например так:

    1. INSERT INTO [tab] ([ColBin])
    2. VALUES (0x0123456789)
    * This source code was highlighted with Source Code Highlighter.
    В общем на этом и можно было бы и закончить, если бы не было интересно. Я прекрасно понимал что какой-то выход должен быть, и он был найден.
  2. Использование XML запроса. Вот сразу табличный пример использования:

    1. ;WITH [cte_d] AS (
    2.   SELECT [ColBin], [sys].[fn_varbintohexstr]([ColBin]) [ColChar] FROM [dbo].[tab]
    3. )
    4. select
    5.   cast('' as xml).value('xs:hexBinary( substring(sql:column("ColChar"), 3) )', 'varbinary(max)') [ColBin_xml]
    6. '0x' + cast('' as xml).value('xs:hexBinary(sql:column("ColBin") )', 'varchar(max)') [ColChar_xml]
    7. ,  [ColBin]
    8. ,  [ColChar]
    9. FROM [cte_d]
    * This source code was highlighted with Source Code Highlighter.
    В CTE выражении я просто готовлю даные, для дальнейшего использования. Самое интересно начинается в SELECT:
    • В поле [ColBin_xml] мы плучаем солонку [ColChar] из [cte_d] - команда sql:column("ColChar")
    • Дальше берем подстроку от этой колонки начиная с 3-го знака (отрезаем "0x") - функция substring(,,)
    • Заставляем xml процессор считать полученую строку как hexBinary
    • А в момент возврата данных они интерпретируются как varbinary(max)
    Аналогичным образом работает и прямое преобразование из binary в varchar (поле [ColChar_xml])
    • Берем колонку [ColBin] sql:column("ColBin")
    • Заставляем процессор считать ее как hexBinary
    • И возвращаем как varchar(max)
    • И по желанию можно добавить в начало строки "0x"
  3. Использование MS SQL 2008 Ура, Microsoft реализовал преобразование в рамках функции convert :)
    Если у Вас MS SQL 2008 и выше, то вы можете воспользоваться функцией convert. В этом случае она будет принимать 2 или 3 аргумента.
    CONVERT ( тип_данных [ ( длина ) ] , выражение [ , стиль ] )
    При конвертировании из бинарного представления в строковый мы можем воспользоваться одним из 3-х стилей
    • 0 - преобразование в соответствии с ASCII кодами (используется по умолчанию)
    • 1 - текстовое представление с добавлением "0x" в начале строки, например так "0x0123456789"
    • 2 - текстовое представление без добавленя "0x" в начале строки, например так "0123456789"

    1. DECLARE @hexbin varbinary(max);
    2. SET @hexbin = 0x48656C6C6F20576F726C64212121;
    3.  
    4. SELECT 1, CONVERT(varchar(MAX), @hexbin)
    5. union
    6. SELECT 2, CONVERT(varchar(MAX), @hexbin,1)
    7. union
    8. SELECT 3, CONVERT(varchar(MAX), @hexbin,2)
    * This source code was highlighted with Source Code Highlighter.
    результат
    ----------------------------------------------------
    style       result
    ----------- ----------------------------------------
    0           Hello World!!!
    1           0x48656C6C6F20576F726C64212121
    2           48656C6C6F20576F726C64212121
    При конвертировании в обратную сторону ( из hexString в binary) у нас теже 3 стиля, которые задают условие преобразования.

    1. DECLARE @hexstring varchar(max);
    2. SET @hexstring = '0x48656C6C6F20576F726C64212121';
    3.  
    4. SELECT 0 style, CONVERT(varbinary(MAX), @hexstring) result
    5. union
    6. SELECT 1, CONVERT(varbinary(MAX), @hexstring,1)
    * This source code was highlighted with Source Code Highlighter.

    1. DECLARE @hexstring varchar(max);
    2. SET @hexstring = '48656C6C6F20576F726C64212121';
    3.  
    4. SELECT 0 style, CONVERT(varbinary(MAX), @hexstring) result
    5. union
    6. SELECT 2, CONVERT(varbinary(MAX), @hexstring,2)
    * This source code was highlighted with Source Code Highlighter.
    результат
    -----------------------------------------------------------------------------
    style       result
    ----------- -----------------------------------------------------------------
    0           0x307834383635364336433646323035373646373236433634323132313231
    1           0x48656C6C6F20576F726C64212121
    -----------------------------------------------------------------------------
    style       result
    ----------- -----------------------------------------------------------------
    0           0x34383635364336433646323035373646373236433634323132313231
    2           0x48656C6C6F20576F726C64212121
Замечание! Если при обратном преобразовании строка не будет соответствовать маске (стили 1 и 2) то SQL будет генерировать ошибку.
Msg 8114, Level 16, State 5, Line 1
Ошибка при преобразовании типа данных varchar к varbinary.
Отправить комментарий