kiran sabne
kiran sabne's Blog

kiran sabne's Blog

Making REST-API from SQL Server procedure

kiran sabne's photo
kiran sabne
·Sep 23, 2022·

3 min read

Subscribe to my newsletter and never miss my upcoming articles

Play this article

Table of contents

  • GET Method call:
  • POST Method call:
  • Summary

Recently I had a requirement, wherein I had to import the data from one of the internal applications to the SQL Server for further processing. We had REST-API open at a particular endpoint so any other service can consume the API and fetch data for inserting/updating into tables in SQL Server.

For this purpose, I ended up creating a stored procedure using the OLE Automation procedure, which will make an HTTP request call and we can be able to perform GET and POST request methods.

OLE Automation Procedures are extended stored procedures allowing users to execute external functions to SQL Server. These can perform use cases like making HTTP calls, Reading and Writing files, Accessing File System objects, etc. The use of these extended procedures is sensitive and should be closely monitored.

The full set of OLE automation procedures are:

  • sp_OACreate
  • sp_OAGetProperty
  • sp_OASetProperty
  • sp_OAMethod
  • sp_OAGetErrorInfo
  • sp_OAStop
  • sp_OADestroy

By default, the OLE Automation is disabled in SQL Server and has to be enabled. Below is the script to check and enable OLE Automation.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Let's continue forward with our demonstration.

GET Method call:

Below is the script I used in the stored procedure, for making HTTP call of GET method request type.

DECLARE @user_id int = 2; -- input parameter, to be passed with GET Request
DECLARE @URL NVARCHAR(MAX) = concat('https://reqres.in/api/users/', @user_id); -- our URL for http call
print '@URL -> '+ @URL;
Declare @Object as Int; -- declare object
Declare @ResponseText as Varchar(8000);

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; -- creating OLE object and assigning it to variable @Object
-- print @Object; -- 16711422

-- passing the @Object created above, with our http call and handling the response with help of sp_OAMethod 
Exec sp_OAMethod @Object, 'open', NULL, 'get',
       @URL,
       'False'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
IF((Select @ResponseText) <> '')
BEGIN
     DECLARE @json NVARCHAR(MAX) = (Select @ResponseText)
     print(@json); -- printing the json output

     select 
        rs.id, rs.email, rs.first_name, rs.last_name, rs.avatar
     from openjson(@json, '$.data') with  -- only parsing data json array object of the responseText
     (
        id int '$.id',
        email varchar(200) '$.email',
        first_name varchar(200) '$.first_name',
        last_name varchar(200) '$.last_name',
        avatar varchar(255) '$.avatar'
     ) rs; -- select query to further process the data.
END
ELSE
BEGIN
     DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
     Print @ErroMsg;
END
Exec sp_OADestroy @Object; -- destory the object when not needed anymore. If not called, the OLE object will be destroyed automatically when the batch execution is completed.

POST Method call:

Below is the script, which can be used in the stored procedure, for making HTTP call of POST method request type.

DECLARE @URL NVARCHAR(MAX) = 'https://reqres.in/api/users/'; -- our URL for post request
DECLARE @Object AS INT; -- object declaration
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) =

'{
    "name": "kiran sabne",
    "job": "leader"
}';
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; -- creating OLE object and assigning it to variable @Object

-- passing the @Object created above, with our http call and handling the response with help of sp_OAMethod 
EXEC sp_OAMethod @Object, 'open', NULL, 'post',
                 @URL,
                 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'send', null, @body
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
-- print 'responseText -> ' + @ResponseText;
IF CHARINDEX('false',(SELECT @ResponseText)) > 0
BEGIN
 SELECT @ResponseText As 'Message'
END
ELSE
BEGIN
 SELECT @ResponseText As 'New User Added Detail'
END
EXEC sp_OADestroy @Object

Summary

As mentioned earlier, usage of OLE Procedures is sensitive and must be strictly monitored. Similarly, we can also perform File Object modifications with the OLE procedures. The other obvious and preferred alternatives are creating another application server for doing work and that application having access to the database.

 
Share this