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