Skip to content

Curl

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

Curl

README.md

CURL in SQL Server using CLR

SQL Server Database Engine doesn't have built-in functions that would enable you to send information to some API using http:// protocol. If you need to call some REST endpoint or a web hook from the T-SQL code, you will need to use WebClient or WebRequest classes from .Net framework and expose them as a T-SQL function or procedure.

One of the most popular tools for calling an API on http: endpoints is curl. This code sample demonstrates how to create CLR User-Defined function/procedure that provides CURL-like functionalities in T-SQL.

This code exposes minimal CURL functionalities required for the basic demo purposes. If you need more advanced features, you can modify the code.

Contents

About this sample
Build the CLR/CURL extension
Add CURL functions to your SQL database
Test the functions
Disclaimers
Appendix - quick install script for your dev environment.

About this sample

  1. Applies to: SQL Server 2005+ Enterprise / Developer / Evaluation Edition, Azure SQL Database (Managed Instance)
  2. Key features:
    • CLR
  3. Programming Language: T-SQL, .NET/C#
  4. Author: Jovan Popovic [jovanpop-msft]

Build the CLR/CURL functions

  1. Download the source code and open the solution using Visual Studio.

  2. Create a .pfx file (go to Project > Properties > Signing) to sign the assembly: Sign assembly

  3. Rebuild the solution in Retail mode.

  4. Open and save SqlClrCurl.tt to generate T-SQL file that will contain the script that inserts CLR assembly with the CURL functionalities, and exposes T-SQL/CLR functions.

Add CURL functions to your SQL database

File SqlClrCurl.sql contains the code that will import the CURL assembly into SQL Database.

If you have not added CLR assemblies in your database, you should use the following script to enable CLR:

sp_configure @configname=clr_enabled, @configvalue=1
GO
RECONFIGURE
GO

Once you enabled CLR, you can use the T-SQL script to add the CURL functions. The script depends on the location where you have built the project, and might look like:


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

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;
GO

This code will import a assembly in SQL Database and add one function and one procedure that provide CURL functionalities. Two modules are provided in this sample:

  • 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 Url 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 Url endpoint where the Http request should be sent.

In the assembly you can find an alternative version of Post procedure that will automatically retry Http request with 50ms delay if some error is returned by destination.

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

Test the functions

Once you create the assembly, you can use CURL functionalities in T-SQL code. 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')

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.

Disclaimers

The code included in this sample is not intended to be a set of best practices on how to build scalable enterprise grade applications. This is beyond the scope of this sample.

Appendix

In order to quickly test the function in your dev environment, you can create and assembly using the following script that imports assembly from binary format. Recommendation it to take the source code, compile it, and execute the script shown above.

EXEC sp_add_trusted_assembly 0x71BF52A56DF5F8049B4DA9858F19F22A9A363B23DF6A19DF5ED6A91300574E3EA8531A6B71D70A02E53767BDC2244FA175B1C2A6FEE15927B24C14D6CD338903, N'SqlClrCurl'
GO

CREATE ASSEMBLY [SqlClrCurl]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300BBAC355D0000000000000000E00022200B013000000E00000008000000000000FA2D0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000A82D00004F000000004000002C04000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000000E000000200000000E000000020000000000000000000000000000200000602E727372630000002C040000004000000006000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001600000000000000000000000000004000004200000000000000000000000000000000DC2D00000000000048000000020005006C2200003C0B00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002003100000001000011731200000A0A0206280400000606036F1300000A0B1201281400000A281500000A6F1600000A6F1700000A731800000A2A000000133003006300000002000011731200000A0A02062804000006036F1900000A2C1072010000707271000070731A00000A7A06046F1300000A0C1202281400000A281500000A036F1300000A0C1202281400000A6F1B00000A0B281C00000A727700007007281D00000A6F1E00000A2A001B300500D500000003000011731200000A0A02062804000006036F1900000A2C1072010000707271000070731A00000A7A7E010000040B72A30000700C0006046F1300000A0D1203281400000A281500000A036F1300000A0D1203281400000A6F1B00000A0C150BDE5D1304281C00000A1B8D10000001251672A5000070A2251711046F1F00000AA2251872B5000070A225197E020000048C1D000001A2251A72CB000070A2282000000A6F1E00000A0717590B7E02000004282100000ADE0007163D76FFFFFF07153315281C00000A72D300007008281D00000A6F1E00000A2A00000001100000000032002C5E005617000001133005005200000004000011026F1900000A2D49026F1300000A0B1201281400000A0A06282200000A2D3206178D1F00000125161F3B9D6F2300000A0C160D2B1608099A1304036F2400000A11046F2500000A0917580D09088E6932E42A1E02282600000A2A3A1980010000041F3280020000042A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C00000078030000237E0000E40300002004000023537472696E67730000000004080000000100002355530004090000100000002347554944000000140900002802000023426C6F620000000000000002000001571502000900000000FA01330016000001000000200000000200000002000000060000000B0000002600000012000000040000000100000003000000000095020100000000000600870168030600060268030600900036030F00880300000600B800C10206006A01C10206003601C1020600ED01C1020600A701C1020600D201C1020600E500C1020600A400490306008200490306001901C102060000013F020600C803BA020A0055010E030A00C0010E030A00AC0397030E00E403D3030A00870297030A00CF000E030600EF02BA020E009102BA0206008A02BA020600E702BA020A00FA030E030A007A000E0306000100BA02060042002E020600FF02BA020E00D302D303000000000700000000000100010001001000B5020000410001000100310021009D00310012009D005020000000009600CF03A00001009020000000009600F503A900040000210000000096001104A9000700F4210000000091000403B3000A005222000000008618290306000C005A220000000091182F03BB000C0000000000000000000100100000000200B60200000100100000000200500000000300B60200000100100000000200500000000300B60200000100100000000200EE03090029030100110029030600190029030A00290029031000310029031000390029031000410029031000490029031000510029031000590029031000610029031500690029031000710029031000790029031000890029030600910029030600B10029030600A10029030600990085022100A90024022600C10075022A00A10059022F00C900050434009900290339009900A4024700D10029034B00A10068025100D90071005700C900C1035C00E1004D001000B90065002600C900C1036D00F100F9027300C90052008200C900DE038700A100B5038E0001014900100081002903060020007B000F022400830014022E000B00BF002E001300C8002E001B00E7002E002300F0002E002B0000012E00330042012E003B0048012E00430057012E004B0076012E00530042012E005B0042012E0063008E012E006B00B8012E007300C50140008B000F0260008B000F021A003F0062007800048000000100000000000000000000000000AF020000040000000000000000000000940039000000000004000000000000000000000094002D00000000000400000000000000000000009400BA0200000000000000496E743332003C4D6F64756C653E00480044454C41595F4F4E5F4552524F520052455452595F434F554E540053797374656D2E44617461006D73636F726C696200546872656164004164640053656E640049734E756C6C4F7257686974655370616365006765745F4D657373616765006765745F506970650053716C5069706500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C654174747269627574650053716C50726F63656475726541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F647563744174747269627574650053716C466163657441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053797374656D2E546872656164696E670053797374656D2E52756E74696D652E56657273696F6E696E6700446F776E6C6F6164537472696E670055706C6F6164537472696E6700457363617065557269537472696E6700546F53716C537472696E67005572690053716C436C724375726C2E646C6C006765745F49734E756C6C0053716C436C724375726C0053797374656D0053797374656D2E5265666C656374696F6E00576562486561646572436F6C6C656374696F6E00417267756D656E74457863657074696F6E00536C656570004368617200416464486561646572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053716C4368617273006765745F4865616465727300436F6E636174004F626A656374004765740053797374656D2E4E65740053706C697400576562436C69656E7400636C69656E7400506F73740053716C436F6E7465787400546F43686172417272617900506F73745769746852657472790000006F59006F00750020006D007500730074002000730070006500630069006600790020006400610074006100200074006800610074002000770069006C006C002000620065002000730065006E007400200074006F002000740068006500200065006E00640070006F0069006E007400000540006400002B52006500710075006500730074002000690073002000650078006500630075007400650064002E0020000001000F4500720072006F0072003A00090000152E002000570061006900740069006E006700200000076D0073002E00002952006500710075006500730074002000690073002000650078006500630075007400650064002E000000000048478248D6CBC6478D18D459D8F01E0A00042001010803200001052001011111042001010E04200101020607021251115504200011550320000E0400010E0E0420010E0E0420001D03052001011D0307070312510E115503200002052002010E0E0520020E0E0E04000012710500020E0E0E0A07051251080E1155125D0500010E1D1C04000101080907050E11551D0E080E040001020E0620011D0E1D0305200012808108B77A5C561934E089020608080002124D124D124D09000301124D124D124D07000201124D1251030000010801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000000F01000A53716C436C724375726C00004101003C496D706C656D656E746174696F6E206F66204355524C20636F6D6D616E6420696E2053514C2053657276657220446174616261736520456E67696E6500000501000000000E0100094D6963726F736F667400001E01001953716C20536572766572204769744875622053616D706C6573000017010012436F7079726967687420C2A920203230313800002901002432363565306263332D616435662D343466332D626231372D63363166373762393834376600000C010007312E302E302E3000004901001A2E4E45544672616D65776F726B2C56657273696F6E3D76342E350100540E144672616D65776F726B446973706C61794E616D65122E4E4554204672616D65776F726B20342E35040100000012010001005408074D617853697A65FFFFFFFF00D02D00000000000000000000EA2D0000002000000000000000000000000000000000000000000000DC2D0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00300000000000000000000D00334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430030000010053007400720069006E006700460069006C00650049006E0066006F0000000C030000010030003000300030003000340062003000000092003D00010043006F006D006D0065006E0074007300000049006D0070006C0065006D0065006E0074006100740069006F006E0020006F00660020004300550052004C00200063006F006D006D0061006E006400200069006E002000530051004C002000530065007200760065007200200044006100740061006200610073006500200045006E00670069006E0065000000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000003E000B000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0043006C0072004300750072006C0000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000003E000F00010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C0072004300750072006C002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100380000002A00010001004C006500670061006C00540072006100640065006D00610072006B007300000000000000000046000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C0072004300750072006C002E0064006C006C000000000054001A000100500072006F0064007500630074004E0061006D00650000000000530071006C00200053006500720076006500720020004700690074004800750062002000530061006D0070006C00650073000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000FC3D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

--Create the schema where CURL modules will be placed.
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;
GO