(SQL Server) Lightspeed - Create a Product

Create a new product based on the given parameters.

For more information, see https://developers.lightspeedhq.com/ecom/endpoints/product/#post-create-a-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
    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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Implements the following CURL command:

    -- curl   -u API_KEY:API_SECRET \
    --     -H "Content-Type: application/json" \
    --     -X POST \
    --     -d '{
    --   "product": {
    --     "visibility": "visible",
    --     "data01": "",
    --     "data02": "",
    --     "data03": "",
    --     "title": "Lookin Sharp T-Shirt",
    --     "fulltitle": "Lookin Sharp T-Shirt",
    --     "description": "Description of the Lookin Sharp T-Shirt",
    --     "content": "Long Description of the Lookin Sharp T-Shirt",
    --     "deliverydate": 6488,
    --     "supplier": 78794,
    --     "brand": 1171202
    --   }
    -- }' \
    -- "https://api.webshopapp.com/en/products.json"

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

    EXEC sp_OASetProperty @http, 'Login', 'API_KEY'
    EXEC sp_OASetProperty @http, 'Password', 'API_SECRET'

    -- 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": {
    --     "visibility": "visible",
    --     "data01": "",
    --     "data02": "",
    --     "data03": "",
    --     "title": "Lookin Sharp T-Shirt",
    --     "fulltitle": "Lookin Sharp T-Shirt",
    --     "description": "Description of the Lookin Sharp T-Shirt",
    --     "content": "Long Description of the Lookin Sharp T-Shirt",
    --     "deliverydate": 6488,
    --     "supplier": 78794,
    --     "brand": 1171202
    --   }
    -- }

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.visibility', 'visible'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.data01', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.data02', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.data03', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.title', 'Lookin Sharp T-Shirt'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.fulltitle', 'Lookin Sharp T-Shirt'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.description', 'Description of the Lookin Sharp T-Shirt'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'product.content', 'Long Description of the Lookin Sharp T-Shirt'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'product.deliverydate', 6488
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'product.supplier', 78794
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'product.brand', 1171202

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

    -- Use the correct cluster for your shop.  Here are the choices:
    -- eu1 https://api.webshopapp.com/en/
    -- us1 https://api.shoplightspeed.com/en/
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://api.webshopapp.com/en/products.json', '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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
    DECLARE @jResp int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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": 20974460,
    --     "createdAt": "2019-05-28T20:14:26+00:00",
    --     "updatedAt": "2019-05-28T20:14:26+00:00",
    --     "isVisible": true,
    --     "visibility": "visible",
    --     "hasMatrix": false,
    --     "data01": "",
    --     "data02": "",
    --     "data03": "",
    --     "url": "lookin-sharp-t-shirt",
    --     "title": "Lookin' Sharp T-Shirt",
    --     "fulltitle": "Lookin' Sharp T-Shirt",
    --     "description": "Description of the Lookin' Sharp T-Shirt",
    --     "content": "<p>Long Description of the Lookin' Sharp T-Shirt</p>",
    --     "set": false,
    --     "brand": {
    --       "resource": {
    --         "id": 1171202,
    --         "url": "brands/1171202",
    --         "link": "https://api.shoplightspeed.com/us/brands/1171202.json"
    --       }
    --     },
    --     "categories": {
    --       "resource": {
    --         "id": false,
    --         "url": "categories/products?product=20974460",
    --         "link": "https://api.shoplightspeed.com/us/categories/products.json?product=20974460"
    --       }
    --     },
    --     "deliverydate": {
    --       "resource": {
    --         "id": 6488,
    --         "url": "deliverydates/6488",
    --         "link": "https://api.shoplightspeed.com/us/deliverydates/6488.json"
    --       }
    --     },
    --     "image": false,
    --     "images": false,
    --     "relations": {
    --       "resource": {
    --         "id": false,
    --         "url": "products/20974460/relations",
    --         "link": "https://api.shoplightspeed.com/us/products/20974460/relations.json"
    --       }
    --     },
    --     "metafields": {
    --       "resource": {
    --         "id": false,
    --         "url": "products/20974460/metafields",
    --         "link": "https://api.shoplightspeed.com/us/products/20974460/metafields.json"
    --       }
    --     },
    --     "reviews": {
    --       "resource": {
    --         "id": false,
    --         "url": "reviews?product=20974460",
    --         "link": "https://api.shoplightspeed.com/us/reviews.json?product=20974460"
    --       }
    --     },
    --     "type": false,
    --     "attributes": {
    --       "resource": {
    --         "id": false,
    --         "url": "products/20974460/attributes",
    --         "link": "https://api.shoplightspeed.com/us/products/20974460/attributes.json"
    --       }
    --     },
    --     "supplier": {
    --       "resource": {
    --         "id": 78794,
    --         "url": "suppliers/78794",
    --         "link": "https://api.shoplightspeed.com/us/suppliers/78794.json"
    --       }
    --     },
    --     "tags": {
    --       "resource": {
    --         "id": false,
    --         "url": "tags/products?product=20974460",
    --         "link": "https://api.shoplightspeed.com/us/tags/products.json?product=20974460"
    --       }
    --     },
    --     "variants": {
    --       "resource": {
    --         "id": false,
    --         "url": "variants?product=20974460",
    --         "link": "https://api.shoplightspeed.com/us/variants.json?product=20974460"
    --       }
    --     },
    --     "movements": {
    --       "resource": {
    --         "id": false,
    --         "url": "variants/movements?product=20974460",
    --         "link": "https://api.shoplightspeed.com/us/variants/movements.json?product=20974460"
    --       }
    --     }
    --   }
    -- }

    -- 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 @productId int
    EXEC sp_OAMethod @jResp, 'IntOf', @productId OUT, 'product.id'
    DECLARE @productCreatedAt nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productCreatedAt OUT, 'product.createdAt'
    DECLARE @productUpdatedAt nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productUpdatedAt OUT, 'product.updatedAt'
    DECLARE @productIsVisible int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productIsVisible OUT, 'product.isVisible'
    DECLARE @productVisibility nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productVisibility OUT, 'product.visibility'
    DECLARE @productHasMatrix int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productHasMatrix OUT, 'product.hasMatrix'
    DECLARE @productData01 nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productData01 OUT, 'product.data01'
    DECLARE @productData02 nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productData02 OUT, 'product.data02'
    DECLARE @productData03 nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productData03 OUT, 'product.data03'
    DECLARE @productUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productUrl OUT, 'product.url'
    DECLARE @productTitle nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productTitle OUT, 'product.title'
    DECLARE @productFulltitle nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productFulltitle OUT, 'product.fulltitle'
    DECLARE @productDescription nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productDescription OUT, 'product.description'
    DECLARE @productContent nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productContent OUT, 'product.content'
    DECLARE @productSet int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productSet OUT, 'product.set'
    DECLARE @productBrandResourceId int
    EXEC sp_OAMethod @jResp, 'IntOf', @productBrandResourceId OUT, 'product.brand.resource.id'
    DECLARE @productBrandResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productBrandResourceUrl OUT, 'product.brand.resource.url'
    DECLARE @productBrandResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productBrandResourceLink OUT, 'product.brand.resource.link'
    DECLARE @productCategoriesResourceId int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productCategoriesResourceId OUT, 'product.categories.resource.id'
    DECLARE @productCategoriesResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productCategoriesResourceUrl OUT, 'product.categories.resource.url'
    DECLARE @productCategoriesResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productCategoriesResourceLink OUT, 'product.categories.resource.link'
    DECLARE @productDeliverydateResourceId int
    EXEC sp_OAMethod @jResp, 'IntOf', @productDeliverydateResourceId OUT, 'product.deliverydate.resource.id'
    DECLARE @productDeliverydateResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productDeliverydateResourceUrl OUT, 'product.deliverydate.resource.url'
    DECLARE @productDeliverydateResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productDeliverydateResourceLink OUT, 'product.deliverydate.resource.link'
    DECLARE @productImage int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productImage OUT, 'product.image'
    DECLARE @productImages int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productImages OUT, 'product.images'
    DECLARE @productRelationsResourceId int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productRelationsResourceId OUT, 'product.relations.resource.id'
    DECLARE @productRelationsResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productRelationsResourceUrl OUT, 'product.relations.resource.url'
    DECLARE @productRelationsResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productRelationsResourceLink OUT, 'product.relations.resource.link'
    DECLARE @productMetafieldsResourceId int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productMetafieldsResourceId OUT, 'product.metafields.resource.id'
    DECLARE @productMetafieldsResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productMetafieldsResourceUrl OUT, 'product.metafields.resource.url'
    DECLARE @productMetafieldsResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productMetafieldsResourceLink OUT, 'product.metafields.resource.link'
    DECLARE @productReviewsResourceId int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productReviewsResourceId OUT, 'product.reviews.resource.id'
    DECLARE @productReviewsResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productReviewsResourceUrl OUT, 'product.reviews.resource.url'
    DECLARE @productReviewsResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productReviewsResourceLink OUT, 'product.reviews.resource.link'
    DECLARE @productType int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productType OUT, 'product.type'
    DECLARE @productAttributesResourceId int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productAttributesResourceId OUT, 'product.attributes.resource.id'
    DECLARE @productAttributesResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productAttributesResourceUrl OUT, 'product.attributes.resource.url'
    DECLARE @productAttributesResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productAttributesResourceLink OUT, 'product.attributes.resource.link'
    DECLARE @productSupplierResourceId int
    EXEC sp_OAMethod @jResp, 'IntOf', @productSupplierResourceId OUT, 'product.supplier.resource.id'
    DECLARE @productSupplierResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productSupplierResourceUrl OUT, 'product.supplier.resource.url'
    DECLARE @productSupplierResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productSupplierResourceLink OUT, 'product.supplier.resource.link'
    DECLARE @productTagsResourceId int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productTagsResourceId OUT, 'product.tags.resource.id'
    DECLARE @productTagsResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productTagsResourceUrl OUT, 'product.tags.resource.url'
    DECLARE @productTagsResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productTagsResourceLink OUT, 'product.tags.resource.link'
    DECLARE @productVariantsResourceId int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productVariantsResourceId OUT, 'product.variants.resource.id'
    DECLARE @productVariantsResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productVariantsResourceUrl OUT, 'product.variants.resource.url'
    DECLARE @productVariantsResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productVariantsResourceLink OUT, 'product.variants.resource.link'
    DECLARE @productMovementsResourceId int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productMovementsResourceId OUT, 'product.movements.resource.id'
    DECLARE @productMovementsResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productMovementsResourceUrl OUT, 'product.movements.resource.url'
    DECLARE @productMovementsResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productMovementsResourceLink OUT, 'product.movements.resource.link'

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


END
GO