SSMS: import data from REST call

Question

I want to see if I can import data to MSSQL from a REST call to a URL. I would like to reuse this code for an hypothetic stored procedure in the future. This just wants so be a proof of concept.

This URL has some JSON that I can use for my experiment:

http://api.plos.org/search?q=title:DNA

I check if the URL returns some JSON with Postman:

All good.

I now follow this guide that tells me how to structure the call and this is my code:

DECLARE @Object AS INT;
DECLARE @ResponseText AS nvarchar(max);

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'GET','http://api.plos.org/search?q=title:DNA', 'false'
EXEC sp_OAMethod @Object, 'send'
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
SELECT @ResponseText
EXEC sp_OADestroy @Object

But unfortunately it returns me a NULL column.

What am I doing wrong?

Is there anyone out there which has ever arrived to make a REST call through SSMS?

Please don't do that! Especially not COM and the ancient MSXML2 classes that came with Windows 98. Either create a separate application eg in C# using HttpClient or a custom source in SSIS using a C# script component. — Panagiotis Kanavos, Sep 26 '18 at 10:26
Thank you @PanagiotisKanavos, please understand that this is a Proof Of Concept. I just want to know why I cannot retrieve the JSON. Of course is a bad approach and no one should ever do this — Francesco Mantovani, Sep 26 '18 at 21:56
@PanagiotisKanavos, I found an alternative to `MSXML2`, it's `WinHttp.WinHttpRequest.5.1` and it comes straight from 2018 — Francesco Mantovani, Oct 07 '18 at 09:36
Not really. The problem is that you are using sp_OAxxx in the first place, when there's no need to do so. You *still* need to check the status code of the response. — Panagiotis Kanavos, Oct 08 '18 at 06:57
As for `WinHttp.WinHttpRequest.5.1` it was released with [Windows Server 2003](https://learn.microsoft.com/en-us/windows/desktop/winhttp/winhttprequest#requirements) — Panagiotis Kanavos, Oct 08 '18 at 07:01
@PanagiotisKanavos, you are right. Suggest me what DLL I should use and I will write the query. Thank you — Francesco Mantovani, Oct 09 '18 at 00:13

mohabbati · Answer 1 · 2018-10-07T06:16:36.707

1

I suggest you to use SSIS(SQL Server Integration Services) instead SSMS. you can take a look on the following link:

Reference Link 1

Reference Link 2

edited Oct 07 '18 at 06:16

answered Sep 26 '18 at 10:23

mohabbati

1,162
1
13
31

Thank you @Mohammad, I didn't know I could do that through SSIS. It is definitely the right approach. BTW, I'm don't need to actually query any URL, I just want to understand what is wrong with that code. Is just an experiment, a Proof Of Concept. – Francesco Mantovani Sep 26 '18 at 21:58

score 0 · Accepted Answer · answered Oct 02 '18 at 09:07

0

I found what was the problem, on line 3:

NVARCHAR(MAX) can handle only 4000 characters
VARCHAR(MAX) can handle only 4000 characters
VARCHAR(8000) can handle 8000 characters

So if I try to query an URL that return less data everything is fine:

DECLARE @obj AS INT
DECLARE @Uri AS NVARCHAR(4000)
DECLARE @Response AS VARCHAR(8000)

SET @Uri = 'http://overpass-api.de/api/interpreter?data=area[name="Nelson"]->.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;'
EXEC sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
EXEC sp_OAMethod @obj, 'open', NULL, 'GET', @Uri, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'ResponseText', @Response OUTPUT

SELECT @Response [response] 
EXEC sp_OADestroy @obj

But the problem still persists above 8000 characters.

There was space for another question, if you want to dig further follow my next question here

answered Oct 02 '18 at 09:07

Francesco Mantovani

10,216
13
73
113

That's still a bad way to make HTTP calls. The first problem is `sp_OAxxx` itself. You *still* don't check the status code, which means you won't know what happend. You use the *WRONG* text type - HTTP responses are predominantly UTF8. This means you'll get mangled text if you get any non-US characters in the response – Panagiotis Kanavos Oct 08 '18 at 06:59
@PanagiotisKanavos, I wrote another reply here: https://stackoverflow.com/questions/22067593/calling-an-api-from-sql-server-stored-procedure and, yes, as you can see in that post I can catch the error. How can I change the text type if UTF8 is not good? I don't see in my query what manage the UTF8. Thank you – Francesco Mantovani Oct 09 '18 at 00:15
Now compare it with the OP's question. Dozens of lines that don't even do what the two-liner does. There's a reason sp_OAxxx isn't used and SQLCLR was introduced instead. BTW in case of error both answers will leak COM objects which are *NOT* garbage-collected. COM objects are reference counted. If you don't call `sp_OADestroy` they'll remain alive, probably until the service restarts. You need a `TRY .. CATCH` block – Panagiotis Kanavos Oct 09 '18 at 06:41
To put it another way, people that worked with `sp_OA` in the past abandoned it very quickly once SQLCLR came out. It creates a lot of problems, weakens security for *no* benefit – Panagiotis Kanavos Oct 09 '18 at 06:41
You convinced me @PanagiotisKanavos, I will try to use SQLCLR. BTW, fun fact: `WinHTTP.WinHTTPRequest.5.1` cannot read japanese but `MSXML2.ServerXMLHTTP.6.0` can apparently parse UTF-8 because I see the character in my database – Francesco Mantovani Oct 10 '18 at 08:51

SSMS: import data from REST call

2 Answers2