sp_OAMethod always returns NULL when consuming a Rest API

38 Views Asked by At

sp_OAMethod always returns NULL when consuming a Rest API... I'm have no idea where the problem could be. Here is the source code of the procedure:

CREATE PROCEDURE MakeCEPAbertoRequest
    @cep VARCHAR(8) -- Tamanho do CEP no Brasil
AS
BEGIN
    DECLARE @token VARCHAR(1000) = 'Token token=d142c65bc45454595b15897e1d70c04b6';
    DECLARE @url VARCHAR(1000) = 'https://www.cepaberto.com/api/v3/cep?cep=' + @cep;
    DECLARE @requestResult NVARCHAR(MAX);
    DECLARE @Stat  INT 
    DECLARE @resposta NVARCHAR(4000)
    
    -- Criar um objeto XMLHTTP
    DECLARE @objectID INT;
    EXEC @Stat = sp_OACreate N'MSXML2.ServerXMLHTTP', @objectID OUT;
    
    -- Abrir a conexão para o URL desejado
    EXEC sp_OAMethod @objectID, N'open', NULL, N'GET', @url, false;
    
    EXEC sp_OAMethod @objectID, N'setRequestHeader', NULL, 'Content-Type', 'application/json'
    -- Definir o cabeçalho de autorização
    EXEC sp_OAMethod @objectID, N'setRequestHeader', NULL, N'Authorization', @token;
    
    -- Enviar a solicitação
    EXEC sp_OAMethod @objectID, N'send';
    
    -- Obter a resposta
    EXEC sp_OAMethod @objectID, N'responseText', @requestResult OUTPUT;

    -- Destruir o objeto XMLHTTP
    EXEC sp_OADestroy @objectID;
    
    -- Retorna a resposta
    SELECT @requestResult AS Response
END
GO

I have created the equivalent of this procedure with CLR but the behavior is the same (I always get NULL output).

When I use this API in Insomnia (with the same parameters) I get the expected response.

0

There are 0 best solutions below