SQL Server
SQL Server
Lightspeed - Create a Product
See more Lightspeed Examples
Create a new product based on the given parameters.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
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 -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.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 @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://api.webshopapp.com/en/products.json', @json, 'application/json', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
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 @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 @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
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": 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 @resp
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
END
GO