次の方法で共有

Calling Http endpoints in T-SQL using CURL extension

  • [アーティクル]

SQL Server Database Engine don't have built-in functions that would enable you to send information to some API using http protocol. If you would need to call some REST endpoint or a web hook from the T-SQL code, you would need to use WebClient or WebRequest classes from .Net framework and expose them as T-SQL function or procedure. In this post you will see how to create that kind of extension.

CURL

One of the most popular tool for calling API on http endpoints is curl. CURL can be executed from the  command line to send HTTP request to some endpoint. In the following example you can see how to call curl from PowerShell:

 $endpoint= "https://......"
$header= "aeg-sas-key: ....."
$body= "....."
curl -X POST -H $header -d "$body" $endpoint

The last command line will call API on specified endpoint with specified header and body.

You can extend T-SQL functionalities by adding CLR procedures and functions that implement CURL-like feature in order to do the same thing in T-SQL.

Sample code

On the SQL Server GitHub Samples repository, you can find a sample demonstrates how to create CLR User-Defined function/procedure that provides CURL-like functionalities in T-SQL. You can download the code and build your .Net assembly that can be imported in SQL Server Database Engine using something like following script:

 CREATE ASSEMBLY SqlClrCurl
FROM 'C:\GitHub\sql-server-samples\samples\features\sql-clr\Curl\bin\Release\SqlClrCurl.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;

 

Once you import assembly, you can create a schema and functions that implement CURL functionalities in T-SQL

 CREATE SCHEMA CURL;
GO

--Create the function/procedure
CREATE FUNCTION CURL.XGET (@H NVARCHAR(MAX), @url NVARCHAR(4000))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrCurl.Curl.Get;
GO

CREATE PROCEDURE CURL.XPOST (@H NVARCHAR(MAX), @d NVARCHAR(MAX), @url NVARCHAR(4000))
AS EXTERNAL NAME SqlClrCurl.Curl.Post;

This code will add one function and one procedure in SQL Server that implements the following CURL functions:

  • CURL.XGET - function that calls API on some http endpoint using get method and fetches the response. It has two parameters:
    • @H representing the header information that should be sent to remote endpoint (null for none).
    • @url representing the endpoint where the Http request should be sent.
  • CURL.XPOST - procedure that sends text to some http endpoint using post method and prints response. It has three parameters:
    • @H representing the header information that should be sent to the remote endpoint (null for none).
    • @d representing the data that should be sent to remote endpoint in the request body.
    • @url representing the endpoint where the Http request should be sent.

Calling external Http API with CURL

Once you create the assembly, you can use CURL functionalities in T-SQL code to call external Web API using Http protocol. The following simple example gets the Microsoft earning from Investors Exchange ("IEX") API data REST API:

 select curl.xget(null, 'https://api.iextrading.com/1.0/stock/msft/earnings')

Note that execution time of this procedure depends on the network latency between SQL Server and external API and can be larger that execution of classic database engine procedures.

The following example sends one event to Azure Event Grid.

 declare @hkey nvarchar(200) = N'aeg-sas-key: 9CwFFHbPIwTPVEdXS+W7eMnuPk1/+pouIlhzf5=';

declare @body nvarchar(4000) = N'[{"id":"1807","eventType":"recordInserted","subject":"myapp/vehicles/motorcycles","eventTime": "2017-08-10T21:03:07+00:00","data": {"make": "Ducati","model":"Monster"},"dataVersion":"1.0","metadataVersion":"1"}]';

declare @endpoint nvarchar(1000) = N'https://test-event-grid.eventgrid.azure.net/api/events';

exec curl.XPOST @H = @hkey, @d = @body, @url = @endpoint;

 

Note: The code might return an error if your firewall/networking rules don't allow access to targeted Url. Security and configuring access right in your networks is beyond the scope of this sample.

Conclusion

CLR extension in SQL Server Database engine enables you to easily extend T-SQL functionalities and add the features that are not built-in into the SQL Server Database Engine. Accessing external Http endpoints is one of the functionalities that you might add in T-SQL using this sample code from SQL Server GitHub samples repository .