SQL Server
SQL Server
Shopify Create a new product with multiple product variants
See more Shopify Examples
Create a new product with multiple product variantsChilkat 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
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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
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
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
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
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