среда, 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.

суббота, 23 июня 2012 г.

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

Каждый, кто ведет блог связаный с программированием, хотел бы, выкладывая свой код, делать его "красивым" и в соответствии с общепринятой подсветкой, аналогичной используемому редактору. Вот для таких случаев и пригодятся найденые мною 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/

понедельник, 11 июня 2012 г.

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 

четверг, 7 июня 2012 г.

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.



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








пятница, 1 июня 2012 г.

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

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


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


OrderDetailsTable




OrderIDItemIDQuantity
4085121
4086133


ItemTable




IDName
12Potato
13Chicken


В этом сценарии одна таблица, 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