SQL Server
SQL Server
Add order to a ShippingEasy account
See more HTTP Misc Examples
Adds an order to a ShippingEasy account (calls the ShippingEasy 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 @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- First create the JSON for the order.
-- See https://shippingeasy.readme.io/v1.0/docs/creating-a-signature
-- for sample JSON.
-- You may use the online tool at https://tools.chilkat.io/Default.cshtml to
-- generate the following code by pasting sample JSON into the tool.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.external_order_identifier', 'ABC-100'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.ordered_at', '2014-01-16 14:37:56 -0600'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.order_status', 'awaiting_shipment'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.subtotal_including_tax', '10.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.total_including_tax', '10.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.total_excluding_tax', '10.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.discount_amount', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.coupon_discount', '1.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.subtotal_including_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.subtotal_excluding_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.subtotal_excluding_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.subtotal_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.total_tax', '0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.base_shipping_cost', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.shipping_cost_including_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.shipping_cost_excluding_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.shipping_cost_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.base_handling_cost', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.handling_cost_excluding_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.handling_cost_including_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.handling_cost_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.base_wrapping_cost', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.wrapping_cost_excluding_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.wrapping_cost_including_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.wrapping_cost_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.notes', 'Please send promptly.'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_company', 'Acme Inc.'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_first_name', 'Fred'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_last_name', 'Jones'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_address', '1234 Street'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_address2', 'Suite 100'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_city', 'Austin'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_state', 'TX'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_postal_code', '78701'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_country', 'USA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_phone_number', '512-123-1234'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.billing_email', 'test@test.com'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].first_name', 'Colin'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].last_name', 'Homenick'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].company', 'Wintheiser-Hickle'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].email', 'charles.crona@okeefe.org'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].phone_number', '637-481-6505'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].residential', 'true'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].address', '21937 Adelbert Springs'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].address2', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].province', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].state', 'CT'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].city', 'Terryfurt'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].postal_code', '93322'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].postal_code_plus_4', '1234'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].country', 'Andorra'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].shipping_method', 'Ground'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].base_cost', '10.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].cost_excluding_tax', '10.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].cost_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].base_handling_cost', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].handling_cost_excluding_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].handling_cost_including_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].handling_cost_tax', '0.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].shipping_zone_id', '123'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].shipping_zone_name', 'XYZ'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].items_total', '1'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].items_shipped', '0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].line_items[0].item_name', 'Pencil Holder'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].line_items[0].sku', '9876543'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].line_items[0].bin_picking_number', '7'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].line_items[0].unit_price', '1.30'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].line_items[0].total_excluding_tax', '1.30'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].line_items[0].weight_in_ounces', '10'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].line_items[0].product_options.pa_size', 'large'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].line_items[0].product_options.Colour', 'Blue'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'order.recipients[0].line_items[0].quantity', '1'
-- Replace these values with your actual apiKey and apiSecret.
-- (These are not valid values..)
DECLARE @storeApiKey nvarchar(4000)
SELECT @storeApiKey = '569dc30d267f2c4d1f85f9920b2bf9f4'
DECLARE @apiKey nvarchar(4000)
SELECT @apiKey = 'f9a7c8ebdfd34beaf260d9b0296c7059'
DECLARE @apiSecret nvarchar(4000)
SELECT @apiSecret = 'd3e9f8737d7196b1271a725f89e2156672bc1d900a25e6e0c1364e2f7a20f605'
-- Our request will be sent to:
-- https://app.shippingeasy.com/api/stores/:store_api_key/orders
-- The path part of this request is: /api/stores/:store_api_key/orders
-- See https://shippingeasy.readme.io/v1.0/docs/authentication-signing-requests
DECLARE @sbSigStr int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSigStr OUT
EXEC sp_OAMethod @sbSigStr, 'Append', @success OUT, 'POST&/api/stores/'
EXEC sp_OAMethod @sbSigStr, 'Append', @success OUT, @storeApiKey
EXEC sp_OAMethod @sbSigStr, 'Append', @success OUT, '/orders&'
EXEC sp_OAMethod @sbSigStr, 'Append', @success OUT, 'api_key='
EXEC sp_OAMethod @sbSigStr, 'Append', @success OUT, @apiKey
EXEC sp_OAMethod @sbSigStr, 'Append', @success OUT, '&api_timestamp='
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT
EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
EXEC sp_OAMethod @dt, 'GetAsUnixTimeStr', @sTmp0 OUT, 0
EXEC sp_OAMethod @sbSigStr, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @sbSigStr, 'Append', @success OUT, '&'
EXEC sp_OASetProperty @json, 'EmitCompact', 1
EXEC sp_OAMethod @json, 'EmitSb', @success OUT, @sbSigStr
-- Calculate the HMAC-SHA256 of the API Signature String using the API Secret as the HMAC key.
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
EXEC sp_OASetProperty @crypt, 'MacAlgorithm', 'hmac'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'
EXEC sp_OAMethod @crypt, 'SetMacKeyString', @success OUT, @apiSecret
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
DECLARE @sbSignature int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSignature OUT
EXEC sp_OAMethod @sbSigStr, 'GetAsString', @sTmp1 OUT
EXEC sp_OAMethod @crypt, 'MacStringENC', @sTmp0 OUT, @sTmp1
EXEC sp_OAMethod @sbSignature, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @sbSignature, 'ToLowercase', @success OUT
DECLARE @sbUrl int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrl OUT
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, 'https://app.shippingeasy.com/api/stores/'
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, @storeApiKey
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, '/orders?'
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, 'api_key='
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, @apiKey
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, '&api_timestamp='
EXEC sp_OAMethod @dt, 'GetAsUnixTimeStr', @sTmp0 OUT, 0
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, '&api_signature='
EXEC sp_OAMethod @sbSignature, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, @sTmp0
-- Send a POST equivalent the following curl request:
-- curl -H "Content-Type: application/json" --data @body.json "https://app.shippingeasy.com/api/stores/27aa472e16faa83dd13b7758d31974ed/orders?
-- api_key=f9a7c8ebdfd34beaf260d9b0296c7059&
-- api_timestamp=1401803554&
-- api_signature=c65f43beed46e581939898a78acd10064cfa146845e97885ec02124d7ad648e4"
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OASetProperty @http, 'SessionLogFilename', 'qa_output/shippingEasy.txt'
DECLARE @url nvarchar(4000)
EXEC sp_OAMethod @sbUrl, 'GetAsString', @url 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 @json
EXEC @hr = sp_OADestroy @sbSigStr
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @sbSignature
EXEC @hr = sp_OADestroy @sbUrl
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
PRINT 'response body:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbSigStr
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @sbSignature
EXEC @hr = sp_OADestroy @sbUrl
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
END
GO