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

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

Connecting to WooCommerce

In order to connect to WooCommerce, you also must specify the URL of the instance to connect to.

• Url - Set this to the base URL of the WooCommerce instance to connect to. For Example : Url=http://localhost/woocommerce/;

Authenticate to WooCommerce

WooCommerce supports the following authentication methods: Basic Authentication, one-legged OAuth1.0 Authentication and standard OAuth2.0 Authentication.

Basic

Some plugins and some servers may interfere with the Authorization header, if not configured properly.

For these cases, Basic authentication may be used. In this authentication mode, the user provides the consumer_key and the consumer_secret as user and password respectively.

These credentials are then sent as query parameters with each request.

Specify the following properties:

• AuthScheme - Set this to Basic.
• User - Set this to your consumer key
• Password - Set this to your consumer secret

One-legged OAuth 1.0

Specify the following properties (NOTE: the below credentials are generated from WooCommerce settings page and should not be confused with the credentials generated by using WooCommerce OAuth2.0 plugin):

• AuthScheme - Set this to OAuthOneLegged.
• ConsumerKey
• ConsumerSecret

OAuth 2.0

After having configured the OAuth2 Plugin plugin, you may connect to WooCommerce by providing the following connection proeprties:

• AuthScheme - Set this to OAuth.
• OAuthClientId
• OAuthClientSecret
• CallbackURL
• InitiateOAuth - Set this to either GETANDREFRESH or REFRESH

This section details a selection of advanced features of the WooCommerce Sync App.

User Defined Views

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

SSL Configuration

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

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to WooCommerce and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

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

Customizing the SSL Configuration

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

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

Connecting Through a Firewall or Proxy

HTTP Proxies

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

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

Other Proxies

Set the following properties:

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

Overview

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

Key Features

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

Views

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

Stored Procedures

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

System Tables

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

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

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

WooCommerce Connector for CData Sync Tables

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

Retrieve product categories.

Table Specific Information

Select

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

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

Insert

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

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

Columns

Retrieve and modify coupons.

Table Specific Information

Select

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

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

Update

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

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

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

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

Insert

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

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

Columns

Retrieve and modify customers.

Table Specific Information

Select

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

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

Update

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

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

Insert

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

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

Columns

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

Table Specific Information

Select

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

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

Insert

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

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

Update

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

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

Columns

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 Sync App 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

Retrieve and modify order refunds.

Table Specific Information

Select

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

SELECT * FROM OrderRefunds WHERE OrderId = 21

Insert

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

INSERT INTO OrderRefunds (OrderId) VALUES (33)

Columns

Retrieve and modify orders.

Table Specific Information

Select

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

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

Update

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

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

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

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

Insert

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

Columns

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'

Insert

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

Columns

Retrieve the terms for an attribute.

Table Specific Information

Select

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

SELECT * FROM ProductAttributeTerms

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

Insert

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

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

Columns

Retrieve and modify products reviews.

Table Specific Information

Select

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

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

Insert

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

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

Columns

Retrieve and modify products.

Table Specific Information

Select

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

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

Update

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

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

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

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

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

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

Insert

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

Columns

Retrieve and modify product shipping classes.

Table Specific Information

Select

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

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

Insert

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

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

Columns

Retrieve and modify product tags.

Table Specific Information

Select

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

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

Insert

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

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

Columns

Retrieve and modify product variations.

Table Specific Information

Select

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

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

Update

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

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

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

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

Insert

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

INSERT INTO ProductVariations (ProductId) VALUES (167)

Columns

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 Sync App 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

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 Sync App 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

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

Retrieve and modify tax classes.

Table Specific Information

Select

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

Retrieve and modify tax rates.

Table Specific Information

Select

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

SELECT * FROM TaxRates WHERE Id = 4

Insert

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

Columns

Retrieve and modify webhooks.

Table Specific Information

Select

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

SELECT * FROM Webhooks WHERE Id = 4

Insert

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

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

Columns

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

WooCommerce Connector for CData Sync Views

Retrieve continents.

Table Specific Information

Select

The Sync App will process all filters client side.

Columns

Retrieve countries.

Table Specific Information

Select

The Sync App will process all filters client side.

Columns

Retrieve available currencies.

Table Specific Information

Select

The Sync App will process all filters client side.

Columns

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

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 Sync App 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

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 Sync App will fetch a list of all the orders and perform a request for each order in the list. The above behaviour will happen apply also for refunds, if 'RefundId' is not specified.

SELECT * FROM OrderRefundLineItems WHERE OrderId = 16

Columns

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 Sync App 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

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 Sync App 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

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 Sync App 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

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 Sync App 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

Retrieve images for a product.

Table Specific Information

Select

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

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

Columns

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

Retrieve settings groups.

Table Specific Information

Select

The Sync App will process all filters client side.

Columns

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 Sync App 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

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

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 Sync App 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

Retrieve system status items.

Table Specific Information

Select

This table represents a singleton entity.

Columns

Retrieve and system status items.

Table Specific Information

Select

The Sync App will process all filters client side.

Columns

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

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

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

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.

Authentication

OAuth

SSL

Firewall

Proxy

Logging

Schema

Miscellaneous

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

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

Remarks

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

The URL of the WooCommerce instance.

Remarks

The URL of the WooCommerce instance.

The WooCommerce user account used to authenticate.

Remarks

Together with Password, this field is used to authenticate against the WooCommerce server.

The password used to authenticate the user.

Remarks

The User and Password are together used to authenticate with the server.

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

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

Remarks

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

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

Remarks

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

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

Remarks

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

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

Remarks

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

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

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

Remarks

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

This property can take the following forms:

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

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

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

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

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

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

• BASIC: The Sync App performs HTTP BASIC authentication.
• DIGEST: The Sync App performs HTTP DIGEST authentication.
• NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
• PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

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

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

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

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Remarks

The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\CData\\WooCommerce Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

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

Remarks

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

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

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

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

These hidden properties are used only in specific use cases.

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

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

Remarks

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

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

Remarks

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

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

Remarks

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

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

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

Remarks

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

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

A filepath pointing to the JSON configuration file containing your custom views.

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Sync App.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM Orders WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"