Efficient Way To Call Web API From SQL Server

Jump To Right Section Show

This put up goes via the steps and processes to Call Web API from SQL Server Stored Procedure. These steps are relevant to Microsoft SQL Server 2014.

Pre-Requisites

Initially, to make an HTTP request name Web API from SQL Server Stored Procedure, it’s worthwhile to allow the OLE automation procedures. By default, the OLE automation procedures are disabled in your SQL server instance. Solely a system administrator person can enable entry to OLE Automation procedures through the use of sp_configure.


sp_configure 'present superior choices', 1;
GO
RECONFIGURE;
GO

sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

sp_configure 'present superior choices', 1;

RECONFIGURE;

sp_configure 'Ole Automation Procedures', 1;

RECONFIGURE;

OLE Automation Procedures

There are numerous predefined procedures supplied by the SQL server utilizing which we are able to use to eat API in a saved process:

1) sp_OACreate: sp_OACreate permits you to create an occasion of an OLE object. The newly created OLE object is robotically destroyed on the finish of the Transact-SQL assertion batch.


sp_OACreate  clsid  , objecttoken OUTPUT [ , context ]

sp_OACreate clsid , objecttoken OUTPUT [ , context ]

objecttoken OUTPUT is the object returned, and it should be an area variable of information sort int. This object token will establish the created OLE object and might be used to name the opposite OLE Automation stored procedures.

2) sp_OAMethod: sp_OAMethod means that you can name a way of an OLE object.


sp_OAMethod objecttoken , methodname
[ , returnvalue OUTPUT ]
[ , [ @parametername = ] parameter [ OUTPUT ] [ ...n ] ]

sp_OAMethod objecttoken , methodname

[ , returnvalue OUTPUT ]

[ , [ @parametername = ] parameter [ OUTPUT ] [ ...n ] ]

objecttoken is the token of an OLE object created through the use of sp_OACreate.
methodname is the tactic title to name.
returnvalue OUTPUT is the return worth of the tactic. When specified, it should be an area variable of the suitable knowledge sort.
parameter is a technique parameter. When specified, it should be a worth of the suitable knowledge sort.

3) sp_OADestroy: sp_OADestroy will destroy a created OLE object. As talked about within the sp_OACreate technique, If sp_OADestroy is just not known as, the created OLE object will robotically be destroyed on the finish of the batch.


sp_OADestroy objecttoken

sp_OADestroy objecttoken

objecttoken is the token of an OLE object created through the use of sp_OACreate.

Notice: OLE Automation procedures do present different procedures, however we are going to solely use solely the above three in our instance.

Call Web API from SQL Server Stored Procedure

The next script Put together and Ship API request. Request URL, Headers, and Physique are outlined utilizing SQL parameters.


/*====================================================================                            
* --Mission Identify         : SMS Panel                             
* --Description          : SMS Broadcaster                                     
* --Final Modify          : Selim Ahmed                                
* --Begin Date(MM/DD/YY) : 11/12/2022                             
* ====================================================================*/        
                                                    
CREATE PROCEDURE [dbo].[SP_SMS_BROADCASTER]
	@Masking	NVARCHAR(MAX),
	@ReceiverNumber	NVARCHAR(MAX),
	@Message	NVARCHAR(MAX),
	@End result		INT=0 OUTPUT
AS                                      
BEGIN                                        
SET NOCOUNT ON;                                      
	DECLARE                               
		@ErrorState  INT,                              
		@ErrorMessage VARCHAR(100),                                     
		@ErrorSeverity VARCHAR(32)  
             
BEGIN TRY  

	DECLARE @ret INT,@token INT;
	DECLARE @ApiUrl NVARCHAR(MAX), @contentType NVARCHAR(64),@responseText NVARCHAR(2000),@standing NVARCHAR(32),@statusText NVARCHAR(32);
	DECLARE @UserName NVARCHAR(MAX)='username', @Password NVARCHAR(MAX)='password'

	SET @contentType = 'utility/x-www-form-urlencoded';
	SET @ApiUrl=('http://api.example-sms-provider.com/externalApiSendTextMessage.php?masking='+@Masking+'&userName='+UserName+'&password='+@Password+'&MsgType=TEXT&receiver='+@ReceiverNumber+'&message='+@Message)
	--PRINT  @ApiUrl

	-- Open the connection.
	EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
	IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
	-- Ship the request.
	EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @ApiUrl, 'false';
	EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content material-type', @contentType;
	EXEC @ret = sp_OAMethod @token, 'ship';

	-- Deal with the response.
	EXEC @ret = sp_OAGetProperty @token, 'standing', @standing OUT;
	EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
	EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

	-- Present the response.
	--PRINT 'Standing: ' + @standing + ' (' + @statusText + ')';
	--PRINT 'Response textual content: ' + @responseText;
	IF LEN(@responseText)>10 AND CHARINDEX('"success":1',@responseText) > 0
	BEGIN
		SET @End result=1
	END

	-- Shut the connection.
	EXEC @ret = sp_OADestroy @token;
	IF @ret <> 0 RAISERROR('Unable to shut HTTP connection.', 10, 1);

	RETURN @End result

END TRY                                      
	BEGIN CATCH                                        
		SET @ErrorState = ERROR_STATE();                                      
		SET @ErrorSeverity = ERROR_SEVERITY();                                      
		SET @ErrorMessage = CAST(ERROR_MESSAGE() as varchar(100));                                        
		RAISERROR ( @ErrorMessage,@ErrorSeverity,@ErrorState );                                
	END CATCH                                
END


--GO           
--EXEC SP_SMS_BROADCASTER @Masking='TEST',@ReceiverNumber='01711148432',@Message='Hope you might be properly by the grace of Almighty Allah'

/*====================================================================

* --Mission Identify : SMS Panel

* --Description : SMS Broadcaster

* --Final Modify : Selim Ahmed

* --Begin Date(MM/DD/YY) : 11/12/2022

* ====================================================================*/

CREATE PROCEDURE [dbo].[SP_SMS_BROADCASTER]

@Masking NVARCHAR(MAX),

@ReceiverNumber NVARCHAR(MAX),

@Message NVARCHAR(MAX),

@End result INT=0 OUTPUT

BEGIN

SET NOCOUNT ON;

DECLARE

@ErrorState INT,

@ErrorMessage VARCHAR(100),

@ErrorSeverity VARCHAR(32)

BEGIN TRY

DECLARE @ret INT,@token INT;

DECLARE @ApiUrl NVARCHAR(MAX), @contentType NVARCHAR(64),@responseText NVARCHAR(2000),@standing NVARCHAR(32),@statusText NVARCHAR(32);

DECLARE @UserName NVARCHAR(MAX)='username', @Password NVARCHAR(MAX)='password'

SET @contentType = 'utility/x-www-form-urlencoded';

SET @ApiUrl=('http://api.example-sms-provider.com/externalApiSendTextMessage.php?masking='+@Masking+'&userName='+UserName+'&password='+@Password+'&MsgType=TEXT&receiver='+@ReceiverNumber+'&message='+@Message)

--PRINT @ApiUrl

-- Open the connection.

EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;

IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Ship the request.

EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @ApiUrl, 'false';

EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content material-type', @contentType;

EXEC @ret = sp_OAMethod @token, 'ship';

-- Deal with the response.

EXEC @ret = sp_OAGetProperty @token, 'standing', @standing OUT;

EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;

EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Present the response.

--PRINT 'Standing: ' + @standing + ' (' + @statusText + ')';

--PRINT 'Response textual content: ' + @responseText;

IF LEN(@responseText)>10 AND CHARINDEX('"success":1',@responseText) > 0

BEGIN

SET @End result=1

END

-- Shut the connection.

EXEC @ret = sp_OADestroy @token;

IF @ret <> 0 RAISERROR('Unable to shut HTTP connection.', 10, 1);

RETURN @End result

END TRY

BEGIN CATCH

SET @ErrorState = ERROR_STATE();

SET @ErrorSeverity = ERROR_SEVERITY();

SET @ErrorMessage = CAST(ERROR_MESSAGE() as varchar(100));

RAISERROR ( @ErrorMessage,@ErrorSeverity,@ErrorState );

END CATCH

END

--GO

--EXEC SP_SMS_BROADCASTER @Masking='TEST',@ReceiverNumber='01711148432',@Message='Hope you might be properly by the grace of Almighty Allah'

Conclusion

There are numerous alternate options once you wish to eat API in your system or put up knowledge to different techniques. Our vote is that the OLE automation process can come in useful once you wish to omit code behind and sync the information into the database. Once you use OLE procedures correctly, sp_OA help permits you to do issues you may’t do one other method. However there are probabilities that you just would possibly end up in a rut.