SQL Server
SQL Server
Square API - Batch Retrieve Catalog Objects
See more Square Examples
Returns a set of objects 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
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/batch-retrieve \
-- -X POST \
-- -H 'Square-Version: 2020-07-22' \
-- -H 'Authorization: Bearer ACCESS_TOKEN' \
-- -H 'Content-Type: application/json' \
-- -d '{
-- "object_ids": [
-- "W62UWFY35CWMYGVWK6TWJDNI",
-- "AA27W3M2GGTF3H6AVPNB77CK"
-- ],
-- "include_related_objects": true
-- }'
-- Use the following online tool to generate HTTP code from a CURL command
-- Convert a cURL Command to HTTP Source Code
-- Use this online tool to generate code from sample JSON:
-- Generate Code to Create JSON
-- The following JSON is sent in the request body.
-- {
-- "object_ids": [
-- "W62UWFY35CWMYGVWK6TWJDNI",
-- "AA27W3M2GGTF3H6AVPNB77CK"
-- ],
-- "include_related_objects": true
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
DECLARE @i int
SELECT @i = 0
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'object_ids[i]', 'W62UWFY35CWMYGVWK6TWJDNI'
SELECT @i = @i + 1
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'object_ids[i]', 'AA27W3M2GGTF3H6AVPNB77CK'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'include_related_objects', 1
-- 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'
-- This example uses the sandbox: connect.squareupsandbox.com
-- Production should use connect.squareup.com
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://connect.squareupsandbox.com/v2/catalog/batch-retrieve', @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)
-- {
-- "objects": [
-- {
-- "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"
-- }
-- }
-- }
-- ]
-- }
-- },
-- {
-- "type": "ITEM",
-- "id": "AA27W3M2GGTF3H6AVPNB77CK",
-- "updated_at": "2016-11-16T22:25:24.878Z",
-- "version": 1479335124878,
-- "is_deleted": false,
-- "present_at_all_locations": true,
-- "item_data": {
-- "name": "Coffee",
-- "description": "Hot Bean Juice",
-- "category_id": "BJNQCF2FJ6S6UIDT65ABHLRX",
-- "tax_ids": [
-- "HURXQOOAIC4IZSI2BEXQRYFY"
-- ],
-- "variations": [
-- {
-- "type": "ITEM_VARIATION",
-- "id": "LBTYIHNHU52WOIHWT7SNRIYH",
-- "updated_at": "2016-11-16T22:25:24.878Z",
-- "version": 1479335124878,
-- "is_deleted": false,
-- "present_at_all_locations": true,
-- "item_variation_data": {
-- "item_id": "AA27W3M2GGTF3H6AVPNB77CK",
-- "name": "Regular",
-- "ordinal": 0,
-- "pricing_type": "FIXED_PRICING",
-- "price_money": {
-- "amount": 250,
-- "currency": "USD"
-- }
-- }
-- },
-- {
-- "type": "ITEM_VARIATION",
-- "id": "PKYIC7HGGKW5CYVSCVDEIMHY",
-- "updated_at": "2016-11-16T22:25:24.878Z",
-- "version": 1479335124878,
-- "is_deleted": false,
-- "present_at_all_locations": true,
-- "item_variation_data": {
-- "item_id": "AA27W3M2GGTF3H6AVPNB77CK",
-- "name": "Large",
-- "ordinal": 1,
-- "pricing_type": "FIXED_PRICING",
-- "price_money": {
-- "amount": 350,
-- "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 @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_dataName nvarchar(4000)
DECLARE @item_dataDescription nvarchar(4000)
DECLARE @item_dataCategory_id nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
DECLARE @strVal nvarchar(4000)
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
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'objects'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'StringOf', @v_type OUT, 'objects[i].type'
EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'objects[i].id'
EXEC sp_OAMethod @jResp, 'StringOf', @updated_at OUT, 'objects[i].updated_at'
EXEC sp_OAMethod @jResp, 'IntOf', @version OUT, 'objects[i].version'
EXEC sp_OAMethod @jResp, 'BoolOf', @is_deleted OUT, 'objects[i].is_deleted'
EXEC sp_OAMethod @jResp, 'BoolOf', @present_at_all_locations OUT, 'objects[i].present_at_all_locations'
EXEC sp_OAMethod @jResp, 'StringOf', @item_dataName OUT, 'objects[i].item_data.name'
EXEC sp_OAMethod @jResp, 'StringOf', @item_dataDescription OUT, 'objects[i].item_data.description'
EXEC sp_OAMethod @jResp, 'StringOf', @item_dataCategory_id OUT, 'objects[i].item_data.category_id'
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'objects[i].item_data.tax_ids'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'objects[i].item_data.tax_ids[j]'
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'objects[i].item_data.variations'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
EXEC sp_OAMethod @jResp, 'StringOf', @v_type OUT, 'objects[i].item_data.variations[j].type'
EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'objects[i].item_data.variations[j].id'
EXEC sp_OAMethod @jResp, 'StringOf', @updated_at OUT, 'objects[i].item_data.variations[j].updated_at'
EXEC sp_OAMethod @jResp, 'IntOf', @version OUT, 'objects[i].item_data.variations[j].version'
EXEC sp_OAMethod @jResp, 'BoolOf', @is_deleted OUT, 'objects[i].item_data.variations[j].is_deleted'
EXEC sp_OAMethod @jResp, 'BoolOf', @present_at_all_locations OUT, 'objects[i].item_data.variations[j].present_at_all_locations'
EXEC sp_OAMethod @jResp, 'StringOf', @item_variation_dataItem_id OUT, 'objects[i].item_data.variations[j].item_variation_data.item_id'
EXEC sp_OAMethod @jResp, 'StringOf', @item_variation_dataName OUT, 'objects[i].item_data.variations[j].item_variation_data.name'
EXEC sp_OAMethod @jResp, 'IntOf', @item_variation_dataOrdinal OUT, 'objects[i].item_data.variations[j].item_variation_data.ordinal'
EXEC sp_OAMethod @jResp, 'StringOf', @item_variation_dataPricing_type OUT, 'objects[i].item_data.variations[j].item_variation_data.pricing_type'
EXEC sp_OAMethod @jResp, 'IntOf', @item_variation_dataPrice_moneyAmount OUT, 'objects[i].item_data.variations[j].item_variation_data.price_money.amount'
EXEC sp_OAMethod @jResp, 'StringOf', @item_variation_dataPrice_moneyCurrency OUT, 'objects[i].item_data.variations[j].item_variation_data.price_money.currency'
SELECT @j = @j + 1
END
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 @json
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
END
GO