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.

Подсветка синтаксиса

Каждый, кто ведет блог связаный с программированием, хотел бы, выкладывая свой код, делать его "красивым" и в соответствии с общепринятой подсветкой, аналогичной используемому редактору. Вот для таких случаев и пригодятся найденые мною online сервисы.
http://source.virtser.net/default.aspx
http://www.dpriver.com/pp/sqlformat.htm
http://poorsql.com/
http://highlight.hohli.com/
http://tohtml.com/sql/
http://quickhighlighter.com/
http://markup.su/highlighter/
http://hilite.me/

MSSQL : Повторная отправка почтовых сообщений

Нашел в архиве :)

На работе переключили почтовый сервер, и конечно забыли исправить в MSSQL.

Поменяли, и за одно повторно отправили сообщения :)

DECLARE @mailitem_id [INT]
DECLARE @rc          INT,
        @sendmailxml VARCHAR(max)
DECLARE @checkDate [DATETIME]

SELECT @checkDate = '20120326'

SELECT @mailitem_id = Min([mailitem_id])
FROM   msdb.dbo.sysmail_mailitems
WHERE  [sent_status] = 2
       AND [send_request_date] > @checkDate

WHILE @mailitem_id IS NOT NULL
  BEGIN
      SET @sendmailxml =
'<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'
+ CONVERT(NVARCHAR(20), @mailitem_id)
+ N'</MailItemId></requests:SendMail>'

    -- Send the send request on queue.
    EXEC @rc = Sp_sendmailqueues
      @sendmailxml

    IF @rc <> 0
      BEGIN
          RAISERROR(14627,16,1,@rc,'send mail')
      --GOTO ErrorHandler;
      END

    SELECT @mailitem_id = Min([mailitem_id])
    FROM   msdb.dbo.sysmail_mailitems
    WHERE  [sent_status] = 2
           AND [send_request_date] > @checkDate
           AND [mailitem_id] > @mailitem_id
END 

MSSQL : Как использовать row_number() в фильтре where

Надеюсь меня не лишат моего маленького звания MSTS....

Сегодня пока решал проблему отправки сообщений, наткнулся на то что row_number() нельзя напрямую использовать в фильтре where.

Честно говоря, когда мною использовалась нумерация, она обычно лежала где-нибудь в подзапросах или CTE, а тут хотел все и сразу, на что MS SQL сказал "неа....".

Собственно если вы попытаетесь выполнить такой код:

1. USE AdventureWorks
2. GO
3.  
4. SELECT ROW_NUMBER() OVER (
5.         ORDER BY addressline1
6.         ) AS rowNum
7.     ,addressline1
8.     ,city
9. FROM person.address
10. WHERE rowNum > 3;

* This source code was highlighted with Source Code Highlighter.

То получите такую ошибку:

Server: Msg 207, Level 16, State 1, Line
6 Invalid column name 'rowNum'

А решать проблему будем по старинке.

Вариант первый (подзапрос):

1. SELECT *
2. FROM (
3.     SELECT ROW_NUMBER() OVER (
4.             ORDER BY addressline1
5.             ) AS rowNum
6.         ,addressline1
7.         ,city
8.     FROM person.address
9.     ) AS x
10. WHERE rowNum > 3;

* This source code was highlighted with Source Code Highlighter.

Вариант второй (CTE):

1. WITH x (
2.     rowNum
3.     ,addressline1
4.     ,city
5.     )
6. AS (
7.     SELECT ROW_NUMBER() OVER (
8.             ORDER BY addressline1
9.             ) AS rowNum
10.         ,addressline1
11.         ,city
12.     FROM person.address
13.     )
14. SELECT *
15. FROM X
16. WHERE rowNum > 3;

* This source code was highlighted with Source Code Highlighter.

Linux : Ubuntu 12.04 - Пока размышления

Собственно как и многие из Вас дождался в апреле новой версии LTS 12.04 (26 апреля она вышла же если память не изменяет).

Не стал писать о первых впечатлениях, так как они были ммм.... сильно отрицательны.

Нет, не интерфейс, хотя сам до сих пор на 10.04 сижу, эта проблема для меня наименее значительна.

Проблема была в работе экрана, который при загрузке решил поморгать, с частотой где-то 2-3 раза в секунду.

К счастью или к сожалению времени разбираться у меня не было, по этой причине откатился назад на уже стабильную предыдущую версию (10.04).

А пост размышлениями назвал, так как подумываю может что-то изменилось за эти 2 месяца, и может стоит попробовать еще раз установить, например на ближайших выходных?

MSSQL : Расчет нарастающим итогом, без курсора

Обожаю классические задачи, вот и сегодня наткнулся на одну из них.

Суть была такова:

Сейчас переписываем систему оповещений о результатах выполнения процедур.

Результат кладется в таблицы и с использованием sp_send_dbmail рассылается адресатам.

Но есть одна проблема, рассылаемый объем не может превышать 1000000 байт (ограничение MS SQL).

Ладно ок, SQL не хочет, тогда надо вводить ограничение, а как?

Имперический метод себя не оправдает, так как объем вычисляется по суммарной длине строк подзапроса (про запрос см. MSDN) и ограничение по ширине строки тоже, тогда нам необходимо заранее рассчитать: а сколько байт будет наше сообщение.

Обратившись к статистике и финансовому учету (ага забавно как эти дисциплины помогают решать проблемы), можно вспомнить о таком термине как "нарастающий итог" (google расскажет о методе :) ).

Так давайте решим задачу "нарастающего итога"

Пусть у нас есть таблица только с одной ячейкой самого сообщения, например:

1. CREATE TABLE msgbodytosend
2.  (
3.      mgsstring VARCHAR(max)
4.  );

* This source code was highlighted with Source Code Highlighter.

Первый вариант - использование одного запроса.

Нам необходимо будет посчитать длины строк и их пронумеровать, в оригинале у нас нет уникального поля.

Дальше используя произведение (INNER JOIN) таблиц и суммирование длин, получаем необходимый результат.

Подзапросы я предпочитаю оборачивать в CTE выражения. На выходе получаем следующий код:

1. WITH
2. cte_msgbody AS (
3.     SELECT row_number() (ORDER BY msgstring) row
4.         , msgstring
5.         , len(msgstring) msgstring_len
6.     )
7. SELECT
8.     mb_l.row
9. ,  mb_l.msgstring
10. ,  sum(mb_r.msgstring_len) Running_Total
11. FROM cte_msgbody mb_l
12. INNER JOIN cte_msgbody mb_r ON mb_l >= mb_r
13. GROUP BY
14. mb_l.row
15.     , mb_l.msgstring
16. HAVING sum(mb_r.msgstring_len) < 1000000

* This source code was highlighted with Source Code Highlighter.

Второй вариант разбить решение на составные части.

В данном случае мы убираем CTE выражение и делаем предварительный расчет во временной таблице.

1. -- Удалим временную таблицу если вдруг она использовалась ранее в тексте
2. IF object_id('tempdb..#msgbody')
3.     DROP TABLE #msgbody;
4.  
5. -- И создадим ее за ново.
6. CREATE TABLE #msgbody (
7.     row INT identity(1, 1) PRIMARY KEY NOT NULL
8.     , msgstring VARCHAR(max) NULL
9.     , msgstring_len INT NULL
10.     , running_total INT NULL
11.     );
12.  
13. -- Заполняем таблицу
14. INSERT INTO #msgbody (
15.     msgstring
16.     , msgstring_len
17.     )
18. SELECT msgstring
19.     , len(msgstring)
20. FROM msgbodytosend
21.  
22. -- Теперь сделаем расчет
23. UPDATE mb
24. SET running_total = (
25.         SELECT sum(msgstring_len)
26.         FROM #msgbody mb_i
27.         WHERE mb_i.row <= mb.row
28.         )
29. FROM #msgbody mb
30. -- Добавим небольшое ограничение, этот кусок можно улучшить
31. WHERE (
32.         SELECT sum(msgstring_len)
33.         FROM #msgbody mb_i
34.         WHERE mb_i.row <= mb.row
35.         ) < 1000000

* This source code was highlighted with Source Code Highlighter.

Вот собственно и все, есть еще вариант решения этой задачи с использованием цикла и курсора, но мне они не кажутся столь интересными.

C# : Создание таблицы подстановки для элемента управления ComboBox, ListBoxили CheckedListBox в Windows Forms

Источник (rus)
Источник (eng)

Иногда полезно отображать данные в удобном для пользователя формате в форме Windows Forms, и при этом сохранять их в формате, требуемом в используемой программе. Например, в бланке заказа продуктов питания могут отображаться элементы меню по их имени в списке элементов. Однако таблица данных регистрации заказа будет содержать уникальные идентификаторы, представляющие продукты питания. В следующей таблице представлен пример хранения и отображения данных бланка заказа продуктов питания.

OrderDetailsTable

OrderID	ItemID	Quantity
4085	12	1
4086	13	3

ItemTable

ID	Name
12	Potato
13	Chicken

В этом сценарии одна таблица, OrderDetailsTable, содержит фактические сведения, интересующие с точки зрения отображения и сохранения. Однако с целью экономии места они представлены в неудобном для восприятия виде. Другая таблица, ItemTable, включает только данные, связанные с представлением, а именно о том, какой код какому продукту соответствует, данные о фактических заказах продуктов в ней отсутствуют.

ItemTable связывается с элементом управления ComboBox, ListBox или CheckedListBox с помощью трех свойств. Свойство DataSource содержит имя данной таблицы. Свойство DisplayMember содержит столбец данных той таблицы, который необходимо отобразить в элементе управления (название продукта). Свойство ValueMember содержит столбец данных таблицы с сохраняемыми данными (идентификатор).

Таблица OrderDetailsTable связывается с элементом управления с помощью коллекции привязок, доступных через свойство DataBindings. При добавлении объекта привязки в коллекцию свойство элемента управления связывается с конкретным элементом данных (столбцом кодов) в источнике данных (таблице OrderDetailsTable). Когда в элементе управления сделан выбор, в данной таблицей сохраняются вводимые данные.

Создание таблицы подстановок

1. Добавьте на форму элемент управления ComboBox, ListBox или CheckedListBox.


2. Произведите подключение к источнику данных.


3. Установите связь между данными в двух таблицах.


4. Задайте следующие свойства. Они могут быть заданы в коде или в конструкторе.
   
   
   
   

   Свойство	Параметр
   DataSource	Таблица, в которой содержатся сведения о том, какому коду соответствует какой элемент. В вышеприведенном сценарии это ItemTable.
   DisplayMember	Столбец таблицы источника данных, который необходимо отобразить в элементе управления. В вышеприведенном сценарии это "Name" (для задания в коде используйте кавычки).
   ValueMember	Столбец таблицы источника данных, который содержит сохраняемую информацию. В вышеприведенном сценарии это "ID" (для задания в коде используйте кавычки).



5. В процедуре вызовите метод Add класса ControlBindingsCollection для привязки свойства SelectedValue элемента управления к таблице, регистрирующей ввод формы. Кроме того, вместо кода это можно сделать в конструкторе с помощью свойства DataBindings элемента управления в окне Свойства. В вышеприведенном сценарии это OrderDetailsTable, а столбец — "ItemID".
   

   VB

   ListBox1.DataBindings.Add("SelectedValue", OrderDetailsTable, "ItemID")

   
   

   C#

   listBox1.DataBindings.Add("SelectedValue", OrderDetailsTable, "ItemID");

   
   

   J#

   listBox1.get_DataBindings().Add("SelectedValue", OrderDetailsTable, "ItemID");

   
   

MSSQL : Корректное использование транзакций в хранимых процедурах

Источник

Задача:

Гарантировать корректное использование транзакционного механизма в случаях работы со сложными процедурами.

Результат:

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

В примере показано использование точки сохранения транзакции для отката изменений, сделанных хранимой процедурой, если выполнение активной транзакции запущено до выполнения хранимой процедуры.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.objects
           WHERE name = N'SaveTranExample')
    DROP PROCEDURE SaveTranExample;
GO
CREATE PROCEDURE SaveTranExample
    @InputCandidateID INT
AS
    -- Detect if the procedure was called
    -- from an active transaction and save
    -- that for later use.
    -- In the procedure, @TranCounter = 0
    -- means there was no active transaction
    -- and the procedure started one.
    -- @TranCounter > 0 means an active
    -- transaction was started before the 
    -- procedure was called.
    DECLARE @TranCounter INT;
    SET @TranCounter = @@TRANCOUNT;
    IF @TranCounter > 0
        -- Procedure called when there is
        -- an active transaction.
        -- Create a savepoint to be able
        -- to roll back only the work done
        -- in the procedure if there is an
        -- error.
        SAVE TRANSACTION ProcedureSave;
    ELSE
        -- Procedure must start its own
        -- transaction.
        BEGIN TRANSACTION;
    -- Modify database.
    BEGIN TRY
        DELETE HumanResources.JobCandidate
            WHERE JobCandidateID = @InputCandidateID;
        -- Get here if no errors; must commit
        -- any transaction started in the
        -- procedure, but not commit a transaction
        -- started before the transaction was called.
        IF @TranCounter = 0
            -- @TranCounter = 0 means no transaction was
            -- started before the procedure was called.
            -- The procedure must commit the transaction
            -- it started.
            COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- An error occurred; must determine
        -- which type of rollback will roll
        -- back only the work done in the
        -- procedure.
        IF @TranCounter = 0
            -- Transaction started in procedure.
            -- Roll back complete transaction.
            ROLLBACK TRANSACTION;
        ELSE
            -- Transaction started before procedure
            -- called, do not roll back modifications
            -- made before the procedure was called.
            IF XACT_STATE() <> -1
                -- If the transaction is still valid, just
                -- roll back to the savepoint set at the
                -- start of the stored procedure.
                ROLLBACK TRANSACTION ProcedureSave;
                -- If the transaction is uncommitable, a
                -- rollback to the savepoint is not allowed
                -- because the savepoint rollback writes to
                -- the log. Just return to the caller, which
                -- should roll back the outer transaction.

        -- After the appropriate rollback, echo error
        -- information to the caller.
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE();
        SELECT @ErrorSeverity = ERROR_SEVERITY();
        SELECT @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
    END CATCH