Adding a Connection to AdobeCommerce

To add a connection to AdobeCommerce:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the AdobeCommerce icon is not available, click the Add More icon to download and install the AdobeCommerce connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Connecting to AdobeCommerce

To connect, set the URL; for example, http://yoursitename/magento2, and optionally provide the StoreCode of the store you are connecting to, if more than one are present.

Authenticating to AdobeCommerce

To authenticate to AdobeCommerce server versions, you can use either the Basic or the Token AuthScheme.

Basic

• AuthScheme Set this to Basic.
• User Set this to your user name in AdobeCommerce.
• Password Set this to your password in AdobeCommerce.

Access Token

• AuthScheme Set this to Token.
• AccessToken Set this to your AccessToken in AdobeCommerce.

To generate the AccessToken, Log in to the AdobeCommerce Stores > Settings > Configuration > Services > OAuth > Access Token.

This section details a selection of advanced features of the AdobeCommerce Sync App.

User Defined Views

The Sync App supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to AdobeCommerce and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

Customizing the SSL Configuration

To enable TLS, set the following:

• URL: Prefix the connection string with https://

With this configuration, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Sync App uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Sync App models your account as relational databases through the AdobeCommerce 2.x API. The Data Models described in this chapter provide examples of what your AdobeCommerce environment might look like. The actual data model will be obtained dynamically based on your AdobeCommerce account.

Key Features

• The Sync App models AdobeCommerce Products, Customers, Inventory, and more as relational tables, allowing you to write SQL to query AdobeCommerce data.
• Stored procedures allow you to execute operations to AdobeCommerce, including downloading and uploading objects.
• Live connectivity to these objects means any changes to your AdobeCommerce account are immediately reflected when using the Sync App.

The Sync App models the data in AdobeCommerce as a list of tables in a relational database that can be queried using standard SQL statements.

Adobe Commerce Connector for CData Sync Tables

Name	Description
AdobeStockAssetApiAsset	AdobeStockAssetApiAsset is an auto generated table
BundleProductOption	BundleProductOption is an auto generated table. The supported operations are Delete, and Querying data from this table.
CatalogAttributeSet	CatalogAttributeSet is an auto generated table. The supported operations are Update, Delete, and Querying data from this table.
CatalogProductAttribute	CatalogProductAttribute is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
CmsBlock	CmsBlock is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
CmsPage	CmsPage is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
ConfigurableProductOption	ConfigurableProductOption is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
CustomerAddress	CustomerAddress is an auto generated table. The supported operations are Delete, and Querying data from this table.
CustomerGroup	CustomerGroup is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
Customers	Customers is an auto generated table. The supported operations are Update, Delete, and Querying data from this table.
DownloadableLink	DownloadableLink is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
DownloadableSample	DownloadableSample is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
EavAttributeSet	EavAttributeSet is an auto generated table. The supported operations are Update, Delete, and Querying data from this table.
GiftMessageCart	GiftMessageCart is an auto generated table. The supported operations are Insert, and Querying data from this table.
GiftMessageGuestCart	GiftMessageGuestCart is an auto generated table. The supported operations are Insert, and Querying data from this table.
GiftMessageGuestItem	GiftMessageGuestItem is an auto generated table. The supported operations are Insert, and Querying data from this table.
GiftMessageItem	GiftMessageItem is an auto generated table. The supported operations are Insert, and Querying data from this table.
GuestBillingAddresses	GuestBillingAddresses is an auto generated table. The supported operations are Insert, and Querying data from this table.
Inventory	Inventory is an auto generated table. The supported operations are Update, and Querying data from this table.
InventoryApiSource	InventoryApiSource is an auto generated table
InventoryApiStock	InventoryApiStock is an auto generated table
OrderComments	OrderComments is an auto generated table. The supported operations are Insert, and Querying data from this table.
Orders	Orders is an auto generated table. The supported operations are Insert, Update, and Querying data from this table.
ProductAttributeGroup	ProductAttributeGroup is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
ProductAttributeOptions	ProductAttributeOptions is an auto generated table. The supported operations are Insert, Delete, and Querying data from this table.
ProductCategories	ProductCategories is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
ProductCustomOption	ProductCustomOption is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
ProductMedia	ProductMedia is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
Products	Products is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
QuoteCart	QuoteCart is an auto generated table. The supported operations are Update, and Querying data from this table.
QuoteCartItem	QuoteCartItem is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
QuoteGuestCartItem	QuoteGuestCartItem is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
SalesCreditmemo	SalesCreditmemo is an auto generated table. The supported operations are Insert, and Querying data from this table.
SalesCreditmemoComments	SalesCreditmemoComments is an auto generated table. The supported operations are Insert, and Querying data from this table.
SalesInvoice	SalesInvoice is an auto generated table. The supported operations are Insert, and Querying data from this table.
SalesInvoiceComments	SalesInvoiceComments is an auto generated table. The supported operations are Insert, and Querying data from this table.
SalesRule	SalesRule is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
SalesRuleCoupon	SalesRuleCoupon is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
SalesShipment	SalesShipment is an auto generated table. The supported operations are Insert, and Querying data from this table.
SalesShipmentComments	SalesShipmentComments is an auto generated table. The supported operations are Insert, and Querying data from this table.
TaxClass	TaxClass is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
TaxRate	TaxRate is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.
TaxRule	TaxRule is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

AdobeStockAssetApiAsset is an auto generated table

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	ID
Category	String	False	This is an auto-generated column
CategoryId	Int	False	Category
CreationDate	String	False	Creation date
Creator	String	False	This is an auto-generated column
CreatorId	Int	False	The creator
ExtensionAttributes	String	False	This is an auto-generated column
IsLicensed	Int	False	Licensed
MediaGalleryId	Int	False	Media gallery asset id
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

BundleProductOption is an auto generated table. The supported operations are Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Sku or OptionId while the rest of the filter is executed client side within the Sync App.

You must specify the Sku column or the Sku and OptionId columns in the WHERE clause to retrieve results from BundleProductOption. Sku and OptionId can be used only with the equals (=) operator. For example:

SELECT * FROM [BundleProductOption] WHERE Sku = '123'

SELECT * FROM [BundleProductOption] WHERE Sku = '123' AND OptionId = '1'

Delete

You can delete Bundle options by providing the Sku and OptionId columns.

DELETE FROM BundleProductOption WHERE Sku = 'sjl-1999' AND OptionId = '1'

Columns

Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
OptionId [KEY]	Int	False	Option id
Position	Int	False	Option position
ProductLinks	String	False	Product links
Required	Bool	False	Is required option
Sku [KEY]	String	False	Product sku
Title	String	False	Option title
Type	String	False	Input type

CatalogAttributeSet is an auto generated table. The supported operations are Update, Delete, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
AttributeSetId [KEY]	Int	False	Attribute set ID
AttributeSetName	String	False	Attribute set name
EntityTypeId	Int	False	Attribute set entity type id
ExtensionAttributes	String	False	This is an auto-generated column
SearchCriteria	String	False	This is an auto-generated column
SortOrder	Int	False	Attribute set sort order index
TotalCount	Int	False	Total count.

CatalogProductAttribute is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
ApplyTo	String	False	Apply to value for the element
AttributeCode [KEY]	String	False	Code of the attribute.
AttributeId	Int	False	Id of the attribute.
BackendModel	String	False	Backend model
BackendType	String	False	Backend type.
DefaultFrontendLabel	String	False	Frontend label for default store
DefaultValue	String	False	Default value for the element.
EntityTypeId	String	False	Entity type id
ExtensionAttributes	String	False	This is an auto-generated column
FrontendClass	String	False	Frontend class of attribute
FrontendInput	String	False	HTML for input element.
FrontendLabels	String	False	Frontend label for each store
IsComparable	String	False	The attribute can be compared on the frontend
IsFilterable	Bool	False	It used in layered navigation
IsFilterableInGrid	Bool	False	It is filterable in catalog product grid
IsFilterableInSearch	Bool	False	It is used in search results layered navigation
IsHtmlAllowedOnFront	Bool	False	The HTML tags are allowed on the frontend
IsRequired	Bool	False	Attribute is required.
IsSearchable	String	False	The attribute can be used in Quick Search
IsUnique	String	False	This is a unique attribute
IsUsedForPromoRules	String	False	The attribute can be used for promo rules
IsUsedInGrid	Bool	False	It is used in catalog product grid
IsUserDefined	Bool	False	Current attribute has been defined by a user.
IsVisible	Bool	False	Attribute is visible on frontend.
IsVisibleInAdvancedSearch	String	False	The attribute can be used in Advanced Search
IsVisibleInGrid	Bool	False	It is visible in catalog product grid
IsVisibleOnFront	String	False	The attribute is visible on the frontend
IsWysiwygEnabled	Bool	False	WYSIWYG flag
Note	String	False	The note attribute for the element.
Options	String	False	Options of the attribute (key => value pairs for select)
Position	Int	False	Position
Scope	String	False	Attribute scope
SourceModel	String	False	Source model
UsedForSortBy	Bool	False	It is used for sorting in product listing
UsedInProductListing	String	False	The attribute can be used in product listing
ValidationRules	String	False	Validation rules.
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

CmsBlock is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	ID
BlockId	String	False	blockId
Active	Bool	False	Active
Content	String	False	Content
CreationTime	String	False	Creation time
Identifier	String	False	Identifier
Title	String	False	Title
UpdateTime	String	False	Update time
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

CmsPage is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	ID
PageId	String	False	pageId
Active	Bool	False	Active
Content	String	False	Content
ContentHeading	String	False	Content heading
CreationTime	String	False	Creation time
CustomLayoutUpdateXml	String	False	Custom layout update xml
CustomRootTemplate	String	False	Custom root template
CustomTheme	String	False	Custom theme
CustomThemeFrom	String	False	Custom theme from
CustomThemeTo	String	False	Custom theme to
Identifier	String	False	Identifier
LayoutUpdateXml	String	False	Layout update xml
MetaDescription	String	False	Meta description
MetaKeywords	String	False	Meta keywords
MetaTitle	String	False	Meta title
PageLayout	String	False	Page layout
SortOrder	String	False	Sort order
Title	String	False	Title
UpdateTime	String	False	Update time
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

ConfigurableProductOption is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Sku or Id while the rest of the filter is executed client side within the Sync App.

You must specify the Sku column or the Sku and Id columns in the WHERE clause to retrieve results from ConfigurableProductOption. Sku and Id can be used only with the equals (=) operator. For example:

SELECT * FROM [ConfigurableProductOption] WHERE Sku = '123' AND Id = '12'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	This is an auto-generated column
Sku	String	False	sku
AttributeId	String	False	This is an auto-generated column
ExtensionAttributes	String	False	This is an auto-generated column
IsUseDefault	Bool	False	This is an auto-generated column
Label	String	False	This is an auto-generated column
Position	Int	False	This is an auto-generated column
ProductId	Int	False	This is an auto-generated column
Values	String	False	This is an auto-generated column

CustomerAddress is an auto generated table. The supported operations are Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by AddressId while the rest of the filter is executed client side within the Sync App.

You must specify the AddressId column in the WHERE clause to retrieve results from CustomerAddress. AddressId can be used only with the equals (=) operator. For example:

SELECT * FROM [CustomerAddress] WHERE AddressId = '123'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	ID
AddressId	String	False	addressId
City	String	False	City name
Company	String	False	Company
CountryId	String	False	Country code in ISO_3166-2 format
CustomerId	Int	False	Customer ID
DefaultBilling	Bool	False	If this address is default billing address
DefaultShipping	Bool	False	If this address is default shipping address.
ExtensionAttributes	String	False	This is an auto-generated column
Fax	String	False	Fax number
Firstname	String	False	First name
Lastname	String	False	Last name
Middlename	String	False	Middle name
Postcode	String	False	Postcode
Prefix	String	False	Prefix
Region	String	False	This is an auto-generated column
RegionId	Int	False	Region ID
Street	String	False	Street
Suffix	String	False	Suffix
Telephone	String	False	Telephone number
VatId	String	False	Vat id

CustomerGroup is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Id
Code	String	False	Code
ExtensionAttributes	String	False	This is an auto-generated column
TaxClassId	Int	False	Tax class id
TaxClassName	String	False	Tax class name
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

Customers is an auto generated table. The supported operations are Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Customers table supports server-side processing for all columns in the WHERE clause. Most of these columns support server-side processing for the >, >=, <, <=, =, IN, NOT IN and LIKE operators.

The following examples show the types of queries processed server side.

• Retrieve all customers from your AdobeCommerce server.
  

  SELECT * FROM Customers

• Retrieve a single customer:
  

  SELECT * FROM Customers WHERE Id = '1'

• Filter by status:
  

  SELECT * FROM Customers WHERE GroupId = 1

• Filter by created date:
  

  SELECT * FROM Customers WHERE CreatedAt > '2017-08-01'

• Example query with multiple filters:
  

  SELECT * FROM Customers WHERE DefaultBilling IN (1, 2) AND Firstname LIKE '%i%' AND WebsiteId = 1 LIMIT 50

Insert

To add a Customer, the FirstName, LastName, and Email fields are required.

INSERT INTO Customers (FirstName, LastName, Email, WebsiteId, GroupId) VALUES ('John', 'Doe', '[email protected]', 1, 1)

Update

AdobeCommerce allows updates for the FirstName, LastName, MiddleName, Suffix, Email, GroupId, Prefix, DisableAutoGroupChange, and Taxvat columns. WebsiteId is a required value.

UPDATE Customers SET FirstName = 'Jane', Email = '[email protected]', GroupId = 2, DisableAutoGroupChange = '0', Prefix = 'x', Middlename = 'J', LastName = 'Doe', Suffix = 'y', Taxvat = 1, WebsiteId = 1 WHERE Id = 4

Delete

Customers can be deleted by providing the Id of the Customer and issuing a DELETE statement.

DELETE FROM Customers WHERE Id = 4

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Customer id
Addresses	String	False	Customer addresses.
Confirmation	String	False	Confirmation
CreatedAt	Datetime	False	Created at time
CreatedIn	String	False	Created in area
DefaultBilling	String	False	Default billing address id
DefaultShipping	String	False	Default shipping address id
DisableAutoGroupChange	Int	False	Disable auto group change flag.
Dob	String	False	Date of birth
Email	String	False	Email address
ExtensionAttributes	String	False	This is an auto-generated column
Firstname	String	False	First name
Gender	Int	False	Gender
GroupId	Int	False	Group id
Lastname	String	False	Last name
Middlename	String	False	Middle name
Prefix	String	False	Prefix
StoreId	Int	False	Store id
Suffix	String	False	Suffix
Taxvat	String	False	Tax Vat
UpdatedAt	Datetime	False	Updated at time
WebsiteId	Int	False	Website id
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

DownloadableLink is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Sku while the rest of the filter is executed client side within the Sync App.

You must specify the Sku column in the WHERE clause to retrieve results from DownloadableLink. Sku can be used only with the equals (=) operator. For example:

SELECT * FROM [DownloadableLink] WHERE Sku = '123'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Sample(or link) id
Sku	String	False	sku
ExtensionAttributes	String	False	This is an auto-generated column
IsShareable	Int	False	Shareable status
LinkFile	String	False	relative file path
LinkFileContent	String	False	This is an auto-generated column
LinkType	String	False	This is an auto-generated column
LinkUrl	String	False	Link url or null when type is 'file'
NumberOfDownloads	Int	False	Of downloads per user
Price	Double	False	Price
SampleFile	String	False	relative file path
SampleFileContent	String	False	This is an auto-generated column
SampleType	String	False	This is an auto-generated column
SampleUrl	String	False	file URL
SortOrder	Int	False	This is an auto-generated column
Title	String	False	This is an auto-generated column

DownloadableSample is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Sku while the rest of the filter is executed client side within the Sync App.

You must specify the Sku column in the WHERE clause to retrieve results from DownloadableSample. Sku can be used only with the equals (=) operator. For example:

SELECT * FROM [DownloadableSample] WHERE Sku = '123'

Insert

To create a new downloadable sample, the Sku, SampleType, SortOrder and Title columns are required.

INSERT INTO DownloadableSample (Sku, SampleType, SortOrder, Title) VALUES ('WJ02', 'crosssell', 12, 'simple')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Sample(or link) id
Sku	String	False	sku
ExtensionAttributes	String	False	This is an auto-generated column
SampleFile	String	False	relative file path
SampleFileContent	String	False	This is an auto-generated column
SampleType	String	False	This is an auto-generated column
SampleUrl	String	False	file URL
SortOrder	Int	False	Order index for sample
Title	String	False	Title

EavAttributeSet is an auto generated table. The supported operations are Update, Delete, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
AttributeSetId [KEY]	Int	False	Attribute set ID
AttributeSetName	String	False	Attribute set name
EntityTypeId	Int	False	Attribute set entity type id
ExtensionAttributes	String	False	This is an auto-generated column
SearchCriteria	String	False	This is an auto-generated column
SortOrder	Int	False	Attribute set sort order index
TotalCount	Int	False	Total count.

GiftMessageCart is an auto generated table. The supported operations are Insert, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by CartId while the rest of the filter is executed client side within the Sync App.

You must specify the CartId column in the WHERE clause to retrieve results from GiftMessageCart. CartId can be used only with the equals (=) operator. For example:

SELECT * FROM [GiftMessageCart] WHERE CartId = '123'

Insert

To create a new gift message, the CartId, Message, Recipient and Sender columns are required.

INSERT INTO GiftMessageCart (Message, Recipient, Sender, CartId) VALUES ('sample', 'recipient name', 'sender name', '12')

Columns

Name	Type	ReadOnly	Description
CustomerId	Int	False	Customer ID. Otherwise, null.
CartId	String	False	cartId
ExtensionAttributes	String	False	This is an auto-generated column
GiftMessageId [KEY]	Int	False	Gift message ID. Otherwise, null.
Message	String	False	Message text.
Recipient	String	False	Recipient name.
Sender	String	False	Sender name.

GiftMessageGuestCart is an auto generated table. The supported operations are Insert, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by CartId while the rest of the filter is executed client side within the Sync App.

You must specify the CartId column in the WHERE clause to retrieve results from GiftMessageGuestCart. CartId can be used only with the equals (=) operator. For example:

SELECT * FROM [GiftMessageGuestCart] WHERE CartId = '123'

Insert

To add a gift message, the CartId, Message, Recipient and Sender columns are required.

INSERT INTO GiftMessageGuestCart (Message, Recipient, Sender, CartId) VALUES ('sample', 'recipient name', 'sender name', '12')

Columns

Name	Type	ReadOnly	Description
CustomerId	Int	False	Customer ID. Otherwise, null.
CartId	String	False	cartId
ExtensionAttributes	String	False	This is an auto-generated column
GiftMessageId [KEY]	Int	False	Gift message ID. Otherwise, null.
Message	String	False	Message text.
Recipient	String	False	Recipient name.
Sender	String	False	Sender name.

GiftMessageGuestItem is an auto generated table. The supported operations are Insert, and Querying data from this table.

Table Specific Information

Select

You must specify the CartId and ItemId columns in the WHERE clause to retrieve results from GiftMessageGuestItem. CartId and ItemId can be used only with the equals (=) operator.

The Sync App uses the AdobeCommerce API to filter the results by CartId and ItemId while the rest of the filter is executed client side within the Sync App. For example:

SELECT * FROM [GiftMessageGuestItem] WHERE ItemId = '123' AND CartId = '234'

Insert

To create a new gift message for an item, the CartId, ItemId, Message, Recipient and Sender columns are required.

INSERT INTO GiftMessageGuestItem (Message, Recipient, Sender, CartId, ItemId) VALUES ('sample', 'recipient name', 'sender name', '12', '13')

Columns

Name	Type	ReadOnly	Description
CustomerId	Int	False	Customer ID. Otherwise, null.
CartId	String	False	cartId
ExtensionAttributes	String	False	This is an auto-generated column
GiftMessageId [KEY]	Int	False	Gift message ID. Otherwise, null.
Message	String	False	Message text.
Recipient	String	False	Recipient name.
Sender	String	False	Sender name.

GiftMessageItem is an auto generated table. The supported operations are Insert, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by CartId and ItemId while the rest of the filter is executed client side within the Sync App.

You must specify the CartId and ItemId column in the WHERE clause to retrieve results from GiftMessageItem. CartId and ItemId can be used only with the equals (=) operator. For example:

SELECT * FROM [GiftMessageItem] WHERE CartId = '123' AND ItemId = '234'

Insert

To create a new gift message for an item, the CartId, ItemId, Message, Recipient and Sender columns are required.

INSERT INTO GiftMessageItem (Message, Recipient, Sender, CartId, ItemId) VALUES ('sample', 'recipient name', 'sender name', '12', '13')

Columns

Name	Type	ReadOnly	Description
CustomerId	Int	False	Customer ID. Otherwise, null.
CartId	String	False	cartId
ExtensionAttributes	String	False	This is an auto-generated column
GiftMessageId [KEY]	Int	False	Gift message ID. Otherwise, null.
Message	String	False	Message text.
Recipient	String	False	Recipient name.
Sender	String	False	Sender name.

GuestBillingAddresses is an auto generated table. The supported operations are Insert, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by CartId while the rest of the filter is executed client side within the Sync App.

You must specify the CartId column must be specified in the WHERE clause to retrieve results from GuestBillingAddresses. CartId can be used only with the equals (=) operator. For example:

SELECT * FROM [GuestBillingAddresses] WHERE CartId = '123'

INSERT

To create a new GuestBillingAddresses, CartId, and FirstName are required.

INSERT INTO GuestBillingAddresses(CartId,FirstName) VALUES('a9f82365c3552','asdw')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Id
CartId	String	False	cartId
City	String	False	City name
Company	String	False	Company
CountryId	String	False	Country id
CustomerAddressId	Int	False	Customer address id
CustomerId	Int	False	Customer id
Email	String	False	Billing/shipping email
ExtensionAttributes	String	False	This is an auto-generated column
Fax	String	False	Fax number
Firstname	String	False	First name
Lastname	String	False	Last name
Middlename	String	False	Middle name
Postcode	String	False	Postcode
Prefix	String	False	Prefix
Region	String	False	Region name
RegionCode	String	False	Region code
RegionId	Int	False	Region id
SameAsBilling	Int	False	Same as billing flag
SaveInAddressBook	Int	False	Save in address book flag
Street	String	False	Street
Suffix	String	False	Suffix
Telephone	String	False	Telephone number
VatId	String	False	Vat id

Inventory is an auto generated table. The supported operations are Update, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by ProductSku while the rest of the filter is executed client side within the Sync App.

You must specify the ProductSKU column in the WHERE clause to retrieve results from Inventory. For example:

SELECT * FROM Inventory WHERE ProductSKU = 'sjl-8082'

Update

To update a product inventory, set the ItemId of an existing item and the ProductSKU.

UPDATE [Inventory] SET Qty = '87' WHERE ProductSKU = 'sjl-8082' AND ItemId = '1'

Columns

Name	Type	ReadOnly	Description
Items	String	False	Items
ProductSku [KEY]	String	False	This is an auto generated column.
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.
Backorders	Int	False	Backorders status
EnableQtyIncrements	Bool	False	Whether Quantity Increments is enabled
ExtensionAttributes	String	False	This is an auto-generated column
IsDecimalDivided	Bool	False	This is an auto-generated column
IsInStock	Bool	False	Stock Availability
IsQtyDecimal	Bool	False	This is an auto-generated column
ItemId [KEY]	Int	False	This is an auto-generated column
LowStockDate	String	False	This is an auto-generated column
ManageStock	Bool	False	Can Manage Stock
MaxSaleQty	Double	False	Maximum Qty Allowed in Shopping Cart data wrapper
MinQty	Double	False	Minimal quantity available for item status in stock
MinSaleQty	Double	False	Minimum Qty Allowed in Shopping Cart or NULL when there is no limitation
NotifyStockQty	Double	False	Notify for Quantity Below data wrapper
ProductId	Int	False	This is an auto-generated column
Qty	Double	False	This is an auto-generated column
QtyIncrements	Double	False	Quantity Increments data wrapper
ShowDefaultNotificationMessage	Bool	False	This is an auto-generated column
StockId	Int	False	Stock identifier
StockStatusChangedAuto	Int	False	This is an auto-generated column
UseConfigBackorders	Bool	False	This is an auto-generated column
UseConfigEnableQtyInc	Bool	False	This is an auto-generated column
UseConfigManageStock	Bool	False	This is an auto-generated column
UseConfigMaxSaleQty	Bool	False	This is an auto-generated column
UseConfigMinQty	Bool	False	This is an auto-generated column
UseConfigMinSaleQty	Int	False	This is an auto-generated column
UseConfigNotifyStockQty	Bool	False	This is an auto-generated column
UseConfigQtyIncrements	Bool	False	This is an auto-generated column
StockItem	String	False	This is an auto-generated column
StockStatus	Int	False	This is an auto-generated column

InventoryApiSource is an auto generated table

Columns

Name	Type	ReadOnly	Description
CarrierLinks	String	False	This is an auto-generated column
City	String	False	Source city
ContactName	String	False	Source contact name
CountryId	String	False	Source country id
Description	String	False	Source description
Email	String	False	Source email
Enabled	Bool	False	If source is enabled. For new entity can be null
ExtensionAttributes	String	False	This is an auto-generated column
Fax	String	False	Source fax
Latitude	Double	False	Source latitude
Longitude	Double	False	Source longitude
Name	String	False	Source name
Phone	String	False	Source phone number
Postcode	String	False	Source post code
Region	String	False	Region title if source has custom region
RegionId	Int	False	Region id if source has registered region.
SearchCriteria	String	False	This is an auto-generated column
SourceCode	String	False	Source code
Street	String	False	Source street name
TotalCount	Int	False	Total count.
UseDefaultCarrierConfig	Bool	False	Is need to use default config

InventoryApiStock is an auto generated table

Columns

Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
Name	String	False	Stock name
SearchCriteria	String	False	This is an auto-generated column
StockId	Int	False	Stock id
TotalCount	Int	False	Total count.

OrderComments is an auto generated table. The supported operations are Insert, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Id while the rest of the filter is executed client side within the Sync App.

You must specify the Id column in the WHERE clause to retrieve results from OrderComments. Id can be used only with the equals (=) operator. For example:

SELECT * FROM [OrderComments] WHERE Id = '123'

INSERT

To create a new OrderComment, Id, Comment and ParentId are required.

INSERT INTO OrderComments(Comment,Id,ParentId) VALUES('ABdCdwawD',2,2)

Columns

Name	Type	ReadOnly	Description
Comment	String	False	Comment.
Id	String	False	id
CreatedAt	Datetime	False	Created-at timestamp.
EntityId [KEY]	Int	False	Order status history ID.
EntityName	String	False	Entity name.
ExtensionAttributes	String	False	This is an auto-generated column
IsCustomerNotified	Int	False	Is-customer-notified flag value.
IsVisibleOnFront	Int	False	Is-visible-on-storefront flag value.
ParentId	Int	False	Parent ID.
SearchCriteria	String	False	This is an auto-generated column
Status	String	False	Status.
TotalCount	Int	False	Total count.

Orders is an auto generated table. The supported operations are Insert, Update, and Querying data from this table.

Select

The Orders table supports server-side processing for all columns in the WHERE clause. Most of these columns support server-side processing for the >, >=, <, <=, =, IN, NOT IN and LIKE operators.

The following examples show the types of queries processed server side.

• Retrieve all orders from your AdobeCommerce server:
  

  SELECT * FROM Orders

• Retrieve a single order:
  

  SELECT * FROM Orders WHERE EntityId = '1'

• Filter by status:
  

  SELECT * FROM Orders WHERE GroupId = 1

• Filter by created date:
  

  SELECT * FROM Orders WHERE CreatedAt > '2017-08-01'

• Example query with multiple filters:
  

  SELECT * FROM Orders WHERE EntityId IN (1, 2) AND CustomerFirstname LIKE '%i%' AND OrderCurrencyCode = 'USD' LIMIT 50

Insert

You can insert items into an order using an INSERT statement. The 'Items' column can be specified using either the temporary table called 'OrderItems#TEMP' or as a simple string aggregate. 'Payment' aggregate column is required for INSERT and UPDATE operations on this table.

• Using the temporary table:
  

  INSERT INTO OrderItems#TEMP (BasePrice, Sku) VALUES (299.99, '8915A40K4')
  INSERT INTO OrderItems#TEMP (BasePrice, Sku) VALUES (100, '7115AP0Z8')
  INSERT INTO Orders (GrandTotal, Items, Payment) VALUES (399.99, OrderItems#TEMP, '{...your payment aggregate here...}')

• Using a text aggregate:
  

  INSERT INTO Orders (GrandTotal, Items, Payment) VALUES (399.99, '{...Items aggregate here...}', '{...Payment aggregate here...}')

Columns

Name	Type	ReadOnly	Description
AdjustmentNegative	Double	False	Negative adjustment value.
Id	String	False	id
AdjustmentPositive	Double	False	Positive adjustment value.
AppliedRuleIds	String	False	Applied rule IDs.
BaseAdjustmentNegative	Double	False	Base negative adjustment value.
BaseAdjustmentPositive	Double	False	Base positive adjustment value.
BaseCurrencyCode	String	False	Base currency code.
BaseDiscountAmount	Double	False	Base discount amount.
BaseDiscountCanceled	Double	False	Base discount canceled.
BaseDiscountInvoiced	Double	False	Base discount invoiced.
BaseDiscountRefunded	Double	False	Base discount refunded.
BaseDiscountTaxCompensationAmount	Double	False	Base discount tax compensation amount.
BaseDiscountTaxCompensationInvoiced	Double	False	Base discount tax compensation invoiced.
BaseDiscountTaxCompensationRefunded	Double	False	Base discount tax compensation refunded.
BaseGrandTotal	Double	False	Base grand total.
BaseShippingAmount	Double	False	Base shipping amount.
BaseShippingCanceled	Double	False	Base shipping canceled.
BaseShippingDiscountAmount	Double	False	Base shipping discount amount.
BaseShippingDiscountTaxCompensationAmnt	Double	False	Base shipping discount tax compensation amount.
BaseShippingInclTax	Double	False	Base shipping including tax.
BaseShippingInvoiced	Double	False	Base shipping invoiced.
BaseShippingRefunded	Double	False	Base shipping refunded.
BaseShippingTaxAmount	Double	False	Base shipping tax amount.
BaseShippingTaxRefunded	Double	False	Base shipping tax refunded.
BaseSubtotal	Double	False	Base subtotal.
BaseSubtotalCanceled	Double	False	Base subtotal canceled.
BaseSubtotalInclTax	Double	False	Base subtotal including tax.
BaseSubtotalInvoiced	Double	False	Base subtotal invoiced.
BaseSubtotalRefunded	Double	False	Base subtotal refunded.
BaseTaxAmount	Double	False	Base tax amount.
BaseTaxCanceled	Double	False	Base tax canceled.
BaseTaxInvoiced	Double	False	Base tax invoiced.
BaseTaxRefunded	Double	False	Base tax refunded.
BaseToGlobalRate	Double	False	Base-to-global rate.
BaseToOrderRate	Double	False	Base-to-order rate.
BaseTotalCanceled	Double	False	Base total canceled.
BaseTotalDue	Double	False	Base total due.
BaseTotalInvoiced	Double	False	Base total invoiced.
BaseTotalInvoicedCost	Double	False	Base total invoiced cost.
BaseTotalOfflineRefunded	Double	False	Base total offline refunded.
BaseTotalOnlineRefunded	Double	False	Base total online refunded.
BaseTotalPaid	Double	False	Base total paid.
BaseTotalQtyOrdered	Double	False	Base total quantity ordered.
BaseTotalRefunded	Double	False	Base total refunded.
BillingAddress	String	False	This is an auto-generated column
BillingAddressId	Int	False	Billing address ID.
CanShipPartially	Int	False	Can-ship-partially flag value.
CanShipPartiallyItem	Int	False	Can-ship-partially-item flag value.
CouponCode	String	False	Coupon code.
CreatedAt	Datetime	False	Created-at timestamp.
CustomerDob	String	False	Customer date-of-birth (DOB).
CustomerEmail	String	False	Customer email address.
CustomerFirstname	String	False	Customer first name.
CustomerGender	Int	False	Customer gender.
CustomerGroupId	Int	False	Customer group ID.
CustomerId	Int	False	Customer ID.
CustomerIsGuest	Int	False	Customer-is-guest flag value.
CustomerLastname	String	False	Customer last name.
CustomerMiddlename	String	False	Customer middle name.
CustomerNote	String	False	Customer note.
CustomerNoteNotify	Int	False	Customer-note-notify flag value.
CustomerPrefix	String	False	Customer prefix.
CustomerSuffix	String	False	Customer suffix.
CustomerTaxvat	String	False	Customer value-added tax (VAT).
DiscountAmount	Double	False	Discount amount.
DiscountCanceled	Double	False	Discount canceled.
DiscountDescription	String	False	Discount description.
DiscountInvoiced	Double	False	Discount invoiced.
DiscountRefunded	Double	False	Discount refunded amount.
DiscountTaxCompensationAmount	Double	False	Discount tax compensation amount.
DiscountTaxCompensationInvoiced	Double	False	Discount tax compensation invoiced amount.
DiscountTaxCompensationRefunded	Double	False	Discount tax compensation refunded amount.
EditIncrement	Int	False	Edit increment value.
EmailSent	Int	False	Email-sent flag value.
EntityId [KEY]	Int	False	Order ID.
ExtCustomerId	String	False	External customer ID.
ExtensionAttributes	String	False	This is an auto-generated column
ExtOrderId	String	False	External order ID.
ForcedShipmentWithInvoice	Int	False	Forced-shipment-with-invoice flag value.
GlobalCurrencyCode	String	False	Global currency code.
GrandTotal	Double	False	Grand total.
HoldBeforeState	String	False	Hold before state.
HoldBeforeStatus	String	False	Hold before status.
IncrementId	String	False	Increment ID.
IsVirtual	Int	False	Is-virtual flag value.
Items	String	False	Array of items.
OrderCurrencyCode	String	False	Order currency code.
OriginalIncrementId	String	False	Original increment ID.
Payment	String	False	This is an auto-generated column
PaymentAuthExpiration	Int	False	Payment authorization expiration date.
PaymentAuthorizationAmount	Double	False	Payment authorization amount.
ProtectCode	String	False	Protect code.
QuoteAddressId	Int	False	Quote address ID.
QuoteId	Int	False	Quote ID.
RelationChildId	String	False	Relation child ID.
RelationChildRealId	String	False	Relation child real ID.
RelationParentId	String	False	Relation parent ID.
RelationParentRealId	String	False	Relation parent real ID.
RemoteIp	String	False	Remote IP address.
SearchCriteria	String	False	This is an auto-generated column
ShippingAmount	Double	False	Shipping amount.
ShippingCanceled	Double	False	Shipping canceled amount.
ShippingDescription	String	False	Shipping description.
ShippingDiscountAmount	Double	False	Shipping discount amount.
ShippingDiscountTaxCompensationAmount	Double	False	Shipping discount tax compensation amount.
ShippingInclTax	Double	False	Shipping including tax amount.
ShippingInvoiced	Double	False	Shipping invoiced amount.
ShippingRefunded	Double	False	Shipping refunded amount.
ShippingTaxAmount	Double	False	Shipping tax amount.
ShippingTaxRefunded	Double	False	Shipping tax refunded amount.
State	String	False	State.
Status	String	False	Status.
StatusHistories	String	False	Array of status histories.
StoreCurrencyCode	String	False	Store currency code.
StoreId	Int	False	Store ID.
StoreName	String	False	Store name.
StoreToBaseRate	Double	False	Store-to-base rate.
StoreToOrderRate	Double	False	Store-to-order rate.
Subtotal	Double	False	Subtotal.
SubtotalCanceled	Double	False	Subtotal canceled amount.
SubtotalInclTax	Double	False	Subtotal including tax amount.
SubtotalInvoiced	Double	False	Subtotal invoiced amount.
SubtotalRefunded	Double	False	Subtotal refunded amount.
TaxAmount	Double	False	Tax amount.
TaxCanceled	Double	False	Tax canceled amount.
TaxInvoiced	Double	False	Tax invoiced amount.
TaxRefunded	Double	False	Tax refunded amount.
TotalCanceled	Double	False	Total canceled.
TotalCount	Int	False	Total count.
TotalDue	Double	False	Total due.
TotalInvoiced	Double	False	Total invoiced amount.
TotalItemCount	Int	False	Total item count.
TotalOfflineRefunded	Double	False	Total offline refunded amount.
TotalOnlineRefunded	Double	False	Total online refunded amount.
TotalPaid	Double	False	Total paid.
TotalQtyOrdered	Double	False	Total quantity ordered.
TotalRefunded	Double	False	Total amount refunded.
UpdatedAt	Datetime	False	Updated-at timestamp.
Weight	Double	False	Weight.
XForwardedFor	String	False	X-Forwarded-For field value.

ProductAttributeGroup is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by AttributeSetId while the rest of the filter is executed client side within the Sync App.

The AttributeSetId column must be specified in the WHERE clause to retrieve results from ProductAttributeGroup. AttributeSetId can be used only with the equals (=) operator. For example:

SELECT * FROM [ProductAttributeGroup] WHERE AttributeSetId = '123'

Columns

Name	Type	ReadOnly	Description
AttributeGroupId [KEY]	String	False	Id
AttributeGroupName	String	False	Name
AttributeSetId	Int	False	Attribute set id
ExtensionAttributes	String	False	This is an auto-generated column
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

ProductAttributeOptions is an auto generated table. The supported operations are Insert, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by AttributeCode while the rest of the filter is executed client side within the Sync App.

The AttributeCode column must be specified in the WHERE clause to retrieve results from ProductAttributeOptions. AttributeCode can be used only with the equals (=) operator. For example:

SELECT * FROM [ProductAttributeOptions] WHERE AttributeCode = '123'

INSERT

To create a new ProductAttributeOption, AttributeCode, and Label are required.

INSERT INTO ProductAttributeOptions(AttributeCode, Label) VALUES(83,'asdw')

Columns

Name	Type	ReadOnly	Description
IsDefault	Bool	False	Default
AttributeCode	String	False	attributeCode
Label [KEY]	String	False	Option label
SortOrder	Int	False	Option order
StoreLabels	String	False	Option label for store scopes
Value	String	False	Option value

ProductCategories is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Id while the rest of the filter is executed client side within the Sync App.

The Id column can be used to filter data within this table. The Id can be used only with the equals (=) operator.

The following examples show the types of queries processed server side.

• Retrieve all product categories:
  

  SELECT * FROM ProductCategories

• Retrieve detailed information about a single product category:
  

  SELECT * FROM ProductCategories WHERE Id = 1

Insert

The Name and IsActive columns are required to create a new category.

INSERT INTO ProductCategories (Name, IsActive, Position) VALUES ('New category', '1', '0')

Update

To update a category, you must set the Id column.

UPDATE ProductCategories SET Name = 'New category', IsActive = 1 WHERE Id = 1

Delete

Product categories can be deleted by providing the Id column.

DELETE FROM ProductCategories WHERE Id = 1

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	This is an auto-generated column
CategoryId	String	False	categoryId
AvailableSortBy	String	False	This is an auto-generated column
Children	String	False	This is an auto-generated column
CreatedAt	Datetime	False	This is an auto-generated column
ExtensionAttributes	String	False	This is an auto-generated column
IncludeInMenu	Bool	False	This is an auto-generated column
IsActive	Bool	False	Whether category is active
Level	Int	False	Category level
Name	String	False	Category name
ParentId	Int	False	Parent category ID
Path	String	False	This is an auto-generated column
Position	Int	False	Category position
UpdatedAt	Datetime	False	This is an auto-generated column
ProductCount	Int	False	Product count

ProductCustomOption is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Sku or OptionId while the rest of the filter is executed client side within the Sync App.

The Sku column or Sku and OptionId columns must be specified in the WHERE clause to retrieve results from ProductCustomOption. Sku and OptionId can be used only with the equals (=) operator. For example:

SELECT * FROM [ProductCustomOption] WHERE Sku = '334' AND OptionId = '123'

Insert

To create a new product custom option, the IsRequire, ProductSku, SortOrder, Title and Type columns are required.

INSERT INTO ProductCustomOption (IsRequire, ProductSku, SortOrder, Title, Type) VALUES (false, '01', 1, 'Title', 'Type')

Delete

Product custom options can be deleted by providing the Sku and OptionId columns.

DELETE FROM ProductCustomOption WHERE Sku = 'sjl-1999' AND OptionId = '1'

Columns

Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
FileExtension	String	False	This is an auto-generated column
ImageSizeX	Int	False	This is an auto-generated column
ImageSizeY	Int	False	This is an auto-generated column
IsRequire	Bool	False	Is require
MaxCharacters	Int	False	This is an auto-generated column
OptionId [KEY]	Int	False	Option id
Price	Double	False	Price
PriceType	String	False	Price type
ProductSku	String	False	Product SKU
Sku	String	False	Sku
SortOrder	Int	False	Sort order
Title	String	False	Option title
Type	String	False	Option type
Values	String	False	This is an auto-generated column

ProductMedia is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Sku while the rest of the filter is executed client side within the Sync App.

The Sku column must be specified in the WHERE clause to retrieve results from ProductMedia. Sku can be used only with the equals (=) operator. For example:

SELECT * FROM [ProductMedia] WHERE Sku = 'sjl-8082'

Update

To update a ProductMedia record, you must set Sku and EntryId (refers to the Id column).

UPDATE ProductMedia SET Label = 'lbl new' WHERE EntryId = '1' AND Sku = 'sjl-8082'

Delete

Product media can be deleted by providing Sku and EntryId (refers to the Id column).

DELETE FROM ProductMedia WHERE EntryId = 1 AND Sky = 'sjl-8082'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Gallery entry ID
Sku	String	False	sku
Content	String	False	This is an auto-generated column
Disabled	Bool	False	If gallery entry is hidden from product page
ExtensionAttributes	String	False	This is an auto-generated column
File	String	False	File path
Label	String	False	Gallery entry alternative text
MediaType	String	False	Media type
Position	Int	False	Gallery entry position (sort order)
Types	String	False	Gallery entry image types (thumbnail, image, small_image etc)

Products is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results with these operators while the rest of the filter is executed client side within the provider.

Custom Attributes will be dynamically retrieved as columns when querying this table. The Products table supports server-side processing for all columns in the WHERE clause. Most of these columns support server-side processing for the >, >=, <, <=, =, IN, NOT IN and LIKE operators.

The following examples show the types of queries processed server side.

• Retrieve all products from your AdobeCommerce server:
  

  SELECT * FROM Products

• Retrieve a single product:
  

  SELECT * FROM Products WHERE Sku = 'sjl-8082'

• Filter by status:
  

  SELECT * FROM Products WHERE Status = 1

• Filter by created date:
  

  SELECT * FROM Products WHERE CreatedAt > '2017-08-01'

• Example query with multiple filters:
  

  SELECT * FROM Products WHERE Id IN (250, 260) AND Name LIKE '%d%' AND Visibility = 4 LIMIT 50

INSERT

To create a new product, Name, AttributeSetId, and Sku are required.

INSERT INTO Products (Sku, AttributeSetId, Name) VALUES ('sjl-1999', '4', 'insert prod')

Update

To update a product, you must set the Sku and Price columns.

UPDATE Products SET Price = '88', CustomAttribute1 = 'ValidValue' WHERE Sku = 'sjl-1999'

Delete

Products can be deleted by providing the Sku column.

DELETE FROM Products WHERE Sku = 'sjl-1999'

Columns

Name	Type	ReadOnly	Description
Id	Int	False	Id
AttributeSetId	Int	False	Attribute set id
CreatedAt	Datetime	False	Created date
ExtensionAttributes	String	False	This is an auto-generated column
MediaGalleryEntries	String	False	Media gallery entries
Name	String	False	Name
Options	String	False	List of product options
Price	Double	False	Price
ProductLinks	String	False	Product links info
Sku [KEY]	String	False	Sku
Status	Int	False	Status
TierPrices	String	False	List of product tier prices
TypeId	String	False	Type id
UpdatedAt	Datetime	False	Updated date
Visibility	Int	False	Visibility
Weight	Double	False	Weight
description	String	False	Custom Attribute field
short_description	String	False	Custom Attribute field
special_price	Decimal	False	Custom Attribute field
special_from_date	Datetime	False	Custom Attribute field
special_to_date	Datetime	False	Custom Attribute field
cost	Decimal	False	Custom Attribute field
manufacturer	Int	False	Custom Attribute field
meta_title	String	False	Custom Attribute field
meta_keyword	String	False	Custom Attribute field
meta_description	String	False	Custom Attribute field
image	String	False	Custom Attribute field
small_image	String	False	Custom Attribute field
thumbnail	String	False	Custom Attribute field
media_gallery	String	False	Custom Attribute field
old_id	Int	False	Custom Attribute field
tier_price	Decimal	False	Custom Attribute field
color	Int	False	Custom Attribute field
news_from_date	Datetime	False	Custom Attribute field
news_to_date	Datetime	False	Custom Attribute field
gallery	String	False	Custom Attribute field
minimal_price	Decimal	False	Custom Attribute field
custom_design	String	False	Custom Attribute field
custom_design_from	Datetime	False	Custom Attribute field
custom_design_to	Datetime	False	Custom Attribute field
custom_layout_update	String	False	Custom Attribute field
page_layout	String	False	Custom Attribute field
category_ids	String	False	Custom Attribute field
options_container	String	False	Custom Attribute field
required_options	String	False	Custom Attribute field
has_options	String	False	Custom Attribute field
image_label	String	False	Custom Attribute field
small_image_label	String	False	Custom Attribute field
thumbnail_label	String	False	Custom Attribute field
created_at	String	False	Custom Attribute field
updated_at	String	False	Custom Attribute field
country_of_manufacture	String	False	Custom Attribute field
quantity_and_stock_status	Int	False	Custom Attribute field
custom_layout	String	False	Custom Attribute field
url_key	String	False	Custom Attribute field
url_path	String	False	Custom Attribute field
msrp	Decimal	False	Custom Attribute field
msrp_display_actual_price_type	String	False	Custom Attribute field
price_type	Int	False	Custom Attribute field
sku_type	Int	False	Custom Attribute field
weight_type	Int	False	Custom Attribute field
price_view	Int	False	Custom Attribute field
shipment_type	Int	False	Custom Attribute field
links_purchased_separately	Int	False	Custom Attribute field
samples_title	String	False	Custom Attribute field
links_title	String	False	Custom Attribute field
links_exist	Int	False	Custom Attribute field
swatch_image	String	False	Custom Attribute field
tax_class_id	Int	False	Custom Attribute field
gift_message_available	String	False	Custom Attribute field
ryanattribute	String	False	Custom Attribute field
ryan_attribute	String	False	Custom Attribute field
deti_attr	String	False	Custom Attribute field
deti_attribut	String	False	Custom Attribute field
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

QuoteCart is an auto generated table. The supported operations are Update, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Cart/quote ID.
CartId	String	False	cartId
BillingAddress	String	False	This is an auto-generated column
ConvertedAt	String	False	Cart conversion date and time. Otherwise, null.
CreatedAt	Datetime	False	Cart creation date and time. Otherwise, null.
Currency	String	False	This is an auto-generated column
Customer	String	False	This is an auto-generated column
CustomerIsGuest	Bool	False	For guest customers, false for logged in customers
CustomerNote	String	False	Notice text
CustomerNoteNotify	Bool	False	Customer notification flag
CustomerTaxClassId	Int	False	Customer tax class ID.
ExtensionAttributes	String	False	This is an auto-generated column
IsActive	Bool	False	Active status flag value. Otherwise, null.
IsVirtual	Bool	False	Virtual flag value. Otherwise, null.
Items	String	False	Array of items. Otherwise, null.
ItemsCount	Int	False	Number of different items or products in the cart. Otherwise, null.
ItemsQty	Double	False	Total quantity of all cart items. Otherwise, null.
OrigOrderId	Int	False	Original order ID. Otherwise, null.
ReservedOrderId	Int	False	Reserved order ID. Otherwise, null.
SearchCriteria	String	False	This is an auto-generated column
StoreId	Int	False	Store identifier
TotalCount	Int	False	Total count.
UpdatedAt	Datetime	False	Cart last update date and time. Otherwise, null.

QuoteCartItem is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by CartId while the rest of the filter is executed client side within the Sync App.

The CartId column must be specified in the WHERE clause to retrieve results from QuoteCartItem. CartId can be used only with the equals (=) operator. For example:

SELECT * FROM [QuoteCartItem] WHERE CartId = '123'

Columns

Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
CartId	String	False	cartId
ItemId	Int	False	Item ID. Otherwise, null.
Name	String	False	Product name. Otherwise, null.
Price	Double	False	Product price. Otherwise, null.
ProductOption	String	False	This is an auto-generated column
ProductType	String	False	Product type. Otherwise, null.
Qty	Double	False	Product quantity.
QuoteId [KEY]	String	False	Quote id.
Sku	String	False	Product SKU. Otherwise, null.

QuoteGuestCartItem is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by CartId while the rest of the filter is executed client side within the Sync App.

The CartId column must be specified in the WHERE clause to retrieve results from QuoteGuestCartItem. CartId can be used only with the equals (=) operator. For example:

SELECT * FROM [QuoteGuestCartItem] WHERE CartId = '123'

Columns

Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
CartId	String	False	cartId
ItemId	Int	False	Item ID. Otherwise, null.
Name	String	False	Product name. Otherwise, null.
Price	Double	False	Product price. Otherwise, null.
ProductOption	String	False	This is an auto-generated column
ProductType	String	False	Product type. Otherwise, null.
Qty	Double	False	Product quantity.
QuoteId [KEY]	String	False	Quote id.
Sku	String	False	Product SKU. Otherwise, null.

SalesCreditmemo is an auto generated table. The supported operations are Insert, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
Adjustment	Double	False	Credit memo adjustment.
Id	String	False	id
AdjustmentNegative	Double	False	Credit memo negative adjustment.
AdjustmentPositive	Double	False	Credit memo positive adjustment.
BaseAdjustment	Double	False	Credit memo base adjustment.
BaseAdjustmentNegative	Double	False	Credit memo negative base adjustment.
BaseAdjustmentPositive	Double	False	Credit memo positive base adjustment.
BaseCurrencyCode	String	False	Credit memo base currency code.
BaseDiscountAmount	Double	False	Credit memo base discount amount.
BaseDiscountTaxCompensationAmount	Double	False	Credit memo base discount tax compensation amount.
BaseGrandTotal	Double	False	Credit memo base grand total.
BaseShippingAmount	Double	False	Credit memo base shipping amount.
BaseShippingDiscountTaxCompensationAmnt	Double	False	Credit memo base shipping discount tax compensation amount.
BaseShippingInclTax	Double	False	Credit memo base shipping including tax.
BaseShippingTaxAmount	Double	False	Credit memo base shipping tax amount.
BaseSubtotal	Double	False	Credit memo base subtotal.
BaseSubtotalInclTax	Double	False	Credit memo base subtotal including tax.
BaseTaxAmount	Double	False	Credit memo base tax amount.
BaseToGlobalRate	Double	False	Credit memo base-to-global rate.
BaseToOrderRate	Double	False	Credit memo base-to-order rate.
BillingAddressId	Int	False	Credit memo billing address ID.
Comments	String	False	Array of any credit memo comments. Otherwise, null.
CreatedAt	Datetime	False	Credit memo created-at timestamp.
CreditmemoStatus	Int	False	Credit memo status.
DiscountAmount	Double	False	Credit memo discount amount.
DiscountDescription	String	False	Credit memo discount description.
DiscountTaxCompensationAmount	Double	False	Credit memo discount tax compensation amount.
EmailSent	Int	False	Credit memo email sent flag value.
EntityId [KEY]	Int	False	Credit memo ID.
ExtensionAttributes	String	False	This is an auto-generated column
GlobalCurrencyCode	String	False	Credit memo global currency code.
GrandTotal	Double	False	Credit memo grand total.
IncrementId	String	False	Credit memo increment ID.
InvoiceId	Int	False	Credit memo invoice ID.
Items	String	False	Array of credit memo items.
OrderCurrencyCode	String	False	Credit memo order currency code.
OrderId	Int	False	Credit memo order ID.
ShippingAddressId	Int	False	Credit memo shipping address ID.
ShippingAmount	Double	False	Credit memo shipping amount.
ShippingDiscountTaxCompensationAmount	Double	False	Credit memo shipping discount tax compensation amount.
ShippingInclTax	Double	False	Credit memo shipping including tax.
ShippingTaxAmount	Double	False	Credit memo shipping tax amount.
State	Int	False	Credit memo state.
StoreCurrencyCode	String	False	Credit memo store currency code.
StoreId	Int	False	Credit memo store ID.
StoreToBaseRate	Double	False	Credit memo store-to-base rate.
StoreToOrderRate	Double	False	Credit memo store-to-order rate.
Subtotal	Double	False	Credit memo subtotal.
SubtotalInclTax	Double	False	Credit memo subtotal including tax.
TaxAmount	Double	False	Credit memo tax amount.
TransactionId	String	False	Credit memo transaction ID.
UpdatedAt	Datetime	False	Credit memo updated-at timestamp.
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

SalesCreditmemoComments is an auto generated table. The supported operations are Insert, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Id while the rest of the filter is executed client side within the Sync App.

The Id column must be specified in the WHERE clause to retrieve results from SalesCreditmemoComments. Id can be used only with the equals (=) operator. For example:

SELECT * FROM [SalesCreditmemoComments] WHERE Id = '123'

INSERT

To create a new SalesCreditmemoComment, Id, Comment and ParentId are required.

INSERT INTO SalesCreditmemoComments(Comment,Id,ParentId) VALUES('ABdCdwawD',2,2)

Columns

Name	Type	ReadOnly	Description
Comment	String	False	Comment.
Id	String	False	id
CreatedAt	Datetime	False	Created-at timestamp.
EntityId [KEY]	Int	False	Credit memo ID.
ExtensionAttributes	String	False	This is an auto-generated column
IsCustomerNotified	Int	False	Is-customer-notified flag value.
IsVisibleOnFront	Int	False	Is-visible-on-storefront flag value.
ParentId	Int	False	Parent ID.
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

SalesInvoice is an auto generated table. The supported operations are Insert, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
BaseCurrencyCode	String	False	Base currency code.
Id	String	False	id
BaseDiscountAmount	Double	False	Base discount amount.
BaseDiscountTaxCompensationAmount	Double	False	Base discount tax compensation amount.
BaseGrandTotal	Double	False	Base grand total.
BaseShippingAmount	Double	False	Base shipping amount.
BaseShippingDiscountTaxCompensationAmnt	Double	False	Base shipping discount tax compensation amount.
BaseShippingInclTax	Double	False	Base shipping including tax.
BaseShippingTaxAmount	Double	False	Base shipping tax amount.
BaseSubtotal	Double	False	Base subtotal.
BaseSubtotalInclTax	Double	False	Base subtotal including tax.
BaseTaxAmount	Double	False	Base tax amount.
BaseToGlobalRate	Double	False	Base-to-global rate.
BaseToOrderRate	Double	False	Base-to-order rate.
BaseTotalRefunded	Double	False	Base total refunded.
BillingAddressId	Int	False	Billing address ID.
CanVoidFlag	Int	False	Can void flag value.
Comments	String	False	Array of any invoice comments. Otherwise, null.
CreatedAt	Datetime	False	Created-at timestamp.
DiscountAmount	Double	False	Discount amount.
DiscountDescription	String	False	Discount description.
DiscountTaxCompensationAmount	Double	False	Discount tax compensation amount.
EmailSent	Int	False	Email-sent flag value.
EntityId [KEY]	Int	False	Invoice ID.
ExtensionAttributes	String	False	This is an auto-generated column
GlobalCurrencyCode	String	False	Global currency code.
GrandTotal	Double	False	Grand total.
IncrementId	String	False	Increment ID.
IsUsedForRefund	Int	False	Is-used-for-refund flag value.
Items	String	False	Array of invoice items.
OrderCurrencyCode	String	False	Order currency code.
OrderId	Int	False	Order ID.
SearchCriteria	String	False	This is an auto-generated column
ShippingAddressId	Int	False	Shipping address ID.
ShippingAmount	Double	False	Shipping amount.
ShippingDiscountTaxCompensationAmount	Double	False	Shipping discount tax compensation amount.
ShippingInclTax	Double	False	Shipping including tax.
ShippingTaxAmount	Double	False	Shipping tax amount.
State	Int	False	State.
StoreCurrencyCode	String	False	Store currency code.
StoreId	Int	False	Store ID.
StoreToBaseRate	Double	False	Store-to-base rate.
StoreToOrderRate	Double	False	Store-to-order rate.
Subtotal	Double	False	Subtotal.
SubtotalInclTax	Double	False	Subtotal including tax.
TaxAmount	Double	False	Tax amount.
TotalCount	Int	False	Total count.
TotalQty	Double	False	Total quantity.
TransactionId	String	False	Transaction ID.
UpdatedAt	Datetime	False	Updated-at timestamp.

SalesInvoiceComments is an auto generated table. The supported operations are Insert, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Id while the rest of the filter is executed client side within the Sync App.

The Id column must be specified in the WHERE clause to retrieve results from SalesInvoiceComments. Id can be used only with the equals (=) operator. For example:

SELECT * FROM [SalesInvoiceComments] WHERE Id = '123'

INSERT

To create a new SalesInvoiceComments, Comment and ParentId are required.

INSERT INTO SalesInvoiceComments(Comment,ParentId) VALUES('ABdsCwD',1)

Columns

Name	Type	ReadOnly	Description
Comment	String	False	Comment.
Id	String	False	id
CreatedAt	Datetime	False	Created-at timestamp.
EntityId [KEY]	Int	False	Invoice ID.
ExtensionAttributes	String	False	This is an auto-generated column
IsCustomerNotified	Int	False	Is-customer-notified flag value.
IsVisibleOnFront	Int	False	Is-visible-on-storefront flag value.
ParentId	Int	False	Parent ID.
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

SalesRule is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
ActionCondition	String	False	This is an auto-generated column
ApplyToShipping	Bool	False	The rule applies to shipping
Condition	String	False	This is an auto-generated column
CouponType	String	False	Coupon type
CustomerGroupIds	String	False	Ids of customer groups that the rule applies to
Description	String	False	Description
DiscountAmount	Double	False	Discount amount
DiscountQty	Double	False	Maximum qty discount is applied
DiscountStep	Int	False	Discount step
ExtensionAttributes	String	False	This is an auto-generated column
FromDate	String	False	The start date when the coupon is active
IsActive	Bool	False	The coupon is active
IsAdvanced	Bool	False	Is this field needed
IsRss	Bool	False	Whether the rule is in RSS
Name	String	False	Rule name
ProductIds	String	False	Product ids
RuleId [KEY]	Int	False	Rule id
SimpleAction	String	False	Simple action of the rule
SimpleFreeShipping	String	False	To grant free shipping
SortOrder	Int	False	Sort order
StopRulesProcessing	Bool	False	To stop rule processing
StoreLabels	String	False	Display label
TimesUsed	Int	False	How many times the rule has been used
ToDate	String	False	The end date when the coupon is active
UseAutoGeneration	Bool	False	To auto generate coupon
UsesPerCoupon	Int	False	Limit of uses per coupon
UsesPerCustomer	Int	False	Number of uses per customer
WebsiteIds	String	False	A list of websites the rule applies to
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

SalesRuleCoupon is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
Code	String	False	Coupon code
CouponId [KEY]	Int	False	Coupon id
CreatedAt	Datetime	False	When the coupon is created
ExpirationDate	String	False	Expiration date
ExtensionAttributes	String	False	This is an auto-generated column
IsPrimary	Bool	False	The coupon is primary coupon for the rule that it's associated with
RuleId	Int	False	The id of the rule associated with the coupon
TimesUsed	Int	False	The number of times the coupon has been used
Type	Int	False	Of coupon
UsageLimit	Int	False	Usage limit
UsagePerCustomer	Int	False	Usage limit per customer
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

SalesShipment is an auto generated table. The supported operations are Insert, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
BillingAddressId	Int	False	Billing address ID.
Id	String	False	id
Comments	String	False	Array of comments.
CreatedAt	Datetime	False	Created-at timestamp.
CustomerId	Int	False	Customer ID.
EmailSent	Int	False	Email-sent flag value.
EntityId [KEY]	Int	False	Shipment ID.
ExtensionAttributes	String	False	This is an auto-generated column
IncrementId	String	False	Increment ID.
Items	String	False	Array of items.
OrderId	Int	False	Order ID.
Packages	String	False	Array of packages, if any. Otherwise, null.
ShipmentStatus	Int	False	Shipment status.
ShippingAddressId	Int	False	Shipping address ID.
ShippingLabel	String	False	Shipping label.
StoreId	Int	False	Store ID.
TotalQty	Double	False	Total quantity.
TotalWeight	Double	False	Total weight.
Tracks	String	False	Array of tracks.
UpdatedAt	Datetime	False	Updated-at timestamp.
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

SalesShipmentComments is an auto generated table. The supported operations are Insert, and Querying data from this table.

Table Specific Information

Select

The Sync App uses the AdobeCommerce API to filter the results by Id while the rest of the filter is executed client side within the Sync App.

The Id column must be specified in the WHERE clause to retrieve results from SalesShipmentComments. Id can be used only with the equals (=) operator. For example:

SELECT * FROM [SalesShipmentComments] WHERE Id = '123'

INSERT

To create a new SalesShipmentComments, Id, Comment and ParentId are required.

INSERT INTO SalesShipmentComments(Id,Comment,ParentId) VALUES(3,'ABCrwD',3)

Columns

Name	Type	ReadOnly	Description
Comment	String	False	Comment.
Id	String	False	id
CreatedAt	Datetime	False	Created-at timestamp.
EntityId [KEY]	Int	False	Invoice ID.
ExtensionAttributes	String	False	This is an auto-generated column
IsCustomerNotified	Int	False	Is-customer-notified flag value.
IsVisibleOnFront	Int	False	Is-visible-on-storefront flag value.
ParentId	Int	False	Parent ID.
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.

TaxClass is an auto generated table. The supported operations are Insert, Update, Delete, and Querying data from this table.

Columns

Name	Type	ReadOnly	Description
ClassId [KEY]	Int	False	Tax class ID.
TaxClassId	String	False	taxClassId
ClassName	String	False	Tax class name.
ClassType	String	False	Tax class type.
ExtensionAttributes	String	False	This is an auto-generated column
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.