Hey Guys!
Tudo bem com vocês ?
Neste post rápido, vou demonstrar mais uma vez o uso de procedures OLE Automation e CLR para consumir informações na Web e trazer para o nosso banco SQL Server, de forma que possamos trabalhar com essa informação conforme nossa necessidade. Desta vez, vou mostrar como consultar as informações de cidade, estado, bairro e logradouro a partir de um cep informado, o que é muito utilizado atualmente.
Como o retorno da API contém dados no formato JSON, vou utilizar a função JSON_VALUE do SQL Server 2016. Caso você queira saber muito mais sobre tratamento de strings e arquivos JSON no SQL Server 2016, acesse o meu post SQL Server 2016 – Utilizando o suporte nativo a JSON (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).
Esse artigo é um complemento do meu post Como consultar informações de um CEP no SQL Server, onde demonstrei como consultar dados de um CEP utilizando a API do Viacep.com.br e que retornava os dados no formato XML. Resolvi fazer um novo post demonstrando como fazer isso utilizando uma outra API e tratando os dados no formato JSON.
Consultando os dados do CEP
Para realizar essa operação, vou utilizar o ótimo serviço da página CEP Bemean para consultar as informações de CEP.
Para realizar as requisições Web pelo SQL Server, você pode utilizar a solução utilizando OLE Automation, que vou demonstrar abaixo ou utilizando a Stored Procedure stpWs_Requisicao (minha forma preferida), do SQL CLR (C#), que eu demonstrei no post Realizando requisições POST e GET utilizando CLR (C#) no SQL Server.
Consultando as informações do CEP utilizando OLE Automation
Agora vou demonstrar como realizar essa consulta utilizando OLE Automation. A rotina está preparada para ativar o recurso na instância, caso não esteja ativado e desativar novamente ao final da execução.
Visualizar código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
CREATE PROCEDURE dbo.stpConsulta_CEP_OLE ( @Nr_CEP VARCHAR(20) ) AS BEGIN -------------------------------------------------------------------------------- -- Habilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures') IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE SP_CONFIGURE 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE; END DECLARE @obj INT, @Url VARCHAR(255), @resposta VARCHAR(8000), @xml XML -- Recupera apenas os números do CEP DECLARE @startingIndex INT = 0 WHILE (1=1) BEGIN SET @startingIndex = PATINDEX('%[^0-9]%', @Nr_CEP) IF (@startingIndex <> 0) SET @Nr_CEP = REPLACE(@Nr_CEP, SUBSTRING(@Nr_CEP, @startingIndex, 1), '') ELSE BREAK END SET @Url = 'https://cep-bemean.herokuapp.com/api/br/' + @Nr_CEP EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, FALSE EXEC sys.sp_OAMethod @obj, 'send' EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT EXEC sys.sp_OADestroy @obj SELECT JSON_VALUE(@resposta, '$.code') AS CEP, JSON_VALUE(@resposta, '$.address') AS Logradouro, JSON_VALUE(@resposta, '$.district') AS Bairro, JSON_VALUE(@resposta, '$.city') AS Cidade, JSON_VALUE(@resposta, '$.state') AS Estado -------------------------------------------------------------------------------- -- Desativando o OLE Automation (Se não estava habilitado antes) -------------------------------------------------------------------------------- IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE; EXECUTE SP_CONFIGURE 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; END END |
Exemplo de uso:
|
1 2 |
EXEC dbo.stpConsulta_CEP_OLE @Nr_CEP = '29200260' -- varchar(20) |
Result:
Consultando as informações do CEP utilizando o CLR
Agora vou demonstrar como realizar essa consulta utilizando o CLR (C#), que na minha visão, é a melhor solução, embora seja mais complexa para implementar e exija conhecimentos em uma linguagem de programação (C# ou VB).
Caso você não saiba o que é o CLR e gostaria de aprender mais sobre esse fantástico recurso que pode ser utilizado no SQL Server, veja o post Introdução ao SQL CLR (Common Language Runtime) no SQL Server. O código-fonte da procedure do CLR stpWs_Requisicao está disponível no post Realizando requisições POST e GET utilizando CLR (C#) no SQL Server.
Visualizar código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
CREATE PROCEDURE dbo.stpConsulta_CEP_CLR ( @Nr_CEP VARCHAR(20) ) AS BEGIN -- Recupera apenas os números do CEP DECLARE @startingIndex INT = 0 WHILE (1=1) BEGIN SET @startingIndex = PATINDEX('%[^0-9]%', @Nr_CEP) IF (@startingIndex <> 0) SET @Nr_CEP = REPLACE(@Nr_CEP, SUBSTRING(@Nr_CEP, @startingIndex, 1), '') ELSE BREAK END DECLARE @Url VARCHAR(500) = 'https://cep-bemean.herokuapp.com/api/br/' + @Nr_CEP, @resposta NVARCHAR(MAX); EXEC CLR.dbo.stpWs_Requisicao @Ds_Url = @Url , -- nvarchar(max) @Ds_Metodo = N'GET' , -- nvarchar(max) @Ds_Parametros = N'' , -- nvarchar(max) @Ds_Codificacao = N'UTF-8' , -- nvarchar(max) @Ds_Retorno_OUTPUT = @resposta OUTPUT -- nvarchar(max) SELECT JSON_VALUE(@resposta, '$.code') AS CEP, JSON_VALUE(@resposta, '$.address') AS Logradouro, JSON_VALUE(@resposta, '$.district') AS Bairro, JSON_VALUE(@resposta, '$.city') AS Cidade, JSON_VALUE(@resposta, '$.state') AS Estado END |
Exemplo de uso:
|
1 2 |
EXEC dbo.stpConsulta_CEP_CLR @Nr_CEP = '29200290' -- varchar(20) |
Result:
And that's it, folks!
Espero que tenham gostado desse post.
Abraço e até a próxima.
Portuguese 
English
Ola Meu Amigo Bom dia,
Muito Obrigado Por Suas Publicações.
Estou querendo aproveitar essa stored procedure para buscar dados em outra web api,
mais ao fazer a mudança do link e alterar o parametro o resultado sempre retorna nulo, ja fiz o teste sem colocar parametro nenhum apenas o link completo da pesquisa na web api e o resultado sempre vem como nulo.
por favor alguma luz!!!! kkkkkkkk
Oi Lucas, boa noite!! Qual a URL que você está tentando consultar?
Bom Dia Meu Amigo,
Segue Link
https://bibipecasapi.azurewebsites.net/api/v1/vehicle/plate/nwh0939?key=cf2a84247634431c958c563094d9850a&token=3a3dbe9ba144495cb286d82f4b3b8c8544da41253f0b42f2bd02d3d43994fbf3
aonde tem nwh0939 é a variavel.
Grato desde ja