(SQL Server) Shippo List All Orders

Demonstrates how to list all orders created by sending a GET request to the orders endpoint.

For more information, see https://goshippo.com/docs/orders/

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    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

    DECLARE @success int

    -- Implements the following CURL command:

    -- curl https://api.goshippo.com/orders/ \
    --     -H "Authorization: ShippoToken <API_Token>"

    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', 'ShippoToken <API_Token>'

    DECLARE @sbResponseBody int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://api.goshippo.com/orders/', @sbResponseBody
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @jResp int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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 @http, 'LastStatus', @respStatusCode OUT

    PRINT 'Response Status Code = ' + @respStatusCode
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        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)

    -- {
    --   "count": 1382,
    --   "next": "https://api.goshippo.com/orders/?page=2",
    --   "previous": null,
    --   "results": [
    --     {
    --       "object_id": "4f2bc588e4e5446cb3f9fdb7cd5e190b",
    --       "object_owner": "shippotle@goshippo.com",
    --       "order_number": "#1068",
    --       "order_status": "PAID",
    --       "placed_at": "2016-09-23T01:28:12Z",
    --       "to_address": {
    --         "object_created": "2016-09-23T01:38:56Z",
    --         "object_updated": "2016-09-23T01:38:56Z",
    --         "object_id": "d799c2679e644279b59fe661ac8fa488",
    --         "object_owner": "shippotle@goshippo.com",
    --         "is_complete": true,
    --         "validation_results": {},
    --         "name": "Mr Hippo",
    --         "company": "Shippo",
    --         "street1": "215 Clayton St.",
    --         "street2": "",
    --         "city": "San Francisco",
    --         "state": "CA",
    --         "zip": "94117",
    --         "country": "US",
    --         "phone": "15553419393",
    --         "email": "shippotle@goshippo.com",
    --         "is_residential": null,
    --         "metadata": ""
    --       },
    --       "from_address": null,
    --       "line_items": [
    --         {
    --           "object_id": "abf7d5675d744b6ea9fdb6f796b28f28",
    --           "title": "Hippo Magazines",
    --           "variant_title": "",
    --           "sku": "HM-123",
    --           "quantity": 1,
    --           "total_price": "12.10",
    --           "currency": "USD",
    --           "weight": "0.40",
    --           "weight_unit": "lb",
    --           "manufacture_country": null,
    --           "max_ship_time": null,
    --           "max_delivery_time": null,
    --           "description": null
    --         }
    --       ],
    --       "shipping_cost": "12.83",
    --       "shipping_cost_currency": "USD",
    --       "shipping_method": "USPS First Class Package",
    --       "shop_app": "Shippo",
    --       "subtotal_price": "12.10",
    --       "total_price": "24.93",
    --       "total_tax": "0.00",
    --       "currency": "USD",
    --       "transactions": [
    --       ],
    --       "weight": "0.40",
    --       "weight_unit": "lb",
    --       "notes": null
    --     },
    --     ...
    --   ]
    -- }

    -- 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 @intVal int

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @object_id nvarchar(4000)

    DECLARE @title nvarchar(4000)

    DECLARE @variant_title nvarchar(4000)

    DECLARE @sku nvarchar(4000)

    DECLARE @quantity int

    DECLARE @total_price nvarchar(4000)

    DECLARE @currency nvarchar(4000)

    DECLARE @weight nvarchar(4000)

    DECLARE @weight_unit nvarchar(4000)

    DECLARE @manufacture_country nvarchar(4000)

    DECLARE @max_ship_time nvarchar(4000)

    DECLARE @max_delivery_time nvarchar(4000)

    DECLARE @description nvarchar(4000)

    DECLARE @count int
    EXEC sp_OAMethod @jResp, 'IntOf', @count OUT, 'count'
    DECLARE @next nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @next OUT, 'next'
    DECLARE @previous nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @previous OUT, 'previous'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'results'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'IntOf', @intVal OUT, 'results[i]'
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'results[i].line_items'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @object_id OUT, 'results[i].line_items[j].object_id'
            EXEC sp_OAMethod @jResp, 'StringOf', @title OUT, 'results[i].line_items[j].title'
            EXEC sp_OAMethod @jResp, 'StringOf', @variant_title OUT, 'results[i].line_items[j].variant_title'
            EXEC sp_OAMethod @jResp, 'StringOf', @sku OUT, 'results[i].line_items[j].sku'
            EXEC sp_OAMethod @jResp, 'IntOf', @quantity OUT, 'results[i].line_items[j].quantity'
            EXEC sp_OAMethod @jResp, 'StringOf', @total_price OUT, 'results[i].line_items[j].total_price'
            EXEC sp_OAMethod @jResp, 'StringOf', @currency OUT, 'results[i].line_items[j].currency'
            EXEC sp_OAMethod @jResp, 'StringOf', @weight OUT, 'results[i].line_items[j].weight'
            EXEC sp_OAMethod @jResp, 'StringOf', @weight_unit OUT, 'results[i].line_items[j].weight_unit'
            EXEC sp_OAMethod @jResp, 'StringOf', @manufacture_country OUT, 'results[i].line_items[j].manufacture_country'
            EXEC sp_OAMethod @jResp, 'StringOf', @max_ship_time OUT, 'results[i].line_items[j].max_ship_time'
            EXEC sp_OAMethod @jResp, 'StringOf', @max_delivery_time OUT, 'results[i].line_items[j].max_delivery_time'
            EXEC sp_OAMethod @jResp, 'StringOf', @description OUT, 'results[i].line_items[j].description'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'results[i].transactions'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp


END
GO