(SQL Server) UPS Tracking API

Demonstrates making a call to the UPS tracking REST API. Parses the tracking response and extracts the base64 signature image to a gif file.

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.

    DECLARE @success int

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

    -- This is the testing endpoint for the tracking API:
    DECLARE @url nvarchar(4000)
    SELECT @url = 'https://wwwcie.ups.com/rest/Track'

    -- Send an HTTP request with the following JSON body:

    -- {
    --   "UPSSecurity": {
    --     "UsernameToken": {
    --       "Username": "Your Username",
    --       "Password": "Your Password"
    --     },
    --     "ServiceAccessToken": {
    --       "AccessLicenseNumber": "Your Access License Number"
    --     }
    --   },
    --   "TrackRequest": {
    --     "Request": {
    --       "RequestOption": "1",
    --       "TransactionReference": {
    --         "CustomerContext": "Your Test Case Summary Description"
    --       }
    --     },
    --     "InquiryNumber": "YourTrackingNumber"
    --   }
    -- }
    -- 

    -- Build the above JSON.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'UPSSecurity.UsernameToken.Username', 'UPS_USERNAME'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'UPSSecurity.UsernameToken.Password', 'UPS_PASSWORD'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'UPSSecurity.ServiceAccessToken.AccessLicenseNumber', 'UPS_ACCESS_KEY'
    -- Request all activity...
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'TrackRequest.Request.RequestOption', 'activity'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'TrackRequest.Request.TransactionReference.CustomerContext', 'Your Test Case Summary Description'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'TrackRequest.InquiryNumber', '1Z12345E0205271688'

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

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, @url, 'application/json', @json
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sb
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'status = ' + @iTmp0

    -- A 200 response status indicate success.
    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sb
        RETURN
      END

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- {
    --   "TrackResponse": {
    --     "Response": {
    --       "ResponseStatus": {
    --         "Code": "1",
    --         "Description": "Success"
    --       },
    --       "TransactionReference": {
    --         "CustomerContext": "Your Test Case Summary Description"
    --       }
    --     },
    --     "Shipment": {
    --       "InquiryNumber": {
    --         "Code": "01",
    --         "Description": "ShipmentIdentificationNumber",
    --         "Value": "1Z12345E0205271688"
    --       },
    --       "ShipmentType": {
    --         "Code": "01",
    --         "Description": "Small Package"
    --       },
    --       "ShipperNumber": "12345E",
    --       "Service": {
    --         "Code": "002",
    --         "Description": "2ND DAY AIR"
    --       },
    --       "ReferenceNumber": {
    --         "Code": "01",
    --         "Value": "LINE4AND115"
    --       },
    --       "PickupDate": "19990608",
    --       "Package": {
    --         "TrackingNumber": "1Z12345E0205271688",
    --         "Activity": [
    --           {
    --             "ActivityLocation": {
    --               "Address": {
    --                 "City": "ANYTOWN",
    --                 "StateProvinceCode": "GA",
    -- 
    --                 "PostalCode": "30340",
    --                 "CountryCode": "US"
    --               },
    --               "Code": "ML",
    --               "Description": "BACK DOOR",
    --               "SignedForByName": "JOHN DOE"
    --             },
    --             "Status": {
    --               "Type": "D",
    --               "Description": "DELIVERED",
    --               "Code": "KM"
    --             },
    --             "Date": "19990610",
    --             "Time": "120000",
    --             "Document": {
    --               "Type": {
    --                 "Code": "01",
    --                 "Description": "Signature Image"
    --               },
    --               "Content": "R0lGODdhoA ... JU9Y8RdHsRKLMVJ4MVDMREAAADs=",
    --               "Format": {
    --                 "Code": "01",
    --                 "Description": "GIF"
    --               }
    --             }
    --           },
    --           {
    --             "Status": {
    --               "Type": "M",
    --               "Description": "BILLING INFORMATION RECEIVED. SHIPMENT DATE PENDING.",
    --               "Code": "MP"
    --             },
    --             "Date": "19990608",
    --             "Time": "120000"
    --           }
    --         ],
    --         "PackageWeight": {
    --           "UnitOfMeasurement": {
    --             "Code": "LBS"
    --           },
    --           "Weight": "5.00"
    --         },
    --         "ReferenceNumber": [
    --           {
    --             "Code": "01",
    --             "Value": "LINE4AND115"
    --           },
    --           {
    --             "Code": "08",
    --             "Value": "LJ67Y5"
    --           }
    --         ]
    --       }
    --     },
    --     "Disclaimer": "You are using UPS tracking service on customer integration test environment, please switch to UPS production environment once you finish the test. The URL is https://onlinetools.ups.com/webservices/Track"
    --   }
    -- }

    -- Use the online tool at Generate JSON Parsing Code
    -- to generate JSON parsing code.

    DECLARE @statusCode nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @statusCode OUT, 'TrackResponse.Response.ResponseStatus.Code'
    DECLARE @statusDescription nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @statusDescription OUT, 'TrackResponse.Response.ResponseStatus.Description'

    PRINT 'statusCode: ' + @statusCode

    PRINT 'statusDescription' + @statusDescription
    DECLARE @customerContext nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @customerContext OUT, 'TrackResponse.Response.TransactionReference.CustomerContext'
    DECLARE @inquiryNumberCode nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @inquiryNumberCode OUT, 'TrackResponse.Shipment.InquiryNumber.Code'
    DECLARE @inquiryNumberDescription nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @inquiryNumberDescription OUT, 'TrackResponse.Shipment.InquiryNumber.Description'
    DECLARE @inquiryNumberValue nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @inquiryNumberValue OUT, 'TrackResponse.Shipment.InquiryNumber.Value'
    DECLARE @shipmentTypeCode nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @shipmentTypeCode OUT, 'TrackResponse.Shipment.ShipmentType.Code'
    DECLARE @shipmentTypeDescription nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @shipmentTypeDescription OUT, 'TrackResponse.Shipment.ShipmentType.Description'
    DECLARE @shipperNumber nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @shipperNumber OUT, 'TrackResponse.Shipment.ShipperNumber'
    DECLARE @serviceCode nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @serviceCode OUT, 'TrackResponse.Shipment.Service.Code'
    DECLARE @serviceDescription nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @serviceDescription OUT, 'TrackResponse.Shipment.Service.Description'
    DECLARE @referenceNumberCode nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @referenceNumberCode OUT, 'TrackResponse.Shipment.ReferenceNumber.Code'
    DECLARE @referenceNumberValue nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @referenceNumberValue OUT, 'TrackResponse.Shipment.ReferenceNumber.Value'
    DECLARE @pickupDate nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @pickupDate OUT, 'TrackResponse.Shipment.PickupDate'
    DECLARE @trackingNumber nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @trackingNumber OUT, 'TrackResponse.Shipment.Package.TrackingNumber'
    DECLARE @unitOfMeasurementCode nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @unitOfMeasurementCode OUT, 'TrackResponse.Shipment.Package.PackageWeight.UnitOfMeasurement.Code'
    DECLARE @weight nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @weight OUT, 'TrackResponse.Shipment.Package.PackageWeight.Weight'
    DECLARE @disclaimer nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @disclaimer OUT, 'TrackResponse.Disclaimer'

    DECLARE @i int
    SELECT @i = 0
    DECLARE @activityCount int
    EXEC sp_OAMethod @json, 'SizeOfArray', @activityCount OUT, 'TrackResponse.Shipment.Package.Activity'

    PRINT 'activityCount: ' + @activityCount

    WHILE @i < @activityCount
      BEGIN

        PRINT '-- activity ' + @i
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Address.City'
        IF @iTmp0 = 1
          BEGIN
            DECLARE @city nvarchar(4000)
            EXEC sp_OAMethod @json, 'StringOf', @city OUT, 'TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Address.City'

            PRINT 'city: ' + @city
            DECLARE @provinceCode nvarchar(4000)
            EXEC sp_OAMethod @json, 'StringOf', @provinceCode OUT, 'TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Address.StateProvinceCode'
            DECLARE @postalCode nvarchar(4000)
            EXEC sp_OAMethod @json, 'StringOf', @postalCode OUT, 'TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Address.PostalCode'
            DECLARE @countryCode nvarchar(4000)
            EXEC sp_OAMethod @json, 'StringOf', @countryCode OUT, 'TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Address.CountryCode'
          END
        DECLARE @locationCode nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @locationCode OUT, 'TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Code'
        DECLARE @locationDescription nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @locationDescription OUT, 'TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Description'
        DECLARE @locationSignedForByName nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @locationSignedForByName OUT, 'TrackResponse.Shipment.Package.Activity[i].ActivityLocation.SignedForByName'

        DECLARE @activityStatusType nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @activityStatusType OUT, 'TrackResponse.Shipment.Package.Activity[i].Status.Type'

        PRINT 'activityStatusType: ' + @activityStatusType
        DECLARE @activityStatusDescription nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @activityStatusDescription OUT, 'TrackResponse.Shipment.Package.Activity[i].Status.Description'

        PRINT 'activityStatusDescription: ' + @activityStatusDescription
        DECLARE @activityStatusCode nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @activityStatusCode OUT, 'TrackResponse.Shipment.Package.Activity[i].Status.Code'

        PRINT 'activityStatusCode: ' + @activityStatusCode

        DECLARE @activityDate nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @activityDate OUT, 'TrackResponse.Shipment.Package.Activity[i].Date'
        DECLARE @activityTime nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @activityTime OUT, 'TrackResponse.Shipment.Package.Activity[i].Time'

        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'TrackResponse.Shipment.Package.Activity[i].Document'
        IF @iTmp0 = 1
          BEGIN
            DECLARE @typeCode int
            EXEC sp_OAMethod @json, 'IntOf', @typeCode OUT, 'TrackResponse.Shipment.Package.Activity[i].Document.Type.Code'
            DECLARE @typeDescription nvarchar(4000)
            EXEC sp_OAMethod @json, 'StringOf', @typeDescription OUT, 'TrackResponse.Shipment.Package.Activity[i].Document.Type.Description'
            DECLARE @documentContent nvarchar(4000)
            EXEC sp_OAMethod @json, 'StringOf', @documentContent OUT, 'TrackResponse.Shipment.Package.Activity[i].Document.Content'
            DECLARE @documentFormatCode nvarchar(4000)
            EXEC sp_OAMethod @json, 'StringOf', @documentFormatCode OUT, 'TrackResponse.Shipment.Package.Activity[i].Document.Format.Code'
            -- Format description would be something like "GIF" for a signature image.
            DECLARE @documentFormatDescription nvarchar(4000)
            EXEC sp_OAMethod @json, 'StringOf', @documentFormatDescription OUT, 'TrackResponse.Shipment.Package.Activity[i].Document.Format.Description'

            -- 01 - Signature Image
            -- 02 - Delivery Receipt
            -- 03 - Free Astray
            -- 04 - POD
            IF @typeCode = 1
              BEGIN
                -- We have a signature image.  Get the image data and save to a file.
                DECLARE @sbImagePath int
                -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
                EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbImagePath OUT

                EXEC sp_OAMethod @sbImagePath, 'Append', @success OUT, 'qa_output/sig_'
                EXEC sp_OAMethod @sbImagePath, 'Append', @success OUT, @trackingNumber
                EXEC sp_OAMethod @sbImagePath, 'Append', @success OUT, '.'
                EXEC sp_OAMethod @sbImagePath, 'Append', @success OUT, @documentFormatDescription
                DECLARE @imageData int
                -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
                EXEC @hr = sp_OACreate 'Chilkat.BinData', @imageData OUT

                EXEC sp_OAMethod @imageData, 'AppendEncoded', @success OUT, @documentContent, 'base64'
                -- Write to "qa_output/sig_1Z12345E0205271688.GIF"
                EXEC sp_OAMethod @sbImagePath, 'GetAsString', @sTmp0 OUT
                EXEC sp_OAMethod @imageData, 'WriteFile', @success OUT, @sTmp0
              END
          END
        SELECT @i = @i + 1
      END

    SELECT @i = 0
    DECLARE @refnumCount int
    EXEC sp_OAMethod @json, 'SizeOfArray', @refnumCount OUT, 'TrackResponse.Shipment.Package.ReferenceNumber'
    WHILE @i < @refnumCount
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        DECLARE @refnumCode nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @refnumCode OUT, 'TrackResponse.Shipment.Package.ReferenceNumber[i].Code'
        DECLARE @refnumValue nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @refnumValue OUT, 'TrackResponse.Shipment.Package.ReferenceNumber[i].Value'
        SELECT @i = @i + 1
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @resp


    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @sbImagePath
    EXEC @hr = sp_OADestroy @imageData


END
GO