1 year ago

#380656

test-img

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

Accepted video resources