Getting Started

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.

Establishing a Connection

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:

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

To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
Set these connection properties:
- OAuthClientId: The Client Id in your application settings.
- OAuthClientSecret: The Client Secret in your application settings.
Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
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:

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

Creating and Registering the Application

Log into your site through the admin panel.
Navigate to OAuth Server.
Select Add New Client.
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.
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.

SSL Configuration

Customizing the SSL Configuration

To enable TLS, set the following:

URL: Prefix the connection string with https://

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

To specify another certificate, see the SSLServerCert connection property.

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

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

Other Proxies

Set the following properties:

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

Data Model

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.

Tables

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 variations of products.
SettingOptions	View and manage settings options.
ShippingZoneMethods	Retrieve and modify shipping zone methods.
ShippingZones	Retrieve and modify shipping zones.
SubscriptionNotes	Retrieves all subscription notes. This table shows data only if the WooCommerce Subscriptions Plugin is enabled.
Subscriptions	The Subscriptions table enables you to create, view, update, and delete individual, or a batch of subscriptions. This table shows data only if the WooCommerce Subscriptions Plugin is enabled.
TaxClasses	Retrieve and modify tax classes.
TaxRates	Retrieve and modify tax rates.
Webhooks	Retrieve and modify webhooks.

Attributes

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

Coupons

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. Following columns are supported server side, all other columns are processed client side.

Id supports the '=,IN,NOT IN' operators.
Code supports the '=' operator.
DateCreated supports the '>,<,>=,<=,=' operators.
DateModified supports the '>,<,>=,<=,=' operators.

SELECT * FROM Coupons WHERE Id = 4
SELECT * FROM Coupons WHERE Id IN (4,5)
SELECT * FROM Coupons WHERE Code = 'criteria'
SELECT * FROM Coupons WHERE DateCreated = '2024-08-22 08:47:21.0'
SELECT * FROM Coupons WHERE DateModified = '2024-08-22 08:47:21.0'

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	References	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.
Metadata	String	True		Meta data. See Coupon - Meta data properties.

Customers

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. Following columns are supported server side, all other columns are processed client side.

Id supports the '=,IN,NOT IN' operators.
Email supports the '=' operator.
Role supports the '=' operator.

SELECT * FROM Customers WHERE Id = 4
SELECT * FROM Customers WHERE Id IN (4,5)
SELECT * FROM Customers WHERE Email = '[email protected]'
SELECT * FROM Customers WHERE Username = 'user' AND IsPayingCustomer = true

By default, the following query returns the results for Role = All.

SELECT * FROM Customers;

Update

To introduce new metadata fields which are not present in the schema, the 'MetaDataAggregate' column 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 MetaDataAggregate = '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	References	Description
Id [KEY]	Long	True		The 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		The customer's first name.
LastName	String	False		The customer's last name.
Role	String	True		The customer role. Includes all roles by default. The allowed values are administrator, editor, author, contributor, subscriber, customer, shop_manager.
Username	String	False		The customer login name.
Password	String	False		The customer password.
IsPayingCustomer	Bool	True		Indicates whether the customer is a paying customer.
AvatarUrl	String	True		The avatar URL.
MetaDataAggregate	String	False		The customer metadata. See CustomerMetadata properties.
BillingFirstName	String	False		The billing first name.
BillingLastName	String	False		The billing last name.
BillingCompany	String	False		The billing company name.
BillingAddress1	String	False		The billing address line 1.
BillingAddress2	String	False		The billing address line 2.
BillingCity	String	False		The billing city name.
BillingState	String	False		The ISO code or name of the billing state, province or district.
BillingPostcode	String	False		The billing postal code.
BillingCountry	String	False		The ISO code of the billing country.
BillingEmail	String	False		The billing email address.
BillingPhone	String	False		The billing phone number.
ShippingFirstName	String	False		The shipping first name.
ShippingLastName	String	False		The shipping last name.
ShippingCompany	String	False		The shipping company name.
ShippingAddress1	String	False		The shipping address line 1.
ShippingAddress2	String	False		The shipping address line 2.
ShippingCity	String	False		The shipping city name.
ShippingState	String	False		The shipping ISO code or name of the state, province or district.
ShippingPostcode	String	False		The shipping postal code.
ShippingCountry	String	False		The shipping ISO code of the country.

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 is used in Bulk operations to get specific values from the Temp tables.

OrderLineItems

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. Following columns are supported server side, 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.

OrderId supports the '=,IN,NOT IN,ORDER BY' operators.

SELECT * FROM OrderLineItems WHERE OrderId = 13
SELECT * FROM OrderLineItems WHERE OrderId IN (13,14)
SELECT * FROM OrderLineItems WHERE OrderId NOT IN (13,14)
SELECT * FROM OrderLineItems ORDER BY OrderId

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	References	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 [KEY]	String	True	Orders.Id	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.

OrderNotes

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	References	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 [KEY]	String	False	Orders.Id	Id of the order.

OrderRefunds

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. Following columns are supported server side, 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.

OrderId supports '=,IN' operators.
Id supports the '=,IN,NOT IN' operators.
DateCreated supports the '>,<,>=,<=,=' operators.

SELECT * FROM OrderRefunds WHERE OrderId = 21
SELECT * FROM OrderRefunds WHERE Id = 21
SELECT * FROM OrderRefunds WHERE Id IN (21,22)
SELECT * FROM OrderRefunds WHERE Id NOT IN (21,22)
Select * from OrderRefunds where DateCreated < '2024-08-22 08:47:21.0'

Insert

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

INSERT INTO OrderRefunds (OrderId) VALUES (33)

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	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
Metadata	String	True		Meta data. See OrderRefunds - Meta data 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
OrderId [KEY]	String	False	Orders.Id	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.
ApiRestock	Boolean	When true, the selected line items are restocked. Default is true.

Orders

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. Following columns are supported server side, all other columns are processed client side.

Id support the '=,IN,NOT IN' operators.
ParentId support the '=' operator.
Status support the '=' operator.
ProductId support the '=' operator.
DateCreated support the '=' operator.
DateModified support the '>,<,>=,<=,=' operators.

SELECT * FROM Orders WHERE Id = 4
SELECT * FROM Orders WHERE Id IN (4,5)
SELECT * FROM Orders WHERE ParentId = 4
SELECT * FROM Orders WHERE Status = 'pending' AND CustomerId = 501
SELECT * FROM Orders WHERE DateCreated = '2024-08-22 08:47:21.0'
SELECT * FROM Orders WHERE DateModified = '2024-08-22 08:47:21.0'

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

Update can also be done by providing the metadata column as a json aggregate:

UPDATE Orders SET metadata = '[{\"key\": \"cdatatest\",\"value\": {\"test\":1234,\"test2\":125554}}]' WHERE ID = 2102;

Insert

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

Columns


Name	Type	ReadOnly	References	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.
Metadata	String	False		Meta data. See Order - Meta data properties.
ProductId	Long	False		Limit result set to resources assigned to a specific product.

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.

PaymentGateways

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	References	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.
SettingsInstructionsId	String	True		Id value for the setting.
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.
SettingsIsDismissedId	String	True		Id of the Settings dismissed object.
SettingsIsDismissedLabel	String	True		Label of the Settings dismissed object.
SettingsIsDismissedDescription	String	True		Description of the Settings dismissed object.
SettingsIsDismissedType	String	True		Type of the Settings dismissed object.
SettingsIsDismissedValue	String	True		Value of the Settings dismissed object.
SettingsIsDismissedDefault	String	True		Default value of the Settings dismissed object.
SettingsIsDismissedTip	String	True		UI tip of the Settings dismissed object.
SettingsIsDismissedPlaceholder	String	True		Placeholder of the Settings dismissed object.
SettingsEnableForMethodsId	String	True		Id of the Settings enable for methods
SettingsEnableForMethodsLabel	String	True		Label of the Settings enable for methods
SettingsEnableForMethodsDescription	String	True		Description of the Settings enable for methods
SettingsEnableForMethodsType	String	True		Type of the Settings enable for methods
SettingsEnableForMethodsValue	String	True		Value of the Settings enable for methods
SettingsEnableForMethodsDefault	String	True		Default value of the Settings enable for methods
SettingsEnableForMethodsTip	String	True		UI tip of the Settings enable for methods
SettingsEnableForMethodsPlaceholder	String	True		Placeholder of the Settings enable for methods
SettingsEnableForMethodsOptionsAggregate	String	True		Options of the Settings enable for methods
SettingsEnableForVirtualId	String	True		Id of the Settings enable for virtual object
SettingsEnableForVirtualLabel	String	True		Label of the Settings enable for virtual object
SettingsEnableForVirtualDescription	String	True		Description of the Settings enable for virtual object
SettingsEnableForVirtualType	String	True		Type of the Settings enable for virtual object
SettingsEnableForVirtualValue	String	True		Value of the Settings enable for virtual object
SettingsEnableForVirtualDefault	String	True		Default value of the Settings enable for virtual object
SettingsEnableForVirtualTip	String	True		UI tip of the Settings enable for virtual object
SettingsEnableForVirtualPlaceholder	String	True		Placeholder of the Settings enable for virtual object

ProductAttributeTerms

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. Following columns are supported server side, 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.

Id supports the '=,IN,NOT IN' operators.
Slug supports the '=' operator.
AttributeId supports the '=' operator.
ProductId supports the '=' operator.

SELECT * FROM ProductAttributeTerms WHERE Id IN (4,5)
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	References	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 [KEY]	Long	False	ProductAttributes.Id	Id of the attribute.

ProductReviews

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. Following columns are supported server side, all other columns are processed client side.

Id supports the '=, NOT IN' operators.
Status supports the '=' operator.
Reviewer supports the '=, IN, NOT IN' operators.
ReviewerEmail supports the '=' operator.
ProductId supports the '=' operator.
DateCreated supports the '>,<,>=,<=,=' operators.

SELECT * FROM ProductReviews WHERE Id = 4
SELECT * FROM ProductReviews WHERE ReviewerEmail = '[email protected]'
SELECT * FROM ProductReviews WHERE DateCreated = '2024-08-22 08:47:21.0'

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

Products

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. The price filter may be used twice to specify a range. All other columns are processed client side.

Id supports the 'IN,NOT IN' operator.
Slug supports the '=' operator.
Type supports the 'IN,NOT IN' operator.
Status supports the 'IN,NOT IN' operator.
Sku supports the '=' operator.
Price supports the '<,>' operator.
OnSale supports the '=' operator.
TaxClass supports the '=' operator.
StockStatus supports the '=' operator.
ShippingClass supports the '=' operator.
ParentId supports the 'IN,NOT IN' operator.
Featured supports the '=' operator.
Virtual supports the '=' operator.
Downloadable supports the '=' operator.
DateCreated supports the '>,<,>=,<=,=' operator.
DateModified supports the '>,<,>=,<=,=' operator.

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
SELECT * FROM Products WHERE Downloadable = true
SELECT * FROM Products WHERE DateModified = '2024-08-22 08:47:21.0'

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	References	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.
PurchaseNote	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.

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.

ProductShippingClasses

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. Following columns are supported server side, all other columns are processed client side.

Id supports the '=,IN,NOT IN' operator.
Slug supports the '=' operator.
ProductId supports the '=' operator.

SELECT * FROM ProductShippingClasses WHERE Id = 4
SELECT * FROM ProductShippingClasses WHERE Id IN (4,5)
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	References	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.

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.

ProductTags

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. Following columns are supported server side, all other columns are processed client side.

Id supports the '=,IN,NOT IN' operator.
Slug supports the '=' operator.
Product supports the '=' operator.
HideEmpty supports the '=' operator.

SELECT * FROM ProductTags WHERE Id = 4
SELECT * FROM ProductTags WHERE Id IN (4,5)
SELECT * FROM ProductTags WHERE Id NOT IN (4,5)
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	References	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.

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.

ProductVariations

Retrieve and modify variations of products.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. The price filter may be used twice to specify a range. All other columns are processed client side.

Id supports the 'IN,NOT IN' operator.
Sku supports the '=' operator.
Price supports the '<,>' operator.
OnSale supports the '=' operator.
Status supports the '=' operator.
TaxClass supports the '=' operator.
StockStatus supports the '=' operator.
ProductId supports the '=' operator.
ParentId supports the 'IN,NOT IN' operator.
DateCreated supports the '>,<,>=,<=,=' operator.

SELECT * FROM ProductVariations WHERE Id = 4
SELECT * FROM ProductVariations WHERE Id IN (4,5)
SELECT * FROM ProductVariations WHERE Id NOT IN (4,5)
SELECT * FROM ProductVariations WHERE DateCreated = '2024-08-22 08:47:21.0'
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	References	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 [KEY]	Long	False	Products.Id	Id of the product.
ParentId	Long	False		The id of the parent, filtered server side.

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.

SettingOptions

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	References	Description
Id [KEY]	String	True		A unique identifier for the setting.
SettingId [KEY]	String	False	SettingGroups.Id	Id of the setting group.
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.
Options	String	True		Array of options (key value pairs) for inputs such as select, multiselect, and radio buttons.

ShippingZoneMethods

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	References	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.
SettingsTaxStatusOptions	String	True		Options under Tax Status.
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.
SettingsTypeOptions	String	True		Setting Type Options.
SettingsClassCostsId	String	True		A unique identifier for the setting.
SettingsClassCostsLabel	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostsDescription	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostsType	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostsDefault	String	True		Default value for the setting.
SettingsClassCostsPlaceholder	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostsTip	String	True		Additional help text shown to the user about the setting.
SettingsClassCostsValue	String	True		Setting value.
SettingsClassCostId29	String	True		A unique identifier for the setting.
SettingsClassCostLabel29	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription29	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType29	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault29	String	True		Default value for the setting.
SettingsClassCostPlaceholder29	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip29	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue29	String	True		Setting value.
SettingsClassCostId30	String	True		A unique identifier for the setting.
SettingsClassCostLabel30	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription30	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType30	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault30	String	True		Default value for the setting.
SettingsClassCostPlaceholder30	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip30	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue30	String	True		Setting value.
SettingsClassCostId31	String	True		A unique identifier for the setting.
SettingsClassCostLabel31	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription31	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType31	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault31	String	True		Default value for the setting.
SettingsClassCostPlaceholder31	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip31	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue31	String	True		Setting value.
SettingsClassCostId32	String	True		A unique identifier for the setting.
SettingsClassCostLabel32	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription32	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType32	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault32	String	True		Default value for the setting.
SettingsClassCostPlaceholder32	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip32	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue32	String	True		Setting value.
SettingsClassCostId33	String	True		A unique identifier for the setting.
SettingsClassCostLabel33	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription33	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType33	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault33	String	True		Default value for the setting.
SettingsClassCostPlaceholder33	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip33	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue33	String	True		Setting value.
SettingsClassCostId34	String	True		A unique identifier for the setting.
SettingsClassCostLabel34	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription34	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType34	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault34	String	True		Default value for the setting.
SettingsClassCostPlaceholder34	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip34	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue34	String	True		Setting value.
SettingsClassCostId54	String	True		A unique identifier for the setting.
SettingsClassCostLabel54	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription54	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType54	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault54	String	True		Default value for the setting.
SettingsClassCostPlaceholder54	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip54	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue54	String	True		Setting value.
SettingsClassCostId55	String	True		A unique identifier for the setting.
SettingsClassCostLabel55	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription55	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType55	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault55	String	True		Default value for the setting.
SettingsClassCostPlaceholder55	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip55	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue55	String	True		Setting value.
SettingsClassCostId56	String	True		A unique identifier for the setting.
SettingsClassCostLabel56	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription56	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType56	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault56	String	True		Default value for the setting.
SettingsClassCostPlaceholder56	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip56	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue56	String	True		Setting value.
SettingsClassCostId57	String	True		A unique identifier for the setting.
SettingsClassCostLabel57	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription57	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType57	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault57	String	True		Default value for the setting.
SettingsClassCostPlaceholder57	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip57	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue57	String	True		Setting value.
SettingsClassCostId58	String	True		A unique identifier for the setting.
SettingsClassCostLabel58	String	True		A human readable label for the setting used in interfaces.
SettingsClassCostDescription58	String	True		A human readable description for the setting used in interfaces.
SettingsClassCostType58	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsClassCostDefault58	String	True		Default value for the setting.
SettingsClassCostPlaceholder58	String	True		Placeholder text to be displayed in text inputs.
SettingsClassCostTip58	String	True		Additional help text shown to the user about the setting.
SettingsClassCostValue58	String	True		Setting value.
SettingsNoCostId	String	True		A unique identifier for the setting.
SettingsNoCostLabel	String	True		A human readable label for the setting used in interfaces.
SettingsNoCostDescription	String	True		A human readable description for the setting used in interfaces.
SettingsNoCostType	String	True		Type of setting. Options: text, email, number, color, password, textarea, select, multiselect, radio, image_width and checkbox.
SettingsNoCostDefault	String	True		Default value for the setting.
SettingsNoCostPlaceholder	String	True		Placeholder text to be displayed in text inputs.
SettingsNoCostTip	String	True		Additional help text shown to the user about the setting.
SettingsNoCostValue	String	True		Setting value.
ZoneId [KEY]	Long	False	ShippingZones.Id	Id of the shipping zone.

ShippingZones

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	References	Description
Id [KEY]	Long	True		Unique identifier for the resource.
Name	String	False		Shipping zone name.
Order	Integer	False		Shipping zone order.

SubscriptionNotes

Retrieves all subscription notes. This table shows data only if the WooCommerce Subscriptions Plugin is enabled.

Table Specific Information

Select

WooCommerce supports server-side filtering only on a limited set of columns in the SubscriptionNotes table. All other filters are applied client-side. The following columns support server-side filtering with the specified operators:

Id supports the '=' operator.
SubscriptionId supports the '=' operator.

If the SubscriptionId filter is not specified, the Cloud retrieves all subscriptions and performs an individual request for each related note. This can result in significantly slower queries.

The following example demonstrates a basic query with both filters:

-- Query a specific subscription note by ID and subscription
SELECT * FROM SubscriptionNotes WHERE Id = 4 AND SubscriptionId = 10

INSERT

The following columns are required when inserting records into the SubscriptionNotes table: SubscriptionId and Note.

-- Add a new note to a subscription
INSERT INTO SubscriptionNotes (Note, SubscriptionId) 
VALUES ('qwewq', 94)

Update

Update operations are not supported for the SubscriptionNotes table.

Delete

The following example demonstrates a basic delete operation using both columns:

-- Delete a note from a specific subscription
DELETE FROM SubscriptionNotes WHERE Id = 25 AND SubscriptionId = 45

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Unique identifier for the resource.
SubscriptionId [KEY]	Integer	False	Subscriptions.Id	Subscription ID.
AddedByUser	Boolean	False		If true, the note is attributed to the current user. If false, it is attributed to the system. The default value is false.
Author	String	True		Subscription note author.
CustomerNote	Boolean	False		If true, the note is shown to customers and they are notified. If false, the note is for admin reference only. The default value is false.
DateCreated	Datetime	True		The date the subscription note was created, in the site's timezone.
DateCreatedGmt	Datetime	True		The date the subscription note was created, as GMT.
Note	String	False		Subscription note.

Subscriptions

The Subscriptions table enables you to create, view, update, and delete individual, or a batch of subscriptions. This table shows data only if the WooCommerce Subscriptions Plugin is enabled.

Table Specific Information

Select

WooCommerce supports server-side filtering only on a limited set of columns in the Subscriptions table. All other filters are applied client-side. The following columns support server-side filtering with the specified operators:

Id supports the '=' operator.
ParentId supports the '=', '!=', 'IN', and 'NOT IN' operators.
CustomerId supports the '=' operator.
Status supports the '=' operator.
Product supports the '=' operator.

The following examples demonstrate queries processed server-side:

-- Filter by subscription ID
SELECT * FROM Subscriptions WHERE Id = 4

-- Filter by parent subscription
SELECT * FROM Subscriptions WHERE ParentId = 4

-- Exclude a specific parent
SELECT * FROM Subscriptions WHERE ParentId != 4

-- Match any of multiple parents
SELECT * FROM Subscriptions WHERE ParentId IN (4, 5)

-- Exclude multiple parents
SELECT * FROM Subscriptions WHERE ParentId NOT IN (4, 5)

-- Filter by status and customer
SELECT * FROM Subscriptions WHERE Status = 'pending' AND CustomerId = 501

-- Filter by product ID
SELECT * FROM Subscriptions WHERE Product = 501

Insert

No columns are required for basic insert operations on the Subscriptions table.

To insert values into aggregate columns such as LineItems, use a temporary table or pass the value as a JSON-formatted string.

-- Create a temporary table for line items
INSERT INTO SubscriptionLineItems#TEMP (ProductId, ReferenceNumber) VALUES (37, 1)

-- Insert a subscription referencing the temporary table
INSERT INTO Subscriptions#TEMP (CustomerId, LineItems, ReferenceNumber) 
VALUES ('109533', 'SubscriptionLineItems#TEMP', 1)

-- Commit the data to the Subscriptions table
INSERT INTO Subscriptions (CustomerId, LineItems) 
SELECT CustomerId, LineItems FROM Subscriptions#TEMP

Update

To update aggregate columns that contain object arrays, use a temporary table or a JSON-formatted string, as described above in the Insert section.

To add or update metadata, use the Metadata column. If a key does not exist, it is created. If it already exists, its value is updated.

-- Add or update metadata fields
UPDATE Subscriptions 
SET Metadata = 'key1:val1, key2:val2' 
WHERE Id = 120

Delete

Delete operations are not supported for the Subscriptions table.

To delete a subscription, use the DeleteSubscription stored procedure.

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Unique identifier for the resource.
ParentId	Integer	False		Parent/initial order ID for the subscription.
BillingAddress1	String	False		Billing Address line 1.
BillingAddress2	String	False		Billing Address line 2.
BillingCity	String	False		Billing City.
BillingCompany	String	False		Billing Company.
BillingCountry	String	False		Billing Country.
BillingEmail	String	False		Billing Email.
BillingFirstName	String	False		Billing First Name.
BillingLastName	String	False		Billing Last Name.
BillingPhone	String	False		Billing Phone.
BillingPostcode	String	False		Billing Postcode.
BillingState	String	False		Billing State.
BillingInterval	Integer	False		The number of billing periods between subscription renewals.
BillingPeriod	String	False		The subscription's billing period. The allowed values are day, week, month, year.
CancelledDateGmt	Datetime	False		The date the subscription was cancelled in GMT. In a write context, it must be the format YYYY-mm-dd H:i:s
CartTax	String	True		Sum of line item taxes only.
CouponLines	String	False		Coupons line data.
CreatedVia	String	True		Where the subscription was created.
Currency	String	False		Currency the subscription was created with, in ISO format.
CustomerId	Integer	False	Customers.Id	User ID who owns the subscription.
CustomerIpAddress	String	True		Customer's IP address.
CustomerNote	String	False		Note left by customer during checkout.
CustomerUserAgent	String	True		User agent of the customer.
DateCompleted	Datetime	True		The date the subscription was completed, in the site's timezone.
DateCompletedGmt	Datetime	False		The date the subscription was completed, as GMT.
DateCreated	Datetime	True		The date the subscription was created, in the site's timezone.
DateCreatedGmt	Datetime	False		The date the subscription was created, as GMT.
DateModified	Datetime	True		The date the subscription was last modified, in the site's timezone.
DateModifiedGmt	Datetime	False		The date the subscription was modified, as GMT.
DatePaid	Datetime	True		The date the subscription has been paid, in the site's timezone.
DatePaidGmt	Datetime	False		The date the subscription was paid, as GMT.
DiscountTax	String	True		Total discount tax amount for the subscription.
DiscountTotal	String	True		Total discount amount for the subscription.
EndDateGmt	Datetime	False		The subscription's trial end date in GMT.
FeeLines	String	False		Fee lines data.
IsEditable	Boolean	True		Is editable.
LastPaymentDateGmt	Datetime	False		The subscription's last parent, renewal or switch order date in GMT.
LineItems	String	False		Line items data.
MetaData	String	False		Meta data.
NeedsPayment	Boolean	True		Needs payment.
NeedsProcessing	Boolean	True		Needs processing.
NextPaymentDateGmt	Datetime	False		The subscription's next payment date in GMT.
Number	String	False		The subscription's number.
OrderKey	String	True		Order key.
PaymentMethod	String	False		Payment method ID.
PaymentMethodTitle	String	False		Payment method title.
PaymentRetryDateGmt	Datetime	True		The subscription's payment retry date in GMT.
PaymentUrl	String	True		Payment URL.
PricesIncludeTax	Boolean	True		Shows if the prices included tax during checkout.
RemovedLineItems	String	True		Item data for items removed by the customer from their my account page.
RequiresManualRenewal	Boolean	True		Requires manual renewal.
ResubscribedFrom	String	True		The ID of the subscription this subscription was resubscribed from.
ResubscribedSubscription	String	True		The ID of the new resubscribed subscription.
ShippingAddress1	String	False		Address line 1.
ShippingAddress2	String	False		Address line 2.
ShippingCity	String	False		City name.
ShippingCompany	String	False		Company name.
ShippingCountry	String	False		Country code in ISO 3166-1 alpha-2 format.
ShippingFirstName	String	False		First name.
ShippingLastName	String	False		Last name.
ShippingPhone	String	False		Phone number.
ShippingPostcode	String	False		Postal code.
ShippingState	String	False		ISO code or name of the state, province or district.
ShippingLines	String	False		Shipping lines data.
ShippingTax	String	False		Total shipping tax amount for the subscription.
ShippingTotal	String	False		Total shipping amount for the subscription.
StartDateGmt	Datetime	True		The subscription's start date in GMT.
Status	String	False		Subscription status. The default value is pending.
SuspensionCount	Integer	True		Suspension count.
TaxLines	String	True		Tax lines data.
Total	String	True		Grand total.
TotalTax	String	True		Sum of all taxes.
TrialEndDateGmt	Datetime	False		The subscription's trial end date in GMT.
Version	String	True		Version of WooCommerce when the subscription was made.

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
TransitionStatus	String	The subscription status to transition to. This will work only for Insert.
PaymentDetails	String	Payment method data. This will work only for Update and Insert.
Product	String	Limit result set to subscriptions assigned a specific product ID.
ReferenceNumber	String	This column will be used in Bulk operations to get specific values from the Temp tables.

TaxClasses

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	References	Description
Slug [KEY]	String	True		Unique identifier for the resource.
Name	String	False		Tax class name. required

TaxRates

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. Following columns are supported server side, all other columns are processed client side.

Id supports the '= ' operator.
Class supports the '=' operator.
Priority supports the 'ORDER BY' operator.
Orders supports the 'ORDER BY' operator.

SELECT * FROM TaxRates WHERE Id = 4
SELECT * FROM TaxRates order by Priority
SELECT * FROM TaxRates order By Orders

Insert

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

Columns


Name	Type	ReadOnly	References	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.
Orders	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.

Webhooks

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. Following columns are supported server side, all other columns are processed client side.

Id supports the '=,IN,NOT IN ' operator.
Status supports the '=' operator.
DateCreated supports the '>,<,>=,<=,=' operator.

SELECT * FROM Webhooks WHERE Id = 4
SELECT * FROM Webhooks WHERE Id IN (4,5)
SELECT * FROM Webhooks WHERE DateCreated = '2024-08-22 08:47:21.0'

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	References	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	False		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

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.
CustomerDownloads	Retrieves the permissions required for customer downloads.
CustomerMetadata	Retrieve the metadata for each customer.
Data	Retrieves a list of available data endpoints.
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.
Refunds	Retrieve and view refunds from your store, regardless of which order they are associated with.
Reports	Retrieves a list of available reports.
SalesReport	Executes a Sales report.
SettingGroups	Retrieve settings groups.
ShippingLocations	Retrieve shipping zone locations.
ShippingMethods	Retrieve shipping methods.
States	Retrieve shipping states.
SubscriptionCouponLines	Retrieves all subscription coupon lines. This view shows data only if the WooCommerce Subscriptions Plugin is enabled.
SubscriptionFeeLines	Retrieves all subscription fee lines. This view shows data only if the WooCommerce Subscriptions Plugin is enabled.
SubscriptionLineItems	Retrieves all subscription line items. This view shows data only if the WooCommerce Subscriptions Plugin is enabled.
SubscriptionOrders	Retrieves all subscription orders. This view shows data only if the WooCommerce Subscriptions Plugin is enabled.
SubscriptionShippingLines	Retrieves all subscription shipping lines. This view shows data only if the WooCommerce Subscriptions Plugin is enabled.
SystemStatus	Retrieves 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.

Continents

Retrieve continents.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. Following columns are supported server side, all other columns are processed client side.

Code supports the '=' operator.

SELECT * FROM Continents
SELECT * FROM Continents WHERE Code = 'AF'

Columns


Name	Type	References	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

Countries

Retrieve countries.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. Following columns are supported server side, all other columns are processed client side.

Code supports the '=' operator.

SELECT * FROM Countries
SELECT * FROM Countries WHERE Code = 'AF'

Columns


Name	Type	References	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

Currencies

Retrieve available currencies.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. Following columns are supported server side, all other columns are processed client side.

Code supports the '=' operator.

SELECT * FROM Currencies
SELECT * FROM Currencies WHERE Code = 'AF'

Columns


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

CustomerDownloads

Retrieves the permissions required for customer downloads.

Table Specific Information

Select

Only a limited number of columns can be used in the WHERE clause of a SELECT query. These columns support only the = comparison, unless otherwise specified. The available column for this table is CustomerId. All other columns are processed client-side. If the 'CustomerId' filter is not specified, the Cloud fetches a list of all the customers and sends a request for each customer in that list.

SELECT * FROM CustomerDownloads where CustomerId=6

Columns


Name	Type	References	Description
DownloadId	String		Download Id (MD5).
DownloadName	String		Downloadable file name.
DownloadUrl	String		Download file URL.
DownloadsRemaining	String		Number of downloads remaining.
File	String		URL of the file in the downloadable product.
FileName	String		Name of the file in the downloadable product.
OrderId	Long	Orders.Id	Order Id.
OrderKey	String		Order key.
ProductId	Long	Products.Id	Product Id.
ProductName	String		Product name
LinksCollectionAggregate	String		A collection of links for all downloads by this customer.
LinksOrderAggregate	String		A collection of links to fetch the order details.
LinksProductAggregate	String		A collection of links to fetch the product details.
AccessExpires	String		The date when download access expires, in the site's timezone.
AccessExpiresGmt	String		The date when download access expires, as GMT.
CustomerId	Long	Customers.Id	Unique identifier for the Customer.

CustomerMetadata

Retrieve the metadata for each customer.

Table Specific Information

Select

WooCommerce allows only a small subset of columns to be used in the WHERE clause of a SELECT query. Following columns are supported server side, all other columns are processed client side.

CustomerId supports the '=,IN,NOT IN,ORDER BY' operators.

SELECT * FROM Categories WHERE CustomerId = 294
SELECT * FROM Categories WHERE CustomerId IN (294,295)
SELECT * FROM Categories WHERE CustomerId NOT IN (294,295)
SELECT * FROM Categories ORDER BY CustomerId

Columns


Name	Type	References	Description
Id [KEY]	String		Metadata ID.
Key	String		Metadata key.
Value	String		Metadata value.
CustomerId	String	Customers.Id	Id of the customer.

Data

Retrieves a list of available data endpoints.

Table Specific Information

Select

All the columns are processed client side.

SELECT * FROM Data

Columns


Name	Type	References	Description
Slug [KEY]	String		An alphanumeric identifier for the resource.
Description	String		Description of the data source.
LinksCollectionAggregate	String		A collection of links for all data sources.
LinksSelfAggregate	String		Links corresponding to this data source.

OrderCouponLines

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	References	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 [KEY]	String	Orders.Id	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.

OrderFeeLines

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	References	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 [KEY]	String	Orders.Id	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.

OrderRefundLineItems

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
SELECT * FROM OrderRefundLineItems WHERE OrderId = 16
SELECT * FROM OrderRefundLineItems WHERE RefundId = 6
SELECT * FROM OrderRefundLineItems where OrderId IN (12,13) and RefundId IN(5,6)

Columns


Name	Type	References	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	Orders.Id	Id of the order.
RefundId	String	OrderRefunds.Id	Id of the refund.

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.
RefundTotal	String		The amount to refund for this line item, excluding taxes.

OrderShippingLines

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	References	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 [KEY]	Long	Orders.Id	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.

OrderTaxLines

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	References	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 [KEY]	Long	Orders.Id	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.

ProductAttributes

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	References	Description
Id [KEY]	Long		Attribute ID.
Name	String		Attribute name.
Slug	String		An alphanumeric identifier for the resource unique to its type.
Position	Integer		Position of the product attribute.
Variation	Boolean		If there's variation of the attribute available.
Visible	Boolean		If the attribute is visible.
OptionsAggregate	String		The array of options for the attribute.
ProductId [KEY]	Long	Products.Id	Id of the product.

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.

ProductCategories

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	References	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 [KEY]	Long	Products.Id	Id of the product.

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.

ProductImages

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. Following columns are supported server side, all other columns are processed client side. If the 'ProductId' filter is not specified, the Cloud will fetch a list of all the attributes and perform a request for each attribute in the list.

Id supports the '=' operator.
Name supports the '=' operator.
Src supports the '=' operator.
Alt supports the '=' operator.
ProductId supports the '=,IN,NOT IN' operator.
DateCreated supports the '>,<,>=,<=,=' operators.
DateModified supports the '>,<,>=,<=,=' operators.

SELECT * FROM ProductImages WHERE Id = 4
SELECT * FROM ProductImages WHERE ProductId = 6
SELECT * FROM ProductImages WHERE DateCreated = '2024-08-22 08:47:21.0'
SELECT * FROM ProductImages WHERE DateModified = '2024-08-22 08:47:21.0'

Columns


Name	Type	References	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 [KEY]	Long	Products.Id	Id of the product.

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.

Refunds

Retrieve and view refunds from your store, regardless of which order they are associated with.

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 the =, !=, IN, and NOT IN comparisons unless specified otherwise. The available columns for this table are Id and parentId. The Id column also supports Order by. All other columns are processed client-side.

SELECT * FROM Refunds ORDER BY Id
SELECT * FROM Refunds WHERE Id = 2592
SELECT * FROM Refunds WHERE Id IN (2592,45)
Select * from Refunds where parentid !=2102
Select * from Refunds where parentid NOT IN (12,2102)

Columns


Name	Type	References	Description
Id [KEY]	Long		Unique identifier for the resource.
Amount	Decimal		The total refund amount
DateCreated	Datetime		The date the order refund was created, in the site's timezone.
DateCreatedGMT	Datetime		The date the order refund was created, as GMT.
Reason	String		Reason for refund.
RefundedBy	Long		User ID of user who created the refund.
RefundedPayment	Boolean		If the payment was refunded via the API.
ParentId	Long	Orders.Id	The ID of the order the refund is associated with.
LinksCollectionAggregate	String		A collection of links for all refunds.
LinksSelfAggregate	String		Links corresponding to this refund.
LinksUpAggregate	String		Links corresponding to the parent order details.
FeeLinesAggregate	String		Fee lines data for the refund.
LineItemsAggregate	String		Line items data for the refund.
MetaDataAggregate	String		Metadata for the order refund.
ShippingLines	String		Shipping lines data for the order refund.
TaxLinesAggregate	String		Tax lines data for the order refund.

Reports

Retrieves a list of available reports.

Table Specific Information

Select

All the columns are processed client side.

SELECT * FROM Reports

Columns


Name	Type	References	Description
Slug [KEY]	String		An alphanumeric identifier for the resource.
Description	String		Description of the data source.
LinksCollectionAggregate	String		A collection of links for all reports.
LinksSelfAggregate	String		Links corresponding to this report.

SalesReport

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

SettingGroups

Retrieve settings groups.

Table Specific Information

Select

The Cloud will process all filters client side.

Columns


Name	Type	References	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.
SubGroups	String		ID for settings sub groups.

ShippingLocations

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	References	Description
Code	String		Shipping zone location code.
Type	String		Shipping zone location type. Options: postcode, state, country and continent. Defaults to country.
ZoneId	Long	ShippingZones.Id	Id of the shipping zone.

ShippingMethods

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	References	Description
Id [KEY]	String		Method ID.
Title	String		Shipping method title.
Description	String		Shipping method description.

States

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	References	Description
Country	String		State code.
Code	String		State code.
Name	String		Full name of the state.
CountryName	String		Full name of the country.

SubscriptionCouponLines

Retrieves all subscription coupon lines. This view shows data only if the WooCommerce Subscriptions Plugin is enabled.

View-Specific Information

Select

WooCommerce supports server-side filtering only on a limited set of columns in the SubscriptionCouponLines view. All other filters are applied client-side. The following columns support server-side filtering with the specified operators:

SubscriptionId supports the '=' operator.
ParentId supports the '=', '!=', 'IN', and 'NOT IN' operators.
CustomerId supports the '=' operator.
Status supports the '=' operator.
Product supports the '=' operator.

The following examples demonstrate basic queries using supported filters:

-- Query by subscription ID
SELECT * FROM SubscriptionCouponLines WHERE SubscriptionId = 4

-- Query by parent subscription
SELECT * FROM SubscriptionCouponLines WHERE ParentId = 4

-- Query excluding a parent subscription
SELECT * FROM SubscriptionCouponLines WHERE ParentId != 4

-- Query using a list of parent subscriptions
SELECT * FROM SubscriptionCouponLines WHERE ParentId IN (4, 5)

-- Query excluding a list of parent subscriptions
SELECT * FROM SubscriptionCouponLines WHERE ParentId NOT IN (4, 5)

-- Filter by subscription status and customer
SELECT * FROM SubscriptionCouponLines WHERE Status = 'pending' AND CustomerId = 501

-- Filter by product
SELECT * FROM SubscriptionCouponLines WHERE Product = 101

Columns


Name	Type	References	Description
Id [KEY]	Integer		Item ID.
SubscriptionId	Integer	Subscriptions.Id	Subscription ID.
Code	String		Coupon code.
MetaData	String		Meta data.
Discount	String		Discount total.
DiscountTax	String		Discount total tax.
ParentId	Integer		Parent/initial order ID for the subscription.
CustomerId	Integer	Customers.Id	User ID who owns the subscription.
Status	String		Subscription status. The default value is pending.

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
Product	String		Limit result set to subscriptions assigned a specific product ID.
ReferenceNumber	String		This column will be used in Bulk operations to get specific values from the Temp tables.

SubscriptionFeeLines

Retrieves all subscription fee lines. This view shows data only if the WooCommerce Subscriptions Plugin is enabled.

View-Specific Information

Select

WooCommerce supports server-side filtering only on a limited set of columns in the SubscriptionFeeLines view. All other filters are applied client-side. The following columns support server-side filtering with the specified operators:

SubscriptionId supports the '=' operator.
ParentId supports the '=', '!=', 'IN', and 'NOT IN' operators.
CustomerId supports the '=' operator.
Status supports the '=' operator.
Product supports the '=' operator.

The following examples demonstrate basic queries using supported filters:

-- Query by subscription ID
SELECT * FROM SubscriptionFeeLines WHERE SubscriptionId = 4

-- Query by parent subscription
SELECT * FROM SubscriptionFeeLines WHERE ParentId = 4

-- Query excluding a parent subscription
SELECT * FROM SubscriptionFeeLines WHERE ParentId != 4

-- Query using a list of parent subscriptions
SELECT * FROM SubscriptionFeeLines WHERE ParentId IN (4, 5)

-- Query excluding a list of parent subscriptions
SELECT * FROM SubscriptionFeeLines WHERE ParentId NOT IN (4, 5)

-- Filter by subscription status and customer
SELECT * FROM SubscriptionFeeLines WHERE Status = 'pending' AND CustomerId = 501

-- Filter by product
SELECT * FROM SubscriptionFeeLines WHERE Product = 101

Columns


Name	Type	References	Description
Id	Integer		Item ID.
SubscriptionId	Integer	Subscriptions.Id	Subscription ID.
Amount	String		Fee amount.
MetaData	String		Meta data.
Name	String		Fee name.
TaxClass	String		Tax class.
TaxStatus	String		Specifies whether the fee is taxable. Valid values are 'taxable', 'none', and 'inherit'.
Taxes	String		Line taxes.
Total	String		Line total (after discounts).
TotalTax	String		Line total tax (after discounts).
ParentId	Integer		Parent/initial order ID for the subscription.
CustomerId	Integer	Customers.Id	User ID who owns the subscription.
Status	String		Subscription status. The default value is pending.

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
Product	String		Limit result set to subscriptions assigned a specific product ID.
ReferenceNumber	String		This column will be used in Bulk operations to get specific values from the Temp tables.

SubscriptionLineItems

Retrieves all subscription line items. This view shows data only if the WooCommerce Subscriptions Plugin is enabled.

View-Specific Information

Select

WooCommerce supports server-side filtering only on a limited set of columns in the SubscriptionLineItems view. All other filters are applied client-side. The following columns support server-side filtering with the specified operators:

SubscriptionId supports the '=' operator.
ParentId supports the '=', '!=', 'IN', and 'NOT IN' operators.
CustomerId supports the '=' operator.
Status supports the '=' operator.
Product supports the '=' operator.

The following examples demonstrate basic queries using supported filters:

-- Query by subscription ID
SELECT * FROM SubscriptionLineItems WHERE SubscriptionId = 4

-- Query by parent subscription
SELECT * FROM SubscriptionLineItems WHERE ParentId = 4

-- Query excluding a parent subscription
SELECT * FROM SubscriptionLineItems WHERE ParentId != 4

-- Query using a list of parent subscriptions
SELECT * FROM SubscriptionLineItems WHERE ParentId IN (4, 5)

-- Query excluding a list of parent subscriptions
SELECT * FROM SubscriptionLineItems WHERE ParentId NOT IN (4, 5)

-- Filter by subscription status and customer
SELECT * FROM SubscriptionLineItems WHERE Status = 'pending' AND CustomerId = 501

-- Filter by product
SELECT * FROM SubscriptionLineItems WHERE Product = 101

Columns


Name	Type	References	Description
Id [KEY]	Integer		Item ID.
SubscriptionId	Integer	Subscriptions.Id	Subscription ID.
ImageId	String		Image ID.
ImageSrc	String		Image Source.
MetaData	String		Meta data.
Name	String		Product name.
ParentName	String		Parent product name if the product is a variation.
Price	Integer		Product price.
ProductId	Integer		Product ID.
Quantity	Integer		Quantity ordered.
Sku	String		Product SKU.
Subtotal	String		Line subtotal (before discounts).
SubtotalTax	String		Line subtotal tax (before discounts).
TaxClass	String		Slug of the tax class of product.
Taxes	String		Line taxes.
Total	String		Line total (after discounts).
TotalTax	String		Line total tax (after discounts).
VariationId	Integer		Variation ID, if applicable.
ParentId	Integer		Parent/initial order ID for the subscription.
CustomerId	Integer	Customers.Id	User ID who owns the subscription.
Status	String		Subscription status. The default value is pending.

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
Product	String		Limit result set to subscriptions assigned a specific product ID.
ReferenceNumber	String		This column will be used in Bulk operations to get specific values from the Temp tables.

SubscriptionOrders

Retrieves all subscription orders. This view shows data only if the WooCommerce Subscriptions Plugin is enabled.

View-Specific Information

Select

WooCommerce supports server-side filtering only on a limited set of columns in the SubscriptionShippingLines view. All other filters are applied client-side. The following column supports server-side filtering with the specified operator:

SubscriptionId supports the '=' operator.

The following example demonstrates a basic query using a supported filter:

-- Query shipping lines for a specific subscription
SELECT * FROM SubscriptionShippingLines WHERE SubscriptionId = 4

Columns


Name	Type	References	Description
Id [KEY]	Integer		Unique identifier for the resource.
SubscriptionId	Integer	Subscriptions.Id	Subscription ID.
BillingAddress1	String		Billing Address line 1.
BillingAddress2	String		Billing Address line 2.
BillingCity	String		Billing City name.
BillingCompany	String		Billing Company name.
BillingCountry	String		Billing Country code in ISO 3166-1 alpha-2 format.
BillingEmail	String		Billing Email address.
BillingFirstName	String		Billing First name.
BillingLastName	String		Billing Last name.
BillingPhone	String		Billing Phone number.
BillingPostcode	String		Billing Postal code.
BillingState	String		ISO code or name of the state, province or district.
CartHash	String		MD5 hash of cart items to ensure orders are not modified.
CartTax	String		Sum of line item taxes only.
CouponLines	String		Coupons line data.
CreatedVia	String		Shows where the order was created.
Currency	String		Currency used to create the order, in ISO format (such as USD). Defaults to the current store currency.
CustomerId	Integer		User ID who owns the order. Use 0 for guests. Default is 0.
CustomerIpAddress	String		Customer's IP address.
CustomerNote	String		Note left by customer during checkout.
CustomerUserAgent	String		User agent of the customer.
DateCompleted	Datetime		The date the order was completed, in the site's timezone.
DateCompletedGmt	Datetime		The date the order was completed, as GMT.
DateCreated	Datetime		The date the order was created, in the site's timezone.
DateCreatedGmt	Datetime		The date the order was created, as GMT.
DateModified	Datetime		The date the order was last modified, in the site's timezone.
DateModifiedGmt	Datetime		The date the order was modified, as GMT.
DatePaid	Datetime		The date the order has been paid, in the site's timezone.
DatePaidGmt	Datetime		The date the order was paid, as GMT.
DiscountTax	String		Total discount tax amount for the order.
DiscountTotal	String		Total discount amount for the order.
FeeLines	String		Fee lines data.
IsEditable	Boolean		Is editable.
LineItems	String		Line items data.
MetaData	String		Meta data.
NeedsPayment	Boolean		Needs Payment.
NeedsProcessing	Boolean		Needs Processing
Number	String		The order's number.
OrderKey	String		Order key.
OrderType	String		Order type.
ParentId	Integer		Parent order ID.
PaymentMethod	String		Payment method ID.
PaymentMethodTitle	String		Payment method title.
PaymentUrl	String		Payment Url.
PricesIncludeTax	Boolean		Shows if the prices included tax during checkout.
ShippingAddress1	String		Shipping Address line 1.
ShippingAddress2	String		Shipping Address line 2.
ShippingCity	String		Shipping City name.
ShippingCompany	String		Shipping Company name.
ShippingCountry	String		Shipping Country code in ISO 3166-1 alpha-2 format.
ShippingFirstName	String		Shipping First name.
ShippingLastName	String		Shipping Last name.
ShippingPhone	String		Shipping Phone
ShippingPostcode	String		Shipping Postal code.
ShippingState	String		ISO code or name of the state, province or district.
ShippingLines	String		Shipping lines data.
ShippingTax	String		Total shipping tax amount for the order.
ShippingTotal	String		Total shipping amount for the order.
Status	String		Order status. The default value is pending.
TaxLines	String		Tax lines data.
Total	String		Grand total.
TotalTax	String		Sum of all taxes.
TransactionId	String		Unique transaction ID.
Version	String		Version of WooCommerce when the order was made.

SubscriptionShippingLines

Retrieves all subscription shipping lines. This view shows data only if the WooCommerce Subscriptions Plugin is enabled.

View-Specific Information

Select

WooCommerce supports server-side filtering only on a limited set of columns in the SubscriptionShippingLines view. All other filters are applied client-side. The following columns support server-side filtering with the specified operators:

SubscriptionId supports the '=' operator.
ParentId supports the '=', '!=', 'IN', and 'NOT IN' operators.
CustomerId supports the '=' operator.
Status supports the '=' operator.
Product supports the '=' operator.

The following examples demonstrate basic queries using supported filters:

-- Query by subscription ID
SELECT * FROM SubscriptionShippingLines WHERE SubscriptionId = 4

-- Query by parent subscription
SELECT * FROM SubscriptionShippingLines WHERE ParentId = 4

-- Query excluding a parent subscription
SELECT * FROM SubscriptionShippingLines WHERE ParentId != 4

-- Query using a list of parent subscriptions
SELECT * FROM SubscriptionShippingLines WHERE ParentId IN (4, 5)

-- Query excluding a list of parent subscriptions
SELECT * FROM SubscriptionShippingLines WHERE ParentId NOT IN (4, 5)

-- Filter by subscription status and customer
SELECT * FROM SubscriptionShippingLines WHERE Status = 'pending' AND CustomerId = 501

-- Filter by product
SELECT * FROM SubscriptionShippingLines WHERE Product = 101

Columns


Name	Type	References	Description
Id [KEY]	Integer		Item ID.
SubscriptionId	Integer	Subscriptions.Id	Subscription ID.
InstanceId	String		Shipping instance ID.
MetaData	String		Meta data.
MethodId	String		Shipping method ID.
MethodTitle	String		Shipping method name.
TaxStatus	String		Tax status.
Taxes	String		Line taxes.
Total	String		Line total (after discounts).
TotalTax	String		Line total tax (after discounts).
ParentId	Integer		Parent/initial order ID for the subscription.
CustomerId	Integer	Customers.Id	User ID who owns the subscription.
Status	String		Subscription status. The default value is pending.

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
Product	String		Limit result set to subscriptions assigned a specific product ID.
ReferenceNumber	String		This column will be used in Bulk operations to get specific values from the Temp tables.

SystemStatus

Retrieves system status items.

View-Specific Information

Select

The SystemStatus view represents a singleton entity. When queried, it returns a single row with system status and environment configuration details from the WooCommerce store.

Filters are not supported on this view. SELECT queries return the full set of columns in a single response.

The following example demonstrates a basic query:

-- Retrieve current WooCommerce system status
SELECT * FROM SystemStatus

Columns


Name	Type	References	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
SubscriptionsCacheUpdateFailures	Integer		The number of times report cache updates have failed.
SubscriptionsLiveUrl	String		The URL Subscriptions considers to be the site's live URL.
SubscriptionsMode	String		Whether the site is in 'live' or 'staging' mode.
SubscriptionsPaymentGatewayFeatureSupport	String		A breakdown of the features supported by the active payment gateways.
SubscriptionsReportCacheEnabled	Boolean		Whether the report caches are enabled.
SubscriptionsStatusesAutoDraft	String		Auto Draft Status.
SubscriptionsStatusesTrash	String		Trash Status.
SubscriptionsStatusesWcActive	String		Active Status.
SubscriptionsStatusesWcCancelled	String		Cancelled Status.
SubscriptionsStatusesWcOnHold	String		On hold Status.
SubscriptionsStatusesWcPending	String		Pending Status.
SubscriptionsStatusesWcPendingCancel	String		Pending Cancel Status.
SubscriptionsSubscriptionsByPaymentGateway	String		A breakdown of subscriptions by the gateway and their status.
SubscriptionsWcsDebug	Boolean		Is WC Subscriptions debugging mode active?
InactivePlugginsAggregate	String		Inactive Plugins
PostTypeCountsAggregate	String		Post type counts
LoggingEnabled	Boolean		If the logging is enabled.
LogDefaultHandler	String		The default handler location for the log.
LogRetentionPeriodDays	Integer		Log retention period in days.
LogLevelThreshold	String		The level threshold set for the logs.
LogDirectorySize	String		Log size in KB.

SystemStatusPages

Retrieve and system status items.

Table Specific Information

Select

The Cloud will process all filters client side.

Columns


Name	Type	References	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.
PagesBlock	String		The modular section of the page.
PagesBlockPresent	Boolean		If the page block is present.
PagesBlockRequired	Boolean		If the page is required.

SystemStatusTools

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

TopSellersReports

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

TotalsReport

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

Stored Procedures

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
DeleteSubscription	Delete a subscription.
GetStatuses	Get all the status values.
RunSystemStatusTool	Run the system status tool specified by the id.
UpdateShippingZoneLocations	Update the locations for a shipping zone.

DeleteSubscription

Delete a subscription.

Input


Name	Type	Required	Description
SubscriptionId	String	True	The ID of subscription.
Force	String	False	Use true to permanently delete the subscription. Default is false.

Result Set Columns


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

GetStatuses

Get all the status values.

Result Set Columns


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

RunSystemStatusTool

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.

UpdateShippingZoneLocations

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.

System Tables

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.

sys_catalogs

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.

sys_schemas

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.

sys_tables

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.

sys_tablecolumns

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.
ColumnType	String	The role or classification of the column in the schema. Possible values include SYSTEM, LINKEDCOLUMN, NAVIGATIONKEY, REFERENCECOLUMN, and NAVIGATIONPARENTCOLUMN.

sys_procedures

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.

sys_procedureparameters

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

To include result set columns in addition to the parameters, set the IncludeResultColumns pseudo column to True:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'RunSystemStatusTool' AND IncludeResultColumns='True'

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.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
DataTypeName	String	The name of the data type.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric 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.
Values	String	The values you can set in this parameter are limited to those shown in this column. Possible values are comma-separated.
SupportsStreams	Boolean	Whether the parameter represents a file that you can pass as either a file path or a stream.
IsPath	Boolean	Whether the parameter is a target path for a schema creation operation.
Default	String	The value used for this parameter when no value is specified.
SpecificName	String	A label that, when multiple stored procedures have the same name, uniquely identifies each identically-named stored procedure. If there's only one procedure with a given name, its name is simply reflected here.
IsCDataProvided	Boolean	Whether the procedure is added/implemented by CData, as opposed to being a native WooCommerce procedure.

Pseudo-Columns


Name	Type	Description
IncludeResultColumns	Boolean	Whether the output should include columns from the result set in addition to parameters. Defaults to False.

sys_keycolumns

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.

sys_foreignkeys

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.

sys_primarykeys

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.

sys_indexes

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.

sys_connection_props

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

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.

sys_sqlinfo

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.

sys_identity

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.

sys_information

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information

Columns


Name	Type	Description
Product	String	The name of the product.
Version	String	The version number of the product.
Datasource	String	The name of the datasource the product connects to.
NodeId	String	The unique identifier of the machine where the product is installed.
HelpURL	String	The URL to the product's help documentation.
License	String	The license information for the product. (If this information is not available, the field may be left blank or marked as 'N/A'.)
Location	String	The file path location where the product's library is stored.
Environment	String	The version of the environment or rumtine the product is currently running under.
DataSyncVersion	String	The tier of CData Sync required to use this connector.
DataSyncCategory	String	The category of CData Sync functionality (e.g., Source, Destination).

Connection String Options

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	Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.
OAuthClientSecret	Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server. (Custom OAuth applications only.).
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.
Scope	Specifies the scope of the authenticating user's access to the application, to ensure they get appropriate access to data. If a custom OAuth application is needed, this is generally specified at the time the application is created.

SSL

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

Logging

Property	Description
Verbosity	Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Schema

Property	Description
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Miscellaneous

Property	Description
MaxRows	Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.
Pagesize	Specifies the maximum number of records per page the provider returns when requesting data from WooCommerce.
PseudoColumns	Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.
RowScanDepth	The maximum number of rows to scan to look for the columns available in a table.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.

Authentication

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.

AuthScheme

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

Possible Values

Basic, OAuth, OAuthOneLegged

Data Type

string

Default Value

"Basic"

Remarks

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.

URL

The URL of the WooCommerce instance.

Data Type

string

Default Value

""

Remarks

The URL of the WooCommerce instance.

OAuth

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

Property	Description
OAuthClientId	Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.
OAuthClientSecret	Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server. (Custom OAuth applications only.).
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.
Scope	Specifies the scope of the authenticating user's access to the application, to ensure they get appropriate access to data. If a custom OAuth application is needed, this is generally specified at the time the application is created.

OAuthClientId

Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.

Data Type

string

Default Value

""

Remarks

This property is required in two cases:

When using a custom OAuth application, such as in web-based authentication flows, service-based authentication, or certificate-based flows that require application registration.
If the driver does not provide embedded OAuth credentials.

(When the driver provides embedded OAuth credentials, this value may already be provided by the Cloud and thus not require manual entry.)

OAuthClientId is generally used alongside other OAuth-related properties such as OAuthClientSecret and OAuthSettingsLocation when configuring an authenticated connection.

OAuthClientId is one of the key connection parameters that need to be set before users can authenticate via OAuth. You can usually find this value in your identity provider’s application registration settings. Look for a field labeled Client ID, Application ID, or Consumer Key.

While the client ID is not considered a confidential value like a client secret, it is still part of your application's identity and should be handled carefully. Avoid exposing it in public repositories or shared configuration files.

For more information on how this property is used when configuring a connection, see Establishing a Connection.

OAuthClientSecret

Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server. (Custom OAuth applications only.).

Data Type

string

Default Value

""

Remarks

This property (sometimes called the application secret or consumer secret) is required when using a custom OAuth application in any flow that requires secure client authentication, such as web-based OAuth, service-based connections, or certificate-based authorization flows. It is not required when using an embedded OAuth application.

The client secret is used during the token exchange step of the OAuth flow, when the driver requests an access token from the authorization server. If this value is missing or incorrect, authentication fails with either an invalid_client or an unauthorized_client error.

OAuthClientSecret is one of the key connection parameters that need to be set before users can authenticate via OAuth. You can obtain this value from your identity provider when registering the OAuth application.

Notes:

This value should be stored securely and never exposed in public repositories, scripts, or unsecured environments.
Client secrets may also expire after a set period. Be sure to monitor expiration dates and rotate secrets as needed to maintain uninterrupted access.

For more information on how this property is used when configuring a connection, see Establishing a Connection

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.

Data Type

string

Default Value

""

Remarks

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

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.

Data Type

string

Default Value

""

Remarks

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

Scope

Specifies the scope of the authenticating user's access to the application, to ensure they get appropriate access to data. If a custom OAuth application is needed, this is generally specified at the time the application is created.

Data Type

string

Default Value

""

Remarks

Scopes are set to define what kind of access the authenticating user will have; for example, read, read and write, restricted access to sensitive information. System administrators can use scopes to selectively enable access by functionality or security clearance.

When InitiateOAuth is set to GETANDREFRESH, you must use this property if you want to change which scopes are requested.

When InitiateOAuth is set to either REFRESH or OFF, you can change which scopes are requested using either this property or the Scope input.

SSL

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

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

SSLServerCert

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

Data Type

string

Default Value

""

Remarks

If you are using a TLS/SSL connection, use this property to specify the TLS/SSL certificate to be accepted from the server. If you specify a value for this property, all other certificates that are not trusted by the machine are 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`

Note: It is possible to use '*' to signify that all certificates should be accepted, but due to security concerns this is not recommended.

Logging

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

Property	Description
Verbosity	Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Verbosity

Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Data Type

string

Default Value

"1"

Remarks

This property defines the level of detail the Cloud includes in the log file. Higher verbosity levels increase the detail of the logged information, but may also result in larger log files and slower performance due to the additional data being captured.

The default verbosity level is 1, which is recommended for regular operation. Higher verbosity levels are primarily intended for debugging purposes. For more information on each level, refer to Logging.

When combined with the LogModules property, Verbosity can refine logging to specific categories of information.

Schema

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

Property	Description
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

BrowsableSchemas

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Data Type

string

Default Value

""

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

Miscellaneous

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

Property	Description
MaxRows	Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.
Pagesize	Specifies the maximum number of records per page the provider returns when requesting data from WooCommerce.
PseudoColumns	Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.
RowScanDepth	The maximum number of rows to scan to look for the columns available in a table.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.

MaxRows

Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.

Data Type

int

Default Value

-1

Remarks

The default value for this property, -1, means that no row limit is enforced unless the query explicitly includes a LIMIT clause. (When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting.)

Setting MaxRows to a whole number greater than 0 ensures that queries do not return excessively large result sets by default.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

Pagesize

Specifies the maximum number of records per page the provider returns when requesting data from WooCommerce.

Data Type

int

Default Value

100

Remarks

When processing a query, instead of requesting all of the queried data at once from WooCommerce, the Cloud can request the queried data in pieces called pages.

This connection property determines the maximum number of results that the Cloud requests per page.

Note: Setting large page sizes may improve overall query execution time, but doing so causes the Cloud to use more memory when executing queries and risks triggering a timeout.

PseudoColumns

Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.

Data Type

string

Default Value

""

Remarks

This property allows you to define which pseudocolumns the Cloud exposes as table columns.

To specify individual pseudocolumns, use the following format:

Table1=Column1;Table1=Column2;Table2=Column3

To include all pseudocolumns for all tables use:

*=*

RowScanDepth

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.

Timeout

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.

Data Type

int

Default Value

60

Remarks

The timeout applies to each individual communication with the server rather than the entire query or operation. For example, a query could continue running beyond 60 seconds if each paging call completes within the timeout limit.

Timeout is set to 60 seconds by default. To disable timeouts, set this property to 0.

Disabling the timeout allows operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server.

Note: Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

Third Party Copyrights

LZMA from 7Zip LZMA SDK

LZMA SDK is placed in the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original LZMA SDK code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.

LZMA2 from XZ SDK

Version 1.9 and older are in the public domain.

Xamarin.Forms

Xamarin SDK

The MIT License (MIT)

Copyright (c) .NET Foundation Contributors

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

NSIS 3.10

Copyright (C) 1999-2025 Contributors THE ACCOMPANYING PROGRAM IS PROVIDED UNDER THE TERMS OF THIS COMMON PUBLIC LICENSE ("AGREEMENT"). ANY USE, REPRODUCTION OR DISTRIBUTION OF THE PROGRAM CONSTITUTES RECIPIENT'S ACCEPTANCE OF THIS AGREEMENT.

1. DEFINITIONS

"Contribution" means:

a) in the case of the initial Contributor, the initial code and documentation distributed under this Agreement, and b) in the case of each subsequent Contributor:

i) changes to the Program, and

ii) additions to the Program;

where such changes and/or additions to the Program originate from and are distributed by that particular Contributor. A Contribution 'originates' from a Contributor if it was added to the Program by such Contributor itself or anyone acting on such Contributor's behalf. Contributions do not include additions to the Program which: (i) are separate modules of software distributed in conjunction with the Program under their own license agreement, and (ii) are not derivative works of the Program.

"Contributor" means any person or entity that distributes the Program.

"Licensed Patents " mean patent claims licensable by a Contributor which are necessarily infringed by the use or sale of its Contribution alone or when combined with the Program.

"Program" means the Contributions distributed in accordance with this Agreement.

"Recipient" means anyone who receives the Program under this Agreement, including all Contributors.

2. GRANT OF RIGHTS

a) Subject to the terms of this Agreement, each Contributor hereby grants Recipient a non-exclusive, worldwide, royalty-free copyright license to reproduce, prepare derivative works of, publicly display, publicly perform, distribute and sublicense the Contribution of such Contributor, if any, and such derivative works, in source code and object code form.

b) Subject to the terms of this Agreement, each Contributor hereby grants Recipient a non-exclusive, worldwide, royalty-free patent license under Licensed Patents to make, use, sell, offer to sell, import and otherwise transfer the Contribution of such Contributor, if any, in source code and object code form. This patent license shall apply to the combination of the Contribution and the Program if, at the time the Contribution is added by the Contributor, such addition of the Contribution causes such combination to be covered by the Licensed Patents. The patent license shall not apply to any other combinations which include the Contribution. No hardware per se is licensed hereunder.

c) Recipient understands that although each Contributor grants the licenses to its Contributions set forth herein, no assurances are provided by any Contributor that the Program does not infringe the patent or other intellectual property rights of any other entity. Each Contributor disclaims any liability to Recipient for claims brought by any other entity based on infringement of intellectual property rights or otherwise. As a condition to exercising the rights and licenses granted hereunder, each Recipient hereby assumes sole responsibility to secure any other intellectual property rights needed, if any. For example, if a third party patent license is required to allow Recipient to distribute the Program, it is Recipient's responsibility to acquire that license before distributing the Program.

d) Each Contributor represents that to its knowledge it has sufficient copyright rights in its Contribution, if any, to grant the copyright license set forth in this Agreement.

3. REQUIREMENTS

A Contributor may choose to distribute the Program in object code form under its own license agreement, provided that:

a) it complies with the terms and conditions of this Agreement; and

b) its license agreement:

i) effectively disclaims on behalf of all Contributors all warranties and conditions, express and implied, including warranties or conditions of title and non-infringement, and implied warranties or conditions of merchantability and fitness for a particular purpose;

ii) effectively excludes on behalf of all Contributors all liability for damages, including direct, indirect, special, incidental and consequential damages, such as lost profits;

iii) states that any provisions which differ from this Agreement are offered by that Contributor alone and not by any other party; and

iv) states that source code for the Program is available from such Contributor, and informs licensees how to obtain it in a reasonable manner on or through a medium customarily used for software exchange.

When the Program is made available in source code form:

a) it must be made available under this Agreement; and

b) a copy of this Agreement must be included with each copy of the Program.

Contributors may not remove or alter any copyright notices contained within the Program.

Each Contributor must identify itself as the originator of its Contribution, if any, in a manner that reasonably allows subsequent Recipients to identify the originator of the Contribution.

4. COMMERCIAL DISTRIBUTION

Commercial distributors of software may accept certain responsibilities with respect to end users, business partners and the like. While this license is intended to facilitate the commercial use of the Program, the Contributor who includes the Program in a commercial product offering should do so in a manner which does not create potential liability for other Contributors. Therefore, if a Contributor includes the Program in a commercial product offering, such Contributor ("Commercial Contributor") hereby agrees to defend and indemnify every other Contributor ("Indemnified Contributor") against any losses, damages and costs (collectively "Losses") arising from claims, lawsuits and other legal actions brought by a third party against the Indemnified Contributor to the extent caused by the acts or omissions of such Commercial Contributor in connection with its distribution of the Program in a commercial product offering. The obligations in this section do not apply to any claims or Losses relating to any actual or alleged intellectual property infringement. In order to qualify, an Indemnified Contributor must: a) promptly notify the Commercial Contributor in writing of such claim, and b) allow the Commercial Contributor to control, and cooperate with the Commercial Contributor in, the defense and any related settlement negotiations. The Indemnified Contributor may participate in any such claim at its own expense.

For example, a Contributor might include the Program in a commercial product offering, Product X. That Contributor is then a Commercial Contributor. If that Commercial Contributor then makes performance claims, or offers warranties related to Product X, those performance claims and warranties are such Commercial Contributor's responsibility alone. Under this section, the Commercial Contributor would have to defend claims against the other Contributors related to those performance claims and warranties, and if a court requires any other Contributor to pay any damages as a result, the Commercial Contributor must pay those damages.

5. NO WARRANTY

EXCEPT AS EXPRESSLY SET FORTH IN THIS AGREEMENT, THE PROGRAM IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, EITHER EXPRESS OR IMPLIED INCLUDING, WITHOUT LIMITATION, ANY WARRANTIES OR CONDITIONS OF TITLE, NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Each Recipient is solely responsible for determining the appropriateness of using and distributing the Program and assumes all risks associated with its exercise of rights under this Agreement, including but not limited to the risks and costs of program errors, compliance with applicable laws, damage to or loss of data, programs or equipment, and unavailability or interruption of operations.

6. DISCLAIMER OF LIABILITY

EXCEPT AS EXPRESSLY SET FORTH IN THIS AGREEMENT, NEITHER RECIPIENT NOR ANY CONTRIBUTORS SHALL HAVE ANY LIABILITY FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING WITHOUT LIMITATION LOST PROFITS), HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OR DISTRIBUTION OF THE PROGRAM OR THE EXERCISE OF ANY RIGHTS GRANTED HEREUNDER, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

7. GENERAL

If any provision of this Agreement is invalid or unenforceable under applicable law, it shall not affect the validity or enforceability of the remainder of the terms of this Agreement, and without further action by the parties hereto, such provision shall be reformed to the minimum extent necessary to make such provision valid and enforceable.

If Recipient institutes patent litigation against a Contributor with respect to a patent applicable to software (including a cross-claim or counterclaim in a lawsuit), then any patent licenses granted by that Contributor to such Recipient under this Agreement shall terminate as of the date such litigation is filed. In addition, if Recipient institutes patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Program itself (excluding combinations of the Program with other software or hardware) infringes such Recipient's patent(s), then such Recipient's rights granted under Section 2(b) shall terminate as of the date such litigation is filed.

All Recipient's rights under this Agreement shall terminate if it fails to comply with any of the material terms or conditions of this Agreement and does not cure such failure in a reasonable period of time after becoming aware of such noncompliance. If all Recipient's rights under this Agreement terminate, Recipient agrees to cease use and distribution of the Program as soon as reasonably practicable. However, Recipient's obligations under this Agreement and any licenses granted by Recipient relating to the Program shall continue and survive.

Everyone is permitted to copy and distribute copies of this Agreement, but in order to avoid inconsistency the Agreement is copyrighted and may only be modified in the following manner. The Agreement Steward reserves the right to publish new versions (including revisions) of this Agreement from time to time. No one other than the Agreement Steward has the right to modify this Agreement. IBM is the initial Agreement Steward. IBM may assign the responsibility to serve as the Agreement Steward to a suitable separate entity. Each new version of the Agreement will be given a distinguishing version number. The Program (including Contributions) may always be distributed subject to the version of the Agreement under which it was received. In addition, after a new version of the Agreement is published, Contributor may elect to distribute the Program (including its Contributions) under the new version. Except as expressly stated in Sections 2(a) and 2(b) above, Recipient receives no rights or licenses to the intellectual property of any Contributor under this Agreement, whether expressly, by implication, estoppel or otherwise. All rights in the Program not expressly granted under this Agreement are reserved.

This Agreement is governed by the laws of the State of New York and the intellectual property laws of the United States of America. No party to this Agreement will bring a legal action under this Agreement more than one year after the cause of action arose. Each party waives its rights to a jury trial in any resulting litigation.

Cloud

WooCommerce - CData Cloud

Overview

Key Features

Getting Started

Connecting to WooCommerce

Accessing Data from CData Cloud Services

Establishing a Connection

Connecting to WooCommerce

Authenticating to WooCommerce

Basic Authentication

"One-Legged" Authentication

OAuth 2.0 Authentication

Creating a Custom OAuth Application

Creating a Custom OAuth Application

Installing the Plugin

Creating and Registering the Application

SSL Configuration

Customizing the SSL Configuration

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

Other Proxies

Data Model

Overview

Key Features

Views

Stored Procedures

System Tables

Tables

CData Cloud - WooCommerce Tables

Attributes

Table Specific Information

Select

Insert

Columns

Categories

Table Specific Information

Select

Insert

Columns

Coupons

Table Specific Information

Select

Update

Insert

Columns

Customers

Table Specific Information

Select

Update

Insert

Columns

Pseudo-Columns

OrderLineItems

Table Specific Information

Select

Insert

Update

Columns

Pseudo-Columns

OrderNotes

Table Specific Information

Select

Insert

Columns

OrderRefunds

Table Specific Information

Select

Insert

Columns

Pseudo-Columns

Orders

Table Specific Information

Select

Update

Insert

Columns

Pseudo-Columns

PaymentGateways