(SQL Server) UPS Address Validation (City, State, Zip)

Demonstrates making a call to the UPS address validation REST API.

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 address validation:
    DECLARE @url nvarchar(4000)
    SELECT @url = 'https://wwwcie.ups.com/rest/AV'

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

    -- {
    --   "AccessRequest": {
    --     "AccessLicenseNumber": "Your Access License Number",
    --     "UserId": "Your Username",
    --     "Password": "Your Password"
    --   },
    --   "AddressValidationRequest": {
    --     "Request": {
    --       "TransactionReference": {
    --         "CustomerContext": "Your Customer Context"
    --       },
    --       "RequestAction": "AV"
    --     },
    --     "Address": {
    --       "City": "ALPHARETTA",
    --       "StateProvinceCode": "GA",
    --       "PostalCode": "30005"
    --     }
    --   }
    -- }

    -- 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, 'AccessRequest.AccessLicenseNumber', 'UPS_ACCESS_KEY'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'AccessRequest.UserId', 'UPS_USERNAME'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'AccessRequest.Password', 'UPS_PASSWORD'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'AddressValidationRequest.Request.TransactionReference.CustomerContext', 'Your Customer Context'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'AddressValidationRequest.Request.RequestAction', 'AV'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'AddressValidationRequest.Address.City', 'ALPHARETTA'
    -- We're making an intentional mistake here by passing CA instead of GA.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'AddressValidationRequest.Address.StateProvinceCode', 'CA'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'AddressValidationRequest.Address.PostalCode', '30005'

    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

    -- A successful exact response looks like this:

    -- {
    --   "AddressValidationResponse": {
    --     "Response": {
    --       "TransactionReference": {
    --         "CustomerContext": "Your Customer Context"
    --       },
    --       "ResponseStatusCode": "1",
    --       "ResponseStatusDescription": "Success"
    --     },
    --     "AddressValidationResult": {
    --       "Rank": "1",
    --       "Quality": "1.0",
    --       "Address": {
    --         "City": "ALPHARETTA",
    --         "StateProvinceCode": "GA"
    --       },
    --       "PostalCodeLowEnd": "30005",
    --       "PostalCodeHighEnd": "30005"
    --     }
    --   }
    -- }
    -- 

    -- A successful response that was not an exact match provides an array of closest matches, like this:

    -- {
    --   "AddressValidationResponse": {
    --     "Response": {
    --       "TransactionReference": {
    --         "CustomerContext": "Your Customer Context"
    --         "Quality": "0.9875",
    --         "Address": {
    --       },
    --       "ResponseStatusCode": "1",
    --       "ResponseStatusDescription": "Success"
    --     },
    --     "AddressValidationResult": [
    --       {
    --         "Rank": "1",
    --           "City": "ALPHARETTA",
    --           "StateProvinceCode": "GA"
    --         },
    --         "PostalCodeLowEnd": "30005",
    --         "PostalCodeHighEnd": "30005"
    --       },
    --       {
    --         "Rank": "2",
    --         "Quality": "0.9750",
    --         "Address": {
    --           "City": "ALPHARETTA",
    --           "StateProvinceCode": "GA"
    --         },
    --         "PostalCodeLowEnd": "30004",
    --         "PostalCodeHighEnd": "30004"
    --       },
    --       {
    --         "Rank": "3",
    --         "Quality": "0.9750",
    --         "Address": {
    --           "City": "ALPHARETTA",
    --           "StateProvinceCode": "GA"
    --         },
    --         "PostalCodeLowEnd": "30009",
    --         "PostalCodeHighEnd": "30009"
    --       }
    --     ]
    --   }
    -- }

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

    DECLARE @customerContext nvarchar(4000)

    DECLARE @statusCode nvarchar(4000)

    DECLARE @statusDescription nvarchar(4000)

    DECLARE @resultRank nvarchar(4000)

    DECLARE @resultQuality nvarchar(4000)

    DECLARE @city nvarchar(4000)

    DECLARE @provinceCode nvarchar(4000)

    DECLARE @postalCodeLowEnd nvarchar(4000)

    DECLARE @postalCodeHighEnd nvarchar(4000)

    DECLARE @rank nvarchar(4000)

    DECLARE @quality nvarchar(4000)

    DECLARE @addressCity nvarchar(4000)

    DECLARE @addressStateProvinceCode nvarchar(4000)

    DECLARE @numResults int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numResults OUT, 'AddressValidationResponse.AddressValidationResult'
    IF @numResults < 0
      BEGIN

        -- Here's parse code for the above JSON exact response:
        EXEC sp_OAMethod @json, 'StringOf', @customerContext OUT, 'AddressValidationResponse.Response.TransactionReference.CustomerContext'
        EXEC sp_OAMethod @json, 'StringOf', @statusCode OUT, 'AddressValidationResponse.Response.ResponseStatusCode'
        EXEC sp_OAMethod @json, 'StringOf', @statusDescription OUT, 'AddressValidationResponse.Response.ResponseStatusDescription'
        EXEC sp_OAMethod @json, 'StringOf', @resultRank OUT, 'AddressValidationResponse.AddressValidationResult.Rank'
        EXEC sp_OAMethod @json, 'StringOf', @resultQuality OUT, 'AddressValidationResponse.AddressValidationResult.Quality'
        EXEC sp_OAMethod @json, 'StringOf', @city OUT, 'AddressValidationResponse.AddressValidationResult.Address.City'
        EXEC sp_OAMethod @json, 'StringOf', @provinceCode OUT, 'AddressValidationResponse.AddressValidationResult.Address.StateProvinceCode'
        EXEC sp_OAMethod @json, 'StringOf', @postalCodeLowEnd OUT, 'AddressValidationResponse.AddressValidationResult.PostalCodeLowEnd'
        EXEC sp_OAMethod @json, 'StringOf', @postalCodeHighEnd OUT, 'AddressValidationResponse.AddressValidationResult.PostalCodeHighEnd'


        PRINT 'Exact match!'

        PRINT 'postal code: ' + @postalCodeLowEnd

      END
    ELSE
      BEGIN


        PRINT 'Non-Exact match.'

        EXEC sp_OAMethod @json, 'StringOf', @customerContext OUT, 'AddressValidationResponse.Response.TransactionReference.CustomerContext'
        EXEC sp_OAMethod @json, 'StringOf', @statusCode OUT, 'AddressValidationResponse.Response.ResponseStatusCode'
        EXEC sp_OAMethod @json, 'StringOf', @statusDescription OUT, 'AddressValidationResponse.Response.ResponseStatusDescription'
        DECLARE @i int
        SELECT @i = 0
        WHILE @i < @numResults
          BEGIN
            EXEC sp_OASetProperty @json, 'I', @i
            EXEC sp_OAMethod @json, 'StringOf', @rank OUT, 'AddressValidationResponse.AddressValidationResult[i].Rank'

            PRINT 'rank: ' + @rank
            EXEC sp_OAMethod @json, 'StringOf', @quality OUT, 'AddressValidationResponse.AddressValidationResult[i].Quality'
            EXEC sp_OAMethod @json, 'StringOf', @addressCity OUT, 'AddressValidationResponse.AddressValidationResult[i].Address.City'

            PRINT 'addressCity: ' + @addressCity
            EXEC sp_OAMethod @json, 'StringOf', @addressStateProvinceCode OUT, 'AddressValidationResponse.AddressValidationResult[i].Address.StateProvinceCode'
            EXEC sp_OAMethod @json, 'StringOf', @postalCodeLowEnd OUT, 'AddressValidationResponse.AddressValidationResult[i].PostalCodeLowEnd'

            PRINT 'postal code: ' + @postalCodeLowEnd
            EXEC sp_OAMethod @json, 'StringOf', @postalCodeHighEnd OUT, 'AddressValidationResponse.AddressValidationResult[i].PostalCodeHighEnd'
            SELECT @i = @i + 1
          END
      END

    EXEC @hr = sp_OADestroy @resp


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


END
GO