Reference

Table Products

Description

No description available

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

API Source - FastSpring

Read and write FastSpring data effortlessly. Integrate, manage, and automate orders, subscriptions, quotes, products, and accounts — almost no coding required.

FastSpring

Products

There are no parameters to configure.

SSIS API Source - Read from table or endpoint

Read/write to Products table using API Destination

API Destination - FastSpring

Read and write FastSpring data effortlessly. Integrate, manage, and automate orders, subscriptions, quotes, products, and accounts — almost no coding required.

FastSpring

Products

Select

There are no parameters to configure.

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read products

Gets all products with their attributes (display name, SKU, prices, format, etc.). Use the <code>Products</code> table.

SELECT * FROM Products

Read a product by ID

Gets one product by its ID. Use <code>WHERE Id='...'</code> with the product ID.

SELECT * FROM Products WHERE Id='some-product-id'

Update product

Updates product attributes by ID. Use <code>UPDATE Products SET ... WHERE Id='...'</code> with the product ID and the columns you want to change (display name, SKU, prices, tax code, trial settings, etc.).

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 from CSV

Updates multiple products from an external source. Use <code>UPDATE Products SOURCE('ODBC', ...)</code> with a connection string and a <code>SELECT</code> whose column names (or aliases) match the product columns to update (e.g. <code>Id</code>, <code>TaxCode</code>). Works with ZappySys CSV Driver, SQL Server, MySQL, PostgreSQL, or any ODBC source.

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)

Creates a new product or updates an existing one by ID (upsert). Use <code>UPSERT INTO Products</code> with the same columns as for insert or update. If the ID exists, the row is updated; otherwise a new product is created.

--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 	
)

Read accounts

Gets all accounts for your store. Use the <code>Accounts</code> table. Optionally filter by <code>Email</code>, <code>CustomKey</code>, <code>OrderID</code>, <code>OrderReference</code>, <code>SubscriptionId</code>, <code>Products</code>, <code>Refunds</code>, <code>SubscriptionStatus</code> in the <code>WITH</code> clause.

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'
)
*/

Read accounts with specific product orders

Gets accounts that placed orders for specific products. Use <code>SELECT * FROM Accounts WITH(Products='...')</code> with a comma-separated list of product IDs or paths.

SELECT * FROM Accounts WITH (Products='ssis-pp-pro,ssis-pp-pro-sub,ssis-pp-ent,ssis-pp-ent-sub')

Read account charges

Gets charges for accounts. Use the <code>AccountCharges</code> table. Optionally filter by <code>Email</code>, <code>CustomKey</code>, <code>OrderID</code>, <code>OrderReference</code>, <code>SubscriptionId</code>, <code>Products</code>, <code>Refunds</code>, <code>SubscriptionStatus</code> in the <code>WITH</code> clause.

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'
)
*/

Read account orders by email

Gets account orders filtered by email or other criteria. Use the <code>AccountOrders</code> table and pass <code>Email='...'</code> (or <code>CustomKey</code>, <code>OrderID</code>, <code>OrderReference</code>, <code>SubscriptionId</code>, <code>Products</code>, <code>Refunds</code>, <code>SubscriptionStatus</code>) in the <code>WITH</code> clause.

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: