Call Rest TaxJar API from SQL Server Code

Question

I'm working on calling TaxJar API from SQL Server, I saw some articles like:

calling an api from sql server stored-procedure

but unfirtunately I did know how to pass a Token value to the call

Here is a sample of the Get Call I'm making in Poestman:

https://api.taxjar.com/v2/rates/90404-3370

Token: XXXXXXXXXXX

Postman sample

anythoyghts how to do it please?

Thanks

here is a code sample of what i've done so far:

DECLARE
        @Result INT,
        @Text nVARCHAR(max),
        @Obj int,
        @HTTPStatus smallint,
        @URL Varchar(MAX)

DECLARE @output varchar(255);  
            DECLARE @hr int;  
            DECLARE @source varchar(255);  
            DECLARE @description varchar(255); 

SET @Text =  '-H "Authorization: Bearer [TOKEN VALUE]'

SET @Url = 'https://api.taxjar.com/v2/rates/90404-3370 \'

EXEC @Result = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Obj OUT 
EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Access-Control-Allow-Headers', 'Origin, X-Requested-With, Content-Type, Accept'
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Access-Control-Allow-Origin', '*'
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/json'
EXEC @Result = sp_OAMethod @Obj, send, NULL, @Text
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 


            PRINT @Result

            EXEC @Result = sp_OAGetErrorInfo @obj, @source OUT, @description OUT;  

            IF @Result = 0  
                BEGIN  
                    SET @output = '  Source: ' + @source + CHAR(13) + CHAR(10) 
                    SET @output = @output + '  Description: ' + @description  
                    PRINT 'OLE Automation Error Information';
                    PRINT @output 
                END

================================================================

UPDATE:

HERE IS MY SQL CODE AND IT WORKED PARTIALLY

DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(MAX);
DECLARE @responseText NVARCHAR(MAX);
DECLARE @responseXML NVARCHAR(MAX);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);

-- Set Authentications
SET @authHeader = 'Bearer [TOKEN VALUE]';
SET @contentType = 'application/json';

SET @url = 'https://api.taxjar.com/v2/summary_rates' 

EXEC @ret = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- build a request
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Cache-Control', 'no-cache' ;

EXEC @ret = sp_OAMethod @token, 'send'
-- Handle responce
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
-- Print responec
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;

THE URL IS RETURNING NOTHING IN SQL BUT IN POSTMAN IT RETURNS VALUES!

Please include some code you've written. – jrswgtr Sep 28 '18 at 15:01 — jrswgtr, Sep 28 '18 at 15:01
@jrswgtr done, sorry i forgot to add it – B.Simboliq Sep 28 '18 at 15:12 — B.Simboliq, Sep 28 '18 at 15:12

score 1 · Answer 1 · answered Oct 12 '18 at 04:17

Try using this CLR Stored proc https://github.com/geral2/SQL-APIConsumer

DECLARE @Result AS TABLE
(
    Token VARCHAR(MAX)
)

INSERT INTO @Result

 exec  [dbo].[APICaller_POST]
     @URL = 'http://localhost:5000/api/auth/login'
    ,@BodyJson = '{"Username":"gdiaz","Password":"password"}'

DECLARE @Token AS VARCHAR(MAX)

SELECT TOP 1 @Token = CONCAT('Bearer ',Json.Token)
 FROM @Result
  CROSS APPLY ( SELECT value AS Token FROM OPENJSON(Result)) AS [Json]

EXEC [dbo].[APICaller_GETAuth] 
     @URL     = 'http://localhost:5000/api/values'
   , @Token = @Token

Call Rest TaxJar API from SQL Server Code

1 Answers1