SQL Server
SQL Server
UPS Address Validation (City, State, Zip)
See more HTTP Misc Examples
Demonstrates making a call to the UPS address validation REST API.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 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
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
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
-- 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 @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sb
EXEC @hr = sp_OADestroy @resp
END
GO