Send API POST Request in MS SQL Server
To send an API POST request in MS SQL Server using multipart/form-data, you can use the following T-SQL code:
Example of content type : multipart/form-data
DECLARE @url VARCHAR(200) = 'https://example.com/api';
DECLARE @boundary VARCHAR(50) = '---------------------------' + CAST(NEWID() AS VARCHAR(36));
DECLARE @param1Name VARCHAR(50) = 'name';
DECLARE @param1Value VARCHAR(50) = 'John Doe';
DECLARE @param2Name VARCHAR(50) = 'email';
DECLARE @param2Value VARCHAR(50) = 'johndoe@example.com';
DECLARE @param3Name VARCHAR(50) = 'file';
DECLARE @param3Value VARBINARY(MAX) = (SELECT BulkColumn FROM OPENROWSET(BULK 'C:\path\to\file.txt', SINGLE_BLOB) AS x);
DECLARE @param4Name VARCHAR(50) = 'description';
DECLARE @param4Value VARCHAR(200) = 'This is a sample file';
DECLARE @formData NVARCHAR(MAX) =
N'--' + @boundary + CHAR(13) + CHAR(10) +
N'Content-Disposition: form-data; name="' + @param1Name + N'"' + CHAR(13) + CHAR(10) +
CHAR(13) + CHAR(10) + @param1Value + CHAR(13) + CHAR(10) +
N'--' + @boundary + CHAR(13) + CHAR(10) +
N'Content-Disposition: form-data; name="' + @param2Name + N'"' + CHAR(13) + CHAR(10) +
CHAR(13) + CHAR(10) + @param2Value + CHAR(13) + CHAR(10) +
N'--' + @boundary + CHAR(13) + CHAR(10) +
N'Content-Disposition: form-data; name="' + @param3Name + N'"; filename="file.txt"' + CHAR(13) + CHAR(10) +
N'Content-Type: text/plain' + CHAR(13) + CHAR(10) +
CHAR(13) + CHAR(10) + CAST(@param3Value AS NVARCHAR(MAX)) + CHAR(13) + CHAR(10) +
N'--' + @boundary + CHAR(13) + CHAR(10) +
N'Content-Disposition: form-data; name="' + @param4Name + N'"' + CHAR(13) + CHAR(10) +
CHAR(13) + CHAR(10) + @param4Value + CHAR(13) + CHAR(10) +
N'--' + @boundary + N'--' + CHAR(13) + CHAR(10);
DECLARE @xmlhttp INT;
DECLARE @status INT;
DECLARE @responseText VARCHAR(MAX);
EXEC sp_OACreate 'MSXML2.XMLHTTP', @xmlhttp OUT;
EXEC sp_OAMethod @xmlhttp, 'open', NULL, 'POST', @url, 'false';
EXEC sp_OAMethod @xmlhttp, 'setRequestHeader', NULL, 'Content-Type', 'multipart/form-data; boundary=' + @boundary;
EXEC sp_OAMethod @xmlhttp, 'send', NULL, @formData;
EXEC sp_OAMethod @xmlhttp, 'status', @status OUTPUT;
EXEC sp_OAMethod @xmlhttp, 'responseText', @responseText OUTPUT;
EXEC sp_OADestroy @xmlhttp;
IF @@ERROR = 0 AND @status = 200
BEGIN
PRINT 'API request successful! Response: ' + @responseText;
END
ELSE
BEGIN
PRINT 'API request failed.';
END
In this code, we first declare a variable to store the response from the API. We then create a new instance of the MSXML2.ServerXMLHTTP object using the sp_OACreate stored procedure.
Next, we use sp_OAMethod to open a new request with the POST method and the desired API endpoint. We then set the request header to specify that we are sending multipart/form-data with the boundary set to ----MyBoundary.
We then create the body of the request using a series of CHAR(13) + CHAR(10) (carriage return + line feed) to specify the formatting of the multipart/form-data. We include a Content-Disposition header for each part of the request, with the name parameter set to the desired key name and the value included in the body of the part.
Finally, we use sp_OAMethod again to send the request with the generated multipart/form-data body, and retrieve the response text from the API using sp_OAMethod.
As with the previous example, it's worth noting that this method requires the Ole Automation Procedures option to be enabled in SQL Server, and may require additional security considerations depending on your environment.
Example of content type : application/json
DECLARE @responseMessage NVARCHAR(MAX)
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @responseMessage OUT
EXEC sp_OAMethod @responseMessage, 'open', NULL, 'POST', 'https://your-api-endpoint', 'false'
EXEC sp_OAMethod @responseMessage, 'setRequestHeader', null, 'Content-Type', 'application/json'
DECLARE @body NVARCHAR(MAX)
SET @body = '{"key": "value"}'
EXEC sp_OAMethod @responseMessage, 'send', NULL, @body
EXEC sp_OAMethod @responseMessage, 'responseText', @responseMessage OUT
SELECT @responseMessage
Example of content type : application/x-www-form-urlencoded
DECLARE @responseMessage NVARCHAR(MAX) EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @responseMessage OUT EXEC sp_OAMethod @responseMessage, 'open', NULL, 'POST', 'https://your-api-endpoint', 'false' EXEC sp_OAMethod @responseMessage, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded' DECLARE @body NVARCHAR(MAX) SET @body = 'key1=value1&key2=value2' EXEC sp_OAMethod @responseMessage, 'send', NULL, @body EXEC sp_OAMethod @responseMessage, 'responseText', @responseMessage OUT SELECT @responseMessage
Comments
Post a Comment