(SQL Server) WiX Create Product

Creates a new product.

Note: If you get a 403 error response, try refreshing the access token.

For more information, see https://dev.wix.com/api/rest/wix-stores/catalog/product/create-product

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)
    -- 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 -X POST \
    --    'https://www.wixapis.com/stores/v1/products' \
    --     --data-binary '{
    --                      "product": {
    --                        "name": "T-shirt",
    --                        "productType": "physical",
    --                        "priceData": {
    --                          "price": 10.5
    --                        },
    --                        "description": "nice summer t-shirt",
    --                        "sku": "123df",
    --                        "visible": false,
    --                        "weight": 0.2,
    --                        "discount": {
    --                        	"type": "AMOUNT",
    --                        	"value": 1
    --                        },
    --                        "manageVariants": true,
    --                        "productOptions": [
    --                          {
    --                            "name": "Size",
    --                            "choices": [
    --                              {
    --                                "value": "S",
    --                                "description": "S"
    --                              },
    --                              {
    --                                "value": "L",
    --                                "description": "L"
    --                              }
    --                            ]
    --                          }
    --                        ]
    --                      }
    --                    }' \
    --    -H 'Content-Type: application/json' \
    --    -H 'Authorization: <AUTH>'

    -- Use the following online tool to generate HTTP code from a CURL command
    -- Convert a cURL Command to HTTP Source Code

    -- Use this online tool to generate code from sample JSON:
    -- Generate Code to Create JSON

    -- The following JSON is sent in the request body.

    -- {
    --   "product": {
    --     "name": "T-shirt",
    --     "productType": "physical",
    --     "priceData": {
    --       "price": 10.5
    --     },
    --     "description": "nice summer t-shirt",
    --     "sku": "123df",
    --     "visible": false,
    --     "weight": 0.2,
    --     "discount": {
    --       "type": "AMOUNT",
    --       "value": 1
    --     },
    --     "manageVariants": true,
    --     "productOptions": [
    --       {
    --         "name": "Size",
    --         "choices": [
    --           {
    --             "value": "S",
    --             "description": "S"
    --           },
    --           {
    --             "value": "L",
    --             "description": "L"
    --           }
    --         ]
    --       }
    --     ]
    --   }
    -- }

    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, 'UpdateString', @success OUT, 'product.name', 'T-shirt'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.productType', 'physical'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'product.priceData.price', '10.5'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.description', 'nice summer t-shirt'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.sku', '123df'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'product.visible', 0
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'product.weight', '0.2'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.discount.type', 'AMOUNT'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'product.discount.value', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'product.manageVariants', 1
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.productOptions[0].name', 'Size'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.productOptions[0].choices[0].value', 'S'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.productOptions[0].choices[0].description', 'S'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.productOptions[0].choices[1].value', 'L'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.productOptions[0].choices[1].description', 'L'

    EXEC sp_OASetProperty @http, 'AuthToken', 'ACCESS_TOKEN'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://www.wixapis.com/stores/v1/products', 'application/json', @json
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    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 @resp, 'GetBodySb', @success OUT, @sbResponseBody
    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 @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 @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END
    EXEC @hr = sp_OADestroy @resp

    -- Sample JSON response:
    -- (Sample code for parsing the JSON response is shown below)

    -- {
    --   "product": {
    --     "id": "e28e4ddd-6ed0-4098-a5e5-cf4bd9c4f7b5",
    --     "name": "T-shirt",
    --     "slug": "t-shirt-1",
    --     "visible": false,
    --     "productType": "physical",
    --     "description": "nice summer t-shirt",
    --     "stock": {
    --       "trackInventory": false,
    --       "inStock": true
    --     },
    --     "price": {
    --       "currency": "ILS",
    --       "price": 10.5,
    --       "discountedPrice": 9.5,
    --       "formatted": {
    --         "price": "10.50",
    --         "discountedPrice": "9.50"
    --       }
    --     },
    --     "priceData": {
    --       "currency": "ILS",
    --       "price": 10.5,
    --       "discountedPrice": 9.5,
    --       "formatted": {
    --         "price": "10.50",
    --         "discountedPrice": "9.50"
    --       }
    --     },
    --     "additionalInfoSections": [
    --     ],
    --     "ribbons": [
    --     ],
    --     "media": {
    --       "items": [
    --       ]
    --     },
    --     "customTextFields": [
    --     ],
    --     "manageVariants": true,
    --     "productOptions": [
    --       {
    --         "optionType": "drop_down",
    --         "name": "Size",
    --         "choices": [
    --           {
    --             "value": "S",
    --             "description": "S",
    --             "inStock": true,
    --             "visible": true
    --           },
    --           {
    --             "value": "L",
    --             "description": "L",
    --             "inStock": true,
    --             "visible": true
    --           }
    --         ]
    --       }
    --     ],
    --     "productPageUrl": {
    --       "base": "https://www.itsjusttooeasy123.com/",
    --       "path": "/product-page/t-shirt-1"
    --     },
    --     "numericId": "1567588455405000",
    --     "inventoryItemId": "1d71b222-912f-bf67-5a1a-30b4263b084a",
    --     "discount": {
    --       "type": "AMOUNT",
    --       "value": 1
    --     },
    --     "collectionIds": [
    --     ],
    --     "variants": [
    --       {
    --         "id": "00000000-0000-0001-0005-93fc95e0514a",
    --         "choices": {
    --           "Size": "S"
    --         },
    --         "variant": {
    --           "priceData": {
    --             "currency": "ILS",
    --             "price": 10.5,
    --             "discountedPrice": 9.5,
    --             "formatted": {
    --               "price": "10.50",
    --               "discountedPrice": "9.50"
    --             }
    --           },
    --           "weight": 10,
    --           "visible": true
    --         }
    --       },
    --       {
    --         "id": "00000000-0000-0002-0005-93fc95e0514a",
    --         "choices": {
    --           "Size": "L"
    --         },
    --         "variant": {
    --           "priceData": {
    --             "currency": "ILS",
    --             "price": 10.5,
    --             "discountedPrice": 9.5,
    --             "formatted": {
    --               "price": "10.50",
    --               "discountedPrice": "9.50"
    --             }
    --           },
    --           "visible": true
    --         }
    --       }
    --     ]
    --   }
    -- }

    -- 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 @optionType nvarchar(4000)

    DECLARE @name nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @value nvarchar(4000)

    DECLARE @description nvarchar(4000)

    DECLARE @inStock int

    DECLARE @visible int

    DECLARE @id nvarchar(4000)

    DECLARE @choicesSize nvarchar(4000)

    DECLARE @variantPriceDataCurrency nvarchar(4000)

    DECLARE @variantPriceDataPrice nvarchar(4000)

    DECLARE @variantPriceDataDiscountedPrice nvarchar(4000)

    DECLARE @variantPriceDataFormattedPrice nvarchar(4000)

    DECLARE @variantPriceDataFormattedDiscountedPrice nvarchar(4000)

    DECLARE @variantWeight int

    DECLARE @variantVisible int

    DECLARE @productId nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productId OUT, 'product.id'
    DECLARE @productName nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productName OUT, 'product.name'
    DECLARE @productSlug nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productSlug OUT, 'product.slug'
    DECLARE @productVisible int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productVisible OUT, 'product.visible'
    DECLARE @productProductType nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productProductType OUT, 'product.productType'
    DECLARE @productDescription nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productDescription OUT, 'product.description'
    DECLARE @productStockTrackInventory int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productStockTrackInventory OUT, 'product.stock.trackInventory'
    DECLARE @productStockInStock int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productStockInStock OUT, 'product.stock.inStock'
    DECLARE @productPriceCurrency nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productPriceCurrency OUT, 'product.price.currency'
    DECLARE @productPricePrice nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productPricePrice OUT, 'product.price.price'
    DECLARE @productPriceDiscountedPrice nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productPriceDiscountedPrice OUT, 'product.price.discountedPrice'
    DECLARE @productPriceFormattedPrice nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productPriceFormattedPrice OUT, 'product.price.formatted.price'
    DECLARE @productPriceFormattedDiscountedPrice nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productPriceFormattedDiscountedPrice OUT, 'product.price.formatted.discountedPrice'
    DECLARE @productPriceDataCurrency nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productPriceDataCurrency OUT, 'product.priceData.currency'
    DECLARE @productPriceDataPrice nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productPriceDataPrice OUT, 'product.priceData.price'
    DECLARE @productPriceDataDiscountedPrice nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productPriceDataDiscountedPrice OUT, 'product.priceData.discountedPrice'
    DECLARE @productPriceDataFormattedPrice nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productPriceDataFormattedPrice OUT, 'product.priceData.formatted.price'
    DECLARE @productPriceDataFormattedDiscountedPrice nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productPriceDataFormattedDiscountedPrice OUT, 'product.priceData.formatted.discountedPrice'
    DECLARE @productManageVariants int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productManageVariants OUT, 'product.manageVariants'
    DECLARE @productProductPageUrlBase nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productProductPageUrlBase OUT, 'product.productPageUrl.base'
    DECLARE @productProductPageUrlPath nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productProductPageUrlPath OUT, 'product.productPageUrl.path'
    DECLARE @productNumericId nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productNumericId OUT, 'product.numericId'
    DECLARE @productInventoryItemId nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productInventoryItemId OUT, 'product.inventoryItemId'
    DECLARE @productDiscountType nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productDiscountType OUT, 'product.discount.type'
    DECLARE @productDiscountValue int
    EXEC sp_OAMethod @jResp, 'IntOf', @productDiscountValue OUT, 'product.discount.value'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'product.additionalInfoSections'
    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, 'product.ribbons'
    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, 'product.media.items'
    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, 'product.customTextFields'
    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, 'product.productOptions'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @optionType OUT, 'product.productOptions[i].optionType'
        EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'product.productOptions[i].name'
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'product.productOptions[i].choices'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @value OUT, 'product.productOptions[i].choices[j].value'
            EXEC sp_OAMethod @jResp, 'StringOf', @description OUT, 'product.productOptions[i].choices[j].description'
            EXEC sp_OAMethod @jResp, 'BoolOf', @inStock OUT, 'product.productOptions[i].choices[j].inStock'
            EXEC sp_OAMethod @jResp, 'BoolOf', @visible OUT, 'product.productOptions[i].choices[j].visible'
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'product.collectionIds'
    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, 'product.variants'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'product.variants[i].id'
        EXEC sp_OAMethod @jResp, 'StringOf', @choicesSize OUT, 'product.variants[i].choices.Size'
        EXEC sp_OAMethod @jResp, 'StringOf', @variantPriceDataCurrency OUT, 'product.variants[i].variant.priceData.currency'
        EXEC sp_OAMethod @jResp, 'StringOf', @variantPriceDataPrice OUT, 'product.variants[i].variant.priceData.price'
        EXEC sp_OAMethod @jResp, 'StringOf', @variantPriceDataDiscountedPrice OUT, 'product.variants[i].variant.priceData.discountedPrice'
        EXEC sp_OAMethod @jResp, 'StringOf', @variantPriceDataFormattedPrice OUT, 'product.variants[i].variant.priceData.formatted.price'
        EXEC sp_OAMethod @jResp, 'StringOf', @variantPriceDataFormattedDiscountedPrice OUT, 'product.variants[i].variant.priceData.formatted.discountedPrice'
        EXEC sp_OAMethod @jResp, 'IntOf', @variantWeight OUT, 'product.variants[i].variant.weight'
        EXEC sp_OAMethod @jResp, 'BoolOf', @variantVisible OUT, 'product.variants[i].variant.visible'
        SELECT @i = @i + 1
      END

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


END
GO