Академия Специальных Курсов по Компьютерным Технологиям
    Главная страница Послать письмо
 
AskIt.ru  
   
   
   
   
   
   
 
 
  Главная / Заказные курсы / Программирование для администраторов
 
 

Получить учебные материалы по этому курсу


<-- Назад Читать дальше -->

5. Использование внешних объектных моделей в коде Transact-SQL (хранимые процедуры OLE Automation)

Создание объектов в коде Transact-SQL, хранимые процедуры OLE Automation, SP_OACREATE, SP_OAMETHOD, SP_OASETPROPERTY, SP_OAGETPROPERTY

Функциональность скриптов TSQL, которые выполняются на SQL Server, может быть очень сильно расширена при помощи набора хранимых процедур SP_OA (от Ole Automation - SP_OACREATE, SP_OAMETHOD, SP_OASETPROPERTY, SP_OAGETPROPERTY и остальных). При использовании этих хранимых процедур вы можете во время выполнения скрипта создавать программные объекты (COM-серверы, имеющиеся на вашем компьютере), вызывать их свойства и методы, а затем удалять. Что чаще всего делается при помощи хранимых процедур SP_OA:

  • запись результатов выполнения запросов в файл (например, очень удобно выгружать в файл данные в XML), выполнение других дисковых операций;
  • запуск внешних программ для загрузки/выгрузки данных (например, пакетов DTS);
  • изменение рабочей среды Windows: подключение сетевых дисков и принтеров, создание переменных окружения, изменение параметров в реестре;
  • проведение резервного копирования нестандартными способами;
  • отслеживание событий операционной системы и приложений на своем и чужих компьютерах;
  • работа со службой каталогов (создание/изменение учетных записей пользователей, групп, компьютеров в домене и на локальном компьютере);
  • отправка сообщений электронной почты по SMTP (при помощи объектов CDO.Configuration и CDO.Message), что стандартными способами SQL Server делать не умеет;
  • использование различных возможностей SQL-DMO (подробнее в соответствующем модуле).

Очень часто также разработчики пишут свои COM-серверы и вызывают их из кода Transact-SQL, таким образом расширяя возможности SQL Server.

Некоторые из этих возможностей доступны при помощи bat - файлов и просто вызова внешних исполняемых файлов при помощи расширенной хранимой процедуры XP_CMDSHELL, однако при помощи SP_OA работать во многих ситуациях удобнее:

  • больше функциональность - из скрипта всегда можно вызвать внешний исполняемый файл;
  • можно использовать различные свойства и методы программных объектов - нет необходимости ограничиваться только передачей параметров исполняемому файлу;
  • нет альтернативы при необходимости получения дополнительной информации по работе операционной системы - пакетными файлами вернуть эту информацию на SQL Server очень сложно (например, информация о свободном месте на диске, наличии учетной записи/членстве ее в группе, информация о работающих программах и службах и т.п.)

Поскольку синтаксис SP_OACREATE нельзя признать самым удобным для написания сложных конструкций и их отладки, то рекомендуется вначале проверять работоспособность программных объектов, доступность их свойств и методов при помощи более специализированных средств, таких, как PrimalScript, а затем уже переносить в код Transact-SQL.

Подробнее про наиболее часто используемые программные объекты и как создавать с их использованием административные скрипты - в специальном курсе. Рассмотрим работу с SP_OA на простом примере (из реальной жизни, в Books Online такого нет).

Предположим, что нам необходимо записать результаты выполнения запроса к базе данных на SQL Server в файл output.txt в корневом каталоге диска C:. Запрос будет самый обычный -

SELECT companyname FROM northwind.dbo.customers WHERE CustomerID = 'ALKFI'

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

Пример обычного скрипта Windows, который записывает информацию в текстовый файл на диске:

'Создаем объект файловой системы - FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
'Создаем объект Text Stream (у нас он называется oFile) для записи 'текстовых данных в файл. 8 - значит открыть на добавление данных, True - 'создать, если еще нет, -1 - писать в Unicode
Set oFile = FSO.OpenTextFile("C:\output.txt", 8, True, -1)
'вызываем метод Write для записи информации в файл
oFile.Write("Наши данные")
'Удаляем созданные нами программные объекты
Set oFile = Nothing
Set FSO = Nothing

А теперь переводим то же самое на язык TSQL:

1) вначале - небольшая подготовка: скачиваем результаты запроса в переменную:

DECLARE @Var nvarchar(40)
Select @Var = CompanyName FROM Customers WHERE CustomerId = 'ALFKI'

2) затем создаем объект файловой системы и получаем на него ссылку (handle id) в формате Int - то, что пойдет в переменную FSO. Создание объекта производится при помощи хранимой процедуры SP_OACreate.

-- Объявляем переменные (сразу, чтобы не забыть)
DECLARE @FSO int
DECLARE @hr int
DECLARE @src varchar(255)
DECLARE @desc varchar(255)
DECLARE @oFile int
 
-- Создаем сам объект FSO
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
 
-- и - в соответствии с рекомендациями Microsoft ловим ошибки при помощи --- стандартной конструкции:
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

3) теперь при помощи метода OpenTextFile объекта FSO получаем ссылку на еще один объект (типа TxtStream), который будет называться oFile. Необходимый инструмент - хранимая процедура SP_OAMethod:

EXEC @hr = sp_OAMethod @FSO, 'OpenTextFile', @oFile OUT, 'C:\output.txt', 8 , True, -1
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

Обратить внимание на синтаксис процедуры SP_OAMethod:

  • первый параметр - всегда указатель на созданный объект в виде локальной целочисленной переменной;
  • второй параметр - всегда название вызываемого метода;
  • третий параметр - только исходящий, то, что вернет данный метод. Указывается всегда, если есть хотя бы один входящий параметр. Если метод объекта ничего возвращать не собирается, то обязательно нужно указать NULL.

Далее через запятую, в обычном формате указываем остальные входящие и исходящие параметры.

4) далее еще раз используем процедуру SP_OAMethod, но уже для вызова метода Write объекта oFile. Null для исходящего параметра обязателен!

EXEC @hr = sp_OAMethod @oFile, 'Write', NULL, @Var
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

Осталось в соответствии с правилами хорошего тона убрать за собой мусор из оперативной памяти:

EXEC @hr = sp_OADestroy @FSO
         IF @hr <> 0
         BEGIN
          EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT 
          SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
           RETURN
         END
EXEC @hr = sp_OADestroy @oFile
         IF @hr <> 0
         BEGIN
            EXEC sp_OAGetErrorInfo @oFile, @src OUT, @desc OUT 
            SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
            RETURN
         END

Все, результат запроса добавлен в текстовый файл.

Некоторые дополнительные моменты по SP_OA:

  • получить значение свойства/изменить значение свойства созданного объекта можно при помощи хранимых процедур SP_OAGetProperty и SP_OASetProperty соответственно. Работа с ними выглядит так же, как  и работа с SP_OAMethod, и сложностей не представляет;
  • хранимая процедура SP_OAGetErrorInfo позволяет получить информацию об ошибках, которые могут возникнуть при выполнении операций с внешними программными объектами. Рекомендованный Microsoft способ применения был приведен выше;
  • для всех процедур SP_OA передавать параметры можно только по позиции, но не по их имени;
  • SP_OADestroy можно, в принципе, не вызывать - созданные при помощи SP_OACreate программные объекты автоматически удаляются из памяти при завершении работы скрипта;
  • при первом запуске SP_OACreate в оперативной памяти выделяется специальная программная область - так называемая shared OLE Automation stored procedure execution environment, которая будет жить до перезагрузки сервера. Если по каким-то причинам эту память нужно освободить, то можно выполнить хранимую процедуру SP_OAStop. Однако нужно быть осторожным - если это время выполняется любая другая процедура SP_OA, то она вернет ошибку. Память будет снова выделена автоматически при следующем запуске SP_OACreate.
  • правами на выполнение любых хранимых процедур SP_OA обладают только системные администраторы SQL Server. Эти процедуры входят в группу "повышенного риска" с точки зрения безопасности и дополнительных прав на них предоставлять пользователям категорически не рекомендуется;
  • по умолчанию программный объект работает в контексте учетной записи SQL Server и обладает полными правами на все ресурсы, к которым имеет доступ SQL Server (в том числе выделенной SQL Server оперативной памяти). При вызове SP_OACreate при помощи необязательного параметра можно изменить эти права на более ограничительные.

 

   
   
   
   
   
   
   
   
   
   
 
<-- Назад Читать дальше -->

Получить учебные материалы по этому курсу


 

 
© 2004-2016, Академия Специальных Курсов
по Информационным Технологиям
.
Все права защищены.

Разработка NevaStudio
г. Санкт-Петербург, Васильевский остров,
20-я линия, д. 7
Офис 101, 2-й этаж
Телефон: 8(812)922-47-60
E-mail: info@askit.ru