(SQL Server) HTTP multipart-mixed POST (for a UPS Package Level Detail PLD Request)

Important: This example requires Chilkat v9.5.0.59 or greater.

Demonstrates how to create a POST for a UPS Package Level Detail PLD Request. The request must be a multipart-mixed request having two sub-parts. The first is a application/x-www-form-urlencoded with name=value URL encoded pairs. The second is a application/x-ups-binary containing a UPS PLD request.

The HTTP request created by this example looks like this:

POST /hapld/tos/kdwhapltos HTTP/1.1
Content-Type: multipart/mixed; boundary=------------050801040202040701030308
Host: domain
Content-Length: 658

--------------050801040202040701030308
Content-type: application/x-www-form-urlencoded
Content-length: 134

AppVersion=1.0&AcceptUPSLicenseAgreement=YES&VersionNumber=V4R1&UserId=myUser&Password=myPassword&ResponseType=application/x-ups-pld

--------------050801040202040701030308
Content-type: application/x-ups-binary
Content-length: 721

020094 2014041500000594196532600 000000001*AAV0Y203 DE 5941965326000001*BA1ZV0Y2036650001829 00001+0000000000000012 +0000000000000000KGS07COL10 3CMEUR000001*CA18TC239 AOK Bahnhofstr. Dresden 01099 DE *EAEVS+000000000000104200EUR*PA1ZV0Y2036650001829 02+0000012 +0000000+00000000+00000000+00000000*SA000005

--------------050801040202040701030308--

The HTTP response (if successful) is multipart MIME as shown below. This example will extract the response content using the Chilkat MIME API.

Content-type: text/html
Content-length: 138

<HTML>
<HEAD>
<TITLE>UPS Internet Software</TITLE>
</HEAD>
<BODY>
<P>UPS Internet Software, Copyright UPS 1998</P>
</BODY>
</HTML>

--BOUNDARY
Content-type: application/x-ups-psmpld
Content-length: 104

UPSOnLine%1.0%0000%0000Successful completion - No errors found.                                    

--BOUNDARY
Content-type: application/x-ups-pld
Content-length: 800

000200000787300000640 KDGPLCR1                   United Parcel Service                      09/06/16  Page:1                    Test PLD Control Report                     05:44 PM                                                                                  ------------------------------------------------------------------------------- Pickup Date:20030813 Sequence#:000005900000002 #of Segments:000000000000000005                                                                                                                                                                  Shipper#:0TEST0     Book/Page#:5900000002 Shipments:000001 Packages:0000000001 10000012900000000Successful completion - No errors found.                                        
!!! Test Upload !!!
File Uploaded: n/a

--BOUNDARY--

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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    DECLARE @req int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.HttpRequest', @req OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- The ContentType, HttpVerb, and Path properties should
    -- always be explicitly set.
    EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
    EXEC sp_OASetProperty @req, 'Path', '/hapld/tos/kdwhapltos'
    EXEC sp_OASetProperty @req, 'ContentType', 'multipart/mixed'

    -- The 1st body of the multipart/mixed HTTP request will have a content-type of application/x-www-form-urlencoded
    -- It contains URL encoded parameters.
    -- We'll use a temporary HTTP request object solely for the purpose of generating our URL encoded parameter string.
    DECLARE @paramEncoder int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.HttpRequest', @paramEncoder OUT

    EXEC sp_OAMethod @paramEncoder, 'AddParam', NULL, 'AppVersion', '1.0'
    EXEC sp_OAMethod @paramEncoder, 'AddParam', NULL, 'AcceptUPSLicenseAgreement', 'YES'
    EXEC sp_OAMethod @paramEncoder, 'AddParam', NULL, 'VersionNumber', 'V4R1'
    EXEC sp_OAMethod @paramEncoder, 'AddParam', NULL, 'UserId', 'UPS_USERID'
    EXEC sp_OAMethod @paramEncoder, 'AddParam', NULL, 'Password', 'UPS_PASSWORD'
    DECLARE @urlEncodedParams nvarchar(4000)
    EXEC sp_OAMethod @paramEncoder, 'GetUrlEncodedParams', @urlEncodedParams OUT

    -- The UPS server wants all params to be URL encoded except for the ResponseType param.
    -- Therefore, we'll add ResponseType unencoded at the end, like this:
    DECLARE @sbParams int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbParams OUT

    EXEC sp_OAMethod @sbParams, 'Append', @success OUT, @urlEncodedParams
    EXEC sp_OAMethod @sbParams, 'Append', @success OUT, '&ResponseType=application/x-ups-pld'
    -- Also, it is very important to include a CRLF at the end.  (This is to satisfy undocumented details
    -- required by the UPS PLD server.)
    EXEC sp_OAMethod @sbParams, 'Append', @success OUT, CHAR(13) + CHAR(10)

    -- Add the params as the 1st sub-part of the multipart/mixed request
    EXEC sp_OAMethod @sbParams, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @req, 'AddStringForUpload2', @success OUT, '', '', @sTmp0, '', 'application/x-www-form-urlencoded'

    -- Now for the 2nd part (using some junk data found on the developerkitcommunity.ups.com site.
    -- Notice the CRLF at the end of the string.  This is important.
    DECLARE @pld nvarchar(4000)
    SELECT @pld = '020094                                  2003081300000590000000200   000000001*AA0TEST0    DE                5900000002000001*BA1Z0TEST06800000018                 00001+0000000000000010 +0000000000000000KGS11PRE10                                   3CMEUR000001*CA18                                   United Parcel Service              Goerlitzer Str 1                                                                                         Neuss                              41460    DE                                                        *PA1Z0TEST06800000018                 02+0000010                                                                                      +0000000+00000000+00000000+00000000*SA000004' + CHAR(13) + CHAR(10)

    EXEC sp_OAMethod @req, 'AddStringForUpload2', @success OUT, '', '', @pld, '', 'application/x-ups-binary'

    -- The UPS PLD server requires Content-Length headers within the MIME sub-parts of the request.
    -- A new feature of Chilkat v9.5.0.59 is if a Content-Length sub-header is added (with an arbitrary value, such as 0),
    -- then Chilkat will compute the actual sub-part size and replace the value when the request is sent.
    EXEC sp_OAMethod @req, 'AddSubHeader', @success OUT, 0, 'Content-Length', '0'
    EXEC sp_OAMethod @req, 'AddSubHeader', @success OUT, 1, 'Content-Length', '0'

    -- A few important comments about the HTTP request that is generated:
    -- 
    -- 1) Chilkat automatically generates a random boundary string.   In 99.999% of cases, this should 
    --    be sufficient.
    -- 2) The top-level Content-Length header is automatically generated based on the actual length of the MIME message
    --    that follows the intial (topmost) MIME header.
    -- 3) The HOST header will automatically get filled in with the actual domain when SynchronousRequest
    --    method is called.

    -- Now send the request...
    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT

    -- If desired (for debugging), provide a flie path for a session log.
    -- The session log can be sent to support@chilkatsoft.com if the PLD request fails.
    EXEC sp_OASetProperty @http, 'SessionLogFilename', 'c:/aaworkarea/ups_pld/sessionLog.txt'

    -- Send the PLD request over TLS..
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'SynchronousRequest', @resp OUT, 'www.pld-certify.ups.com', 443, 1, @req
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @paramEncoder
        EXEC @hr = sp_OADestroy @sbParams
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- Load the response MIME.
    DECLARE @mime int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Mime', @mime OUT

    EXEC sp_OAGetProperty @resp, 'FullMime', @sTmp0 OUT
    EXEC sp_OAMethod @mime, 'LoadMime', @success OUT, @sTmp0

    -- If it does not have 3 sub-parts, then something is amiss...
    EXEC sp_OAGetProperty @mime, 'NumParts', @iTmp0 OUT
    IF @iTmp0 <> 3
      BEGIN

        PRINT 'Unexpected number of MIME parts.'
        EXEC sp_OAGetProperty @resp, 'FullMime', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @paramEncoder
        EXEC @hr = sp_OADestroy @sbParams
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

    -- Examine each response part.
    DECLARE @part0 int
    EXEC sp_OAMethod @mime, 'GetPart', @part0 OUT, 0

    PRINT '---- text/html part ----'
    EXEC sp_OAMethod @part0, 'GetBodyDecoded', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @part1 int
    EXEC sp_OAMethod @mime, 'GetPart', @part1 OUT, 1

    PRINT '---- application/x-ups-psmpld part ----'
    EXEC sp_OAMethod @part1, 'GetBodyDecoded', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @part2 int
    EXEC sp_OAMethod @mime, 'GetPart', @part2 OUT, 2

    PRINT '---- application/x-ups-pld part ----'
    EXEC sp_OAMethod @part2, 'GetBodyDecoded', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @part0

    EXEC @hr = sp_OADestroy @part1

    EXEC @hr = sp_OADestroy @part2

    EXEC @hr = sp_OADestroy @resp


    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @paramEncoder
    EXEC @hr = sp_OADestroy @sbParams
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @mime


END
GO