Содержимое письма при таком способе приходится формировать plain-текстом, а рассылать почту либо через xp_sendmail, либо (более гибко) через почтовый COM-объект (например, CDO.Message), инстанциируя и управляя им через SQL-обертки для работы с OLE sp_OAxxxx.
И то, и другое работает, пока вам хватает выразительных средств плейнтекста, иерархичность ваших данных — околонулевая, и отчет потребляется исключительно олдскульным техническим пиплом, который
+-----------+--------------+--------------+
| АБСОЛЮТНО | НЕ ВИДИТ ПРО | БЛЕМ В ТАКОМ |
| ОФОРМЛЕНИ | И СЛУЖЕБНОГО | ВЫВОДА <EOT> |
+-----------+--------------+--------------+
Что делать, если подобный формат начинает напрягать, а регистрировать на сервере свои компоненты, или «выныривать» из уровня БД на уровень приложения для отправки чего-то более красивого ну очень не хочется:
Часто письма все-таки нужно делать форматированными (скажем, если отсылается кусок таблицы аудита), и отсылать в HTML, хотя бы в минимальном (корпоративном) дизайне — и вот тут уже начинается неприятная рутина — формировать построчно HTML средствами T-SQL очень утомительно, особенно, если разных типов почтовых отчетов несколько, структура отчетов — иерархическая, а дизайн — общий, и CSS в нем — развесистый.
В мире уровня приложений для этого еще 20 лет назад придумали удивительный диполь XML/XSL, такой стройный, что описание синтаксиса последнего его компонента еле удерживается в голове на время между чтением примера из MSDN и написанием элемента собственного шаблона.
Ну да ладно — в конце концов, иерархическую информацию тоже представлять в XML гораздно удобнее, и MS SQL нативно делает это очень неплохо последние 15 лет.
Итак, до красивых писем рукой подать — остается лишь найти способ присобачить XSL-трансформацию к этому велосипеду.
К сожалению, из коробки MS SQL этого делать не умеет, и поэтому наш велосипед будет содержать еще одно OLE-колесо.
Покопавшись в памяти и в Интернете, вспоминаем, что у нас есть Microsoft.XMLDOM, который умеет скрещивать ежа и ужа посредством вызова метода transformNode, и вроде бы наша задача — грамотно все инстанциировать, забрать результат, обработать возможные ошибки, и все задестроить, чтобы не течь памятью.
Мы на верном пути, и sp_OACreate, sp_OAMethod, sp_OAGet/SetProperty и sp_OADestroy нам помогут, но есть одна маленькая деталь — если метод возвращает указатель, то все хорошо, а если скаляр — то все плохо, т.к. строковый скаляр не может быть длиннее чем фундаментальная константа в 8кБ. С этим ограничением наш пример останется навек учебным — так как результирующий документ в 4(8) тысяч символов в наш век — это смех, да и только.
Мы прошли длинный лабиринт, и в шаге от выхода, уже видет свет, но бамс! — выход закрыт решеткой. Нет никакой возможности получить из OLE-обертки строку, длиннее чем в 8К. Попытка указать в качестве принимающего параметра тип (n)varchar(MAX) приводит к какой-то невнятной OLE-ошибке.
Понимаем, что нам каким-то образом нужно что-то, что возвращает не скаляр, а указатель на результат (т.к. указатель для SQL — это просто число). Но заставить метод transformNode отдать указатель нет никакой возможности. Отчаившись, лезем в MSDN (или как его там в онлайн), и видим, что за последние 15 лет XMLDOM эволюционировал — MS теперь предлагает метод transformNodeToObject — БИНГО!!! Метод принимает указатель на поток, в который он выливает содержимое документа!
Дальше просто — создаем объект потока, передаем методу, говорим ему вылить документ в поток.
В итоге — в потоке у нас — документ. Как его вытащить в скаляр? Тоже несложно — вычерпать море кружками вычитать чанками, не превышающими 8Кбайт, и склеить из чанков результат в varchar(MAX).
Не забываем, что перед тем, как все вычитать, нужно а) считать из потока его текущий размер — это будет количество символов, которые нам нужно из него получить, и б) поставить указатель чтения потока на начало. Иначе читать будет нечего.
Ну и после всего, нужно аккуратно прибрать за собой. Желательно, чтобы все, что было создано, было и уничтожено — независимо от того, была ошибка или нет, и если была, то на какой фазе произошла.
Вот что в итоге получилось:
CREATE FUNCTION [dbo].[f_Helper_XSLTransform]
(
@XMLData XML,
@XSLTemplate XML
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
IF @XMLData IS NULL OR @XSLTemplate IS NULL
RETURN 'XML or XSL data is NULL'
DECLARE @OLEActionName VARCHAR(128),
@PropOrMethodName VARCHAR(128)
DECLARE @hr INT,
@dummy INT,
@ObjXML INT,
@ObjXSL INT,
@ObjStream INT,
@BuffSize INT
DECLARE @Result NVARCHAR(MAX) = N'',
@Chunk NVARCHAR(4000), -- VVVV should match VVVV
@ChunkCharSize INT = 4000, -- ^^^^^^^^^^^^^^^^^^^^^^
@ResultBuff NVARCHAR(MAX) = N'' -- chunk concat buffer
-- create XMLDOM object for @XMLData
SET @OLEActionName = 'sp_OACreate'
SET @PropOrMethodName = 'Microsoft.XMLDOM'
EXEC @hr = sp_OACreate @PropOrMethodName, @ObjXML OUT
IF @hr <> 0
GOTO FUN_ERROR
-- create XMLDOM object for @XSLData
EXEC @hr = sp_OACreate @PropOrMethodName, @ObjXSL OUT
IF @hr <> 0
GOTO FUN_ERROR
-- create ADODB.Stream object as transformation buffer
SET @PropOrMethodName = 'ADODB.Stream'
EXEC @hr = sp_OACreate @PropOrMethodName, @ObjStream OUT
IF @hr <> 0
GOTO FUN_ERROR
-- load XML data
SET @OLEActionName = 'sp_OAMethod'
SET @PropOrMethodName = 'LoadXML'
EXEC @hr = sp_OAMethod @ObjXML, @PropOrMethodName, @dummy OUT, @XMLData
IF @hr <> 0
GOTO FUN_ERROR
-- load XSL data
EXEC @hr = sp_OAMethod @ObjXSL, @PropOrMethodName, @dummy OUT, @XSLTemplate
IF @hr <> 0
GOTO FUN_ERROR
-- open the stream
SET @PropOrMethodName = 'Open'
EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, NULL
IF @hr <> 0
GOTO FUN_ERROR
-- trying to do XSL transformation, using the stream as the receiver to work around 4/8K limitation
SET @PropOrMethodName = 'TransformNodeToObject'
EXEC @hr = sp_OAMethod @ObjXML, @PropOrMethodName, NULL, @ObjXSL, @ObjStream
IF @hr <> 0
GOTO FUN_ERROR
-- get the size of the stream to read back
SET @OLEActionName = 'sp_OAGetProperty'
SET @PropOrMethodName = 'Size'
EXEC @hr = sp_OAGetProperty @ObjStream, @PropOrMethodName, @BuffSize OUT
IF @hr <> 0
GOTO FUN_ERROR
-- re-position the stream to the head..
SET @OLEActionName = 'sp_OASetProperty'
SET @PropOrMethodName = 'Position'
EXEC @hr = sp_OASetProperty @ObjStream, 'Position', 0 -- offset = 0
IF @hr <> 0
GOTO FUN_ERROR
-- ..and then read chunk by chunk
SET @OLEActionName = 'sp_OAMethod'
SET @PropOrMethodName = 'ReadText'
WHILE @BuffSize > @ChunkCharSize
BEGIN
-- read chunk
EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, @Chunk OUT, @ChunkCharSize
IF @hr <> 0
GOTO FUN_ERROR
-- append it to the accumulated buffer contents..
SET @ResultBuff += @Chunk
-- ..and correct the char counter by the # of chars retrieved
SET @BuffSize -= @ChunkCharSize
END
-- read the last chunk
EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, @Chunk OUT
IF @hr <> 0
GOTO FUN_ERROR
-- append the last chunk to the buffer
SET @ResultBuff += @Chunk
-- close the stream
SET @PropOrMethodName = 'Close'
EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, NULL
IF @hr <> 0
GOTO FUN_ERROR
-- everything is ok, copying buffer to result
SET @Result = @ResultBuff
-- resulting doc is in @Result, cleaning up and exiting..
GOTO OLE_RELEASE
FUN_ERROR:
SET @Result = '#### Error ' + CONVERT(VARCHAR, CONVERT(VARBINARY(4), @hr)) + ', Action ' + @OLEActionName + ', Method/Property ' + @PropOrMethodName + ' ####'
-- fall through OLE release
OLE_RELEASE:
-- destroying XML..
SET @OLEActionName = 'sp_OADestroy'
SET @PropOrMethodName = 'Microsoft.XMLDOM'
IF ISNULL(@ObjXML, 0) <> 0
BEGIN
EXEC @hr = sp_OADestroy @ObjXML
IF @hr <> 0
GOTO OLE_RELEASE_ERROR
END
-- ..and XSL objects
IF ISNULL(@ObjXSL, 0) <> 0
BEGIN
EXEC @hr = sp_OADestroy @ObjXSL
IF @hr <> 0
GOTO OLE_RELEASE_ERROR
END
-- and the stream obj
SET @PropOrMethodName = 'ADODB.Stream'
IF ISNULL(@ObjStream, 0) <> 0
BEGIN
EXEC @hr = sp_OADestroy @ObjStream
IF @hr <> 0
GOTO OLE_RELEASE_ERROR
END
-- exiting with returning the resulting document
RETURN @Result
OLE_RELEASE_ERROR:
-- OLE release error, exiting with error info
RETURN '#### Error ' + CONVERT(VARCHAR, CONVERT(VARBINARY(4), @hr)) + ', Action ' + @OLEActionName + ', Method/Property ' + @PropOrMethodName + ' ####'
END
Теперь проверяем всю колбасу:
Тестовые данные:
declare @xml xml = convert(xml, '<root><item id="1" name="john"/><item id="2" name="bob"/></root>')
Примитивный шаблон:
declare @templatetext varchar(max) = '<?xml version=''1.0'' encoding=''UTF-8''?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match = ''item''>
<tr><td><xsl:value-of select = ''@id'' /></td><td><xsl:value-of select = ''@name'' /></td></tr>
</xsl:template>
<xsl:template match = ''/''>
<HTML>
<BODY>
<TABLE>
<TR><TD colspan=''2''>Item List</TD></TR>
<xsl:apply-templates select = ''root'' />
</TABLE>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>'
И наконец, конвертируем шаблон из текста в XML, и вызываем нашу функцию:
declare @xsl xml = convert(xml, @templatetext)
select dbo.f_Helper_XSLTransform(@xml, @xsl)
И получаем на выходе:
<HTML><BODY><TABLE><TR><TD colspan="2">Item List</TD></TR><tr><td>1</td><td>john</td></tr><tr><td>2</td><td>bob</td></tr></TABLE></BODY></HTML>Про то, как все это отправить почтой (и даже с аттачментами — причем последнее — грязным хаком), могу написать еще один пост, если будет интересно
Комментариев нет:
Отправить комментарий