Table Products
Description
No description available
Parameters
Parameter | Label | Required | Options | Description | Help | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
There are no parameters |
Supported Operations
Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.Method | Supported | Reference EndPoint |
---|---|---|
SELECT | get_products | |
INSERT | post_product | |
UPDATE | put_product | |
UPSERT | post_product | |
DELETE | ||
LOOKUP | get_product |
Examples
SSIS
Use FastSpring Connector in API Source component to read data or in API Destination component to read/write data:
Read from Products table using API Source

Read/write to Products table using API Destination

ODBC application
Use these SQL queries in your ODBC application data source:
Read product details
Read all products
SELECT * FROM Products
Read a single product by id
Read a single product by id
SELECT * FROM Products Where Id='some-product-id'
Update Product
Update product attributes by its ID
UPDATE Products
SET
Display='Product ABCD'
, Sku='PRD-ABCD-01'
, Summary='Product Abcd **STD**'
, Description='This product can be used to call **API** supports markdown syntax'
, Action='**Action** supports markdown'
, TaxCode='DC010500'
, Image='https://zappysys.com/api/images/ZappySys-icon.png'
, FulfillmentInstructions='Thank you for shopping. **Activate** license by using our License App'
, Format='digital'
, PriceUSD='101.5'
, PriceEUR='102.5'
, PriceGBP='103.5'
, PriceCAD='104.5'
, PriceNZD='105.5'
, PriceCHF='106.5'
, PriceSEK='107.5'
, PriceCZK='108.5'
, PriceDKK='109.5'
, PricePLN='101.5'
, PriceSGD='102.5'
, PriceJPY='103.5'
, PriceCNY='104.5'
, PriceHKD='105.5'
, PriceINR='106.5'
, PriceBRL='107.5'
, PriceAUD='108.5'
, Badge='Badge-1'
, Rank=1
, Trial=14 --how many trial days
, TrialPriceUSD=10.5
--, Renew=1 --not allowed for update : Only for create (enable subscription)
, Interval='year' --allowed values: adhoc, day, week, year, only needed if you are creating a subscription
, IntervalLength=1
, IntervalCount=0
, QuantityBehavior='allow' --allowed values: allow, lock, hide
, QuantityDefault=1
, Attributes = '{"mykey-1" : "some-value-1", "mykey-2" : "some-value-2"}'
--, Fulfillments='[ { fullfillment }, { fullfillment } ... ]' --JSON fragment
Where Id='abcd'
Update multiple Products (read from CSV file)
Update multiple products from CSV file or other external source (e.g. Microsoft SQL Server, MySQL, Postgresql, ODBC)
UPDATE Products
SOURCE('ODBC',
--File input example (use as to match Column name allowed in Products Table to write)
--'Driver={ZappySys CSV Driver};DataPath=c:\data\products.csv',
--'select P_ID as Id,P_TAXCODE as TaxCode From $'
--Direct input example
'Driver={ZappySys CSV Driver}',
'select ''abcd'' as Id, ''DC010500'' as TaxCode UNION
select ''xyz'' as Id, ''DC010500'' as TaxCode UNION
select ''zzz'' as Id, ''DC010500'' as TaxCode
'
)
Create or Update Product (Upsert)
Create or Update product (Upsert)
--Insert or Update
--UPSERT INTO Products
--Insert or Update
UPSERT INTO Products
( [Id] , [Display] , [Summary]
, [Description] , [Action] , [Format]
, [Sku] , [TaxCode] , [Image]
, [Renew] , [Interval] , [IntervalLength]
, [IntervalCount] , [QuantityBehavior] , [QuantityDefault]
, [PriceUSD] , [PriceEUR] , [PriceGBP]
, [PriceCAD] , [PriceNZD] , [PriceCHF]
, [PriceSEK] , [PriceCZK] , [PriceDKK]
, [PricePLN] , [PriceSGD] , [PriceJPY]
, [PriceCNY] , [PriceHKD] , [PriceINR] , [PriceBRL] , [PriceAUD]
, [Trial]
, [TrialPriceUSD] , [TrialPriceEUR] , [TrialPriceGBP]
, [PaymentCollected] , [PaidTrial]
, [FulfillmentInstructions]
, [DateLimitsEnabled] , [CancellationInterval] , [CancellationIntervalLength]
, [SetupFeeTitle]
, [SetupFeePriceUSD] , [SetupFeePriceEUR] , [SetupFeePriceGBP]
, [Badge] , [Rank]
, [Attributes]
)
VALUES('test-std-id'
, '3Year **standard** subscription edition'
, 'summary **standard edition** _some italic_ ' -- markdown syntax allowed
, 'long description **test STD** and many more lines' --markdown syntax allowed
, 'action text with **markdown** '
, 'digital' -- e.g. digital OR physical OR digital-and-physical
, 'TEST-STD-001'
, 'DC010500'
, 'https://zappysys.com/api/images/ZappySys-icon.png'
, true , 'year' , 1
, 1 , 'allow' -- e.g. allow Or lock Or hide
, 3 --stop renewal after 3 periods
, 10.12 , 11.12 , 12.12
, 13.12 , 14.12 , 15.12
, 16.12 , 17.12 , 18.12
, 19.12 , 20.12 , 21.12
, 22.12 , 23.12 , 24.12 , 25.12 , 26.12
, 14 --how many days trial allowed
, 10.12 , 11.12 , 12.12
, 'true', true
, 'Thank you for shopping, **here are** license instructions' --markdown supported
, false, 'day', 5
, 'Setup title'
--setup fees in common currencies
, 10.50 , 11.50 , 12.50
, 'badge-1' , 1
, '{"mykey-1" : "some-value-1", "mykey-2" : "some-value-2"}' --JSON fragment
--, '[... fullfillments.. ]' --JSON fragment
)
Get all accounts
Get all accounts for your store
SELECT *
FROM Accounts
SELECT "Id"
, "ContactFirst"
, "ContactLast"
, "ContactEmail"
, "ContactCompany"
, "ContactPhone"
, "ContactSubscribed"
, "AddressLine1"
, "AddressLine2"
, "City"
, "Region"
, "RegionCustom"
, "PostalCode"
, "AddressCompany"
, "Language"
, "Country"
, "LookupGlobal"
, "Url"
, "PaymentMethods"
, "PaymentActive"
, "Orders"
, "Subscriptions"
, "Charges"
, "Subscribed"
, "TaxExemptionData"
FROM Accounts
--Use WITH clause --OR-- Key column(s) in WHERE clause
--WHERE [Id] = 'abcd'
--search by one or more parameters below
/*
WITH(
Email='X'
, CustomKey='X'
, GlobalKey='X'
, OrderID='X'
, OrderReference='X'
, SubscriptionId='X'
, Products='PROD-1,PROD-2,PROD-3'
, Refunds='true'
, SubscriptionStatus='active'
)
*/
Get all accounts with specific product orders
Use below query to search accounts who placed orders for specific products
SELECT * from Accounts WITH(Products='ssis-pp-pro,ssis-pp-pro-sub,ssis-pp-ent,ssis-pp-ent-sub')
Get account charges
Use below query to search account charges
SELECT * from AccountCharges
--search by one or more parameters below
/*
WITH(
Email='X'
, CustomKey='X'
, GlobalKey='X'
, OrderID='X'
, OrderReference='X'
, SubscriptionId='X'
, Products='PROD-1,PROD-2,PROD-3'
, Refunds='true'
, SubscriptionStatus='active'
)
*/
Get account orders by email id
Use below query to search for accounts and orders with search criteria
SELECT * from AccountOrders
WITH(
Email='bob@abc.com'
)
--search by one or more parameters below
/*
WITH(
Email='X'
, CustomKey='X'
, GlobalKey='X'
, OrderID='X'
, OrderReference='X'
, SubscriptionId='X'
, Products='PROD-1,PROD-2,PROD-3'
, Refunds='true'
, SubscriptionStatus='active'
)
*/
SQL Server
Use these SQL queries in SQL Server after you create a data source in Data Gateway:
Read product details
Read all products
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Products';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_FASTSPRING_IN_DATA_GATEWAY];
Read a single product by id
Read a single product by id
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Products Where Id=''some-product-id''';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_FASTSPRING_IN_DATA_GATEWAY];
Update Product
Update product attributes by its ID
DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Products
SET
Display=''Product ABCD''
, Sku=''PRD-ABCD-01''
, Summary=''Product Abcd **STD**''
, Description=''This product can be used to call **API** supports markdown syntax''
, Action=''**Action** supports markdown''
, TaxCode=''DC010500''
, Image=''https://zappysys.com/api/images/ZappySys-icon.png''
, FulfillmentInstructions=''Thank you for shopping. **Activate** license by using our License App''
, Format=''digital''
, PriceUSD=''101.5''
, PriceEUR=''102.5''
, PriceGBP=''103.5''
, PriceCAD=''104.5''
, PriceNZD=''105.5''
, PriceCHF=''106.5''
, PriceSEK=''107.5''
, PriceCZK=''108.5''
, PriceDKK=''109.5''
, PricePLN=''101.5''
, PriceSGD=''102.5''
, PriceJPY=''103.5''
, PriceCNY=''104.5''
, PriceHKD=''105.5''
, PriceINR=''106.5''
, PriceBRL=''107.5''
, PriceAUD=''108.5''
, Badge=''Badge-1''
, Rank=1
, Trial=14 --how many trial days
, TrialPriceUSD=10.5
--, Renew=1 --not allowed for update : Only for create (enable subscription)
, Interval=''year'' --allowed values: adhoc, day, week, year, only needed if you are creating a subscription
, IntervalLength=1
, IntervalCount=0
, QuantityBehavior=''allow'' --allowed values: allow, lock, hide
, QuantityDefault=1
, Attributes = ''{"mykey-1" : "some-value-1", "mykey-2" : "some-value-2"}''
--, Fulfillments=''[ { fullfillment }, { fullfillment } ... ]'' --JSON fragment
Where Id=''abcd''';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_FASTSPRING_IN_DATA_GATEWAY];
Update multiple Products (read from CSV file)
Update multiple products from CSV file or other external source (e.g. Microsoft SQL Server, MySQL, Postgresql, ODBC)
DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Products
SOURCE(''ODBC'',
--File input example (use as to match Column name allowed in Products Table to write)
--''Driver={ZappySys CSV Driver};DataPath=c:\data\products.csv'',
--''select P_ID as Id,P_TAXCODE as TaxCode From $''
--Direct input example
''Driver={ZappySys CSV Driver}'',
''select ''''abcd'''' as Id, ''''DC010500'''' as TaxCode UNION
select ''''xyz'''' as Id, ''''DC010500'''' as TaxCode UNION
select ''''zzz'''' as Id, ''''DC010500'''' as TaxCode
''
)';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_FASTSPRING_IN_DATA_GATEWAY];
Create or Update Product (Upsert)
Create or Update product (Upsert)
DECLARE @MyQuery NVARCHAR(MAX) = '--Insert or Update
--UPSERT INTO Products
--Insert or Update
UPSERT INTO Products
( [Id] , [Display] , [Summary]
, [Description] , [Action] , [Format]
, [Sku] , [TaxCode] , [Image]
, [Renew] , [Interval] , [IntervalLength]
, [IntervalCount] , [QuantityBehavior] , [QuantityDefault]
, [PriceUSD] , [PriceEUR] , [PriceGBP]
, [PriceCAD] , [PriceNZD] , [PriceCHF]
, [PriceSEK] , [PriceCZK] , [PriceDKK]
, [PricePLN] , [PriceSGD] , [PriceJPY]
, [PriceCNY] , [PriceHKD] , [PriceINR] , [PriceBRL] , [PriceAUD]
, [Trial]
, [TrialPriceUSD] , [TrialPriceEUR] , [TrialPriceGBP]
, [PaymentCollected] , [PaidTrial]
, [FulfillmentInstructions]
, [DateLimitsEnabled] , [CancellationInterval] , [CancellationIntervalLength]
, [SetupFeeTitle]
, [SetupFeePriceUSD] , [SetupFeePriceEUR] , [SetupFeePriceGBP]
, [Badge] , [Rank]
, [Attributes]
)
VALUES(''test-std-id''
, ''3Year **standard** subscription edition''
, ''summary **standard edition** _some italic_ '' -- markdown syntax allowed
, ''long description **test STD** and many more lines'' --markdown syntax allowed
, ''action text with **markdown** ''
, ''digital'' -- e.g. digital OR physical OR digital-and-physical
, ''TEST-STD-001''
, ''DC010500''
, ''https://zappysys.com/api/images/ZappySys-icon.png''
, true , ''year'' , 1
, 1 , ''allow'' -- e.g. allow Or lock Or hide
, 3 --stop renewal after 3 periods
, 10.12 , 11.12 , 12.12
, 13.12 , 14.12 , 15.12
, 16.12 , 17.12 , 18.12
, 19.12 , 20.12 , 21.12
, 22.12 , 23.12 , 24.12 , 25.12 , 26.12
, 14 --how many days trial allowed
, 10.12 , 11.12 , 12.12
, ''true'', true
, ''Thank you for shopping, **here are** license instructions'' --markdown supported
, false, ''day'', 5
, ''Setup title''
--setup fees in common currencies
, 10.50 , 11.50 , 12.50
, ''badge-1'' , 1
, ''{"mykey-1" : "some-value-1", "mykey-2" : "some-value-2"}'' --JSON fragment
--, ''[... fullfillments.. ]'' --JSON fragment
)';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_FASTSPRING_IN_DATA_GATEWAY];
Get all accounts
Get all accounts for your store
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM Accounts
SELECT "Id"
, "ContactFirst"
, "ContactLast"
, "ContactEmail"
, "ContactCompany"
, "ContactPhone"
, "ContactSubscribed"
, "AddressLine1"
, "AddressLine2"
, "City"
, "Region"
, "RegionCustom"
, "PostalCode"
, "AddressCompany"
, "Language"
, "Country"
, "LookupGlobal"
, "Url"
, "PaymentMethods"
, "PaymentActive"
, "Orders"
, "Subscriptions"
, "Charges"
, "Subscribed"
, "TaxExemptionData"
FROM Accounts
--Use WITH clause --OR-- Key column(s) in WHERE clause
--WHERE [Id] = ''abcd''
--search by one or more parameters below
/*
WITH(
Email=''X''
, CustomKey=''X''
, GlobalKey=''X''
, OrderID=''X''
, OrderReference=''X''
, SubscriptionId=''X''
, Products=''PROD-1,PROD-2,PROD-3''
, Refunds=''true''
, SubscriptionStatus=''active''
)
*/';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_FASTSPRING_IN_DATA_GATEWAY];
Get all accounts with specific product orders
Use below query to search accounts who placed orders for specific products
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Accounts WITH(Products=''ssis-pp-pro,ssis-pp-pro-sub,ssis-pp-ent,ssis-pp-ent-sub'')';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_FASTSPRING_IN_DATA_GATEWAY];
Get account charges
Use below query to search account charges
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from AccountCharges
--search by one or more parameters below
/*
WITH(
Email=''X''
, CustomKey=''X''
, GlobalKey=''X''
, OrderID=''X''
, OrderReference=''X''
, SubscriptionId=''X''
, Products=''PROD-1,PROD-2,PROD-3''
, Refunds=''true''
, SubscriptionStatus=''active''
)
*/';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_FASTSPRING_IN_DATA_GATEWAY];
Get account orders by email id
Use below query to search for accounts and orders with search criteria
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from AccountOrders
WITH(
Email=''bob@abc.com''
)
--search by one or more parameters below
/*
WITH(
Email=''X''
, CustomKey=''X''
, GlobalKey=''X''
, OrderID=''X''
, OrderReference=''X''
, SubscriptionId=''X''
, Products=''PROD-1,PROD-2,PROD-3''
, Refunds=''true''
, SubscriptionStatus=''active''
)
*/';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_FASTSPRING_IN_DATA_GATEWAY];