SQL Server
SQL Server
UPS Tracking API
See more HTTP Misc Examples
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 SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
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
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
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', @url, @json, 'application/json', @resp
IF @success = 0
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
EXEC @hr = sp_OADestroy @resp
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 @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sb
EXEC @hr = sp_OADestroy @resp
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
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
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 @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sb
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @sbImagePath
EXEC @hr = sp_OADestroy @imageData
END
GO