(SQL Server) Send SOAP multipart/related with XML Body and Zip Attachment

Demonstrates how to construct and send a multipart/related POST containing a SOAP XML body and a binary zip attachment.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example constructs and sends the following HTTP request.
    -- (The boundary strings will be different and auto-generated by Chilkat)

    -- Accept-Encoding: gzip,deflate
    -- SOAPAction: invio
    -- Content-Type: Multipart/Related; boundary="MIME_boundary"; type="text/xml"; start="mailto:rootpart@soapui.org"
    -- MIME-Version: 1.0
    -- 
    -- --MIME_boundary
    -- Content-Type: text/xml; charset=UTF-8
    -- Content-Transfer-Encoding: 8bit
    -- Content-ID: <rootpart@soapui.org>
    --  
    -- <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:attachment.ws.it">
    -- <soapenv:Header/>
    -- <soapenv:Body>
    --   <urn:inputBean>
    --    <nomeFileAllegato>myfile.zip</nomeFileAllegato>
    --   </urn:inputBean>
    -- </soapenv:Body>
    -- </soapenv:Envelope>
    --  
    -- --MIME_boundary
    -- Content-Type: application/zip
    -- Content-Transfer-Encoding: binary
    -- Content-ID: <myfile.zip>
    -- Content-Disposition: attachment; name="myfile.zip"; filename="myfile.zip"
    -- 
    -- ... binary zip data goes here ...
    -- --MIME_boundary--
    -- 

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Accept-Encoding', 'gzip,deflate'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'SOAPAction', 'invio'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'Multipart/Related; boundary="MIME_boundary"; type="text/xml"; start="mailto:rootpart@soapui.org"'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'MIME-Version', '1.0'

    -- Build the SOAP XML:
    -- Use this online tool to generate the code from sample XML: 
    -- Generate Code to Create XML

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'soapenv:Envelope'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:soapenv', 'http://schemas.xmlsoap.org/soap/envelope/'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:urn', 'urn:attachment.ws.it'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soapenv:Header', ''
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soapenv:Body|urn:inputBean|nomeFileAllegato', 'myfile.zip'

    -- Build the SOAP XML sub-part
    EXEC sp_OASetProperty @rest, 'PartSelector', '1'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'text/xml; charset=UTF-8'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Transfer-Encoding', '8bit'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-ID', '<rootpart@soapui.org>'
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'SetMultipartBodyString', @success OUT, @sTmp0

    -- Build the sub-part containing the .zip
    EXEC sp_OASetProperty @rest, 'PartSelector', '2'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/zip'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Transfer-Encoding', 'binary'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-ID', '<myfile.zip>'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Disposition', 'attachment; name="myfile.zip"; filename="myfile.zip"'

    -- Add a zip file to the 2nd sub-part body.
    -- This example will load the .zip file into memory, but it is also possible to stream directly from the file as the request is sent
    -- by calling SetMultipartBodyStream.
    DECLARE @bd int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, 'qa_data/zips/helloWorld.zip'
    EXEC sp_OAMethod @rest, 'SetMultipartBodyBd', @success OUT, @bd

    -- To be safe, always revert the PartSelector back to 0..
    EXEC sp_OASetProperty @rest, 'PartSelector', '0'

    -- Send the request by connecting to the web server and then sending..
    -- Connect using TLS.
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'www.chilkatsoft.com', 443, 1, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    -- In this example, we're setting DebugMode.
    -- When debug mode is turned on, no request is actually sent.
    -- Instead, the request that would be sent is recorded to memory
    -- and we can retrieve it afterwards..
    EXEC sp_OASetProperty @rest, 'DebugMode', 1

    DECLARE @responseStr nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestMultipart', @responseStr OUT, 'POST', '/someTargetPath'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    -- Retrieve the request that would've been sent and save it to a file.
    -- We can then examine the file to see if the request was structured as we desired.
    -- (Note: The zip bytes will be binary data and will appear as garbled garbage in a text editor..)
    DECLARE @bdRequest int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdRequest OUT

    EXEC sp_OAMethod @rest, 'GetLastDebugRequest', @success OUT, @bdRequest
    EXEC sp_OAMethod @bdRequest, 'WriteFile', @success OUT, 'qa_output/multipartRelatedRequest.bin'


    PRINT 'OK, examine the output file..'

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @bdRequest


END
GO