Post Request API with SQL Server

Question

我想在SQL Server中创建一个存储过程来完成一个POST请求。该请求将接受两个参数。

我在Postman中使用的Curl:

curl -X 'POST' 
'https://link' 
-H 'accept: application/json' 
-H 'Authorization: Bearer somepassword' 
-H 'Content-Type: application/json' 
-d '{
"userName": "username",
"password": "password"
}'

我试着写下面的代码，但它似乎不工作…

DECLARE @token INT;
DECLARE @ret INT;
DECLARE @url NVARCHAR(MAX);
DECLARE @apiKey NVARCHAR(32);
DECLARE @json AS TABLE(Json_Table NVARCHAR(MAX))
DECLARE @userName varchar(max)              
DECLARE @password varchar(max)
DECLARE @body     varchar(max)
SET @url = 'https://link'
SET @userName = 'username'
SET @password = 'password'
SET @body = '
{
"userName": "'+@username+'",
"password": "'+@password+'"
}
'
EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
IF @ret <> 0 
RAISERROR('Unable to open HTTP connection.', 10, 1);
-- This calls the necessary methods.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'send', null, @body
-- Grab the responseText property, and insert the JSON string into a table temporarily. This is very important, if you don't do this step you'll run into problems.
INSERT INTO @json (Json_Table) 
EXEC sp_OAGetProperty @token, 'responseText'
-- This is all I have to add to insert into a non-existing table.
SELECT * 
FROM OPENJSON((SELECT * FROM @json))

Answer 1

我建议不要使用遗留的、难以使用的OA过程。相反，这是一个通过SQLCLR的。net库的任务。多年来，我一直使用这个库来处理来自数据库的API调用。