Skip to content

Instantly share code, notes, and snippets.

@JahsonKim
Last active May 17, 2023 12:56
  • Star 24 You must be signed in to star a gist
  • Fork 6 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save JahsonKim/837c85fa9406fa4eeb0bb6ddb91e9e98 to your computer and use it in GitHub Desktop.
How to send http POST request from sql server stored procedure. Important! For some reason sp_OAGetProperty is returning null. Am not sure why and if anyone has an idea can update the gists. In case you need to try another option SQL CLR would help. Check here https://gist.github.com/JahsonKim/05e6af7744f2d7ef814e5ed331419db5
--This query enables ole automation procedures. set 0 to disable
exec master.dbo.sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
--OLE utomation disabled the following error is thrown.
--SQL Server blocked access to procedure 'sys.sp_OACreate' of component
--'Ole Automation Procedures' because this component is turned off as part
--of the security configuration for this server.
--A system administrator can enable the use of 'Ole Automation Procedures'
--by using sp_configure. For more information about enabling 'Ole Automation Procedures',
--search for 'Ole Automation Procedures' in SQL Server Books Online.
--Running the above query may result to this erro
--The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.
--To fix this error run the query below and the enable OLE automation
exec master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
--create/alter a stored proceudre accordingly
create procedure webRequest
as
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);
DECLARE @Authorization NVARCHAR(200);
--set your post params
SET @authHeader = 'BASIC 0123456789ABCDEF0123456789ABCDEF';
SET @contentType = 'application/x-www-form-urlencoded';
SET @postData = 'KeyValue1=value1&KeyValue2=value2'
SET @url = 'set your url end point here'
-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
--set a custom header Authorization is the header key and VALUE is the value in the header
EXEC sp_OAMethod @token, 'SetRequestHeader', NULL, 'Authorization', 'VALUE'
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', NULL, @postData;
-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;
-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);
go
@JahsonKim
Copy link
Author

JahsonKim commented Sep 10, 2020

You can set the header as follows

EXEC sp_OAMethod @token, 'SetRequestHeader', NULL, 'api-key', 'YOUR API KEY'

@ashok877
Copy link

ashok877 commented Sep 10, 2020 via email

@JahsonKim
Copy link
Author

You are welcome.

@ashok877
Copy link

Futher, i would like to capture the successful/ failed response(in integer value like 200 or 400) from the web API and insert into a table and keep it for future record.
To capture, two fields would be enough -- say for example --- Unique POST ID and response.
May i request you to guide me further on it JahsonKim sir?

Thanks in advance!
Have a good day sir!

@JahsonKim
Copy link
Author

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

In the above code snippet, @status contains the HTTP request status code which could be 200, 201 or any other HTTP status code.
@responseText contains the response from your API which you can process and then insert into a table.

I suppose that your response is in JSON format, SQL Server 2016 and later provides support for JSON as in this documentation you can extract JSON data and use it in queries.

I hope this helps.

@ashok877
Copy link

Thank you again. Simply had to insert unique post id & response into audit table.

Thank you again for your support.

@JahsonKim
Copy link
Author

👍👍

@ashok877
Copy link

Can i have your email JSON JahsonKim?
would share my code snippet to get out of an issue i am stuck at.

Thanks a lot !

@JahsonKim
Copy link
Author

Can i have your email JSON JahsonKim?
would share my code snippet to get out of an issue i am stuck at.

Thanks a lot !

You can use kimulukyalo [at] gmail

@basantsh
Copy link

Hi, I want to call a web service authenticated by oauth2 using application client id and secret. Could you suggest how can I implement that authentication mechanism? Since application uses AAD for authorization, I can't have username and password for basic authentication.

@JahsonKim
Copy link
Author

Basantsh you could pass them as headers in this manner
EXEC sp_OAMethod @token, 'SetRequestHeader', NULL, 'api-key', 'YOUR API KEY'
to your web service

@tazuddinleton
Copy link

tazuddinleton commented Oct 31, 2021

Hi, I can't make it work for localhost https://localhost:44327/api/event/test but it perfectly works with other domains like https://disease.sh/v3/covid-19/states. Can't figure out why, any help will be appreciated. I have a running .NET 5 project which works nicely via postman.
Thanks

@JahsonKim
Copy link
Author

Are you accessing https://localhost:44327/api/event/test from your local machine or another machine?

@tazuddinleton
Copy link

Hi @JahsonKim, thanks for replying, I'm accessing it from my local machine.

@JahsonKim
Copy link
Author

Use http instead of https for localhost

@tazuddinleton
Copy link

Hi @JahsonKim, using http worked!
Thank you so much. :)

@mrpezhman
Copy link

Hi @JahsonKim
how we can take OAUth2 Token at this way?
In fact, I need to get Json Bearer Token output by running this SP

@EOM
Copy link

EOM commented Jan 6, 2022

Hii
One example use in Triggers Insert or Update

ALTER TRIGGER [dbo].[my_test]
ON [dbo].[webhook_logs]
WITH EXECUTE AS CALLER
AFTER INSERT, UPDATE
AS
BEGIN

	SET NOCOUNT ON
	
	DECLARE @id INT
	DECLARE @procesado INT = 0
	DECLARE @urlBase VARCHAR(8000) = 'http://192.168.0.2:8080/' -- Local use or Best security use HTTPS://
	DECLARE @jsonBody VARCHAR(8000) = ''
	DECLARE @jsonReps VARCHAR(8000) = ''

	SELECT @id = I.id, @procesado = I.procesado, @jsonBody = I.json FROM inserted I

	-- Change
	IF (SELECT @jsonBody)<>'' AND @procesado=0
	BEGIN 

		SELECT @jsonReps = dbo.my_function_post_json(@urlBase+'controller/post.json', @jsonBody);

		IF CHARINDEX('"ok":true', @jsonReps)>0
		BEGIN
				-- IS INSERT..?
				IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)  
				BEGIN
					UPDATE [dbo].[tableX] SET
								[field1] = 1,
								[field2] = GETDATE(),
								[field3] = GETDATE() 
					WHERE [id] = @id 
				END
				ELSE
				BEGIN
					UPDATE [dbo].[tableX] SET
								[field1] = 1,
								[field2] = GETDATE() 
					WHERE [id] = @id 
				END
		
		END
		
	END

END

@NelsonV27
Copy link

@JahsonKim Good morning everyone, your publication is great but I would like to know how you send a cookie in the post request, such as authorization headers since in a project I ran into this problem and it does not return the data that is in json, someone who has idea of how to keep that session cookie until the client is closed,
that if I am performing a stored procedure with that requirement

Thank you very much in advance, I look forward to your answers

@ROCNDAV
Copy link

ROCNDAV commented Aug 9, 2022

Three years later, you're still helping people! Thanks so much for this.

@kadzema
Copy link

kadzema commented Jan 24, 2023

Yes! Still very useful! Thank you!

@BumboobeeThe
Copy link

BumboobeeThe commented Feb 24, 2023

I've triyng to use like the example above, here's my code to give a post:

DECLARE @object int;
DECLARE @url varchar(8000);
DECLARE @token varchar(1000);
DECLARE @headers varchar(8000);
DECLARE @body varchar(8000);
DECLARE @response varchar(8000);

-- Define o endpoint da stored procedure
SET @url = 'https://api.openai.com/v1/completions';

-- Define o token de autenticação
SET @token = 'Bearer openIAToken';

-- Define o cabeçalho da requisição com o token de autenticação e o tipo de conteúdo JSON
SET @headers = 'Authorization: ' + @token + char(13) + char(10)
  + 'Content-Type: application/json' + char(13) + char(10);

-- Define o corpo da mensagem no formato JSON
SET @body = '{"model": "text-davinci-001", "prompt": "What gifts should I buy for Christmas?", "max_tokens": 64, "temperature": 0.5}';

-- Cria o objeto de conexão HTTP
EXEC sp_OACreate 'MSXML2.XMLHTTP', @object OUT;

-- Envia a requisição POST com o corpo da mensagem no formato JSON
EXEC sp_OAMethod @object, 'open', NULL, 'POST', @url, false;
EXEC sp_OAMethod @object, 'setRequestHeader', NULL, @headers;
EXEC sp_OAMethod @object, 'send', NULL, @body;

-- Obtém a resposta da requisição
EXEC sp_OAMethod @object, 'responseText', @response OUTPUT;

-- Imprime a resposta
PRINT @response;

-- Libera o objeto de conexão HTTP
EXEC sp_OADestroy @object;

But the response is always empty. Any ideias on how may be causing this empty response?

UPDATE
Here is my solution to call all kind methods inside ``Sql Server`

@rickheber
Copy link

Olá Jose, tudo bem?

Estou com o mesmo problema. O meu response sempre vem vazio. Você conseguiu achar uma solução ou correção do código?

Grato!

@BumboobeeThe
Copy link

BumboobeeThe commented Mar 14, 2023

Oi, @rickheber... td certo?

Consegui encontrar uma solução sim, inclusive fui um pouco mais além, montei os metodos de GET e PUT.
ô, vale resaltar que o uso do metodo de interação com json, como por exemplo o JSON_VALUE, somente estão disponíveis a partir da versão 2016 do SQL Server.

Mas se voce possuir um versão inferior, pode tentar verificar se a mesma possui compatibilidade com esses metodos,

SELECT compatibility_level
FROM sys.databases
WHERE name = 'DataBaseName';

Caso o retorno seja um valor igual ou superior a 130 (default para versão 2016), significa que é compatível, portanto não precisa fazer a atualização da versão!

Eu postei as procedures em meu GitHuib pessoal, no serviço do gist, voce pode acessar elas por aqui.

Outra coisa, antes rodar as procedures, você precisa ativar as configurações de conexão externa do SQL Server, basta rodar o comando abaixo:

sp_configure 'show advanced options', 1;
GO
     RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
    RECONFIGURE;
GO
  • Caso elas não estejam ativas, as procedures não funcionaram!!
    Qualquer duvida, pode abrir uma nova discussão!!

@BumboobeeThe
Copy link

Not working with SQLServer 2008. Could you help update this?

Sql Server 2008 probabily do not suport this methods, try run this comand in you Sql Server, before run the procedure:

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

This basic allows you to use the methods to call external services in Sql Server.

If that do not help, try see in the link this forum about compatibility ⬇
-- See about compatibility level here

@rickheber
Copy link

Oi José....

Cara, muito obrigado pelo seu rapido retorno! Eu vou dar uma olhada na sua publicação agora mesmo!

Por enquanto, muito obrigado por compartilhar o seu conhecimento!

Abraços

@BumboobeeThe
Copy link

Abraço, @rickheber 🤠🤙

@ericxin1982
Copy link

@JahsonKim

If the data to post is not querystring mode, it is json structure writing, how to adjust this?

Thanks
Eric Xin

@JahsonKim
Copy link
Author

For some reason sp_OAGetProperty is returning null.

Am not sure why and if anyone has an idea can update the gists.
In case you are looking for an alternative to making http requests from the database, you can try SQL CLR.
Check here on how to implement SQL CLR

@JahsonKim
Copy link
Author

JahsonKim commented May 17, 2023

@ericxin1982 If you sent the post data as json string I think it will processed by the API. Ensure you also set the content type as application/json. If this doesn't work maybe you can use SQL CLR which is more flexible.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment