Create a connection to Magento by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Magento icon is not available, click the Add More icon to download and install the Magento connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Connecting to Magento 2.x

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 Magento

Basic

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

Access Token

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

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

This section details a selection of advanced features of the Magento 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 Magento 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 Magento 2.x API. The Data Models described in this chapter provide examples of what your Magento environment might look like. The actual data model will be obtained dynamically based on your Magento account.

Key Features

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

The Sync App models the data in Magento into a list of tables that can be queried using standard SQL statements.

Generally, querying Magento tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Magento Connector for CData Sync Tables

BundleProductLinkChildren is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

BundleProductOption is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CatalogAttributeSet is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CatalogCategoryAttribute is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CatalogCategoryAttributeOptionOptions is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CatalogCategoryLinkProducts is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CatalogProductAttribute is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CatalogProductAttributeTypesList is an auto generated table

Columns

CatalogProductLinkTypeList is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CatalogProductTierPriceTiers is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CatalogProductTypeList is an auto generated table

Columns

CheckoutAgreementsCheckoutAgreements is an auto generated table

Columns

CmsBlock is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CmsPage is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ConfigurableProductLinkChildren is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ConfigurableProductOption is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CustomerAccountBillingAddress is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CustomerAccountShippingAddress is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CustomerAddress is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CustomerGroup is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CustomerGroupDefault is an auto generated table

Columns

Customers is an auto generated table

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

Magento 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

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

DirectoryCountryInformationAcquirer is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

DirectoryCurrencyInformationAcquirer is an auto generated table

Columns

DownloadableLink is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

DownloadableSample is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

EavAttributeSet is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GiftMessageCart is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GiftMessageGuestCart is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GiftMessageGuestItem is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GiftMessageItem is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GuestBillingAddresses is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GuestPaymentInformation is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GuestPaymentMethods is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GuestPaymentSelectedMethods is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GuestShippingMethods is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Inventory is an auto generated table

Select

You must specify the product SKU to query this table. The Sync App will use the Magento API to filter the results by ProductSku_p while the rest of the filter is executed client side within the Sync App.

For example, the following query is the only one supported server side:

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

Update

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

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

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

OrderComments is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

OrderItems is an auto generated table

Select

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

The following examples demonstrate the types of queries processed on the server.

• Retrieve information about all order items:
  

  SELECT * FROM OrderItems

• Filter by ItemId to get detailed information about an order item:
  

  SELECT * FROM OrderItems WHERE ItemId = '3'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Orders is an auto generated 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 Magento 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

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

PaymentMethods is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

PaymentSelectedMethods is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProductAttributeGroup is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProductAttributeOptions is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProductAttributes is an auto generated table

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProductCategories is an auto generated table