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

Connecting to WooCommerce

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

Accessing Data from CData Cloud Services

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

Connect to WooCommerce 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 WooCommerce

In order to connect to WooCommerce, you also must specify the service instance's URL:

• Url: The base URL of the WooCommerce instance to connect to. For example, http://localhost/woocommerce/.

Authenticating to WooCommerce

WooCommerce supports Basic authentication, "one-legged" OAuth1.0 authentication, and standard OAuth2.0 authentication.

Basic Authentication

Certain plugins or servers can interfere with the Authorization header if not configured properly. In those cases, users can employ Basic authentication to validate their credentials. In Basic authentication, the user provides the WooCommerce consumer_key as the user, and the WooCommerce consumer_secret as the password. These credentials are then sent as query parameters with each request.

To authenticate using Basic authentication, specify these properties:

• AuthScheme: Basic.
• ConsumerKey: The WooCommerce consumer key(User).
• ConsumerSecret: The WooCommerce consumer secret(Password).

"One-Legged" Authentication

One-legged (OAuth 1.0) authentication uses credentials generated from the WooCommerce setttings page. These are not the same as the credentials generated using the WooCommerce OAuth 2.0 plugin.

To authenticate using one-legged authentication, specify these properties:

• AuthScheme: OAuthOneLegged.
• ConsumerKey
• ConsumerSecret

OAuth 2.0 Authentication

To enable OAuth 2.0 authentication from all OAuth flows, you must install the WooCommerce OAuth2 Plugin, create a custom OAuth application, and set AuthScheme to OAuth.

The following subsections describe how to authenticate to WooCommerce from three common authentication flows. For information about how to create a custom OAuth application, see Creating a Custom OAuth Application. For a complete list of connection string properties available in WooCommerce, see Connection.

Automatic refresh of the OAuth access token:

To have the Cloud automatically refresh the OAuth access token:

1. Before connecting to data for the first time, set these connection properties:
   • InitiateOAuth: REFRESH.
   • OAuthClientId: The client Id in your application settings.
   • OAuthClientSecret: The client secret in your application settings.
   • OAuthAccessToken: The access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation: The path where you want the Cloud to save the OAuth values, which persist across connections.
2. On subsequent data connections, set:
   • InitiateOAuth
   • OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAuth access token is the OAuth refresh token.

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
2. Set these connection properties:
   • OAuthClientId: The Client Id in your application settings.
   • OAuthClientSecret: The Client Secret in your application settings.

3. Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Creating a Custom OAuth Application

Creating a custom OAuth 2.0 application for authenticating to WooCommerce entails installing the WooCommerce OAuth 2.0 plugin and adding a new OAuth client application via the WooCommerce admin panel.

Installing the Plugin

Before you can authenticate via OAuth 2.0, you must install the WooCommerce OAuth2 Plugin.

To install the plugin manually:

1. Copy the downloaded folder to the wp-content\plugins folder.
2. Activate the plugin through the admin interface's 'Plugins' menu.

Creating and Registering the Application

1. Log into your site through the admin panel.
2. Navigate to OAuth Server.
3. Select Add New Client.
4. Fill in all required fields. One of the fields requests that you specify a Callback url for the new client, where the user is redirected after granting access. However, WooCommerce does not perform any validation on the Callback URL, you can specify something like http://localhost:33333.
5. Click Create Client. The WooCommerce admin panel displays the following Oauth credentials:
   • Client Key: Specify this value when setting the OAuthClientId variable.
   • Client Secret: Specify this value when setting the OAuthClientSecret variable.

   Record these values for future use.

Customizing the SSL Configuration

By default, the Cloud 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.

Overview

This section shows the available API objects and provides more information on executing SQL to WooCommerce APIs.

Key Features

• The Cloud models WooCommerce entities like orders, customers, and products as relational tables and views, allowing you to write SQL to query WooCommerce data.
• Stored procedures allow you to execute operations to WooCommerce, including running system tools and updating the available locations for a given shipping zone.
• Live connectivity to these objects means any changes to your WooCommerce account are immediately reflected when using the Cloud.

Views

Pre-defined Tables and Views are available for read or write access to data from WooCommerce.

Stored Procedures

The Cloud allows you to run WooCommerce tools and update locations for a given shipping zone via Stored Procedures.

System Tables

System Tables contains information about the objects and resources belonging to your database.

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

CData Cloud - WooCommerce Tables

Name	Description
Attributes	Retrieve and modify product attributes.
Categories	Retrieve product categories.
Coupons	Retrieve and modify coupons.
Customers	Retrieve and modify customers.
OrderLineItems	Add, update and view the line items for an order.
OrderNotes	Retrieve and modify the notes for an order.
OrderRefunds	Retrieve and modify order refunds.
Orders	Retrieve and modify orders.
PaymentGateways	Retrieve and modify payment gateways.
ProductAttributeTerms	Retrieve the terms for an attribute.
ProductReviews	Retrieve and modify products reviews.
Products	Retrieve and modify products.
ProductShippingClasses	Retrieve and modify product shipping classes.
ProductTags	Retrieve and modify product tags.
ProductVariations	Retrieve and modify product variations.
SettingOptions	View and manage settings options.
ShippingZoneMethods	Retrieve and modify shipping zone methods.
ShippingZones	Retrieve and modify shipping zones.
TaxClasses	Retrieve and modify tax classes.
TaxRates	Retrieve and modify tax rates.
Webhooks	Retrieve and modify webhooks.

Retrieve and modify product attributes.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id. All other columns are processed client side.

SELECT * FROM Attributes WHERE Id = 4

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO Attributes (Name) VALUES ('attribute_name')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	False	Attribute ID.
Name	String	False	Attribute name.
Slug	String	False	An alphanumeric identifier for the resource unique to its type.
Type	String	False	Type of attribute. By default only select is supported.
OrderBy	String	False	Default sort order. Options: menu_order, name, name_num and id. Defaults to menu_order.
HasArchives	Boolean	False	Enable/Disable attribute archives. Defaults to false.

Retrieve product categories.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, Parent, Product. All other columns are processed client side.

SELECT * FROM Categories WHERE Id = 4
SELECT * FROM Categories WHERE Slug = 'criteria'
SELECT * FROM Categories WHERE Parent = 2

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO Categories (Name) VALUES ('category_name')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Category name.
Slug	String	False	An alphanumeric identifier for the resource unique to its type.
Parent	Long	False	The ID for the parent of the resource.
Description	String	False	HTML description of the resource.
Display	String	False	Category archive display type. Options: default, products, subcategories and both.
MenuOrder	Integer	False	Menu order, used to custom sort the resource.
Count	Integer	True	Number of published products for the resource.
ImageId	Long	False	Image ID.
ImageDateCreated	Datetime	True	The date the image was created, in the site's timezone.
ImageDateModified	Datetime	True	The date the image was last modified, in the site's timezone.
ImageSrc	String	False	Image URL.
ImageName	String	False	Image name.
ImageAlt	String	False	Image alternative text.

Retrieve and modify coupons.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Code. All other columns are processed client side.

SELECT * FROM Coupons WHERE Id = 4
SELECT * FROM Coupons WHERE Code = 'criteria'

Update

To perform an update or insert using any of the aggregate columns which contain simple primitive arrays, we can simply pass a JSON array string as the value:

UPDATE Coupons SET ProductIdsAggregate = '[14, 16, 29]' WHERE ID = 42

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE Coupons SET metadata = 'key1:val1, key2:val2' WHERE ID = 58

Insert

The following attribute is required when performing an insert: Code.

INSERT INTO Coupons (Code) VALUES ('coupon_code')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the object.
Code	String	False	Coupon code.
Amount	String	False	The amount of discount. Should always be numeric, even if setting a percentage.
DateCreated	Datetime	True	The date the coupon was created, in the site's timezone.
DateModified	Datetime	True	The date the coupon was last modified, in the site's timezone.
DiscountType	String	False	Determines the type of discount that will be applied. Options: percent, fixed_cart and fixed_product. Defaults to fixed_cart.
Description	String	False	Coupon description.
DateExpires	Datetime	False	The date the coupon expires, in the site's timezone.
UsageCount	Integer	True	Number of times the coupon has been used already.
IndividualUse	Boolean	False	If true, the coupon can only be used individually. Other applied coupons will be removed from the cart. Defaults to false.
ProductIdsAggregate	String	False	List of product IDs the coupon can be used on.
ExcludedProductIdsAggregate	String	False	List of product IDs the coupon cannot be used on.
UsageLimit	Integer	False	How many times the coupon can be used in total.
UsageLimitPerUser	Integer	False	How many times the coupon can be used per customer.
LimitUsageToXItems	Integer	False	Max number of items in the cart the coupon can be applied to.
FreeShipping	Boolean	False	If true and if the free shipping method requires a coupon, this coupon will enable free shipping. Defaults to false.
ProductCategoriesAggregate	String	False	List of category IDs the coupon applies to.
ExcludedProductCategoriesAggregate	String	False	List of category IDs the coupon does not apply to.
ExcludeSaleItems	Boolean	False	If true, this coupon will not be applied to items that have sale prices. Defaults to false.
MinimumAmount	String	False	Minimum order amount that needs to be in the cart before coupon applies.
MaximumAmount	String	False	Maximum order amount allowed when using the coupon.
EmailRestrictionsAggregate	String	False	List of email addresses that can use this coupon.
UsedByAggregate	String	True	List of user IDs (or guest email addresses) that have used the coupon.

Retrieve and modify customers.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Email, Role. All other columns are processed client side.

SELECT * FROM Customers WHERE Id = 4
SELECT * FROM Customers WHERE Email = '[email protected]'
SELECT * FROM Customers WHERE Username = 'user' AND IsPayingCustomer = true

Update

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE Customers SET metadata = 'key1:val1, key2:val2' WHERE ID = 58

Insert

The following attribute is required when performing an insert: Email.

INSERT INTO Customers (Email) VALUES ('[email protected]')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
DateCreated	Datetime	True	The date the customer was created, in the site's timezone.
DateModified	Datetime	True	The date the customer was last modified, in the site's timezone.
Email	String	False	The email address for the customer.
FirstName	String	False	Customer first name.
LastName	String	False	Customer last name.
Role	String	True	Customer role.
Username	String	False	Customer login name.
Password	String	False	Customer password. write-only
IsPayingCustomer	Bool	True	Is the customer a paying customer? read-only
AvatarUrl	String	True	Avatar URL.
MetaData	String	False	Meta data. See Customer - Meta data properties
BillingFirstName	String	False	First name.
BillingLastName	String	False	Last name.
BillingCompany	String	False	Company name.
BillingAddress1	String	False	Address line 1
BillingAddress2	String	False	Address line 2
BillingCity	String	False	City name.
BillingState	String	False	ISO code or name of the state, province or district.
BillingPostcode	String	False	Postal code.
BillingCountry	String	False	ISO code of the country.
BillingEmail	String	False	Email address.
BillingPhone	String	False	Phone number.
ShippingFirstName	String	False	First name.
ShippingLastName	String	False	Last name.
ShippingCompany	String	False	Company name.
ShippingAddress1	String	False	Address line 1
ShippingAddress2	String	False	Address line 2
ShippingCity	String	False	City name.
ShippingState	String	False	ISO code or name of the state, province or district.
ShippingPostcode	String	False	Postal code.
ShippingCountry	String	False	ISO code of the country.

Add, update and view the line items for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: TotalTax, OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Cloud will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderLineItems WHERE OrderId = 13
SELECT * FROM OrderLineItems WHERE TotalTax = 14.21

Insert

The following attribute is required when performing an insert: OrderId.

INSERT INTO OrderLineItems (Productid, Quantity, OrderId) VALUES (28, 9, 35)

Update

The following attribute is required when performing an update: OrderId.

UPDATE OrderLineItems SET Quantity = 12 WHERE Id = 4 AND OrderId = 35

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	False	Item ID.
Name	String	False	Product name.
ProductId	Long	False	Product ID.
VariationId	Long	False	Variation ID, if applicable.
Quantity	Integer	False	Quantity ordered.
TaxClass	String	False	Tax class of product.
Subtotal	String	False	Line subtotal (before discounts).
SubtotalTax	String	True	Line subtotal tax (before discounts).
Total	String	False	Line total (after discounts).
TotalTax	String	True	Line total tax (after discounts).
Taxes	String	True	Line taxes. See Order - Taxes properties read-only
MetaData	String	False	Meta data. See Order - Meta data properties
Sku	String	True	Product SKU.
Price	String	True	Product price.
OrderId	String	True	Id of the order.

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.

Name	Type	Description
ReferenceNumber	String	This column will be used in Bulk operations to get specific values from the Temp tables.

Retrieve and modify the notes for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Cloud will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderNotes WHERE OrderId = 21

Insert

The following attributes are required when performing an insert: Note, OrderId.

INSERT INTO OrderNotes (Note, OrderId) VALUES ("note_text", 144)

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	False	Unique identifier for the resource.
Author	String	True	Order note author.
DateCreated	Datetime	True	The date the order note was created, in the site's timezone.
Note	String	False	Order note content.
CustomerNote	Boolean	False	If true, the note will be shown to customers and they will be notified. If false, the note will be for admin reference only. Defaults to false.
AddedByUser	Boolean	False	If true, this note will be attributed to the current user. If false, the note will be attributed to the system. Defaults to false.
OrderId	String	True	Id of the order.

Retrieve and modify order refunds.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Cloud will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderRefunds WHERE OrderId = 21

Insert

The following attribute is required when performing an insert: OrderId.

INSERT INTO OrderRefunds (OrderId) VALUES (33)

Columns

Name	Type	ReadOnly	Description
Id	Long	False	Unique identifier for the resource.
DateCreated	Datetime	True	The date the order refund was created, in the site's timezone.
Amount	String	False	Refund amount.
Reason	String	False	Reason for refund.
RefundedBy	Long	False	User ID of user who created the refund.
RefundedPayment	Boolean	True	If the payment was refunded via the API. See api_refund.
LineItems	String	False	Line items data. See Order refund - Line items properties
ApiRefund	Boolean	False	When true, the payment gateway API is used to generate the refund. Defaults to true. write-only
OrderId	String	True	Id of the order.

Retrieve and modify orders.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, ParentId, Status, DateModified, ProductId. All other columns are processed client side.

SELECT * FROM Orders WHERE Id = 4
SELECT * FROM Orders WHERE ParentId = 4
SELECT * FROM Orders WHERE Status = 'pending' AND CustomerId = 501

Update

To perform an update or insert using any of the aggregate columns which contain object arrays, we can use a temporary table, or pass the value as a JSON string:

INSERT INTO OrderCouponLines#TEMP (Code) VALUES ('CouponCode')
UPDATE Orders SET CouponLinesAggregate = 'OrderCouponLines#TEMP' WHERE ID = 14

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE Orders SET metadata = 'key1:val1, key2:val2' WHERE ID = 58

Insert

This table does not have any required attributes when performing an insert.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
ParentId	Long	False	Parent order ID.
Number	String	True	Order number.
OrderKey	String	True	Order key.
CreatedVia	String	True	Shows where the order was created.
Version	String	True	Version of WooCommerce which last updated the order.
Status	String	False	Order status. Options: pending, processing, on-hold, completed, cancelled, refunded, failed and trash. Defaults to pending.
Currency	String	False	Currency the order was created with, in ISO format. Options: AED, AFN, ALL, AMD, ANG, AOA, ARS, AUD, AWG, AZN, BAM, BBD, BDT, BGN, BHD, BIF, BMD, BND, BOB, BRL, BSD, BTC, BTN, BWP, BYR, BZD, CAD, CDF, CHF, CLP, CNY, COP, CRC, CUC, CUP, CVE, CZK, DJF, DKK, DOP, DZD, EGP, ERN, ETB, EUR, FJD, FKP, GBP, GEL, GGP, GHS, GIP, GMD, GNF, GTQ, GYD, HKD, HNL, HRK, HTG, HUF, IDR, ILS, IMP, INR, IQD, IRR, IRT, ISK, JEP, JMD, JOD, JPY, KES, KGS, KHR, KMF, KPW, KRW, KWD, KYD, KZT, LAK, LBP, LKR, LRD, LSL, LYD, MAD, MDL, MGA, MKD, MMK, MNT, MOP, MRO, MUR, MVR, MWK, MXN, MYR, MZN, NAD, NGN, NIO, NOK, NPR, NZD, OMR, PAB, PEN, PGK, PHP, PKR, PLN, PRB, PYG, QAR, RON, RSD, RUB, RWF, SAR, SBD, SCR, SDG, SEK, SGD, SHP, SLL, SOS, SRD, SSP, STD, SYP, SZL, THB, TJS, TMT, TND, TOP, TRY, TTD, TWD, TZS, UAH, UGX, USD, UYU, UZS, VEF, VND, VUV, WST, XAF, XCD, XOF, XPF, YER, ZAR and ZMW. Defaults to USD.
DateCreated	Datetime	True	The date the order was created, in the site's timezone.
DateModified	Datetime	True	The date the order was last modified, in the site's timezone.
DiscountTotal	String	True	Total discount amount for the order.
DiscountTax	String	True	Total discount tax amount for the order.
ShippingTotal	String	True	Total shipping amount for the order.
ShippingTax	String	True	Total shipping tax amount for the order.
CartTax	String	True	Sum of line item taxes only.
Total	String	True	Grand total.
TotalTax	String	True	Sum of all taxes.
PricesIncludeTax	Boolean	True	True the prices included tax during checkout.
CustomerId	Long	False	User ID who owns the order. 0 for guests. Defaults to 0.
CustomerIpAddress	String	True	Customer's IP address.
CustomerUserAgent	String	True	User agent of the customer.
CustomerNote	String	False	Note left by customer during checkout.
PaymentMethod	String	False	Payment method ID.
PaymentMethodTitle	String	False	Payment method title.
TransactionId	String	False	Unique transaction ID.
DatePaid	Datetime	True	The date the order was paid, in the site's timezone.
DateCompleted	Datetime	True	The date the order was completed, in the site's timezone.
CartHash	String	True	MD5 hash of cart items to ensure orders are not modified.
LineItemsAggregate	String	False	Line items data. See Order - Line items properties
TaxLinesAggregate	String	True	Tax lines data. See Order - Tax lines properties read-only
ShippingLinesAggregate	String	False	Shipping lines data. See Order - Shipping lines properties
FeeLinesAggregate	String	False	Fee lines data. See Order - Fee lines properties
CouponLinesAggregate	String	False	Coupons line data. See Order - Coupon lines properties
RefundsAggregate	String	True	List of refunds. See Order - Refunds properties read-only
SetPaid	Boolean	False	Define if the order is paid. It will set the status to processing and reduce stock items. Defaults to false. write-only
BillingFirstName	String	False	First name.
BillingLastName	String	False	Last name.
BillingCompany	String	False	Company name.
BillingAddress1	String	False	Address line 1
BillingAddress2	String	False	Address line 2
BillingCity	String	False	City name.
BillingState	String	False	ISO code or name of the state, province or district.
BillingPostcode	String	False	Postal code.
BillingCountry	String	False	Country code in ISO 3166-1 alpha-2 format.
BillingEmail	String	False	Email address.
BillingPhone	String	False	Phone number.
ShippingFirstName	String	False	First name.
ShippingLastName	String	False	Last name.
ShippingCompany	String	False	Company name.
ShippingAddress1	String	False	Address line 1
ShippingAddress2	String	False	Address line 2
ShippingCity	String	False	City name.
ShippingState	String	False	ISO code or name of the state, province or district.
ShippingPostcode	String	False	Postal code.
ShippingCountry	String	False	Country code in ISO 3166-1 alpha-2 format.
ProductId	Long	False	Limit result set to resources assigned to a specific product.

Retrieve and modify payment gateways.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id. All other columns are processed client side.

SELECT * FROM PaymentGateways WHERE Id = 'id'

Update

The following attribute is required when performing an update: Id.

UPDATE PaymentGateways SET Title='Credit card / debit card', Description='Enter your card details' WHERE Id = 'woocommerce_payments'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	Payment gateway ID.
Title	String	False	Payment gateway title on checkout.
Description	String	False	Payment gateway description on checkout.
Order	Integer	False	Payment gateway sort order.
Enabled	Boolean	False	Payment gateway enabled status.
MethodTitle	String	True	Payment gateway method title.
MethodDescription	String	True	Payment gateway method description.
MethodSupportsAggregate	String	True	Supported features for this payment gateway.
SettingsTitleId	String	True	A unique identifier for the setting.
SettingsTitleDefault	String	True	Default value for the setting.
SettingsTitleDescription	String	True	A human readable description for the setting used in interfaces.
SettingsTitleLabel	String	True	A human readable label for the setting used in interfaces.
SettingsTitlePlaceholder	String	True	Placeholder text to be displayed in text inputs.
SettingsTitleTip	String	True	Additional help text shown to the user about the setting.
SettingsTitleType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsTitleValue	String	True	Setting value.
SettingsInstructionsDefault	String	True	Default value for the setting.
SettingsInstructionsDescription	String	True	A human readable description for the setting used in interfaces.
SettingsInstructionsLabel	String	True	A human readable label for the setting used in interfaces.
SettingsInstructionsPlaceholder	String	True	Placeholder text to be displayed in text inputs.
SettingsInstructionsTip	String	True	Additional help text shown to the user about the setting.
SettingsInstructionsType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsInstructionsValue	String	True	Setting value.

Retrieve the terms for an attribute.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, AttributeId, ProductId. All other columns are processed client side. If the 'AttributeId' filter is not specified, the Cloud will fetch a list of all the attributes and perform a request for each attribute in the list.

SELECT * FROM ProductAttributeTerms WHERE AttributeId = 4
SELECT * FROM ProductAttributeTerms WHERE ProductId = 41 AND Slug = 'criteria'

ProductAttributeTerms.rsd

Insert

The following attributes are required when performing an insert: Name, AttributeId.

INSERT INTO ProductAttributeTerms (Name, AttributeId) VALUES ('term_name', 2)

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Term name.
Slug	String	False	An alphanumeric identifier for the resource unique to its type.
Description	String	False	HTML description of the resource.
MenuOrder	Integer	False	Menu order, used to custom sort the resource.
Count	Integer	True	Number of published products for the resource.
AttributeId	Long	True	Id of the attribute.

Retrieve and modify products reviews.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Status, Reviewer, ReviewerEmail, ProductId. All other columns are processed client side.

SELECT * FROM ProductReviews WHERE Id = 4
SELECT * FROM ProductReviews WHERE ReviewerEmail = '[email protected]'

Insert

The following attributes are required when performing an insert: ProductId, Reviewer, Review.

INSERT INTO ProductReviews (ProductId, Reviewer, ReviewerEmail, Review) VALUES (167, 'reviewer', '[email protected]', 'review_content')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
DateCreated	Datetime	True	The date the review was created, in the site's timezone.
ProductId	Long	False	Unique identifier for the product that the review belongs to.
Status	String	False	Status of the review. Options: approved, hold, spam, unspam, trash and untrash. Defauls to approved.
Reviewer	String	False	Reviewer name.
ReviewerEmail	String	False	Reviewer email.
Review	String	False	The content of the review.
Rating	Integer	False	Review rating (0 to 5).
Verified	Boolean	False	Shows if the reviewer bought the product or not.

Retrieve and modify products.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, Type, Status, Sku, Price, OnSale, TaxClass, StockStatus, ShippingClass, ParentId. The price filter can be used with the <, > conditions and may be used twice to specify a range. All other columns are processed client side.

SELECT * FROM Products WHERE Id = 4
SELECT * FROM Products WHERE Slug = 'criteria' AND OnSale = true
SELECT * FROM Products WHERE Price < 14.99 AND Price > 11.99

Update

To perform an update or insert using any of the aggregate columns which contain simple primitive arrays, we can simply pass a JSON array string as the value:

UPDATE Products SET Name = 'ProductName', RelatedIdsAggregate = '[14, 16, 29]' WHERE ID = 42

To perform an update or insert using any of the aggregate columns which contain object arrays, we can use a temporary table, or pass the value as a JSON string:

INSERT INTO ProductCategories#TEMP (Name) VALUES ('Category')
INSERT INTO ProductCategories#TEMP (Name) VALUES ('Category2')
UPDATE Products SET CategoriesAggregate = 'ProductCategories#TEMP' WHERE ID = 167

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE Products SET metadata = 'key1:val1, key2:val2' WHERE ID = 58

Insert

This table does not have any required attributes when performing an insert.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Product name.
Slug	String	False	Product slug.
Permalink	String	True	Product URL.
DateCreated	Datetime	True	The date the product was created, in the site's timezone.
DateModified	Datetime	True	The date the product was last modified, in the site's timezone.
Type	String	False	Product type. Options: simple, grouped, external and variable. Defaults to simple.
Status	String	False	Product status (post status). Options: draft, pending, private and publish. Defaults to publish.
Featured	Boolean	False	Featured product. Defaults to false.
CatalogVisibility	String	False	Catalog visibility. Options: visible, catalog, search and hidden. Defaults to visible.
Description	String	False	Product description.
ShortDescription	String	False	Product short description.
Sku	String	False	Unique identifier.
Price	String	True	Current product price.
RegularPrice	String	False	Product regular price.
SalePrice	String	False	Product sale price.
DateOnSaleFrom	Datetime	False	Start date of sale price, in the site's timezone.
DateOnSaleTo	Datetime	False	End date of sale price, in the site's timezone.
PriceHtml	String	True	Price formatted in HTML.
OnSale	Boolean	True	Shows if the product is on sale.
Purchasable	Boolean	True	Shows if the product can be bought.
TotalSales	Integer	True	Amount of sales.
Virtual	Boolean	False	If the product is virtual. Defaults to false.
Downloadable	Boolean	False	If the product is downloadable. Defaults to false.
Downloads	String	False	List of downloadable files. See Product - Downloads properties
DownloadLimit	Integer	False	Number of times downloadable files can be downloaded after purchase. Defaults to -1.
DownloadExpiry	Integer	False	Number of days until access to downloadable files expires. Defaults to -1.
ExternalUrl	String	False	Product external URL. Only for external products.
ButtonText	String	False	Product external button text. Only for external products.
TaxStatus	String	False	Tax status. Options: taxable, shipping and none. Defaults to taxable.
TaxClass	String	False	Tax class.
ManageStock	Boolean	False	Stock management at product level. Defaults to false.
StockQuantity	Integer	False	Stock quantity.
StockStatus	String	False	Controls the stock status of the product. Options: instock, outofstock, onbackorder. Defaults to instock.
Backorders	String	False	If managing stock, this controls if backorders are allowed. Options: no, notify and yes. Defaults to no.
BackordersAllowed	Boolean	True	Shows if backorders are allowed.
Backordered	Boolean	True	Shows if the product is on backordered.
SoldIndividually	Boolean	False	Allow one item to be bought in a single order. Defaults to false.
Weight	String	False	Product weight.
ShippingRequired	Boolean	True	Shows if the product need to be shipped.
ShippingTaxable	Boolean	True	Shows whether or not the product shipping is taxable.
ShippingClass	String	False	Shipping class slug.
ShippingClassId	Long	True	Shipping class ID.
ReviewsAllowed	Boolean	False	Allow reviews. Defaults to true.
AverageRating	String	True	Reviews average rating.
RatingCount	Integer	True	Amount of reviews that the product have.
RelatedIdsAggregate	String	True	List of related products IDs.
UpsellIdsAggregate	String	False	List of up-sell products IDs.
CrossSellIdsAggregate	String	False	List of cross-sell products IDs.
ParentId	Long	False	Product parent ID.
PurchaseNoteAggregate	String	False	Optional note to send the customer after purchase.
CategoriesAggregate	String	False	List of categories. See Product - Categories properties
TagsAggregate	String	False	List of tags. See Product - Tags properties
ImagesAggregate	String	False	List of images. See Product - Images properties
AttributesAggregate	String	False	List of attributes. See Product - Attributes properties
DefaultAttributesAggregate	String	False	Defaults variation attributes. See Product - Default attributes properties
VariationsAggregate	String	True	List of variations IDs.
GroupedProductsAggregate	String	False	List of grouped products ID.
MenuOrder	Integer	False	Menu order, used to custom sort products.
MetaData	String	False	Meta data. See Product - Meta data properties
DimensionsLength	String	False	Product length.
DimensionsWidth	String	False	Product width.
DimensionsHeight	String	False	Product height.

Retrieve and modify product shipping classes.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, ProductId. All other columns are processed client side.

SELECT * FROM ProductShippingClasses WHERE Id = 4
SELECT * FROM ProductShippingClasses WHERE ProductId = 16

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO ProductShippingClasses (Name) VALUES ('class_name')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Shipping class name.
Slug	String	False	An alphanumeric identifier for the resource unique to its type.
Description	String	False	HTML description of the resource.
Count	Integer	True	Number of published products for the resource.
ProductId	Long	False	Limit result set to resources assigned to a specific product.

Retrieve and modify product tags.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, Product, HideEmpty. All other columns are processed client side.

SELECT * FROM ProductTags WHERE Id = 4
SELECT * FROM ProductTags WHERE Slug = 'criteria'
SELECT * FROM ProductTags WHERE ProductId = 16

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO ProductTags (Name) VALUES ('tag_name')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Tag name.
Slug	String	False	An alphanumeric identifier for the resource unique to its type.
Description	String	False	HTML description of the resource.
Count	Integer	True	Number of published products for the resource.
ProductId	Long	False	Limit result set to resources assigned to a specific product.

Retrieve and modify product variations.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id, Sku, Price, OnSale, Status, TaxClass, StockStatus, ProductId, ParentId. The price filter can be used with the <, > conditions and may be used twice to specify a range. All other columns are processed client side.

SELECT * FROM ProductVariations WHERE Id = 4
SELECT * FROM ProductVariations WHERE Price < 14.99 AND Price > 11.99 AND OnSale = true

Update

To introduce new metadata fields which are not present in the schema, the 'metadata' pseudocolumn can be used. The update below will create two new metadata fields with keys 'key1' and 'key2' and set their respective values. If any of the keys specified already exists, its value will be updated.

UPDATE ProductVariations SET metadata = 'key1:val1, key2:val2' WHERE ID = 58 AND ProductID = 80

To update Stock information, you must set ManageStock to true. For example:

UPDATE ProductVariations SET StockQuantity = 6, ManageStock = true WHERE ID = 58 AND ProductID = 80

Insert

The following attribute is required when performing an insert: ProductId.

INSERT INTO ProductVariations (ProductId) VALUES (167)

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
DateCreated	Datetime	True	The date the variation was created, in the site's timezone.
DateModified	Datetime	True	The date the variation was last modified, in the site's timezone.
Description	String	False	Variation description.
Permalink	String	True	Variation URL.
Sku	String	False	Unique identifier.
Price	String	True	Current variation price.
RegularPrice	String	False	Variation regular price.
SalePrice	String	False	Variation sale price.
DateOnSaleFrom	Datetime	False	Start date of sale price, in the site's timezone.
DateOnSaleTo	Datetime	False	End date of sale price, in the site's timezone.
OnSale	Boolean	True	Shows if the variation is on sale.
Status	String	False	Variation status. Options: draft, pending, private and publish. Defaults to publish.
Purchasable	Boolean	True	Shows if the variation can be bought.
Virtual	Boolean	False	If the variation is virtual. Defaults to false.
Downloadable	Boolean	False	If the variation is downloadable. Defaults to false.
DownloadsAggregate	String	False	List of downloadable files. See Product variation - Downloads properties
DownloadLimit	Integer	False	Number of times downloadable files can be downloaded after purchase. Defaults to -1.
DownloadExpiry	Integer	False	Number of days until access to downloadable files expires. Defaults to -1.
TaxStatus	String	False	Tax status. Options: taxable, shipping and none. Defaults to taxable.
TaxClass	String	False	Tax class.
ManageStock	String	False	Stock management at variation level. Defaults to false. If the stock is managed by product and not variaation then the value is parent.
StockQuantity	Integer	False	Stock quantity.
StockStatus	String	False	Controls the stock status of the product. Options: instock, outofstock, onbackorder. Defaults to instock.
Backorders	String	False	If managing stock, this controls if backorders are allowed. Options: no, notify and yes. Defaults to no.
BackordersAllowed	Boolean	True	Shows if backorders are allowed.
Backordered	Boolean	True	Shows if the variation is on backordered.
Weight	String	False	Variation weight.
ShippingClass	String	False	Shipping class slug.
ShippingClassId	String	True	Shipping class ID.
AttributesAggregate	String	False	List of attributes. See Product variation - Attributes properties
MenuOrder	Integer	False	Menu order, used to custom sort products.
MetaData	String	False	Meta data. See Product variation - Meta data properties
DimensionsLength	String	False	Variation length.
DimensionsWidth	String	False	Variation width.
DimensionsHeight	String	False	Variation height.
ImageId	Long	False	Image ID.
ImageDateCreated	Datetime	True	The date the image was created, in the site's timezone.
ImageDateModified	Datetime	True	The date the image was last modified, in the site's timezone.
ImageSrc	String	False	Image URL.
ImageName	String	False	Image name.
ImageAlt	String	False	Image alternative text.
ProductId	Long	True	Id of the product.
ParentId	Long	False	The id of the parent, filtered server side.

View and manage settings options.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, SettingId. All other columns are processed client side. If the 'SettingId' filter is not specified, the Cloud will fetch a list of all the settings and perform a request for each setting in the list.

SELECT * FROM SettingOptions WHERE Id = 'woocommerce_excluded_report_order_statuses'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	A unique identifier for the setting.
Label	String	True	A human readable label for the setting used in interfaces.
Description	String	True	A human readable description for the setting used in interfaces.
Value	String	False	Setting value.
Default	String	True	Default value for the setting.
Tip	String	True	Additional help text shown to the user about the setting.
Placeholder	String	True	Placeholder text to be displayed in text inputs.
Type	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
GroupId	String	True	An identifier for the group this setting belongs to.
SettingId	String	True	Id of the setting group.

Retrieve and modify shipping zone methods.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: InstanceId, ZoneId. All other columns are processed client side. If the 'ZoneId' filter is not specified, the Cloud will fetch a list of all the zones and perform a request for each zone in the list.

SELECT * FROM ShippingZoneMethods WHERE InstanceId = 4 AND ZoneId = 11

Insert

The following attributes are required when performing an insert: MethodId, ZoneId.

INSERT INTO ShippingZoneMethods (MethodId, ZoneId) VALUES ('flat_rate', 1)

Columns

Name	Type	ReadOnly	Description
InstanceId [KEY]	Long	False	Shipping method instance ID.
Enabled	Boolean	False	Shipping method enabled status.
MethodDescription	String	False	hipping method description.
MethodId	String	False	Shipping method ID.
MethodTitle	String	False	Shipping method title.
Order	Integer	False	Shipping method sort order.
Title	String	False	Shipping method customer facing title.
SettingsTitleId	String	True	A unique identifier for the setting.
SettingsTitleLabel	String	True	A human readable label for the setting used in interfaces.
SettingsTitleDescription	String	True	A human readable description for the setting used in interfaces.
SettingsTitleType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsTitleValue	String	False	Setting value.
SettingsTitleDefault	String	True	Default value for the setting.
SettingsTitleTip	String	True	Additional help text shown to the user about the setting.
SettingsTitlePlaceholder	String	True	Placeholder text to be displayed in text inputs.
SettingsTaxStatusId	String	True	A unique identifier for the setting.
SettingsTaxStatusLabel	String	True	A human readable label for the setting used in interfaces.
SettingsTaxStatusDescription	String	True	A human readable description for the setting used in interfaces.
SettingsTaxStatusType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsTaxStatusValue	String	False	Setting value.
SettingsTaxStatusDefault	String	True	Default value for the setting.
SettingsTaxStatusTip	String	True	Additional help text shown to the user about the setting.
SettingsTaxStatusPlaceholder	String	True	Placeholder text to be displayed in text inputs.
SettingsCostId	String	True	A unique identifier for the setting.
SettingsCostLabel	String	True	A human readable label for the setting used in interfaces.
SettingsCostDescription	String	True	A human readable description for the setting used in interfaces.
SettingsCostType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsCostValue	String	False	Setting value.
SettingsCostDefault	String	True	Default value for the setting.
SettingsCostTip	String	True	Additional help text shown to the user about the setting.
SettingsCostPlaceholder	String	True	Placeholder text to be displayed in text inputs.
SettingsTypeId	String	True	A unique identifier for the setting.
SettingsTypeLabel	String	True	A human readable label for the setting used in interfaces.
SettingsTypeDescription	String	True	A human readable description for the setting used in interfaces.
SettingsTypeType	String	True	Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsTypeValue	String	False	Setting value.
SettingsTypeDefault	String	True	Default value for the setting.
SettingsTypeTip	String	True	Additional help text shown to the user about the setting.
SettingsTypePlaceholder	String	True	Placeholder text to be displayed in text inputs.
ZoneId	Long	True	Id of the shipping zone.

Retrieve and modify shipping zones.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id. All other columns are processed client side.

SELECT * FROM ShippingZones WHERE Id = 13

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO ShippingZones (Name) VALUES ('zone_name')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	Shipping zone name.
Order	Integer	False	Shipping zone order.

Retrieve and modify tax classes.

Table Specific Information

Select

The Cloud will process all filters client side.

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO TaxClasses (Name) VALUES ('tax_class_name')

Columns

Name	Type	ReadOnly	Description
Slug [KEY]	String	True	Unique identifier for the resource.
Name	String	False	Tax class name. required

Retrieve and modify tax rates.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Class. All other columns are processed client side.

SELECT * FROM TaxRates WHERE Id = 4

Insert

This table does not have any required attributes when performing an insert.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Country	String	False	Country ISO 3166 code. See ISO 3166 Codes (Countries) for more details
State	String	False	State code.
Postcode	String	False	Postcode/ZIP.
City	String	False	City name.
Rate	String	False	Tax rate.
Name	String	False	Tax rate name.
Priority	Integer	False	Tax priority. Only 1 matching rate per priority will be used. To define multiple tax rates for a single area you need to specify a different priority per rate. Defaults to 1.
Compound	Boolean	False	Whether or not this is a compound rate. Compound tax rates are applied on top of other tax rates. Defaults to false.
Shipping	Boolean	False	Whether or not this tax rate also gets applied to shipping. Defaults to true.
Order	Integer	False	Indicates the order that will appear in queries.
Class	String	False	Tax class. Defaults to standard.
Postcodes	String	False	Postcodes/ZIP.
Cities	String	False	City names.

Retrieve and modify webhooks.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Status. All other columns are processed client side.

SELECT * FROM Webhooks WHERE Id = 4

Insert

The following attributes are required when performing an insert: Topic, DeliveryUrl.

INSERT INTO Webhooks (Topic, DeliveryUrl) VALUES ('order.updated', 'http://url.com')

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Long	True	Unique identifier for the resource.
Name	String	False	A friendly name for the webhook.
Status	String	False	Webhook status. Options: active, paused and disabled. Defaults to active.
Topic	String	False	Webhook topic.
Resource	String	True	Webhook resource.
Event	String	True	Webhook event.
HooksAggregate	String	True	WooCommerce action names associated with the webhook.
DeliveryUrl	String	True	The URL where the webhook payload is delivered.
Secret	String	False	Secret key used to generate a hash of the delivered webhook and provided in the request headers. This will Defaults to a MD5 hash from the current user's ID
DateCreated	Datetime	True	The date the webhook was created, in the site's timezone.
DateModified	Datetime	True	The date the webhook was last modified, in the site's timezone.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - WooCommerce Views

Name	Description
Continents	Retrieve continents.
Countries	Retrieve countries.
Currencies	Retrieve available currencies.
OrderCouponLines	Retrieve the coupon lines for an order.
OrderFeeLines	Retrieve the fee lines for an order.
OrderRefundLineItems	Retrieve the line items for a refund.
OrderShippingLines	Retrieve the shipping lines for an order.
OrderTaxLines	Retrieve the tax lines for an order.
ProductAttributes	Retrieve product attributes.
ProductCategories	Retrieve product categories.
ProductImages	Retrieve images for a product.
SalesReport	Executes a Sales report.
SettingGroups	Retrieve settings groups.
ShippingLocations	Retrieve shipping zone locations.
ShippingMethods	Retrieve shipping methods.
States	Retrieve shipping states.
SystemStatus	Retrieve system status items.
SystemStatusPages	Retrieve and system status items.
SystemStatusTools	Retrieve system status tools.
TopSellersReports	Execute a top sellers report.
TotalsReport	Execute a totals report.

Retrieve continents.

Table Specific Information

Select

The Cloud will process all filters client side.

Columns

Name	Type	Description
Code	String	2 character continent code.
Name	String	Full name of continent.
CountriesAggregate	String	List of countries on this continent. See Continents - Countries properties read-only

Retrieve countries.

Table Specific Information

Select

The Cloud will process all filters client side.

Columns

Name	Type	Description
Code	String	ISO3166 alpha-2 country code.
Name	String	Full name of country.
StatesAggregate	String	List of states in this country. See Countries - States properties read-only

Retrieve available currencies.

Table Specific Information

Select

The Cloud will process all filters client side.

Columns

Name	Type	Description
Code	String	ISO4217 currency code.
Name	String	Full name of currency.
Symbol	String	Currency symbol.

Retrieve the coupon lines for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side.

SELECT * FROM OrderCouponLines WHERE OrderId = 144

Columns

Name	Type	Description
Id [KEY]	Long	Item ID.
Code	String	Coupon code.
Discount	String	Discount total.
DiscountTax	String	Discount total tax.
MetaData	String	Meta data. See Order - Meta data properties
OrderId	String	Id of the order.

Retrieve the fee lines for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Cloud will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderFeeLines WHERE OrderId = 21

Columns

Name	Type	Description
Id [KEY]	Long	Item ID.
Name	String	Fee name.
TaxClass	String	Tax class of fee.
TaxStatus	String	Tax status of fee. Options: taxable and none.
Total	String	Line total (after discounts).
TotalTax	String	Line total tax (after discounts).
Taxes	String	Line taxes. See Order - Taxes properties read-only
MetaData	String	Meta data. See Order - Meta data properties
OrderId	String	Id of the order.

Retrieve the line items for a refund.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: OrderId, RefundId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Cloud will fetch a list of all the orders and perform a request for each order in the list. The above behaviour will happen apply also for refunds, if 'RefundId' is not specified.

SELECT * FROM OrderRefundLineItems WHERE OrderId = 16

Columns

Name	Type	Description
Id	Long	Item ID.
Name	String	Product name.
ProductId	Long	Product ID.
VariationId	Long	Variation ID, if applicable.
Quantity	Integer	Quantity ordered.
TaxClass	Integer	Tax class of product.
Subtotal	String	Line subtotal (before discounts).
SubtotalTax	String	Line subtotal tax (before discounts).
Total	String	Line total (after discounts).
TotalTax	String	Line total tax (after discounts).
TaxesAggregate	String	Line taxes. See Order refund line item - Taxes properties read-only
MetaDataAggregate	String	Meta data. See Order refund - Meta data properties
Sku	String	Product SKU.
Price	String	Product price.
OrderId	String	Id of the order.
RefundId	String	Id of the refund.

Retrieve the shipping lines for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Cloud will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderShippingLines WHERE OrderId = 21

Columns

Name	Type	Description
Id [KEY]	Long	Item ID.
MethodTitle	String	Shipping method name.
MethodId	String	Shipping method ID.
Total	String	Line total (after discounts).
TotalTax	String	Line total tax (after discounts).
Taxes	String	Line taxes. See Order - Taxes properties read-only
MetaData	String	Meta data. See Order - Meta data properties
OrderId	Long	Id of the order.

Retrieve the tax lines for an order.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: OrderId. All other columns are processed client side. If the 'OrderId' filter is not specified, the Cloud will fetch a list of all the orders and perform a request for each order in the list.

SELECT * FROM OrderTaxLines WHERE OrderId = 12

Columns

Name	Type	Description
Id [KEY]	Long	Item ID.
RateCode	String	Tax rate code.
RateId	String	Tax rate ID.
Label	String	Tax rate label.
Compound	Boolean	Show if is a compound tax rate.
TaxTotal	String	Tax total (not including shipping taxes).
ShippingTaxTotal	String	Shipping tax total.
MetaData	String	Meta data. See Order - Meta data properties
OrderId	Long	Id of the order.

Retrieve product attributes.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: ProductId. All other columns are processed client side. If the 'ProductId' filter is not specified, the Cloud will fetch a list of all the products and perform a request for each product in the list.

SELECT * FROM ProductAttributes WHERE ProductId = 4

Columns

Name	Type	Description
Id [KEY]	Long	Attribute ID.
Name	String	Attribute name.
Slug	String	An alphanumeric identifier for the resource unique to its type.
Type	String	Type of attribute. By default only select is supported.
OrderBy	String	Default sort order. Options: menu_order, name, name_num and id. Defaults to menu_order.
HasArchives	Boolean	Enable/Disable attribute archives. Defaults to false.
ProductId	Long	Id of the product.

Retrieve product categories.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, Slug, ProductId. All other columns are processed client side. If the 'ProductId' filter is not specified, the Cloud will fetch a list of all the products and perform a request for each product in the list.

SELECT * FROM ProductCategories WHERE Id = 4
SELECT * FROM ProductCategories WHERE Slug = 'criteria'

Columns

Name	Type	Description
Id [KEY]	Long	Unique identifier for the resource.
Name	String	Category name.
Slug	String	An alphanumeric identifier for the resource unique to its type.
ProductId	Long	Id of the product.

Retrieve images for a product.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Id, DateCreated, DateModified, Src, Name, Alt, ProductId. All other columns are processed client side. If the 'ProductId' filter is not specified, the Cloud will fetch a list of all the products and perform a request for each product in the list.

SELECT * FROM ProductImages WHERE Id = 4
SELECT * FROM ProductImages WHERE ProductId = 6

Columns

Name	Type	Description
Id [KEY]	Long	Image ID.
DateCreated	Datetime	The date the image was created, in the site's timezone.
DateModified	Datetime	The date the image was last modified, in the site's timezone.
Src	String	Image URL.
Name	String	Image name.
Alt	String	Image alternative text.
ProductId	Long	Id of the product.

Executes a Sales report.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Period, StartDate, EndDate. All other columns are processed client side.

SELECT * FROM SalesReport WHERE Period = 'week' AND StartDate = '01/01/2019'

Columns

Name	Type	Description
TotalSales	String	Gross sales in the period.
NetSales	String	Net sales in the period.
AverageSales	String	Average net daily sales.
TotalOrders	Integer	Total of orders placed.
TotalItems	Integer	Total of items purchased.
TotalTax	String	Total charged for taxes.
TotalShipping	String	Total charged for shipping.
TotalRefunds	Integer	Total of refunded orders.
TotalDiscount	Integer	Total of coupons used.
TotalsGroupedBy	String	Group type.
TotalsAggregate	String	Totals.
Period	String	Report period. Defaults to week. Options: week, month, last_month and year.
StartDate	Date	Return sales for a specific start date, the date need to be in the YYYY-MM-DD format.
EndDate	Date	Return sales for a specific end date, the date need to be in the YYYY-MM-DD format.

Retrieve settings groups.

Table Specific Information

Select

The Cloud will process all filters client side.

Columns

Name	Type	Description
Id [KEY]	String	A unique identifier that can be used to link settings together.
Label	String	A human readable label for the setting used in interfaces.
Description	String	A human readable description for the setting used in interfaces.
ParentId	String	ID of parent grouping.

Retrieve shipping zone locations.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: ZoneId. All other columns are processed client side. If the 'ZoneId' filter is not specified, the Cloud will fetch a list of all the zones and perform a request for each zone in the list.

SELECT * FROM ShippingLocations WHERE ZoneId = 13

Columns

Name	Type	Description
Code	String	Shipping zone location code.
Type	String	Shipping zone location type. Options: postcode, state, country and continent. Defaults to country.
ZoneId	Long	Id of the shipping zone.

Retrieve shipping methods.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id. All other columns are processed client side.

SELECT * FROM ShippingMethods WHERE Id = 'flat_rate'

Columns

Name	Type	Description
Id [KEY]	String	Method ID.
Title	String	Shipping method title.
Description	String	Shipping method description.

Retrieve shipping states.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Country. All other columns are processed client side. If the 'Country' filter is not specified, the Cloud will fetch a list of all the countries and perform a request for each country in the list.

SELECT * FROM States WHERE Country = 'Germany'

Columns

Name	Type	Description
Country	String	State code.
Code	String	State code.
Name	String	Full name of the state.

Retrieve system status items.

Table Specific Information

Select

This table represents a singleton entity.

Columns

Name	Type	Description
ActivePluginsAggregate	String	Active plugins.
PagesAggregate	String	WooCommerce pages.
EnvironmentHomeUrl	String	Home URL.
EnvironmentSiteUrl	String	Site URL.
EnvironmentWcVersion	String	WooCommerce version.
EnvironmentLogDirectory	String	Log directory.
EnvironmentLogDirectoryWritable	Boolean	Is log directory writable? read-only
EnvironmentWpVersion	String	WordPress version.
EnvironmentWpMultisite	Boolean	Is WordPress multisite? read-only
EnvironmentWpMemoryLimit	Integer	WordPress memory limit.
EnvironmentWpDebugMode	Boolean	Is WordPress debug mode active? read-only
EnvironmentWpCron	Boolean	Are WordPress cron jobs enabled? read-only
EnvironmentLanguage	String	WordPress language.
EnvironmentServerInfo	String	Server info.
EnvironmentPhpVersion	String	PHP version.
EnvironmentPhpPostMaxSize	Integer	PHP post max size.
EnvironmentPhpMaxExecutionTime	Integer	PHP max execution time.
EnvironmentPhpMaxInputVars	Integer	PHP max input vars.
EnvironmentCurlVersion	String	cURL version.
EnvironmentSuhosinInstalled	Boolean	Is SUHOSIN installed? read-only
EnvironmentMaxUploadSize	Integer	Max upload size.
EnvironmentMysqlVersion	String	MySQL version.
EnvironmentDefaultTimezone	String	Default timezone.
EnvironmentFsockopenOrCurlEnabled	Boolean	Is fsockopen/cURL enabled? read-only
EnvironmentSoapclientEnabled	Boolean	Is SoapClient class enabled? read-only
EnvironmentDomdocumentEnabled	Boolean	Is DomDocument class enabled? read-only
EnvironmentGzipEnabled	Boolean	Is GZip enabled? read-only
EnvironmentMbstringEnabled	Boolean	Is mbstring enabled? read-only
EnvironmentRemotePostSuccessful	Boolean	Remote POST successful? read-only
EnvironmentRemotePostResponse	String	Remote POST response.
EnvironmentRemoteGetSuccessful	Boolean	Remote GET successful? read-only
EnvironmentRemoteGetResponse	String	Remote GET response.
DatabaseWcDatabaseVersion	String	WC database version.
DatabasePrefix	String	Database prefix.
DatabaseMaxmindGeoipDatabase	String	MaxMind GeoIP database.
DatabaseTablesAggregate	String	Database tables.
ThemeName	String	Theme name.
ThemeVersion	String	Theme version.
ThemeVersionLatest	String	Latest version of theme.
ThemeAuthorUrl	String	Theme author URL.
ThemeIsChildTheme	Boolean	Is this theme a child theme? read-only
ThemeHasWoocommerceSupport	Boolean	Does the theme declare WooCommerce support? read-only
ThemeHasWoocommerceFile	Boolean	Does the theme have a woocommerce.php file? read-only
ThemeHasOutdatedTemplates	Boolean	Does this theme have outdated templates? read-only
ThemeOverridesAggregate	String	Template overrides.
ThemeParentName	String	Parent theme name.
ThemeParentVersion	String	Parent theme version.
ThemeParentAuthorUrl	String	Parent theme author URL.
SettingsApiEnabled	Boolean	REST API enabled? read-only
SettingsForceSsl	Boolean	SSL forced? read-only
SettingsCurrency	String	Currency.
SettingsCurrencySymbol	String	Currency symbol.
SettingsCurrencyPosition	String	Currency position.
SettingsThousandSeparator	String	Thousand separator.
SettingsDecimalSeparator	String	Decimal separator.
SettingsNumberOfDecimals	Integer	Number of decimals.
SettingsGeolocationEnabled	Boolean	Geolocation enabled? read-only
SettingsTaxonomiesAggregate	String	Taxonomy terms for product/order statuses.
SecuritySecureConnection	Boolean	Is the connection to your store secure? read-only
SecurityHideErrors	Boolean	Hide errors from visitors? read-only

Retrieve and system status items.

Table Specific Information

Select

The Cloud will process all filters client side.

Columns

Name	Type	Description
PageName	String	Name of the page.
PageId	String	Id of the page.
PageSet	String	Whether the page is set.
PageExists	String	Whether the page exists.
PageVisible	String	Whether the page is visible.
Shortcode	String	Page short code.
ShortcodeRequired	String	Whether shortcode is required.
ShortcodePresent	String	Whether shortcode is present.

Retrieve system status tools.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available column for this table is: Id. All other columns are processed client side.

SELECT * FROM SystemStatusTools WHERE Id = 'install_pages'

Columns

Name	Type	Description
Id [KEY]	String	A unique identifier for the tool.
Name	String	Tool name.
Action	String	What running the tool will do.
Description	String	Tool description.
Success	Boolean	Did the tool run successfully?
Message	String	Tool return message.
Confirm	Boolean	Confirm execution of the tool. Defaults to false. write-only

Execute a top sellers report.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: Title, ProductId, Quantity, StartDate, EnDDate. All other columns are processed client side.

SELECT * FROM TopSellersReports WHERE Title = 'criteria'
SELECT * FROM TopSellersReports WHERE ProductId = 4

Columns

Name	Type	Description
Title	String	Product title.
ProductId	Long	Product ID.
Quantity	Integer	Total number of purchases.
StartDate	Date	Return the report for a specific start date.
EndDate	Date	Return the report for a specific end date.

Execute a totals report.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless stated otherwise. The available columns for this table are: ReportType, StartDate, EndDate. All other columns are processed client side.

SELECT * FROM TotalsReport WHERE ReportType = 'reviews' AND StartDate = '01/01/2019'

Columns

Name	Type	Description
Slug	String	An alphanumeric identifier for the resource.
Name	String	Review type name.
Total	String	Amount of reviews.
ReportType	String	Type of the report. Must be one of: reviews, products, orders, customers, coupons.
StartDate	Date	Return the report for a specific start date.
EndDate	Date	Return the report for a specific end date.

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with WooCommerce.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from WooCommerce, along with an indication of whether the procedure succeeded or failed.

CData Cloud - WooCommerce Stored Procedures

Name	Description
RunSystemStatusTool	Run the system status tool specified by the id.
UpdateShippingZoneLocations	Update the locations for a shipping zone.

Run the system status tool specified by the id.

Input

Name	Type	Required	Description
Id	String	True	The ID of the tool to be run.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Update the locations for a shipping zone.

Input

Name	Type	Required	Description
ZoneId	String	True	The ID of the zone to update.
Locations	String	False	This value shows a boolean indication of whether the operation was successful or not.

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for WooCommerce:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the Cloud can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations::

• sys_identity: Returns information about batch operations or single updates.

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	The database name.

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

Name	Type	Description
CatalogName	String	The database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view.
TableType	String	The table type (table or view).
Description	String	A description of the table or view.
IsUpdateable	Boolean	Whether the table can be updated.

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Orders table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Orders' 

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The storage size of the column.
DisplaySize	Int32	The designated column's normal maximum width in characters.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsHidden	Boolean	Whether the column is hidden.
IsArray	Boolean	Whether the column is an array.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Name	Type	Description
CatalogName	String	The database containing the stored procedure.
SchemaName	String	The schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure.
Description	String	A description of the stored procedure.
ProcedureType	String	The type of the procedure, such as PROCEDURE or FUNCTION.

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the RunSystemStatusTool stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='RunSystemStatusTool' AND Direction=1 OR Direction=2

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure containing the parameter.
ColumnName	String	The name of the stored procedure parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName	String	The name of the data type.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
IsRequired	Boolean	Whether the parameter is required for execution of the procedure.
IsArray	Boolean	Whether the parameter is an array.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.

Describes the primary and foreign keys.

The following query retrieves the primary key for the Orders table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Orders' 
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.
ForeignKeyType	String	Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
KeySeq	String	The sequence number of the primary key.
KeyName	String	The name of the primary key.

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:woocommerce:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.
Sensitivity	String	The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName	String	A camel-cased truncated form of the connection property name.
Ordinal	Int32	The index of the parameter.
CatOrdinal	Int32	The index of the parameter category.
Hierarchy	String	Shows dependent properties associated that need to be set alongside this one.
Visible	Boolean	Informs whether the property is visible in the connection UI.
ETC	String	Various miscellaneous information about the property.

Describes the SELECT query processing that the Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name	Description	Possible Values
AGGREGATE_FUNCTIONS	Supported aggregation functions.	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	Whether COUNT function is supported.	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	The opening character used to escape an identifier.	[
IDENTIFIER_QUOTE_CLOSE_CHAR	The closing character used to escape an identifier.	]
SUPPORTED_OPERATORS	A list of supported SQL operators.	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	Whether GROUP BY is supported, and, if so, the degree of support.	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	Supported string functions.	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	Supported numeric functions.	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	Supported date/time functions.	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS	A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN	String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION	Indicates if the provider supports transactions such as commit and rollback.	YES, NO
DIALECT	Indicates the SQL dialect to use.
KEY_PROPERTIES	Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS	Indicates if multiple schemas may exist for the provider.	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	Indicates if multiple catalogs may exist for the provider.	YES, NO
DATASYNCVERSION	The CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData Data Sync category of this driver.	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	Whether enhanced SQL functionality beyond what is offered by the API is supported.	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	Whether batch operations are supported.	YES, NO
SQL_CAP	All supported SQL capabilities for this driver.	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY	Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.	YES, NO
PSEUDO_COLUMNS	A string array indicating the available pseudo columns.
MERGE_ALWAYS	If the value is true, The Merge Mode is forcibly executed in Data Sync.	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION	Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE	Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY	A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION	Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID	Indicates whether the CheckCache statement should be done against the parent key column.	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity
          

Columns

Name	Type	Description
Id	String	The database-generated Id returned from a data modification operation.
Batch	String	An identifier for the batch. 1 for a single operation.
Operation	String	The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message	String	SUCCESS or an error message if the update in the batch failed.

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

---

Property	Description
AuthScheme	Whether to use Basic Authentication, the one-legged OAuth 1.0 Authentication or the OAuth 2.0 Authentication when connecting to WooCommerce.
URL	The URL of the WooCommerce instance.

OAuth

---

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.
ConsumerKey	The consumer key assigned by WooCommerce when creating the authentication credentials. If this property is set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.
ConsumerSecret	The consumer secret assigned by WooCommerce when creating the authentication credentials. If this and the ConsumerKey properties are set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.

SSL

---

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

Logging

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

Schema

---

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Miscellaneous

---

Property	Description
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Pagesize	The maximum number of results to return per page from WooCommerce.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
RowScanDepth	The maximum number of rows to scan to look for the columns available in a table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

---

Property	Description
AuthScheme	Whether to use Basic Authentication, the one-legged OAuth 1.0 Authentication or the OAuth 2.0 Authentication when connecting to WooCommerce.
URL	The URL of the WooCommerce instance.

Whether to use Basic Authentication, the one-legged OAuth 1.0 Authentication or the OAuth 2.0 Authentication when connecting to WooCommerce.

Possible Values

Auto, Basic, OAuth, OAuthOneLegged

Data Type

string

Default Value

"Auto"

Remarks

• Auto: Lets the driver decide automatically based on the other connection properties you have set.
• Basic: Set this to use Basic user / password authentication. Note that Basic authentication is only supported over HTTPS. If using Basic authentication, the credentials will be sent as query parameters with every request.
• OAuthOneLegged: Set this to use the one-legged OAuth 1.0 Authentication.
• OAuth: Set this to perform OAuth 2.0 authentication.

The URL of the WooCommerce instance.

Data Type

string

Default Value

""

Remarks

The URL of the WooCommerce instance.

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

---

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.
ConsumerKey	The consumer key assigned by WooCommerce when creating the authentication credentials. If this property is set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.
ConsumerSecret	The consumer secret assigned by WooCommerce when creating the authentication credentials. If this and the ConsumerKey properties are set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.

The client Id assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

The consumer key assigned by WooCommerce when creating the authentication credentials. If this property is set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.

Data Type

string

Default Value

""

Remarks

As part of creating a new set of credentials, you will receive the ConsumerKey and ConsumerSecret values.

The consumer secret assigned by WooCommerce when creating the authentication credentials. If this and the ConsumerKey properties are set, and InitiateOAuth is OFF, the provider will authenticate using one-legged OAuth1.0.

Data Type

string

Default Value

""

Remarks

As part of creating a new set of credentials, you will receive the ConsumerKey and ConsumerSecret values.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

---

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

---

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

---

Property	Description
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Pagesize	The maximum number of results to return per page from WooCommerce.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
RowScanDepth	The maximum number of rows to scan to look for the columns available in a table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

The maximum number of results to return per page from WooCommerce.

Data Type

int

Default Value

100

Remarks

The Pagesize property affects the maximum number of results to return per page from WooCommerce. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

The maximum number of rows to scan to look for the columns available in a table.

Data Type

int

Default Value

0

Remarks

The columns in a table must be determined by scanning table rows. This value determines the maximum number of rows that will be scanned.

Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Cloud throws an exception.