What? Calling an API from Database?

Recently we came across a situation where we need to notify the web application when there are some data updates in MSSQL database, automatically.

The only solution for this, I could find is firing HTTP request from the database itself, I know this is a bit weird but we gave it a try and YO man… succeeded!

How we did it?

Let’s have a look at how it can be implemented.

Get API From Database

A basic requirement is you have to have a windows instance with MS SQL server because SQL server uses a predefined stored procedure to fire HTTP request which is supported only on windows! (It’s Microsoft, you know!)

You can use SQL Server Management Studio (SSMS) editor as an interface which is quite user-friendly.

First of all, you need to enable you OLE Automation services, you can do this with the following query, run it through your favorite editor:

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

Now, Suppose we have orders table and we want to update order status of a particular order and pass the order number and order status to the application. So first we will write a stored procedure for firing HTTP request which will pass these parameters to our application. A procedure is as follows:

CREATE procedure [dbo].[change_order_status](
@order_number varchar(max),
@delivery_status int
)
as
// Variable declaration
DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);
// Set Authentications
SET @authHeader = 'BASIC 0123456789ABCDEF0123456789ABCDEF';
SET @contentType = 'application/x-www-form-urlencoded';
// Set your desired url where you want to fire request
SET @url = 'rel="noopener">http://localhost:3000/api/v1/orders/update_status?' + 'id=' + @order_number + '&delivery_status=' + cast(@delivery_status as varchar)
// Open a connection
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
// make a request
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send'
// Handle responce
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
// Print responec
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;

Now, we will write a trigger which will get fired after every update action on the orders table and will call our above-stored procedure.

CREATE TRIGGER [dbo].[update_order_state]
ON [dbo].[orders]
AFTER UPDATE
AS
DECLARE @order_number varchar(max)
DECLARE @delivery_status int
// set values to local variables for passing to stored procedure
SELECT @order_number = order_number from inserted
SELECT @delivery_status = delivery_status FROM inserted
// call stored procedure
EXEC change_order_status @order_number,@delivery_status

Finally, we will write a simple update query:

UPDATE orders SET
[delivery_status] = 1
WHERE
order_number = 'order_2'

That’s It….! Once you fire this update query, your trigger will get fired and stored procedure will get executed which will fire HTTP request!

Read Also: Amazon S3: Enable Bucket Versioning

Click here for more details


At BoTree Technologies, we build enterprise apps with our web development team of 30+ engineers.

We also specialize in RPA, AI, Python, Django, JavaScript and ReactJS.

Consulting is free – let us help you grow!