(SQL Server) Lightspeed - Get All Products

Retrieve a list of all product objects from this shop.

For more information, see https://developers.lightspeedhq.com/ecom/endpoints/product/#get-all-products

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.shoplightspeed.com/en/products.json \
    --   -u {key}:{secret}

    -- 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 the correct cluster for your shop.  Here are the choices:
    -- eu1 https://api.webshopapp.com/en/
    -- us1 https://api.shoplightspeed.com/en/
    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.webshopapp.com/en/products.json', @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)

    -- {
    --   "product": {
    --     "id": 20967267,
    --     "createdAt": "2019-05-28T15:25:46+00:00",
    --     "updatedAt": "2019-05-28T17:16:16+00:00",
    --     "isVisible": true,
    --     "visibility": "visible",
    --     "hasMatrix": false,
    --     "data01": "",
    --     "data02": "",
    --     "data03": "",
    --     "url": "lookin-sharp-tee",
    --     "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": {
    --       "id": 2785,
    --       "createdAt": "2019-05-28T15:32:12+00:00",
    --       "updatedAt": "2019-05-28T15:42:59+00:00",
    --       "name": "Shirts",
    --       "options": [
    --         {
    --           "id": 4626,
    --           "sortOrder": 1,
    --           "name": "Size",
    --           "values": [
    --             {
    --               "id": 14045,
    --               "sortOrder": 1,
    --               "name": "S"
    --             },
    --             {
    --               "id": 14046,
    --               "sortOrder": 2,
    --               "name": "M"
    --             },
    --             {
    --               "id": 14047,
    --               "sortOrder": 3,
    --               "name": "L"
    --             }
    --           ]
    --         }
    --       ]
    --     },
    --     "brand": {
    --       "resource": {
    --         "id": 1171202,
    --         "url": "brands/1171202",
    --         "link": "https://api.shoplightspeed.com/us/brands/1171202.json"
    --       }
    --     },
    --     "categories": {
    --       "resource": {
    --         "id": false,
    --         "url": "categories/products?product=20967267",
    --         "link": "https://api.shoplightspeed.com/us/categories/products.json?product=20967267"
    --       }
    --     },
    --     "deliverydate": {
    --       "resource": {
    --         "id": 6488,
    --         "url": "deliverydates/6488",
    --         "link": "https://api.shoplightspeed.com/us/deliverydates/6488.json"
    --       }
    --     },
    --     "image": {
    --       "createdAt": "2019-05-28T15:25:46+00:00",
    --       "updatedAt": "2019-05-28T15:25:46+00:00",
    --       "extension": "jpg",
    --       "size": 86649,
    --       "title": "17x35x-jcfk-88na-udwd-5hck-z63u-img-16-e0aa4f57-f4",
    --       "thumb": "https://cdn.shoplightspeed.com/shops/000001/files/14119398/50x50x2/17x35x-jcfk-88na-udwd-5hck-z63u-img-16-e0aa4f57-f4.jpg",
    --       "src": "https://cdn.shoplightspeed.com/shops/000001/files/14119398/17x35x-jcfk-88na-udwd-5hck-z63u-img-16-e0aa4f57-f4.jpg"
    --     },
    --     "images": {
    --       "resource": {
    --         "id": false,
    --         "url": "products/20967267/images",
    --         "link": "https://api.shoplightspeed.com/us/products/20967267/images.json"
    --       }
    --     },
    --     "relations": {
    --       "resource": {
    --         "id": false,
    --         "url": "products/20967267/relations",
    --         "link": "https://api.shoplightspeed.com/us/products/20967267/relations.json"
    --       }
    --     },
    --     "metafields": {
    --       "resource": {
    --         "id": false,
    --         "url": "products/20967267/metafields",
    --         "link": "https://api.shoplightspeed.com/us/products/20967267/metafields.json"
    --       }
    --     },
    --     "reviews": {
    --       "resource": {
    --         "id": false,
    --         "url": "reviews?product=20967267",
    --         "link": "https://api.shoplightspeed.com/us/reviews.json?product=20967267"
    --       }
    --     },
    --     "type": false,
    --     "attributes": {
    --       "resource": {
    --         "id": false,
    --         "url": "products/20967267/attributes",
    --         "link": "https://api.shoplightspeed.com/us/products/20967267/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=20967267",
    --         "link": "https://api.shoplightspeed.com/us/tags/products.json?product=20967267"
    --       }
    --     },
    --     "variants": {
    --       "resource": {
    --         "id": false,
    --         "url": "variants?product=20967267",
    --         "link": "https://api.shoplightspeed.com/us/variants.json?product=20967267"
    --       }
    --     },
    --     "movements": {
    --       "resource": {
    --         "id": false,
    --         "url": "variants/movements?product=20967267",
    --         "link": "https://api.shoplightspeed.com/us/variants/movements.json?product=20967267"
    --       }
    --     }
    --   }
    -- }

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

    DECLARE @sortOrder int

    DECLARE @name nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    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 @productSetId int
    EXEC sp_OAMethod @jResp, 'IntOf', @productSetId OUT, 'product.set.id'
    DECLARE @productSetCreatedAt nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productSetCreatedAt OUT, 'product.set.createdAt'
    DECLARE @productSetUpdatedAt nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productSetUpdatedAt OUT, 'product.set.updatedAt'
    DECLARE @productSetName nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productSetName OUT, 'product.set.name'
    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 @productImageCreatedAt nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productImageCreatedAt OUT, 'product.image.createdAt'
    DECLARE @productImageUpdatedAt nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productImageUpdatedAt OUT, 'product.image.updatedAt'
    DECLARE @productImageExtension nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productImageExtension OUT, 'product.image.extension'
    DECLARE @productImageSize int
    EXEC sp_OAMethod @jResp, 'IntOf', @productImageSize OUT, 'product.image.size'
    DECLARE @productImageTitle nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productImageTitle OUT, 'product.image.title'
    DECLARE @productImageThumb nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productImageThumb OUT, 'product.image.thumb'
    DECLARE @productImageSrc nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productImageSrc OUT, 'product.image.src'
    DECLARE @productImagesResourceId int
    EXEC sp_OAMethod @jResp, 'BoolOf', @productImagesResourceId OUT, 'product.images.resource.id'
    DECLARE @productImagesResourceUrl nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productImagesResourceUrl OUT, 'product.images.resource.url'
    DECLARE @productImagesResourceLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @productImagesResourceLink OUT, 'product.images.resource.link'
    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'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'product.set.options'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'IntOf', @id OUT, 'product.set.options[i].id'
        EXEC sp_OAMethod @jResp, 'IntOf', @sortOrder OUT, 'product.set.options[i].sortOrder'
        EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'product.set.options[i].name'
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'product.set.options[i].values'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'IntOf', @id OUT, 'product.set.options[i].values[j].id'
            EXEC sp_OAMethod @jResp, 'IntOf', @sortOrder OUT, 'product.set.options[i].values[j].sortOrder'
            EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'product.set.options[i].values[j].name'
            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