Adding 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

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 allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

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

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

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

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

AdobeStockAssetApiAsset is an auto generated table

Columns

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

Table Specific Information

Select

The Sync App will use 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 the Sku and OptionId columns must be specified 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

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

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

Columns

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

Columns

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

Columns

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

Columns

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

Columns

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 will use 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.

The Sku column or the Sku and Id columns must be specified 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

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

Table Specific Information

Select

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

The AddressId column must be specified 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

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

Columns

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

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 will use 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 DownloadableLink. Sku can be used only with the equals (=) operator. For example:

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

Columns

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 will use 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 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

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

Columns

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

Table Specific Information

Select

The Sync App will use 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 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

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

Table Specific Information

Select

The Sync App will use 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 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

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

Table Specific Information

Select

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

The Sync App will use 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

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

Table Specific Information

Select

The Sync App will use 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.

The CartId and ItemId column must be specified 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

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

Table Specific Information

Select

The Sync App will use 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 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

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

Table Specific Information

Select

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

The ProductSKU column must be specified in the WHERE clause to retrieve results from Inventory. For example:

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

Update

To update a product inventory, the ItemId of an existing item must be set along with the ProductSKU.

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

Columns

InventoryApiSource is an auto generated table

Columns

InventoryApiStock is an auto generated table

Columns

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

Table Specific Information

Select

The Sync App will use 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 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

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

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 will use 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

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 will use 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

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 will use 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

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 will use 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

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 will use 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

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 will use the AdobeCommerce API to filter the results with these operators while the rest of the filter is executed client side within the provider. Client-side execution can be turned off by setting SupportEnhancedSQL to false in which case any search criteria that invokes other operators will cause an error.

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

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

Columns

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 will use 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

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 will use 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

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

Columns

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

Table Specific Information

Select

The Sync App will use 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

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

Columns

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

Table Specific Information

Select

The Sync App will use 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

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

Columns

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

Columns

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

Columns

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

Table Specific Information

Select

The Sync App will use 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

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

Columns

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

Columns