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!)

Read more: 5 DevOps Services Trends to watch in 2021

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

Consulting is free – let us help you grow!