Query web API and return JSON data

Transact-SQL
1 
curl -X GET "url-api.server.com/v1/markets/quotes?symbols=AAPL,VXX190517P00016000&greeks=false" \
-H 'Authorization: Bearer <TOKEN>' \
-H 'Accept: application/json'

How do i run this Rest Json API in sql server directly ?

I believe its a combination of using he below, but i could not figure out the syntax.
sp_OACreate,
sp_OAMethod
sp_OAGetProperty

Python version is here :

Version 3.6.1 import requests

response = requests.get('url-api.server.com/v1/markets/quotes', params={'symbols': 'AAPL,VXX190517P00016000', 'greeks': 'false'},
headers={'Authorization': 'Bearer <TOKEN>', 'Accept': 'application/json'} )
json_response = response.json()
print(response.status_code)
print(json_response)
2

Perhaps try to Download and install Autonomous REST Connector?

Kind regards,

Julie Bunavicz

3

The sp_OA procedures are intended to be used with Windows COM based components. They're mostly obsolete and the suggested replacement is to use SQL CLR:

However, for a web API call, using PowerShell, or curl on a command line, is much easier. Pipe the JSON into a file, load it into SQL Server via OPENROWSET, BULK INSERT, or bcp, then use OPENJSON to read and parse it in SQL.

There's some references in here:

4

thanks i was thinking of that and i have done some of that in the past. But im surprised that these days with all of the api's (REST mainly) Microsoft has not built this into the product without having to goto 3rd party.

Will explore curl, to csv to open-rowset more.

TY

5

If you really need to integrate interaction with external APIs, that's what SQL CLR is for. SQL is a different programming paradigm (declarative) than most other languages (imperative, object-oriented), and using SQL in an imperative manner is less than ideal. There's a risk of embedding too much application logic in the database layer, and even worse, having database transactions dependent on external processes that are either asynchronous or otherwise undependable, and your database performance tanks.