![]() |
Chilkat HOME Android™ AutoIt C C# C++ Chilkat2-Python CkPython Classic ASP DataFlex Delphi DLL Go Java Node.js Objective-C PHP Extension Perl PowerBuilder PowerShell PureBasic Ruby SQL Server Swift Tcl Unicode C Unicode C++ VB.NET VBScript Visual Basic 6.0 Visual FoxPro Xojo Plugin
(SQL Server) Shippo Create the Shipment, Get Rates, and Purchase LabelDemonstrates how retrieve rates and create labels for international shipments. Note: This example requires Chilkat v11.0.0 or greater. For more information, see https://goshippo.com/docs/international/
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls. -- CREATE PROCEDURE ChilkatSample AS BEGIN DECLARE @hr 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 assumes 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 -- Implements the following CURL command: -- curl https://api.goshippo.com/customs/declarations/ \ -- -H "Authorization: ShippoToken <API_TOKEN>" \ -- -H "Content-Type: application/json" \ -- -d '{ -- "contents_type": "MERCHANDISE", -- "non_delivery_option": "RETURN", -- "certify": true, -- "certify_signer": "Simon Kreuz", -- "incoterm": "DDU", -- "items": [{ -- "description": "T-shirt", -- "quantity": 20, -- "net_weight": "5", -- "mass_unit": "lb", -- "value_amount": "200", -- "value_currency": "USD", -- "tariff_number": "", -- "origin_country": "US" -- }] -- }' -- Use this online tool to generate code from sample JSON: -- Generate Code to Create JSON -- The following JSON is sent in the request body. -- { -- "contents_type": "MERCHANDISE", -- "non_delivery_option": "RETURN", -- "certify": true, -- "certify_signer": "Simon Kreuz", -- "incoterm": "DDU", -- "items": [ -- { -- "description": "T-shirt", -- "quantity": 20, -- "net_weight": "5", -- "mass_unit": "lb", -- "value_amount": "200", -- "value_currency": "USD", -- "tariff_number": "", -- "origin_country": "US" -- } -- ] -- } DECLARE @json int EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'contents_type', 'MERCHANDISE' EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'non_delivery_option', 'RETURN' EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'certify', 1 EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'certify_signer', 'Simon Kreuz' EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'incoterm', 'DDU' EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].description', 'T-shirt' EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'items[0].quantity', 20 EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].net_weight', '5' EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].mass_unit', 'lb' EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].value_amount', '200' EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].value_currency', 'USD' EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].tariff_number', '' EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].origin_country', 'US' EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', 'ShippoToken <API_TOKEN>' EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json' DECLARE @resp int EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://api.goshippo.com/customs/declarations/', @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 @resp RETURN END DECLARE @sbResponseBody int EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody DECLARE @jResp int EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jResp OUT EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody EXEC sp_OASetProperty @jResp, 'EmitCompact', 0 PRINT 'Response Body:' EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT PRINT @sTmp0 DECLARE @respStatusCode int EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT PRINT 'Response Status Code = ' + @respStatusCode IF @respStatusCode >= 400 BEGIN PRINT 'Response Header:' EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT PRINT @sTmp0 PRINT 'Failed.' EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @json EXEC @hr = sp_OADestroy @resp EXEC @hr = sp_OADestroy @sbResponseBody EXEC @hr = sp_OADestroy @jResp RETURN END -- Sample JSON response: -- (Sample code for parsing the JSON response is shown below) -- { -- "object_created": "2014-07-17T00:04:06.163Z", -- "object_updated": "2014-07-17T00:04:06.163Z", -- "object_id": "89436997a794439ab47999701e60392e", -- "object_owner": "shippotle@goshippo.com", -- "status": "SUCCESS", -- "address_from": { -- "object_id": "0943ae4e373e4120a99c337e496dcce8", -- "validation_results": {}, -- "is_complete": true, -- "company": "", -- "street_no": "", -- "name": "Mr. Hippo", -- "street1": "215 Clayton St.", -- "street2": "", -- "city": "San Francisco", -- "state": "CA", -- "zip": "94117", -- "country": "US", -- "phone": "+15553419393", -- "email": "support@goshippo.com", -- "is_residential": null -- }, -- "address_to": { -- "object_id": "4c7185d353764d0985a6a7825aed8ffb", -- "validation_results": {}, -- "is_complete": true, -- "name": "Mrs. Hippo", -- "street1": "200 University Ave W", -- "city": "Waterloo", -- "state": "ON", -- "zip": "N2L 3G1", -- "country": "CA", -- "phone": "+1 555 341 9393", -- "email": "support@goshippo.com", -- "is_residential": false -- }, -- "address_return": null, -- "parcels": [ -- { -- "object_id": "ec952343dd4843c39b42aca620471fd5", -- "object_created": "2013-12-01T06:24:21.121Z", -- "object_updated": "2013-12-01T06:24:21.121Z", -- "object_owner": "shippotle@goshippo.com", -- "template": null, -- "length": "5", -- "width": "5", -- "height": "5", -- "distance_unit": "in", -- "weight": "2", -- "mass_unit": "lb", -- "value_amount": null, -- "value_currency": null, -- "metadata": "", -- "line_items": [ -- ], -- "test": true -- } -- ], -- "shipment_date": "2013-12-03T12:00:00Z", -- "extra": { -- "insurance": { -- "currency": "", -- "amount": "" -- }, -- "reference_1": "", -- "reference_2": "" -- }, -- "customs_declaration": "b741b99f95e841639b54272834bc478c", -- "rates": [ -- { -- "object_created": "2014-07-17T00:04:06.263Z", -- "object_id": "545ab0a1a6ea4c9f9adb2512a57d6d8b", -- "object_owner": "shippotle@goshippo.com", -- "shipment": "89436997a794439ab47999701e60392e", -- "attributes": [ -- ], -- "amount": "5.50", -- "currency": "USD", -- "amount_local": "5.50", -- "currency_local": "USD", -- "provider": "USPS", -- "provider_image_75": "https://cdn2.goshippo.com/providers/75/USPS.png", -- "provider_image_200": "https://cdn2.goshippo.com/providers/200/USPS.png", -- "servicelevel": { -- "name": "Priority Mail", -- "token": "usps_priority", -- "terms": "" -- }, -- "days": 2, -- "arrives_by": null, -- "duration_terms": "Delivery in 1 to 3 business days.", -- "messages": [ -- ], -- "carrier_account": "078870331023437cb917f5187429b093", -- "test": false, -- "zone": 1 -- }, -- ... -- ], -- "carrier_accounts": [ -- ], -- "messages": [ -- ], -- "metadata": "Customer ID 123456" -- } -- Sample code for parsing the JSON response... -- Use the following online tool to generate parsing code from sample JSON: -- Generate Parsing Code from JSON DECLARE @template nvarchar(4000) DECLARE @length nvarchar(4000) DECLARE @width nvarchar(4000) DECLARE @height nvarchar(4000) DECLARE @distance_unit nvarchar(4000) DECLARE @weight nvarchar(4000) DECLARE @mass_unit nvarchar(4000) DECLARE @value_amount nvarchar(4000) DECLARE @value_currency nvarchar(4000) DECLARE @test int DECLARE @j int DECLARE @count_j int DECLARE @intVal int DECLARE @object_created nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @object_created OUT, 'object_created' DECLARE @object_updated nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @object_updated OUT, 'object_updated' DECLARE @object_id nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @object_id OUT, 'object_id' DECLARE @object_owner nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @object_owner OUT, 'object_owner' DECLARE @status nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @status OUT, 'status' DECLARE @address_fromObject_id nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromObject_id OUT, 'address_from.object_id' DECLARE @address_fromIs_complete int EXEC sp_OAMethod @jResp, 'BoolOf', @address_fromIs_complete OUT, 'address_from.is_complete' DECLARE @address_fromCompany nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromCompany OUT, 'address_from.company' DECLARE @address_fromStreet_no nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromStreet_no OUT, 'address_from.street_no' DECLARE @address_fromName nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromName OUT, 'address_from.name' DECLARE @address_fromStreet1 nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromStreet1 OUT, 'address_from.street1' DECLARE @address_fromStreet2 nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromStreet2 OUT, 'address_from.street2' DECLARE @address_fromCity nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromCity OUT, 'address_from.city' DECLARE @address_fromState nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromState OUT, 'address_from.state' DECLARE @address_fromZip nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromZip OUT, 'address_from.zip' DECLARE @address_fromCountry nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromCountry OUT, 'address_from.country' DECLARE @address_fromPhone nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromPhone OUT, 'address_from.phone' DECLARE @address_fromEmail nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromEmail OUT, 'address_from.email' DECLARE @address_fromIs_residential nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_fromIs_residential OUT, 'address_from.is_residential' DECLARE @address_toObject_id nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_toObject_id OUT, 'address_to.object_id' DECLARE @address_toIs_complete int EXEC sp_OAMethod @jResp, 'BoolOf', @address_toIs_complete OUT, 'address_to.is_complete' DECLARE @address_toName nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_toName OUT, 'address_to.name' DECLARE @address_toStreet1 nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_toStreet1 OUT, 'address_to.street1' DECLARE @address_toCity nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_toCity OUT, 'address_to.city' DECLARE @address_toState nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_toState OUT, 'address_to.state' DECLARE @address_toZip nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_toZip OUT, 'address_to.zip' DECLARE @address_toCountry nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_toCountry OUT, 'address_to.country' DECLARE @address_toPhone nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_toPhone OUT, 'address_to.phone' DECLARE @address_toEmail nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_toEmail OUT, 'address_to.email' DECLARE @address_toIs_residential int EXEC sp_OAMethod @jResp, 'BoolOf', @address_toIs_residential OUT, 'address_to.is_residential' DECLARE @address_return nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @address_return OUT, 'address_return' DECLARE @shipment_date nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @shipment_date OUT, 'shipment_date' DECLARE @extraInsuranceCurrency nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @extraInsuranceCurrency OUT, 'extra.insurance.currency' DECLARE @extraInsuranceAmount nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @extraInsuranceAmount OUT, 'extra.insurance.amount' DECLARE @extraReference_1 nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @extraReference_1 OUT, 'extra.reference_1' DECLARE @extraReference_2 nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @extraReference_2 OUT, 'extra.reference_2' DECLARE @customs_declaration nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @customs_declaration OUT, 'customs_declaration' DECLARE @metadata nvarchar(4000) EXEC sp_OAMethod @jResp, 'StringOf', @metadata OUT, 'metadata' DECLARE @i int SELECT @i = 0 DECLARE @count_i int EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'parcels' WHILE @i < @count_i BEGIN EXEC sp_OASetProperty @jResp, 'I', @i EXEC sp_OAMethod @jResp, 'StringOf', @object_id OUT, 'parcels[i].object_id' EXEC sp_OAMethod @jResp, 'StringOf', @object_created OUT, 'parcels[i].object_created' EXEC sp_OAMethod @jResp, 'StringOf', @object_updated OUT, 'parcels[i].object_updated' EXEC sp_OAMethod @jResp, 'StringOf', @object_owner OUT, 'parcels[i].object_owner' EXEC sp_OAMethod @jResp, 'StringOf', @template OUT, 'parcels[i].template' EXEC sp_OAMethod @jResp, 'StringOf', @length OUT, 'parcels[i].length' EXEC sp_OAMethod @jResp, 'StringOf', @width OUT, 'parcels[i].width' EXEC sp_OAMethod @jResp, 'StringOf', @height OUT, 'parcels[i].height' EXEC sp_OAMethod @jResp, 'StringOf', @distance_unit OUT, 'parcels[i].distance_unit' EXEC sp_OAMethod @jResp, 'StringOf', @weight OUT, 'parcels[i].weight' EXEC sp_OAMethod @jResp, 'StringOf', @mass_unit OUT, 'parcels[i].mass_unit' EXEC sp_OAMethod @jResp, 'StringOf', @value_amount OUT, 'parcels[i].value_amount' EXEC sp_OAMethod @jResp, 'StringOf', @value_currency OUT, 'parcels[i].value_currency' EXEC sp_OAMethod @jResp, 'StringOf', @metadata OUT, 'parcels[i].metadata' EXEC sp_OAMethod @jResp, 'BoolOf', @test OUT, 'parcels[i].test' SELECT @j = 0 EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'parcels[i].line_items' WHILE @j < @count_j BEGIN EXEC sp_OASetProperty @jResp, 'J', @j SELECT @j = @j + 1 END SELECT @i = @i + 1 END SELECT @i = 0 EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'rates' WHILE @i < @count_i BEGIN EXEC sp_OASetProperty @jResp, 'I', @i EXEC sp_OAMethod @jResp, 'IntOf', @intVal OUT, 'rates[i]' SELECT @j = 0 EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'rates[i].attributes' WHILE @j < @count_j BEGIN EXEC sp_OASetProperty @jResp, 'J', @j SELECT @j = @j + 1 END SELECT @j = 0 EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'rates[i].messages' WHILE @j < @count_j BEGIN EXEC sp_OASetProperty @jResp, 'J', @j SELECT @j = @j + 1 END SELECT @i = @i + 1 END SELECT @i = 0 EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'carrier_accounts' WHILE @i < @count_i BEGIN EXEC sp_OASetProperty @jResp, 'I', @i SELECT @i = @i + 1 END SELECT @i = 0 EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'messages' WHILE @i < @count_i BEGIN EXEC sp_OASetProperty @jResp, 'I', @i SELECT @i = @i + 1 END EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @json EXEC @hr = sp_OADestroy @resp EXEC @hr = sp_OADestroy @sbResponseBody EXEC @hr = sp_OADestroy @jResp END GO |
© 2000-2025 Chilkat Software, Inc. All Rights Reserved.