Send requests to rest API from SQL Server and check http code returned

Question

I'm attempting to send json data to a rest API, ideally from SQL Server 2016. I have come across the following code (from https://gist.github.com/theorigin/fa3c58406ff7b4565ca2) that uses sp_OAMethod and have tested it using webhook.site so I know it sends, but I can't see the http response code from SQL which is what I need to view... the API in question sends a 400 response along with an error message if there is an issue with a submission, or a 200 if everything was correct.

The SQL code returns a NULL column under all scenarios. At present I have a PHP application that I have written that does all this for me, except it is not automated. The application runs through csv files and sends json one line at a time to the endpoint. Before I alter it to look at query results rather than csv files, and before I start looking at adding this to a cron job (or perhaps a scheduled task in Windows?), I have been wondering if what I am trying to achieve is possible using SQL Server, which is where all my data is stored. This may be a round peg into a square hole though and if so then I will discount this method.

DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) = 
'{
    "name": "John",
    "age": 30,
}'  

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post','https://webhook.site/1234', 'false'

EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'send', null, @body

EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
SELECT @ResponseText

EXEC sp_OADestroy @Object

Ideally I will see '200' or '400' as a response for each request and I can parse these after the stored procedure has run.

Any help or suggestions are very welcome. Thanks

Possible duplicate of Set option 9 in SQL Server stored procedure using WinHttp.WinHttpRequest.5.1 for TLS 1.2 — David Browne - Microsoft
– David Browne - Microsoft, Commented Jul 31, 2019 at 17:15
So in other words the sp_OA procedures are a bad idea and I should look at alternatives. SQL CLR? — ajguk
– ajguk, Commented Aug 2, 2019 at 13:53
Basically, yes. There's a relevant sample here: github.com/microsoft/sql-server-samples/tree/master/samples/… — David Browne - Microsoft
– David Browne - Microsoft, Commented Aug 2, 2019 at 14:08

cdrrr · Accepted Answer · 2019-09-08 11:05:47Z

I'm thinking that the reason is because you do SELECT @ResponseText and then you do nothing with whatever the SELECT is returning and finally you destroy the object.

Try to assign the response you hold in @ResponseText to another variable. In the below example I'm doing a login to a rest API and then I assign the response to another variable:

EXEC sp_OACREATE 'MSXML2.ServerXMLHttp', @Object OUT;
EXEC sp_OAMethod @Object, 'Open', NULL, 'POST', 'https://url.net/api/LoginUser', 'false'
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Content-Type', 'application/json'

DECLARE @len int
SET @len = len(@body)

EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Ocp-Apim-Subscription-Key','4f82f9d067914287979884f920d86ffb'
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Ocp-Apim-Trace:true'
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Content-Length', @len
EXEC sp_OAMethod @Object, 'SETRequestBody', null, 'Body', @body
EXEC sp_OAMethod @Object, 'Send', null, @body
EXEC sp_OAMethod @Object, 'ResponseText', @ResponseText OUTPUT

SELECT @Token=@ResponseText

EXEC sp_OADestroy @Object

SET @Token='Bearer '+ REPLACE(@Token, '"', '')

PRINT @Token

Joundill · Accepted Answer · 2021-02-03 02:19:57Z

0

Trying using SELECT into a temp table to evaluate results.

DECLARE @tableResponseText TABLE (
    [ResponseText] VARCHAR(MAX)
    )

Your setup/send statements

Collect the response data

EXEC @RC = sp_OAGetProperty @Object, 'status', @HttpResponseCode OUT
INSERT INTO @tableResponseText ([ResponseText]) EXEC sp_OAGetProperty @Object, 'responseText'

edited Feb 3, 2021 at 2:19

Joundill

7,82113 gold badges40 silver badges54 bronze badges

answered Feb 3, 2021 at 2:04

Mark Allison

1

Collectives™ on Stack Overflow

Send requests to rest API from SQL Server and check http code returned

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related