SQL Server
SQL Server
Lightspeed - Get All Products
See more Lightspeed Examples
Retrieve a list of all product objects from this shop.Chilkat 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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- 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
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
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