Calling an API from SQL Server stored procedure

Question

Calling an API from ASP NET Web Form is very easy   WebClient wc   new WebClient    string urlData   wc DownloadString  http   xxx xxx xx xx sssss getResponse do ID testing      But can we call an API from SQL Server stored procedure   If yes then how can we call an API from a SQL Server stored procedure and how can we get API response

User · Answer

I d recommend using a CLR user defined function  if you already know how to program in C   then the code would be   using System Data SqlTypes  using System Net   public partial class UserDefinedFunctions     Microsoft SqlServer Server SqlFunction   public static SqlString http SqlString url       var wc   new WebClient      var html   wc DownloadString url Value     return new SqlString  html          And here s installation instructions  https   blog dotnetframework org 2019 09 17 make-a-http-request-from-sqlserver-using-a-clr-udf

User · Answer

Simple SQL triggered API call without building a code project  I know this is far from perfect or architectural purity  but I had a customer with a short term  critical need to integrate with a third party product via an immature API  no wsdl  I basically needed to call the API when a database event occurred   I was given basic call info - URL  method  data elements and Token  but no wsdl or other start to import into a code project  All recommendations and solutions seemed start with that import   I used the ARC  Advanced Rest Client  Chrome extension and JaSON to test the interaction with the Service from a browser and refine the call   That gave me the tested  raw call structure and response and let me play with the API quickly  From there  I started trying to generate the wsdl or xsd from the json using online conversions but decided that was going to take too long to get working  so I found cURL  clouds part  music plays   cURL allowed me to send the API calls to a local manager from anywhere  I then broke a few more design rules and built a trigger that queued the DB events and a SQL stored procedure and scheduled task to pass the parameters to cURL and make the calls  I initially had the trigger calling XP CMDShell  I know  booo  but didn t like the transactional implications or security issues  so switched to the Stored Procedure method   In the end  DB insert matching the API call case triggers write to Queue table with parameters for API call Stored procedure run every 5 seconds runs Cursor to pull each Queue table entry  send the XP CMDShell call to the bat file with parameters Bat file contains Curl call with parameters inserted sending output to logs  Works well   Again  not perfect  but for a tight timeline  and a system used short term  and that can be closely monitored to react to connectivity and unforeseen issues  it worked   Hope that helps someone struggling with limited API info get a solution going quickly

User · Answer

I worked so much  I hope my effort might help you out   Just paste this into your SSMS and press F5   Declare  Object as Int  DECLARE  hr  int Declare  json as table Json Table nvarchar max    Exec  hr sp OACreate  MSXML2 ServerXMLHTTP 6 0    Object OUT  IF  hr  lt  gt  0 EXEC sp OAGetErrorInfo  Object Exec  hr sp OAMethod  Object   open   NULL   get                     http   overpass-api de api interpreter data  out json  area name  22Auckland 22 - 3E a  node area a  amenity cinema  way area a  amenity cinema  rel area a  amenity cinema    out    --Your Web Service Url  invoked                    false  IF  hr  lt  gt  0 EXEC sp OAGetErrorInfo  Object Exec  hr sp OAMethod  Object   send  IF  hr  lt  gt  0 EXEC sp OAGetErrorInfo  Object Exec  hr sp OAMethod  Object   responseText    json OUTPUT IF  hr  lt  gt  0 EXEC sp OAGetErrorInfo  Object  INSERT into  json  Json Table  exec sp OAGetProperty  Object   responseText  -- select the JSON string select   from  json -- Parse the JSON string SELECT   FROM OPENJSON  select   from  json   N   elements   WITH             type  nvarchar max  N   type             id    nvarchar max  N   id          lat    nvarchar max  N   lat          lon    nvarchar max  N   lon          amenity    nvarchar max  N   tags amenity          name    nvarchar max  N   tags name         EXEC sp OADestroy  Object   This query will give you 3 results   1  Catch the error in case something goes wrong  don t panic  it will always show you an error above 4000 characters because NVARCHAR MAX  can only store till 4000 characters   2  Put the JSON into a string  which is what we want   3  BONUS  parse the JSON and nicely store the data into a table  how cool is that

User · Answer

Please see a link for more details      Declare  Object as Int  Declare  ResponseText as Varchar 8000    Code Snippet Exec sp OACreate  MSXML2 XMLHTTP    Object OUT  Exec sp OAMethod  Object   open   NULL   get                     http   www webservicex com stockquote asmx GetQuote symbol MSFT   --Your Web Service Url  invoked                    false  Exec sp OAMethod  Object   send  Exec sp OAMethod  Object   responseText    ResponseText OUTPUT  Select  ResponseText  Exec sp OADestroy  Object

User · Answer

I think it would be easier using this CLR Stored procedure  SQL-APIConsumer    exec  dbo   APICaller POST        URL    http   localhost 5000 api auth login        BodyJson      Username   gdiaz   Password   password      It has multiple procedures that allows you calling API that required a parameters and even passing multiples headers and tokens authentications

User · Answer

Screams in to the void - just  no  don t do it  This is a dumb idea   Integrating with external data sources is what SSIS is for  or write a dot net application service which queries the box and makes the API calls   Writing CLR code to enable a SQL process to call web-services is the sort of thing that can bring a SQL box to its knees if done badly - imagine putting the the CLR function in a view somewhere - later someone else comes along not knowing what you ve donem and joins on that view with a million row table - suddenly your SQL box is making a million individual webapi calls   The whole idea is insane   This doing sort of thing is the reason that enterprise DBAs dont  trust developers   CLR is the kind of great power  which brings great responsibility  and the above is an abuse of it

User · Answer

The SQL Query select   from openjson     works only with SQL version 2016 and higher  Need the SQL compatibility mode 130

[sql-server] Calling an API from SQL Server stored procedure

Examples related to sql-server

Examples related to stored-procedures