This page provides a guide to Establishing a Connection to AdobeCommerce in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to AdobeCommerce

Establishing a Connection shows how to authenticate to AdobeCommerce and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from AdobeCommerce through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to AdobeCommerce by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

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.

Customizing the SSL Configuration

To enable TLS, set the following:

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

With this configuration, the Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud.

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

CData Cloud - Adobe Commerce 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 Cloud uses the AdobeCommerce API to filter the results by Sku or OptionId while the rest of the filter is executed client side within the Cloud.

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

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 Cloud uses the AdobeCommerce API to filter the results by Sku or Id while the rest of the filter is executed client side within the Cloud.

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

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

Table Specific Information

Select

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

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

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 Cloud uses the AdobeCommerce API to filter the results by Sku while the rest of the filter is executed client side within the Cloud.

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

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

Table Specific Information

Select

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

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

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 Cloud uses the AdobeCommerce API to filter the results by CartId while the rest of the filter is executed client side within the Cloud.

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

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

Table Specific Information

Select

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

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

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 Cloud uses the AdobeCommerce API to filter the results by CartId and ItemId while the rest of the filter is executed client side within the Cloud. 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 Cloud uses the AdobeCommerce API to filter the results by CartId and ItemId while the rest of the filter is executed client side within the Cloud.

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

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

Table Specific Information

Select

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

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

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

Table Specific Information

Select

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

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

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 Cloud uses the AdobeCommerce API to filter the results by Id while the rest of the filter is executed client side within the Cloud.

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

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 Cloud uses the AdobeCommerce API to filter the results by AttributeSetId while the rest of the filter is executed client side within the Cloud.

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 Cloud uses the AdobeCommerce API to filter the results by AttributeCode while the rest of the filter is executed client side within the Cloud.

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 Cloud uses the AdobeCommerce API to filter the results by Id while the rest of the filter is executed client side within the Cloud.

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 Cloud uses the AdobeCommerce API to filter the results by Sku or OptionId while the rest of the filter is executed client side within the Cloud.

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 Cloud uses the AdobeCommerce API to filter the results by Sku while the rest of the filter is executed client side within the Cloud.

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

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 Cloud uses the AdobeCommerce API to filter the results by CartId while the rest of the filter is executed client side within the Cloud.

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 Cloud uses the AdobeCommerce API to filter the results by CartId while the rest of the filter is executed client side within the Cloud.

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 Cloud uses the AdobeCommerce API to filter the results by Id while the rest of the filter is executed client side within the Cloud.

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 Cloud uses the AdobeCommerce API to filter the results by Id while the rest of the filter is executed client side within the Cloud.

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 Cloud uses the AdobeCommerce API to filter the results by Id while the rest of the filter is executed client side within the Cloud.

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