Clique aqui para ler esse artigo em PDF.
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.
Procedure |
Descrição |
Parâmetros |
Sp_OACreate |
Cria a instância de um objeto OLE |
ProgID : É a string de caracteres que referencia a classe do objeto OLE pelo nome. ‘OLEComponent.Objeto’ Ou Clsid : É a string de caracteres que referencia a classe do objeto pelo ID. '{nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}'
Objecttoken OUTPUT : É o parâmetro de saída retornado pela procedure que identifica o objeto criado. Deve ser retornado para uma variável do tipo ‘int’. [ , context ] : Especifíca o contexto de execução do objeto: 1 – In-process (.dll) OLE server 2 – Local (.exe) OLE server 5 – Ambos in-process e local OLE server |
Sp_OASetProperty |
Atribui um novo valor a propriedade de um OLE automation |
Objecttoken: É o objecttoken retornado pela procedure Sp_OACreate. Propertyname: Nome da propriedade do objeto newvalue : Valor atribuído à propriedade [ , index... ] : É um índice de parâmetros para objetos que solicitem, respeitando o tipo do dado. |
sp_OAMethod |
Chama o método de um OLE automation |
Objecttoken: É o objecttoken retornado pela procedure Sp_OACreate. methodname : Nome do método do objeto [returnvalue OUTPUT ] : É o retorno do método chamado
[[ @parametername = ] parameter [ OUTPUT ] [ ...n ] ]: Caso o método exija parâmetros você deve descrever cada um na seqüência. |
sp_OAGetErrorInfo |
Recupera informações de erro de um OLE automation |
[objecttoken ] : É o objecttoken retornado pela procedure Sp_OACreate. [source OUTPUT ] : Retorna a fonte que gerou o erro [description OUTPUT ] : Retorna a descrição do error [helpfile OUTPUT ] : Retorna o arquivo de help do objeto OLE. [helpid OUTPUT ] : Retorna o ID de contexto dentro do arquivo de help do objeto OLE. |
sp_OAGetProperty |
Lê o valor de propriedades OLE automation |
Objecttoken: É o objecttoken retornado pela procedure Sp_OACreate. Propertyname: Nome da propriedade do objeto [propertyvalue OUTPUT ] : Valor de retorno da propriedade [index...] : É um índice de parâmetros para objetos que solicitem, respeitando o tipo do dado. |
sp_OADestroy |
Destrói um objeto OLE criado |
Objecttoken: É o objecttoken retornado pela procedure Sp_OACreate. |
sp_OAStop |
Pára o lado server do OLE automation |
Não existem parâmetros para esta procedure. |
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.
Propriedades |
Tipo |
Requerido |
Descrição |
Exemplo |
From |
String |
Opcional |
Endereço do remetente |
|
To |
String |
Opcional |
Endereço do destinatário |
|
Cc |
String |
Opcional |
Cópia para outro destinatário |
|
Bcc |
String |
Opcional |
Cópia oculta para outro destinatário |
|
Subject |
String |
Opcional |
Assunto |
Assunto do E-mail |
Body |
String |
Opcional |
Texto da mensagem |
A SQL Magazine está nota 10! |
Importance |
Long |
Opcional |
Importância |
0 (baixa), 1 (normal) e 2(alta) |
BodyFormat |
Long |
Opcional |
Formato do corpo |
0 - formato HTML 1 - formato texto comum |
MailFormat |
Long |
Opcional |
Formato do e-mail |
0 - formato HTML 1 - formato texto comum |
Tabela 2. Atributos da Classe NewMail
Métodos |
Descrição |
Exemplo |
Send |
O método Send da classe NewMail é quem faz o envio da mensagem, os dados podem ser passados via propriedade ou por parâmetros como está descrito no exemplo. |
Pode ser usado passando os parâmetros From, To, Subject, Body e Important
objNewMail.Send ( [From] [, To] [, Subject] [, Body] [, Importance] ) |
SetLocateIDs |
Associa o Codepage ID para a nova mensagem |
850 |
AttachFile |
Anexar arquivos no e-mail |
arquivo.doc |
AttachURL |
Anexar arquivos ou uma URL no e-mail |
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.