SQL Server
SQL Server
Square API - Retrieve Catalog Object
See more Square Examples
Returns a single CatalogItem as a CatalogObject based on the provided ID.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://connect.squareup.com/v2/catalog/object/W62UWFY35CWMYGVWK6TWJDNI?include_related_objects=true
-- -H 'Square-Version: 2020-07-22'
-- -H 'Authorization: Bearer ACCESS_TOKEN'
-- -H 'Content-Type: application/json'
-- Use the following online tool to generate HTTP code from a CURL command
-- Convert a cURL Command to HTTP Source Code
-- Adds the "Authorization: Bearer ACCESS_TOKEN" header.
EXEC sp_OASetProperty @http, 'AuthToken', 'ACCESS_TOKEN'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Square-Version', '2020-07-22'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'id', '2PTZYUOFGGDMT75UZLHQAPAC'
-- This example uses the sandbox: connect.squareupsandbox.com
-- Production should use connect.squareup.com
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://connect.squareupsandbox.com/v2/catalog/object/{$id}?include_related_objects=true', @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)
-- {
-- "object": {
-- "type": "ITEM",
-- "id": "W62UWFY35CWMYGVWK6TWJDNI",
-- "updated_at": "2016-11-16T22:25:24.878Z",
-- "version": 1479335124878,
-- "is_deleted": false,
-- "present_at_all_locations": true,
-- "item_data": {
-- "name": "Tea",
-- "description": "Hot Leaf Juice",
-- "category_id": "BJNQCF2FJ6S6UIDT65ABHLRX",
-- "tax_ids": [
-- "HURXQOOAIC4IZSI2BEXQRYFY"
-- ],
-- "variations": [
-- {
-- "type": "ITEM_VARIATION",
-- "id": "2TZFAOHWGG7PAK2QEXWYPZSP",
-- "updated_at": "2016-11-16T22:25:24.878Z",
-- "version": 1479335124878,
-- "is_deleted": false,
-- "present_at_all_locations": true,
-- "item_variation_data": {
-- "item_id": "W62UWFY35CWMYGVWK6TWJDNI",
-- "name": "Mug",
-- "ordinal": 0,
-- "pricing_type": "FIXED_PRICING",
-- "price_money": {
-- "amount": 150,
-- "currency": "USD"
-- }
-- }
-- }
-- ]
-- }
-- },
-- "related_objects": [
-- {
-- "type": "CATEGORY",
-- "id": "BJNQCF2FJ6S6UIDT65ABHLRX",
-- "updated_at": "2016-11-16T22:25:24.878Z",
-- "version": 1479335124878,
-- "is_deleted": false,
-- "present_at_all_locations": true,
-- "category_data": {
-- "name": "Beverages"
-- }
-- },
-- {
-- "type": "TAX",
-- "id": "HURXQOOAIC4IZSI2BEXQRYFY",
-- "updated_at": "2016-11-16T22:25:24.878Z",
-- "version": 1479335124878,
-- "is_deleted": false,
-- "present_at_all_locations": true,
-- "tax_data": {
-- "name": "Sales Tax",
-- "calculation_phase": "TAX_SUBTOTAL_PHASE",
-- "inclusion_type": "ADDITIVE",
-- "percentage": "5.0",
-- "enabled": 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 @strVal nvarchar(4000)
DECLARE @v_type nvarchar(4000)
DECLARE @id nvarchar(4000)
DECLARE @updated_at nvarchar(4000)
DECLARE @version int
DECLARE @is_deleted int
DECLARE @present_at_all_locations int
DECLARE @item_variation_dataItem_id nvarchar(4000)
DECLARE @item_variation_dataName nvarchar(4000)
DECLARE @item_variation_dataOrdinal int
DECLARE @item_variation_dataPricing_type nvarchar(4000)
DECLARE @item_variation_dataPrice_moneyAmount int
DECLARE @item_variation_dataPrice_moneyCurrency nvarchar(4000)
DECLARE @category_dataName nvarchar(4000)
DECLARE @tax_dataName nvarchar(4000)
DECLARE @tax_dataCalculation_phase nvarchar(4000)
DECLARE @tax_dataInclusion_type nvarchar(4000)
DECLARE @tax_dataPercentage nvarchar(4000)
DECLARE @tax_dataEnabled int
DECLARE @objectType nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @objectType OUT, 'object.type'
DECLARE @objectId nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @objectId OUT, 'object.id'
DECLARE @objectUpdated_at nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @objectUpdated_at OUT, 'object.updated_at'
DECLARE @objectVersion int
EXEC sp_OAMethod @jResp, 'IntOf', @objectVersion OUT, 'object.version'
DECLARE @objectIs_deleted int
EXEC sp_OAMethod @jResp, 'BoolOf', @objectIs_deleted OUT, 'object.is_deleted'
DECLARE @objectPresent_at_all_locations int
EXEC sp_OAMethod @jResp, 'BoolOf', @objectPresent_at_all_locations OUT, 'object.present_at_all_locations'
DECLARE @objectItem_dataName nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @objectItem_dataName OUT, 'object.item_data.name'
DECLARE @objectItem_dataDescription nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @objectItem_dataDescription OUT, 'object.item_data.description'
DECLARE @objectItem_dataCategory_id nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @objectItem_dataCategory_id OUT, 'object.item_data.category_id'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'object.item_data.tax_ids'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'object.item_data.tax_ids[i]'
SELECT @i = @i + 1
END
SELECT @i = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'object.item_data.variations'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'StringOf', @v_type OUT, 'object.item_data.variations[i].type'
EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'object.item_data.variations[i].id'
EXEC sp_OAMethod @jResp, 'StringOf', @updated_at OUT, 'object.item_data.variations[i].updated_at'
EXEC sp_OAMethod @jResp, 'IntOf', @version OUT, 'object.item_data.variations[i].version'
EXEC sp_OAMethod @jResp, 'BoolOf', @is_deleted OUT, 'object.item_data.variations[i].is_deleted'
EXEC sp_OAMethod @jResp, 'BoolOf', @present_at_all_locations OUT, 'object.item_data.variations[i].present_at_all_locations'
EXEC sp_OAMethod @jResp, 'StringOf', @item_variation_dataItem_id OUT, 'object.item_data.variations[i].item_variation_data.item_id'
EXEC sp_OAMethod @jResp, 'StringOf', @item_variation_dataName OUT, 'object.item_data.variations[i].item_variation_data.name'
EXEC sp_OAMethod @jResp, 'IntOf', @item_variation_dataOrdinal OUT, 'object.item_data.variations[i].item_variation_data.ordinal'
EXEC sp_OAMethod @jResp, 'StringOf', @item_variation_dataPricing_type OUT, 'object.item_data.variations[i].item_variation_data.pricing_type'
EXEC sp_OAMethod @jResp, 'IntOf', @item_variation_dataPrice_moneyAmount OUT, 'object.item_data.variations[i].item_variation_data.price_money.amount'
EXEC sp_OAMethod @jResp, 'StringOf', @item_variation_dataPrice_moneyCurrency OUT, 'object.item_data.variations[i].item_variation_data.price_money.currency'
SELECT @i = @i + 1
END
SELECT @i = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'related_objects'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'StringOf', @v_type OUT, 'related_objects[i].type'
EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'related_objects[i].id'
EXEC sp_OAMethod @jResp, 'StringOf', @updated_at OUT, 'related_objects[i].updated_at'
EXEC sp_OAMethod @jResp, 'IntOf', @version OUT, 'related_objects[i].version'
EXEC sp_OAMethod @jResp, 'BoolOf', @is_deleted OUT, 'related_objects[i].is_deleted'
EXEC sp_OAMethod @jResp, 'BoolOf', @present_at_all_locations OUT, 'related_objects[i].present_at_all_locations'
EXEC sp_OAMethod @jResp, 'StringOf', @category_dataName OUT, 'related_objects[i].category_data.name'
EXEC sp_OAMethod @jResp, 'StringOf', @tax_dataName OUT, 'related_objects[i].tax_data.name'
EXEC sp_OAMethod @jResp, 'StringOf', @tax_dataCalculation_phase OUT, 'related_objects[i].tax_data.calculation_phase'
EXEC sp_OAMethod @jResp, 'StringOf', @tax_dataInclusion_type OUT, 'related_objects[i].tax_data.inclusion_type'
EXEC sp_OAMethod @jResp, 'StringOf', @tax_dataPercentage OUT, 'related_objects[i].tax_data.percentage'
EXEC sp_OAMethod @jResp, 'BoolOf', @tax_dataEnabled OUT, 'related_objects[i].tax_data.enabled'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
END
GO