1 year ago
#380656
Rob
Difficulty using sp_OAMethod [MSXML2.ServerXMLHTTP] to post multipart/form-data
Code attempts to post a multpart form to API service. which does not recognize it as multipart. (Content should be multipart)
The request works fine in Postman, I think I could trouble shoot it if I could see the actual HTTP request being sent.
CREATE PROCEDURE [dbo].[test_QAAPI_attachments]
as
BEGIN
SET TEXTSIZE -1;
Declare @Object as Int;
DECLARE @hr int;
Declare @json as table(Json_Table nvarchar(max));
DECLARE @contentType VARCHAR(8000);
DECLARE @contentTypeForm VARCHAR(8000);
Declare @url as Varchar(2048);
Declare @errorDesc as nvarchar (max);
Declare @len as int;
DECLARE @resp int;
DECLARE @jsonText nvarchar(max);
DECLARE @authHeader as varchar(4000);
DECLARE @ret Int;
DECLARE @token Int;
DECLARE @responseText VARCHAR(8000);
DECLARE @runMode as varchar(5); -- run, test, train
SET @contentType = 'application/json';
SET @contentTypeForm = 'application/x-www-form-urlencoded';
DECLARE @moduleReference varchar(2);
DECLARE @formattedAccount varchar(21);
DECLARE @fileBinary as varbinary(max)
Declare @jsonReturned as varchar(max)
Set @errorDesc = 'No Errors'
SET @contentType = 'application/json';
set @url = 'https://qaapi71.auth.civicalg.com/main/api/v2/authority/login'
SET @jsonText =
'{
"userName": "apitest",
"password": "[REMOVED]"
}'
-- Getting the accessToken from Authority API...
Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT
EXEC @hr= sp_OAMethod @Object, 'open', NULL, 'post', @url, 'false'
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'send', null, @jsonText
EXEC sp_OAMethod @Object, 'responseText', @json OUT
INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
Select @authHeader = 'Bearer ' + f.accessToken
FROM OPENJSON(
(select * from @json), N'$.result')
WITH (
accessToken nvarchar(4000) N'$.accessToken'
) as f
Delete @json
DECLARE @formBody varchar(max);
set @url= 'https://qaapi71.auth.civicalg.com/main/api/v2/recordManagement/attachments'
-- text below copied directly from successful Postman http request...
SET @formBody='
----------------------------028844704192657132624492
Content-Disposition: form-data; name="apiVersion"
2
----------------------------028844704192657132624492
Content-Disposition: form-data; name="ModuleReference"
DD
----------------------------028844704192657132624492
Content-Disposition: form-data; name="FormattedAccount"
002.2020.00005653.001
----------------------------028844704192657132624492
Content-Disposition: form-data; name="ExternalDocumentType"
UPLOAD
----------------------------028844704192657132624492
Content-Disposition: form-data; name="Description"
some description
----------------------------028844704192657132624492
Content-Disposition: form-data; name="FileName"
test.jpg
----------------------------028844704192657132624492
Content-Disposition: form-data; name="FileUpload"; filename=""
----------------------------028844704192657132624492--'
-- final [--] indicates the end.
DECLARE @boundary as varchar(200)
SET @boundary='----------------------------028844704192657132624492'
DECLARE @L as varchar(20)
SET @L=CAST (len(@formBody) as varchar)
-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 20, 3);
-- Send the request.
EXEC @ret = sp_OAMethod @token, 'Open', null, 'POST', @Url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-Length',@L;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-Type','multipart/form-data','boundary',@boundary;
EXEC @ret = sp_OAMethod @token, 'send',NULL, @formBody;
EXEC sp_OAMethod @token, 'responseText', @responseText OUTPUT
INSERT into @json (Json_Table) exec sp_OAGetProperty @token, 'responseText'
select @formBody
select * from @json
END
sql-server
tsql
multipartform-data
0 Answers
Your Answer