Consultar uma API de CEP usando SQL Server

Consultar uma API de CEP usando SQL Server

Neste artigo, você vai ver como consumir uma API que retorna dados em formato JSON utilizando o SQL Server. Como exemplo, vamos consultar CEP de uma API pública. Você pode adaptar este exemplo para outros tipos de consulta.

Para consultar o CEP vamos utilizar a API da ViaCEP. Vamos conferir primeiro qual o retorno que é devolvido pela API. No seu navegador, acesse o seguinte endereço:

http://viacep.com.br/ws/01531001/json

Perceba que antes do "/json" se encontra o CEP que desejamo pesquisar, sem o traço.

O retorno desta consulta é o seguinte:

{

  "cep": "01531-001",

  "logradouro": "Avenida da Aclimação",

  "complemento": "lado ímpar",

  "bairro": "Aclimação",

  "localidade": "São Paulo",

  "uf": "SP",

  "unidade": "",

  "ibge": "3550308",

  "gia": "1004"

}


A consulta a API será feita utilizando uma procedure. Como a consulta é online, pode haver uma pequena demora no retorno dos resultados. 

Apenas para fins de controle, vamos criar uma tabela para armazenar o retorno recebido da API.

CREATE TABLE [dbo].[log_api_cep](

[log_id] [int] IDENTITY(1,1) NOT NULL,

[log_cep] [varchar](50) NULL,

[log_resposta] [varchar](500) NULL,

[log_data] [datetime] NULL,

 CONSTRAINT [PK_log_cep] PRIMARY KEY CLUSTERED 

(

[log_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Para que a procedure funcione, é necessário ativar no seu SQL Server algo chamado Ole Automation, este recurso pode não estar ativado por padrão.

A procedure vai receber apenas os números do CEP, ativar o Ole Automation e fazer a conexão com a API.

O código inicial fica da seguinte forma:

CREATE PROCEDURE [dbo].[consultaCEP] (

    @CEP VARCHAR(8)

)

AS BEGIN




    DECLARE 

        @obj INT,

        @Url VARCHAR(255),

        @resposta VARCHAR(8000),

EXECUTE sp_configure 'show advanced options', 1;

    RECONFIGURE WITH OVERRIDE;

    EXEC sp_configure 'Ole Automation Procedures', 1;

    RECONFIGURE WITH OVERRIDE;

    

    SET @Url = 'http://viacep.com.br/ws/' + @CEP + '/json'

 

    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

END

 

No código acima, ativamos o Ole Automation utilizando as procedures "sp_configure", e depois fizemos a conexão para a URL da API através de um objeto "MSXML2.ServerXMLHTTP". A conexão foi feita através do método GET, e o resultado da conexão foi armazenado numa variável chamada "@obj". O retorno da API, recebido no "responseText", que está disponível na variável "@obj", foi atribuído a uma variável chamada "@resposta".

Essa variável "@resposta" será utilizada para alimentar o log e para devolver os resultados para o usuário.

Para fazer o log, basta fazer um insert na tabela de log_api_cep

INSERT into log_api_cep(log_cep, log_resposta, log_data) values (@CEP, @resposta, convert(datetime, getdate(), 103))

Para fazer o parse do JSON e extrair as informações vamos utilizar a função JSON_VALUE. Esta função recebe como parâmetro dois valores, o primeiro é o JSON que deve ser utilizado, o segundo é a propriedade que deve ser extraída. 

O código final de nossa procedure ficará assim:

 

CREATE PROCEDURE [dbo].[consultaCEP] (

    @CEP VARCHAR(8)

)

AS BEGIN

    DECLARE 

        @obj INT,

        @Url VARCHAR(255),

        @resposta VARCHAR(8000),

 

EXECUTE sp_configure 'show advanced options', 1;

    RECONFIGURE WITH OVERRIDE;

    EXEC sp_configure 'Ole Automation Procedures', 1;

    RECONFIGURE WITH OVERRIDE;

    

    SET @Url = 'http://viacep.com.br/ws/' + @CEP + '/json'

 

    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




INSERT into log_api_cep(log_cep, log_resposta, log_data) values (@CEP, @resposta, convert(datetime, getdate(), 103))



EXEC sys.sp_OADestroy @obj



    SELECT

        JSON_VALUE(@resposta, '$.cep') AS CEP,

        JSON_VALUE(@resposta, '$.logradouro') AS Logradouro,

        JSON_VALUE(@resposta, '$.bairro') AS Bairro,

        JSON_VALUE(@resposta, '$.localidade') AS Cidade,

        JSON_VALUE(@resposta, '$.uf') AS Estado

END

 

Exemplo de como utilizar esta procedure:

EXEC dbo.consultaCEP '01531001'

Não esqueça de conferir o log

select * from log_api_cep

Como bônus, vamos aprender como trabalhar com o log. Você percebeu que todo o retorno no formato JSON ficou armazendo dentro de um campo varchar chamado log_resposta. Para extrair os dados deste campo, vamos novamente  usar a função JSON_VALUE

Nossa consulta ao log vai ficar da seguinte forma:

select

 log_cep, 

 JSON_VALUE(log_resposta, '$.logradouro') as logradouro,

 JSON_VALUE(log_resposta, '$.bairro') as bairro,

 JSON_VALUE(log_resposta, '$.localidade') as cidade,

 JSON_VALUE(log_resposta, '$.uf') as estado

from log_api_cep

 

 

Outros conteudos que podem ser de seu interesse

Como calcular o total acumulado com SQL
31/07/2022SQL

Como calcular o total acumulado com SQL

Veja como fazer uma soma cumulativa utilizando SQL

Saiba mais...
Cursos e outros materiais gratuitos para estudar programação
29/11/2020SQL

Cursos e outros materiais gratuitos para estudar programação

Confira vários cursos gratuitos e blogs para aprender programação de graça

Saiba mais...

Conteúdo sobre banco de dados sem complicação!