artigo SQL Magazine 11 - How TO: Usando objetos OLE via SQL Server

Clique aqui para ler todos os artigos desta edição

How TO: Usando objetos OLE via SQL Server

Um dos mais poderosos recursos do transact-SQL do SQL Server é a possibilidade de criar instâncias de objetos OLE em scripts SQL sem o auxílio de linguagens de programação como C ou VB, utilizando assim as funcionalidades desses objetos para aumentar o poder dos procedimentos implementados.

A tecnologia OLE (Object Linked and Embedding) da Microsoft permite a vinculação e incorporação de objetos de outros aplicativos. Esta tecnologia é que permite que o SQL hospede um objeto pertencente a outro aplicativo dentro de seus scripts sem ter a necessidade de conhecer detalhes do aplicativo em questão.

Para isso, o SQL Server disponibiliza algumas procedures de sistema (system procedures) que possibilitam o acesso e manipulação de objetos OLE via transact-SQL. A tabela 1 contém as principais procedures com a descrição de sua função e seus parâmetros. Os parâmetros opcionais estão entre colchetes.

Tabela 1. System Procedures para manipulação de Objetos OLE

Nota

Somente membros da server role sysadmin podem executar as procedures listadas na tabela 1. Para verificar os membros do sysadmin, e conseqüentemente se o seu usuário possui esse role, execute a procedure da listagem 1. Caso você não seja membro desse role, consulte o administrador do seu banco de dados para lhe garantir esse direito ou disponibilizar um usuário para execução dos procedimentos aqui utilizados.

sp_helpsrvrolemember @srvrolename='sysadmin'

GO

ServerRole                          MemberName                     MemberSID                                                                                                                                                                    

----------------------------------- ------------------------------ -------------------------

sysadmin                            BUILTIN\Administradores        0x01020000000000052000000020020000

sysadmin                            SERVER_BD\Administrador        02AD0375C625CBC06DBEB0C50F4010000

sysadmin                            GRUPO_DB\Administrator         0x010500000000000515000000CF0

sysadmin                            distributor_admin              0x341ACF2112019E47A

sysadmin                            sa                             0x01

(5 row(s) affected)

Listagem 1. Verificando os membros do role sysadmin

Criando uma procedure para envio de e-mail

Usando o Microsoft Collaboration Data Objets (CDO) for Windows NT, scripts SQL podem enviar mensagens de e-mail sem ter que acessar o Microsoft Exchange Server. CDO utiliza o protocolo SMTP (Simple Mail Transfer Protocol) do IIS diretamente. O componente CDO pode ser chamado pelo Visual Basic, Visual C++, Visual J++, arquivos ASP e script SQL Server.

Neste exemplo, consideramos o objeto da versão “Microsoft CDO for NTS 1.2 Library”, mas existem outras versões de objetos com eventuais alterações nos códigos, são elas:  “Microsoft CDO 1.21 Library”, “Microsoft CDO for Windows 2000 Library”e “Microsoft CDO for Exchange 2000 Library”.

Veja um exemplo do uso de OLE automation acessando a classe NewMail do objeto CDO do Windows NT Server (CDONTS). Essa classe permite o envio de e-mail utilizando o protocolo SMTP. A tabela 2 lista as propriedades dessa classe e a tabela 3 lista os métodos.

Tabela 2. Atributos da Classe NewMail

Tabela 3. Métodos da Classe NewMail

A listagem 2, traz a implementação da Stored Procedure que cria, inicializa os parâmetros e executa a classe NewMail. Esse procedimento pode ser executado a partir de uma trigger, outra stored procedure ou ainda a partir de uma aplicação cliente, desde que o usuário conectado possua os direitos citados anteriormente.

01 CREATE PROCEDURE sp_Envia_Email

02   @remetente    varchar(1000),

03   @destinatario varchar(1000),

04   @assunto      varchar(1000),

05   @corpodoemail varchar(1000),

06   @anexo        text = NULL,

07   @formato      tinyint = 1

08 AS

09  DECLARE @object int

10  DECLARE @varobj int

11  EXEC @varobj = sp_OACreate 'CDONTS.NewMail', @object OUT

12  EXEC @varobj = sp_OASetProperty @object, 'From', @remetente

13  EXEC @varobj = sp_OASetProperty @object, 'To', @destinatario

14  EXEC @varobj = sp_OASetProperty @object, 'Subject', @assunto

15  EXEC @varobj = sp_OASetProperty @object, 'Body', @corpodoemail

16  IF @formato = 0 BEGIN

17      EXEC @varobj = sp_OASetProperty @object, 'BodyFormat', 0

18      EXEC @varobj = sp_OASetProperty @object, 'MailFormat', 0

19  END

20  IF @anexo IS NOT NULL

21  BEGIN

22      EXEC @varobj = sp_OAMethod @object, 'AttachFile', NULL, @anexo

23  END

24  EXEC @varobj = sp_OAMethod      @object, 'Send', NULL

25  EXEC @varobj = sp_OADestroy     @object

26 GO

Listagem 2. Implementação da procedure

Observando a listagem 2, temos a declaração das variáveis nas linhas 9 e 10. A criação do objeto através da procedure OACreate na linha 11. As linhas de 12 a 19 inicializam as propriedades do objeto de acordo com os parâmetros da procedure. As linhas de 20 a 23 verificam se o parâmetro referente ao arquivo anexo foi informado e atribuem o mesmo através da execução do método AttachFile, na linha 22. O “e-mail” é enviado na linha 24, com a execução do método Send e o objeto é destruído na linha 25 pela procedure OADestroy. Para Executar a procedure criada utilize a seguinte sintaxe, a partir do Query Analizer:

Exec sp_Envia_Email 'email@origem.com.br','email@destino.com.br',

'Teste de Procedure SQL Server', 'Estou testando o envio de e-mail pelo banco de dados'

Caso você não seja membros da server role sysadmin , a procedure não será executada, retornando o erro apresentado na listagem 3.

Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 11

EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.

Server: Msg 229, Level 14, State 5, Procedure sp_OASetProperty, Line 12

EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.

Server: Msg 229, Level 14, State 5, Procedure sp_OASetProperty, Line 13

EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.

Server: Msg 229, Level 14, State 5, Procedure sp_OASetProperty, Line 14

EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.

Server: Msg 229, Level 14, State 5, Procedure sp_OASetProperty, Line 15

EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.

Server: Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 24

EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'.

Server: Msg 229, Level 14, State 5, Procedure sp_OADestroy, Line 25

EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'.

Listagem 3.

Conclusão:

É claro que não vamos fazer uma aplicação completa utilizando somente scripts SQL, ou acessar centenas de objeto OLE dentro de um script, mas essa possibilidade faz com que scripts SQL tenham poderes de acesso a objetos da mesma forma que um código ASP por exemplo. Outro grande trunfo que temos é a possibilidade de acessar a própria DMO (Distributed Management Objects) que encapsula os objetos do SQL Server 2000. Assim, praticamente qualquer atividade feita dentro do Enterprise Manager pode ser realizada dentro de um script SQL instanciando o objeto “SQLServer” da “Microsoft SQLDMO Object Library”. É possível também acessar objetos de busca como o Index Server.