(SQL Server) Shopify Create a new product with multiple product variants

Create a new product with multiple product variants

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)
    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    EXEC sp_OAMethod @rest, 'SetAuthBasic', @success OUT, 'SHOPIFY_PRIVATE_API_KEY', 'SHOPIFY_PRIVATE_API_KEY'

    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'chilkat.myshopify.com', 443, 1, 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- The following code creates the JSON request body.
    -- The JSON created by this code is shown below.
    DECLARE @jsonReq int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonReq OUT

    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'product.title', 'Burton Custom Freestyle 151'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'product.body_html', '<strong>Good snowboard!</strong>'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'product.vendor', 'Burton'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'product.product_type', 'Snowboard'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'product.variants[0].option1', 'First'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'product.variants[0].price', '10.00'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'product.variants[0].sku', '123'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'product.variants[1].option1', 'Second'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'product.variants[1].price', '20.00'
    EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'product.variants[1].sku', '123'

    -- The JSON request body created by the above code:

    -- {
    --   "product": {
    --     "title": "Burton Custom Freestyle 151",
    --     "body_html": "<strong>Good snowboard!<\/strong>",
    --     "vendor": "Burton",
    --     "product_type": "Snowboard",
    --     "variants": [
    --       {
    --         "option1": "First",
    --         "price": "10.00",
    --         "sku": "123"
    --       },
    --       {
    --         "option1": "Second",
    --         "price": "20.00",
    --         "sku": "123"
    --       }
    --     ]
    --   }
    -- }

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

    EXEC sp_OAMethod @jsonReq, 'EmitSb', @success OUT, @sbReq

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/json'

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

    EXEC sp_OAMethod @rest, 'FullRequestSb', @success OUT, 'POST', '/admin/products.json ', @sbReq, @sbJson
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @jsonReq
        EXEC @hr = sp_OADestroy @sbReq
        EXEC @hr = sp_OADestroy @sbJson
        RETURN
      END

    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 201
      BEGIN

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'Received error response code: ' + @iTmp0

        PRINT 'Response body:'
        EXEC sp_OAMethod @sbJson, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @jsonReq
        EXEC @hr = sp_OADestroy @sbReq
        EXEC @hr = sp_OADestroy @sbJson
        RETURN
      END

    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, 'LoadSb', @success OUT, @sbJson

    -- The following code parses the JSON response.
    -- A sample JSON response is shown below the sample code.
    DECLARE @productId int

    DECLARE @productTitle nvarchar(4000)

    DECLARE @productBody_html nvarchar(4000)

    DECLARE @productVendor nvarchar(4000)

    DECLARE @productProduct_type nvarchar(4000)

    DECLARE @productCreated_at nvarchar(4000)

    DECLARE @productHandle nvarchar(4000)

    DECLARE @productUpdated_at nvarchar(4000)

    DECLARE @productPublished_at nvarchar(4000)

    DECLARE @productTemplate_suffix int

    DECLARE @productPublished_scope nvarchar(4000)

    DECLARE @productTags nvarchar(4000)

    DECLARE @productImage int

    DECLARE @i int

    DECLARE @count_i int

    DECLARE @id int

    DECLARE @product_id int

    DECLARE @title nvarchar(4000)

    DECLARE @price nvarchar(4000)

    DECLARE @sku nvarchar(4000)

    DECLARE @position int

    DECLARE @grams int

    DECLARE @inventory_policy nvarchar(4000)

    DECLARE @compare_at_price int

    DECLARE @fulfillment_service nvarchar(4000)

    DECLARE @inventory_management int

    DECLARE @option1 nvarchar(4000)

    DECLARE @option2 int

    DECLARE @option3 int

    DECLARE @created_at nvarchar(4000)

    DECLARE @updated_at nvarchar(4000)

    DECLARE @taxable int

    DECLARE @barcode int

    DECLARE @image_id int

    DECLARE @inventory_quantity int

    DECLARE @weight int

    DECLARE @weight_unit nvarchar(4000)

    DECLARE @old_inventory_quantity int

    DECLARE @requires_shipping int

    DECLARE @name nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @strVal nvarchar(4000)

    EXEC sp_OAMethod @json, 'IntOf', @productId OUT, 'product.id'
    EXEC sp_OAMethod @json, 'StringOf', @productTitle OUT, 'product.title'
    EXEC sp_OAMethod @json, 'StringOf', @productBody_html OUT, 'product.body_html'
    EXEC sp_OAMethod @json, 'StringOf', @productVendor OUT, 'product.vendor'
    EXEC sp_OAMethod @json, 'StringOf', @productProduct_type OUT, 'product.product_type'
    EXEC sp_OAMethod @json, 'StringOf', @productCreated_at OUT, 'product.created_at'
    EXEC sp_OAMethod @json, 'StringOf', @productHandle OUT, 'product.handle'
    EXEC sp_OAMethod @json, 'StringOf', @productUpdated_at OUT, 'product.updated_at'
    EXEC sp_OAMethod @json, 'StringOf', @productPublished_at OUT, 'product.published_at'
    EXEC sp_OAMethod @json, 'IsNullOf', @productTemplate_suffix OUT, 'product.template_suffix'
    EXEC sp_OAMethod @json, 'StringOf', @productPublished_scope OUT, 'product.published_scope'
    EXEC sp_OAMethod @json, 'StringOf', @productTags OUT, 'product.tags'
    EXEC sp_OAMethod @json, 'IsNullOf', @productImage OUT, 'product.image'
    SELECT @i = 0
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'product.variants'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'IntOf', @id OUT, 'product.variants[i].id'
        EXEC sp_OAMethod @json, 'IntOf', @product_id OUT, 'product.variants[i].product_id'
        EXEC sp_OAMethod @json, 'StringOf', @title OUT, 'product.variants[i].title'
        EXEC sp_OAMethod @json, 'StringOf', @price OUT, 'product.variants[i].price'
        EXEC sp_OAMethod @json, 'StringOf', @sku OUT, 'product.variants[i].sku'
        EXEC sp_OAMethod @json, 'IntOf', @position OUT, 'product.variants[i].position'
        EXEC sp_OAMethod @json, 'IntOf', @grams OUT, 'product.variants[i].grams'
        EXEC sp_OAMethod @json, 'StringOf', @inventory_policy OUT, 'product.variants[i].inventory_policy'
        EXEC sp_OAMethod @json, 'IsNullOf', @compare_at_price OUT, 'product.variants[i].compare_at_price'
        EXEC sp_OAMethod @json, 'StringOf', @fulfillment_service OUT, 'product.variants[i].fulfillment_service'
        EXEC sp_OAMethod @json, 'IsNullOf', @inventory_management OUT, 'product.variants[i].inventory_management'
        EXEC sp_OAMethod @json, 'StringOf', @option1 OUT, 'product.variants[i].option1'
        EXEC sp_OAMethod @json, 'IsNullOf', @option2 OUT, 'product.variants[i].option2'
        EXEC sp_OAMethod @json, 'IsNullOf', @option3 OUT, 'product.variants[i].option3'
        EXEC sp_OAMethod @json, 'StringOf', @created_at OUT, 'product.variants[i].created_at'
        EXEC sp_OAMethod @json, 'StringOf', @updated_at OUT, 'product.variants[i].updated_at'
        EXEC sp_OAMethod @json, 'BoolOf', @taxable OUT, 'product.variants[i].taxable'
        EXEC sp_OAMethod @json, 'IsNullOf', @barcode OUT, 'product.variants[i].barcode'
        EXEC sp_OAMethod @json, 'IsNullOf', @image_id OUT, 'product.variants[i].image_id'
        EXEC sp_OAMethod @json, 'IntOf', @inventory_quantity OUT, 'product.variants[i].inventory_quantity'
        EXEC sp_OAMethod @json, 'IntOf', @weight OUT, 'product.variants[i].weight'
        EXEC sp_OAMethod @json, 'StringOf', @weight_unit OUT, 'product.variants[i].weight_unit'
        EXEC sp_OAMethod @json, 'IntOf', @old_inventory_quantity OUT, 'product.variants[i].old_inventory_quantity'
        EXEC sp_OAMethod @json, 'BoolOf', @requires_shipping OUT, 'product.variants[i].requires_shipping'
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'product.options'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'IntOf', @id OUT, 'product.options[i].id'
        EXEC sp_OAMethod @json, 'IntOf', @product_id OUT, 'product.options[i].product_id'
        EXEC sp_OAMethod @json, 'StringOf', @name OUT, 'product.options[i].name'
        EXEC sp_OAMethod @json, 'IntOf', @position OUT, 'product.options[i].position'
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'product.options[i].values'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @strVal OUT, 'product.options[i].values[j]'
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'product.images'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        SELECT @i = @i + 1
      END

    -- A sample JSON response body that is parsed by the above code:

    -- {
    --   "product": {
    --     "id": 1071559755,
    --     "title": "Burton Custom Freestyle 151",
    --     "body_html": "<strong>Good snowboard!<\/strong>",
    --     "vendor": "Burton",
    --     "product_type": "Snowboard",
    --     "created_at": "2017-09-22T14:48:54-04:00",
    --     "handle": "burton-custom-freestyle-151",
    --     "updated_at": "2017-09-22T14:48:55-04:00",
    --     "published_at": "2017-09-22T14:48:54-04:00",
    --     "template_suffix": null,
    --     "published_scope": "global",
    --     "tags": "",
    --     "variants": [
    --       {
    --         "id": 1070325225,
    --         "product_id": 1071559755,
    --         "title": "First",
    --         "price": "10.00",
    --         "sku": "123",
    --         "position": 1,
    --         "grams": 0,
    --         "inventory_policy": "deny",
    --         "compare_at_price": null,
    --         "fulfillment_service": "manual",
    --         "inventory_management": null,
    --         "option1": "First",
    --         "option2": null,
    --         "option3": null,
    --         "created_at": "2017-09-22T14:48:54-04:00",
    --         "updated_at": "2017-09-22T14:48:54-04:00",
    --         "taxable": true,
    --         "barcode": null,
    --         "image_id": null,
    --         "inventory_quantity": 1,
    --         "weight": 0.0,
    --         "weight_unit": "lb",
    --         "old_inventory_quantity": 1,
    --         "requires_shipping": true
    --       },
    --       {
    --         "id": 1070325226,
    --         "product_id": 1071559755,
    --         "title": "Second",
    --         "price": "20.00",
    --         "sku": "123",
    --         "position": 2,
    --         "grams": 0,
    --         "inventory_policy": "deny",
    --         "compare_at_price": null,
    --         "fulfillment_service": "manual",
    --         "inventory_management": null,
    --         "option1": "Second",
    --         "option2": null,
    --         "option3": null,
    --         "created_at": "2017-09-22T14:48:54-04:00",
    --         "updated_at": "2017-09-22T14:48:54-04:00",
    --         "taxable": true,
    --         "barcode": null,
    --         "image_id": null,
    --         "inventory_quantity": 1,
    --         "weight": 0.0,
    --         "weight_unit": "lb",
    --         "old_inventory_quantity": 1,
    --         "requires_shipping": true
    --       }
    --     ],
    --     "options": [
    --       {
    --         "id": 1022828915,
    --         "product_id": 1071559755,
    --         "name": "Title",
    --         "position": 1,
    --         "values": [
    --           "First",
    --           "Second"
    --         ]
    --       }
    --     ],
    --     "images": [
    --     ],
    --     "image": null
    --   }
    -- }


    PRINT 'Example Completed.'

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @jsonReq
    EXEC @hr = sp_OADestroy @sbReq
    EXEC @hr = sp_OADestroy @sbJson
    EXEC @hr = sp_OADestroy @json


END
GO