はじめに

このページでは、CData Cloud でのBigCommerce への接続の確立のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。

BigCommerce への接続

接続の確立は、CData Cloud にデータベースを作成するためのBigCommerce への認証方法と必要な接続プロパティの設定方法について示します。

CData Cloud サービスからデータにアクセス

利用可能な標準サービスを経由してBigCommerce からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメントで詳しく説明します。

接続の確立

Database タブで対応するアイコンを選択して、BigCommerce に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。

BigCommerce への接続

BigCommerce を認証する方法は2つあります。

BigCommerce ユーザーインターフェースで生成されたトークンを使用。
カスタムBigCommerce アプリケーションで作成されたOAuth 資格情報を使用。

BigCommerce トークン

トークンを生成することで、BigCommerce に接続するための資格情報を取得できます。トークンは、特定のアカウント情報の共有を許可します。このフローでは、BigCommerce のUI で直接アプリケーションへのアクセス許可を明示的に作成および承認するため、実行時にWeb ブラウザは必要ありません。これは最も簡単な接続方法であり、個人または会社のデータにアクセスするだけのユーザーに推奨されます。

トークンの生成

BigCommerce ストアに接続してトークンを取得するには、以下のようにストアレベルのAPI アカウントを作成する必要があります。

BigCommerce アカウントにログインします。
Settings -> Store-level API Tokens -> +Create API Account に移動します。
Token type -> V2/V3 API Token を選択します。
アカウント名（4文字以上）を入力します。
API のパスをメモしてください。次のような構造です：https://api.bigcommerce.com/stores/{StoreId}/v3/。API アカウントを使用するには、このパスが必要です。 Note： Store Id はストアハッシュとも呼ばれます。
作成するAPI Account のOAuth Scopes を選択します。Cloud は"None" とマークされたデータにアクセスできません。また、"read-only" とマークされたデータを変更できません。
Save をクリックします。保存に成功すると、API 資格情報を含むポップアップウィンドウが表示されます。このポップアップウィンドウに戻ることはできないので、資格情報を保存してください。

以下の接続プロパティを設定することで、API 資格情報を使用してBigCommerce に接続できるようになります。

AuthScheme：PersonalAccessToken に設定。
StoreId：API パスから取得したStoreId（ストアハッシュ）に設定。
OAuthAccessToken：上記の手順で取得したAccess Token に設定。

BigCommerce カスタムアプリの作成

BigCommerce アプリの作成では、BigCommerce に接続するためのカスタムアプリを作成する方法について説明します。ここで詳しく説明する配布用のアプリケーションを作成する方法は、以下の各セクションで必要となります。

デスクトップアプリケーション
Web アプリケーション
ヘッドレスマシン

これらのすべてのケースでAuthScheme をOAUTH に設定する必要があります。以下のセクションは、すでに設定済みであることを前提として書かれています。

アプリの作成は単一のOAuthAccess トークンを生成するより複雑ですが、複数のクライアントに接続またはアクセスを許可する設計のクライアント統合や、複数のユーザーによって個別に承認されるアプリを作成する管理シナリオでは、より望ましい場合があります。

デスクトップアプリ

下記の手順に従って、カスタムOAuth アプリの資格情報を使用して認証します。詳しくは前述の「アクセストークンの生成」を参照してください。

OAuth アクセストークンの取得

次のプロパティを設定して、接続してください。

OAuthClientId：アプリ設定のクライアントId に設定。
OAuthClientSecret：アプリ設定のクライアントシークレットに設定。
CallbackURL：アプリ設定のリダイレクトURL に設定。
StoreId：StoreId に設定。

接続すると、Cloud はデフォルトブラウザでOAuth エンドポイントを開きます。ログインして、アプリケーションにアクセス許可を与えます。

ヘッドレスマシン

ヘッドレスマシンのユーザーアカウントでOAuth を使用するようにドライバーを設定するには、インターネットブラウザに対応した別の端末で認証する必要があります。

以下の2つのオプションから選択します。
- オプション1：後述の「Verifier code を取得および交換」に従い、OAuthVerifier 値を取得します。
- オプション2：別のマシンにCloud をインストールし、後述の「OAuth 設定を転送」の説明に従い、通常のブラウザベースのフローで認証後にOAuth 認証値を転送します。
その後、ヘッドレスマシンからアクセストークンを自動的にリフレッシュするようにCloud を設定します。

オプション1：Verifier code を取得および交換

Verifier code を取得するには、OAuth Authorization URL で認証する必要があります。

インターネットブラウザに対応したマシンから認証してOAuthVerifier 接続プロパティを取得する方法は次のとおりです。

以下のオプションから選択します。
- 埋め込みOAuth アプリケーションを使用する場合は、BigCommerce OAuth endpoint をクリックし、ブラウザでエンドポイントを開きます。
- カスタムOAuth アプリケーションを使用するには、以下のプロパティを設定し、認証URL を作成します。
  - InitiateOAuth：OFF に設定。
  - OAuthClientId：アプリケーションの登録時に割り当てられたクライアントId に設定。
  - OAuthClientSecret：アプリケーションの登録時に割り当てられたクライアントシークレットに設定。
  次に、適切なCallbackURL を指定してGetOAuthAuthorizationURL ストアドプロシージャを呼び出します。ストアドプロシージャによって返されたURL をブラウザで開きます。
ログインして、Cloud にアクセス許可を与えます。すると、verifier code を含むコールバックURL にリダイレクトされます。
verifier code の値を保存します。後ほどこれをOAuthVerifier 接続プロパティに設定します。

次に、OAuth verifier code をOAuth リフレッシュトークンおよびアクセストークンと交換する必要があります。次のプロパティを設定します。

ヘッドレスマシンでは、次の接続プロパティを設定してOAuth 認証値を取得します。

InitiateOAuth：REFRESH に設定。
OAuthVerifier：verifier code に設定。
OAuthClientId：（カスタムアプリのみ）カスタムOAuth アプリケーション設定のクライアントId に設定。
OAuthClientSecret：（カスタムアプリのみ）カスタムOAuth アプリケーション設定のクライアントシークレットに設定。
OAuthSettingsLocation：ドライバーがOAuth トークン値を保存するファイルの場所に設定。これは接続間で維持されます。

OAuth 設定ファイルが生成されたら、以下のように接続プロパティをリセットする必要があります。

InitiateOAuth：REFRESH に設定。
OAuthClientId：（カスタムアプリのみ）アプリケーションの登録時に割り当てられたクライアントId に設定。
OAuthClientSecret：（カスタムアプリのみ）アプリケーションの登録時に割り当てられたクライアントシークレットに設定。
OAuthSettingsLocation：暗号化されたOAuth 認証値が保存される場所に設定。アクセストークンの自動リフレッシュを有効にするために、この場所がCloud に読み書きのアクセス許可を与えることを確認してください。

オプション2：OAuth 設定を転送

ヘッドレスマシンでの接続に先立ち、インターネットブラウザに対応したデバイスでドライバーとの接続を作成し、インストールする必要があります。上述の「デスクトップアプリケーション」の説明に従って、接続プロパティを設定します。

「デスクトップアプリケーション」の手順が完了すると、生成された認証値は、OAuthSettingsLocation で指定された場所に暗号化されて書き込まれます。デフォルトのファイル名はOAuthSettings.txt です。

接続が正常にテストされたら、OAuth 設定ファイルをヘッドレスマシンにコピーします。

ヘッドレスマシンで、次の接続プロパティを設定し、データに接続します。

InitiateOAuth：REFRESH に設定。
OAuthClientId：（カスタムアプリのみ）アプリケーションの登録時に割り当てられたクライアントId に設定。
OAuthClientSecret：（カスタムアプリのみ）アプリケーションの登録時に割り当てられたクライアントシークレットに設定。
OAuthSettingsLocation：OAuth 設定ファイルの場所に設定。アクセストークンの自動リフレッシュを有効にするために、この場所がCloud に読み書きのアクセス許可を与えることを確認してください。

SSL の設定

SSL 設定のカスタマイズ

デフォルトでは、Cloud はサーバーとのTLS のネゴシエーションを試みます。サーバー証明書は、デフォルトのシステム信頼済み証明書ストアで検証されます。SSLServerCert 接続プロパティを使用して、証明書の検証方法をオーバーライドできます。

別の証明書を指定するには、SSLServerCert 接続プロパティを参照してください。

ファイアウォールとプロキシ

Firewall またはProxy 経由の接続

HTTP プロキシ

HTTP プロキシへの認証には、以下のように設定します。

ProxyServer：HTTP トラフィックをルートするプロキシサーバーのホストネームもしくはIP アドレス。
ProxyPort：プロキシサーバーが起動しているTCP ポート。
ProxyAuthScheme：プロキシサーバーに対して認証する際にCloud が使用する認証方法。
ProxyUser：プロキシサーバーに登録されているユーザーアカウントのユーザー名。
ProxyPassword：ProxyUser に紐付けられたパスワード。

その他のプロキシ

次のプロパティを設定します。

プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
接続をトンネルするには、FirewallType をTUNNEL に設定します。
認証するには、FirewallUser とFirewallPassword を設定します。
SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

データモデル

概要

このセクションでは、利用可能なAPI オブジェクトを示し、BigCommerce API へのSQL の実行について詳しく説明します。

主要機能

Cloud はBigCommerce のカテゴリ、顧客、および注文をリレーショナルテーブルやビューとしてモデル化し、SQL を記述してBigCommerce データをクエリできるようにします。
ストアドプロシージャを使用すると、オブジェクトのダウンロードやアップロードなど、BigCommerce の操作を実行できます。
これらのオブジェクトへのライブ接続により、Cloud を使用するとBigCommerce アカウントへのあらゆる変更が即座に反映されます。

ビュー

BigCommerce からのデータへの読み取りまたは書き込みアクセスには、事前定義されたテーブルおよびビューを使用できます。

ストアドプロシージャ

Cloud を使用すると、BigCommerce オブジェクトをリストし、ストアドプロシージャを介してデータをダウンロード/アップロードできます。

システムテーブル

システムテーブルには、データベースに属するオブジェクトとリソースに関する情報が含まれています。

テーブル

Cloud はBigCommerce のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

CData Cloud - BigCommerce テーブル


Name	Description
Brands	Gets the collection of brands.
Categories	Returns Product Categories.
CategoryTrees	Returns a list of category trees.
ChannelListings	Returns a list of all channel listings for a specific channel.
Channels	Manages and lists BigCommerce sales channels, supporting SELECT, INSERT, and UPDATE channel records.
Coupons	test Create,list,update or delete coupons.
Currencies	Returns data from Currencies table.
CustomerAddresses	Returns a list of Customer Addresses. Returns the addresses belonging to a customer.
Customers	Returns data from Customers table.
OrderItems	Returns Items ordered for every order.
Orders	Returns data from the Orders table.
OrderShipments	Returns shipments and their corresponding orders.
PriceListRecords	Returns a collection of price list records.
PriceLists	Returns a collection of price records.
ProductImages	Returns images registered for products.
ProductMetafields	Returns a list of Product Metafields.
ProductOptions	Returns data from Products table.
ProductOptionValues	Returns data from Products table.
ProductReviews	Returns reviews registered for products.
Products	Returns data from products table.
ProductVariants	Returns data from Products table.
ShippingMethods	Lists all shipping methods.
ShippingZones	Lists all shipping zones.

Brands

Gets the collection of brands.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the =, >, <, >=, <=, IN, and NOT IN comparisons.
Name supports the = comparison.
PageTitle supports the = comparison.

The rest of the filter is executed client-side within the Cloud. For example, the following queries are processed server-side:

SELECT * FROM Brands WHERE Id = 1;

SELECT * FROM Brands WHERE Id >= 1 AND Id <= 5;

SELECT * FROM Brands WHERE Id in (51,54);

SELECT * FROM Brands WHERE Id not in (51,54);

SELECT * FROM Brands WHERE PageTitle = 'modern';

SELECT * FROM Brands WHERE Name = 'Test';

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The id of the brand.
Name	String	False		The name of the brand. Must be unique.
PageTitle	String	False		The title shown in the browser while viewing the brand.
MetaKeywords	String	False		An array of meta keywords to include in the HTML.
MetaDescription	String	False		A meta description to include.
ImageURL	String	False		Image URL used for this category on the storefront.
SearchKeywords	String	False		A comma-separated list of keywords that can be used to locate this brand.
ItemURL	String	False		The custom URL for the product on the storefront.
ItemURLIsCustomized	Boolean	False		Returns true if the URL has been changed from its default state (the auto-assigned URL that BigCommerce provides).

CategoryTrees

Returns a list of category trees.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = and IN comparisons.
Channels supports the = and IN comparisons.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM CategoryTrees WHERE Id = 1;
SELECT * FROM CategoryTrees WHERE Id IN (1,2); 
SELECT * FROM CategoryTrees WHERE Channels = '[1]';
SELECT * FROM CategoryTrees WHERE Channels IN ('[1]', '[100]');

Insert

To insert a category tree, specify the Channel ID in the Channels field. The Channel ID determines the channel where the category tree is created. You can only assign a category tree to one channel. See the example query below.

INSERT INTO CategoryTrees (Name,channels) VALUES ('name','[5]');

Update

The channels field must be absent when updating a category tree. This field is currently unsupported during a category tree update.

UPDATE CategoryTrees SET name = 'test' WHERE Id = 10;

Delete

DELETE FROM CategoryTrees WHERE Id = 18

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Unique ID of the Category Tree.
Name	String	False		The name displayed for the category Tree.
Channels	String	False		Channel ID

ChannelListings

Returns a list of all channel listings for a specific channel.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Listingid supports the = comparison.
ChannelId supports the = comparison.
Datecreated supports the =, >, <, >=, and <= comparisons.
Datemodified supports the =, >, <, >=, and <= comparisons.

The rest of the filter is executed client-side within the Cloud. For example, the following queries are processed server-side:

SELECT * FROM ChannelListings WHERE ChannelId = 667159 AND ListingId = 882998595

Insert

To insert a channel listing, specify the following columns: ProductId, State, Variants, ChannelId, and Name.

INSERT INTO ChannelListings (Productid, State, Variants, ChannelId) VALUES (12345, 'active', 'test', 667159)

Update

The following example illustrates how to update ChannelListings:

UPDATE ChannelListings SET name = 'Test' WHERE ChannelId = 667159

Columns


Name	Type	ReadOnly	References	Description
ListingId [KEY]	Integer	True		The ID of the channel listing that has been created, returned, or updated
ChannelId [KEY]	Integer	True	Channels.Id	The ID of the channel associated with this channel listing
Datecreated	Datetime	True		Date on which the channel listing was first created
Datemodified	Datetime	True		Date on which the channel listing was most recently changed
Name	String	False		Name of the product for this channel listing specifically This is an optional field that can be used to override the product name in the catalog
Description	String	True		Description of the product for this channel listing specifically This is an optional field that can be used to override the product description in the catalog
Productid	Integer	False		The ID of the product associated with this channel listing
State	String	False		The state of the product assignment or channel listing
Variants	String	False		Product variant associated with the channel listing.
ExternalId	String	False		Associated ID within a system / platform outside of BC.

Channels

Manages and lists BigCommerce sales channels, supporting SELECT, INSERT, and UPDATE channel records.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
Datecreated supports the =, >, <, >=, and <= comparisons.
Datemodified supports the =, >, <, >=, and <= comparisons.

The rest of the filter is executed client-side within the Cloud. For example, the following queries are processed server-side:

SELECT * FROM Channels

Insert

To insert a channel, specify the following columns: Name, Type, Platform, and Status.

INSERT INTO Channels (Type, Platform, Status, Name) VALUES ('pos', 'square', 'active', 'tests');

Update

The following example illustrates how to update Channels:

UPDATE Channels SET Name = 'tests' WHERE ID = 123

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the channel.
ConfigMetaAppId	Integer	False		The unique id given to an app registered in DevTools
sections	String	False		If set, when the app is loaded within the control panel, the navigation sections will be directly embedded in the control panel navigation
Datecreated	Datetime	True		Date on which the channel was first create
Datemodified	Datetime	True		Date on which the channel was most recently changed
Externalid	String	False		Associated ID within a system / platform outside of BC
Iconurl	String	False		Link to the platform icon
Isenabled	Boolean	False		Channel with status of prelaunch, active, or connected will have is_enabled set to true. Channel with status of inactive, disconnected, archived, deleted, or terminated will have is_enabled set to false
IslistableFromUi	Boolean	False		Indicates if a channel can create listings from the BigCommerce UI. Default value for this field is based on the channel type and platform combination if not specified on create
Isvisible	Boolean	False		Indicates if a channel is visible within the BigCommerce merchant admin UI (control panel). If false, the channel will not show in Channel Manager nor in any channels dropdown throughout the UI. Default value for this field is true if not specified on create
Name	String	False		Name of the channel as it will appear to merchants in the control panel
Platform	String	False		The name of the platform for the channel channel platform and type must be a valid combination
Status	String	False		The status of the channel channel type, platform, and status must be a valid combination. terminated is not valid for Update or Insert requests
Type	String	False		The type of channel channel platform and type must be a valid combinations

Coupons

test Create,list,update or delete coupons.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the =, >, <, >=, and <= comparisons.
Name supports the = comparison.
Type supports the = and != comparisons.
Code supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM Coupons WHERE Id = 1 

SELECT * FROM Coupons WHERE Id >= 1 AND Id <= 5

SELECT * FROM Coupons WHERE Name = "test"

Insert

To insert a coupon, specify at least the following columns: Name, Type, Code, Amount, AppliesToEntity and AppliesToIds.

INSERT INTO Coupons (Name, Type, Code, Amount, AppliesToEntity, AppliesToIds) VALUES ('CouponName', 'free_shipping', 'CN100', 500, 'products', '88, 80')

Update

UPDATE Coupons SET Code = 'ABC' WHERE Id = 12

Delete

DELETE FROM Coupons WHERE Id = 16

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The coupons ID.
Name	String	False		The name of the coupon.
Type	String	False		Type of the coupon. Must be one of per_item_discount,per_total_discount,shipping_discount,free_shipping or percentage_discount. 使用できる値は次のとおりです。per_item_discount, percentage_discount, per_total_discount, shipping_discount, free_shipping, promotion
Amount	Decimal	False		The discount to apply to an order, as either an amount or a percentage.
MinPurchase	Decimal	False		Specifies a minimum value that an order must have before the coupon can be applied to it.
Expires	Datetime	False		Specifies when a coupon expires.
Enabled	Boolean	False		If the coupon is enabled, this fields value is true; otherwise, false.
Code	String	False		The coupon code that customers uses to receive their discounts.
AppliesToEntity	String	False		What the discount applies to. Can be products or categories.
AppliesToIds	String	False		IDs of either the products or categories
NumUses	Integer	True		Number of times this coupon has been used.
MaxUses	Integer	False		Maximum number of times this coupon can be used.
MaxUsesPerCustomer	Integer	False		Maximum number of times each customer can use this coupon.
DateCreated	Datetime	False		Date Created
ShippingMethods	String	False		List of shipping-method names.
RestrictedToCountries	String	False		Countries where the coupon is restricted.

Currencies

Returns data from Currencies table.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the Id column, which supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM Currencies WHERE Id = 1

Insert

To insert a currency, specify at least the following columns: Code, Name, ExchangeRate, Token, TokenLocation, DecimalToken, ThousandsToken, and DecimalPlaces.

INSERT INTO Currencies (Code, Name, ExchangeRate, Token, TokenLocation, DecimalToken, ThousandsToken, DecimalPlaces) VALUES ('EUR','Euro', '3.0','$','left','.',',','2')

Update

UPDATE Currencies SET Name = 'Testing123' WHERE Id = 2

Delete

DELETE FROM Currencies WHERE Id = 3

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the currency.
IsDefault	Boolean	True		Specifies whether this is the store's default currency.
Code	String	False		Three-letter ISO 4217 code for this currency.
Name	String	False		Name of this currency.
ExchangeRate	Double	False		Amount of this currency that is equivalent to one U.S. dollar.
CountryIso	String	False		Two-letter ISO Alpha-2 code.
AutoUpdate	Boolean	False		Whether to autoupdate currency data.
TokenLocation	String	False		Symbol for this currency.
Token	String	False		Name of the currency.
DecimalToken	String	False		Symbol used as the decimal separator in this currency.
ThousandsToken	String	False		Symbol used as the thousands separator in this currency.
DecimalPlaces	Integer	False		Number of decimal places to show for this currency.
Enabled	Boolean	False		Indicates If the currency is active on the store.
IsTransactional	Boolean	False		Indicates if the currency is set as transactional or not. False means display only currency.
LastUpdated	Date	True		Date the currency was last updated, created or modified.

CustomerAddresses

Returns a list of Customer Addresses. Returns the addresses belonging to a customer.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = and IN comparisons.
CustomerId supports the = and IN comparisons.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM CustomerAddresses WHERE Id = 1 

SELECT * FROM CustomerAddresses WHERE CustomerId = 5

Insert

To insert a customer address, specify at least the following columns: CustomerId, FirstName, LastName, City, CountryCode, Address1, StateOrProvince, and PostalCode.

INSERT INTO CustomerAddresses (CustomerId, FirstName, LastName, City, CountryCode, Address1, StateOrProvince, PostalCode) VALUES ('1', 'FirstName', 'LastName', 'Chapel Hill', 'US', '101 Europa Dr', 'NC', '27517')

Update

UPDATE CustomerAddresses SET FirstName = 'Testing123' WHERE Id = 14

Delete

DELETE FROM CustomerAddresses WHERE Id = 3

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		ID of this customer address.
CustomerId	Integer	False	Customers.Id	ID of the associated customer.
FirstName	String	False		The customers first name.
LastName	String	False		The customers last name.
Company	String	False		The customers company name.
Address1	String	False		The customers street address, line 1.
Address2	String	False		The customers street address, line 2.
City	String	False		The customers city/town/suburb.
StateOrProvince	String	False		The customers state/province.
PostalCode	String	False		The customers ZIP or postal code.
Country	String	False		The customers country. Must be the full country name.
CountryCode	String	False		2-letter ISO Alpha-2 code for the customers country.
Phone	String	False		The customers phone number.
AddressType	String	False		The type of the address.

Customers

Returns data from Customers table.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = and IN comparisons.
Company supports the = and IN comparisons.
Email supports the = and IN comparisons.
DateCreated supports the =, <, >, <=, and >= comparisons.
DateModified supports the =, <, >, <=, and >= comparisons.
CustomerGroup supports the = and IN comparisons.
StoreCreditAmounts supports the = comparison.
RegistrationIpAddress supports the = and IN comparisons.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM Customers WHERE Id = 1 

SELECT * FROM Customers WHERE Company = 'DemoFirst'

SELECT * FROM Customers WHERE Email = '[email protected]'

SELECT * FROM Customers WHERE DateCreated > '2021-09-01 11:25:37.0' 

SELECT * FROM Customers WHERE DateModified <= '2023-10-12 02:05:43.0'

Insert

To insert a customer, specify at least the following columns: FirstName, LastName, and Email.

INSERT INTO Customers (FirstName, LastName, Email) VALUES ('Testing4321', 'Test', '[email protected]')

Update

UPDATE Customers SET FirstName = 'testing345' WHERE Id = 5075

Delete

DELETE FROM Customers WHERE Id = 16

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Unique numeric ID of this customer.
Company	String	False		The name of the company for which the customer works.
FirstName	String	False		First name of the customer.
LastName	String	False		Last name of the customer.
Email	String	False		Email address of the customer.
Phone	String	False		Phone number of the customer.
DateCreated	Datetime	True		Date on which the customer registered from the storefront or was created in the control panel.
DateModified	Datetime	True		The date on which the customer was modified.
CustomerGroup	Integer	False		The group to which the customer belongs.
Notes	String	False		Store Owner notes on the customer.
TaxExemptCategory	String	False		Used to identify customers who fall into special sales-tax categories.
StoreCreditAmounts	Decimal	False		Amount of Store Credit
RegistrationIpAddress	String	False		The IP address from which this customer was registered.
AcceptsProductReviewAbandonedCartEmails	Boolean	False		Determines if the customer is signed up to receive either product review or abandoned cart emails or receive both emails.
ForcePasswordReset	Boolean	True		If true,this customer will be forced to change password on next login.
ChannelIds	String	False		Array of channel ids the Customer has access to.
FullName	String	False		Full name of the customer, combination of the first and last names

OrderItems

Returns Items ordered for every order.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
OrderId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM OrderItems WHERE Id = 1 

SELECT * FROM OrderItems WHERE OrderId = 100

Inserting an Existing Product

To insert an existing product to OrderItems for a specified order, you must specify the order options for that table. For this reason, before inserting an existing product to the OrderItems, you must populate a temporary OrderItemOptionValues table with the desired options for the selected product and use this table as a value for the LinkedOrderOptions pseudo-column during insertion:

INSERT INTO OrderItemOptionValues#TEMP (Id, Value) Values (117, 177);
INSERT INTO OrderItemOptionValues#TEMP (Id, Value) Values (116, 176);
INSERT INTO OrderItems (OrderId, ProductId, QuantityOrdered, LinkedOrderOptions) VALUES (1104, 960, 3, OrderItemOptionValues#TEMP)");

Note: The OrderItemOptionValue inserted to the temporary table must belong to the product that is being inserted into OrderItems. To verify this, check the ProductOptionValues table.

Inserting a New Product

New, custom products, can be added to the OrderItems table as follows:

INSERT INTO OrderItems (OrderId, Name, CustomSKU, PriceIncTax, PriceExTax, QuantityOrdered) VALUES (1107, 'TSS Phone Case', 'PHC-232453', 6.55, 5.75, 1);

Note: OrderItems does not support UPDATE or DELETE operations. Once items have been added to an order, they cannot be removed or modified.

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Numeric ID of this product within this order.
ProductId	Integer	False	Products.Id	Numeric ID of the product.
CustomSKU	String	False		User defined product code/stock keeping unit (SKU).
OrderId	Integer	False	Orders.Id	Numeric ID of the associated order.
Name	String	False		The product name.
PriceExTax	Decimal	False		The price of the product, excluding tax.
PriceIncTax	Decimal	False		The price of the product, including tax.
QuantityOrdered	Integer	False		Quantity of the product ordered.
QuantityShipped	Decimal	True		Quantity of the product shipped.
TotalExtax	Decimal	True		Total base price, excluding tax.
TotalIncTax	Decimal	True		Total base price, including tax.
IsRefunded	Boolean	True		Whether the product has been refunded.
ExternalId	String	False		ID of the order in another system.
AppliedDiscounts	String	True		Array of objects containing discounts applied to the product.
BaseCostPrice	Decimal	False		The product's cost price.
BasePrice	Decimal	False		The product’s base price.
BaseTotal	Decimal	False		Total base price.
BaseWrappingCost	Decimal	False		The value of the base wrapping cost.
BinPickingNumber	String	False		Bin picking number for the physical product.
Brand	String	False		The product's brand.
ConfigurableFields	String	False		The price of the product, excluding tax.
CostPriceExTax	Decimal	False		The product's cost price excluding tax.
CostPriceIncTax	Decimal	False		The product's cost price including tax.
CostPriceTax	Decimal	False		Tax applied to the product’s cost price.
Depth	Decimal	False		Depth of the product.
DiscountedTotalIncTax	Decimal	False		Represent the correct total amount of the line item after deducting all the discounts and including the tax.
EbayItemId	String	False		Item ID for this product on eBay.
EbayTransactionId	String	False		Transaction ID for this product on eBay.
EventDate	Datetime	False		Date of the promotional event/scheduled delivery.
EventName	String	False		Name of promotional event/delivery date.
FixedShippingCost	Decimal	False		Fixed shipping cost for this product.
FulfillmentSource	String	False		The source of the fulfillment.
GiftCertificateId	String	False		ID of the associated gift certificate.
Height	Decimal	False		Height of the product
IsBundledProduct	Boolean	False		Whether this product is bundled with other products.
NameCustomer	String	False		The product name that is shown to customer in storefront.
NameMerchant	String	False		The product name that is shown to merchant in Control Panel.
OptionSetId	Integer	False		Numeric ID of the option set applied to the product.
OrderAddressId	Integer	False		Numeric ID of the associated order address. Value is 0 for items that are not fulfilled by a pickup method.
OrderPickupMethodId	Integer	False		ID of the pickup fulfillment method for this item. Default value is 0 when the item is not fulfilled by pickup method.
ParentOrderProductId	Integer	False		ID of a parent product.
PriceTax	Decimal	False		Amount of tax applied to a single product.
RefundAmount	Decimal	False		The amount to be refunded.
ReturnId	Integer	False		Numeric ID for the refund.
TotalTax	Decimal	False		Total tax applied to products.
Type	String	False		Type of product. 使用できる値は次のとおりです。physical, digital
Upc	String	False		Universal Product Code. Can be written to for custom products and catalog products.
VariantId	Integer	False		Products variant_id.
Weight	Decimal	False		Weight of the product.
Width	Decimal	False		Width of the product.
WrappingCostExTax	Decimal	False		The value of the wrapping cost, excluding tax.
WrappingCostIncTax	Decimal	False		The value of the wrapping cost, including tax.
WrappingCostTax	Decimal	False		Tax applied to gift-wrapping option.
WrappingId	Integer	False		The price of the product, excluding tax.
WrappingMessage	String	False		Message to accompany gift-wrapping option.
WrappingName	String	False		Name of gift-wrapping option.

Pseudo-Columns

疑似カラムフィールドを使用して、ユーザーは新しいレコードの作成時に必要な読み取り不可のフィールドを挿入できます。


Name	Type	Description
LinkedOrderOptions	String	Column for the aggregate table name holding option values.

Orders

Returns data from the Orders table.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the =, <, >, <=, and >= comparisons.
DateCreated supports the =, >, <, >=, and <= comparisons.
DateModified supports the =, >, <, >=, and <= comparisons.

The rest of the filter is executed client-side within the Cloud. For example, the following queries are processed server-side:

SELECT * FROM Orders WHERE Id = 1

SELECT * FROM Orders WHERE Id > 104

SELECT * FROM Orders WHERE Id < 104

SELECT * FROM Orders WHERE Id >= 104

SELECT * FROM Orders WHERE Id <= 104

Insert

To insert an order, you must include at least one order item in the INSERT query for that order. First, populate a temporary OrderItems table with the items you want to include in the order. Then, use this table as a source for the LinkedProducts pseudo-column during the insertion process:

INSERT INTO OrderItems#TEMP (ProductId, QuantityOrdered) VALUES (961, 4);

INSERT INTO OrderItems#TEMP (ProductId, QuantityOrdered) VALUES (962, 3);

INSERT INTO Orders (CustomerId, LinkedProducts, StatusId, ItemsTotal, ItemsShipped, PaymentMethod, OrderIsDigital, BillingFirstName, BillingLastName, BillingCompany, BillingCountry, BillingZip, BillingState, BillingEmail, BillingPhone) VALUES (1, OrderItems#TEMP, 4, 6, 2, 'cash', 'true', 'TSS', 'Dev', 'London Corp.', 'United Kingdom', 23433, 'London', '[email protected]', '+355534445');

Update

The following example illustrates how to update Orders:

UPDATE Orders SET StaffNotes ='Testing123' WHERE Id = 103

Delete

The following example illustrates how to delete a row in Orders whose Id equals 3:

DELETE FROM Orders WHERE Id = 3

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The Id of the order.
CustomerId	String	False	Customers.Id	The Id of the customer assigned to this order.
DateCreated	Datetime	True		The date of creation for the order.
DateModified	Datetime	True		The last modification date of the order.
DateShipped	Datetime	True		The date of shipment for the order.
StatusId	String	False	OrderStatuses.Id	The Id of the status for this order.
CartId	String	True		The Id of the cart from which this order is assigned.
Status	String	True		Order Statuses.
TotalTax	String	True		The total value of the order.
SubtotalTax	String	True		The value for subtotal tax.
SubtotalExTax	Decimal	False		The value for subtotal, excluding tax.
SubtotalIncTax	Decimal	False		The value for subtotal, including tax.
ShippingCostExTax	Decimal	False		The value of shipping cost, excluding tax.
ShippingCostIncTax	Decimal	False		The value of shipping cost, including tax.
HandlingCostExTax	Decimal	False		The value of handling cost, excluding tax.
HandlingCostIncTax	Decimal	False		The value of handling cost, including tax.
IpAddress	String	False		IPv4 Address of the customer, if known.
WrappingCostExTax	Decimal	False		The value of wrapping cost, excluding tax.
WrappingCostIncTax	Decimal	False		The value of wrapping cost, including tax.
TotalExTax	Decimal	False		The total value of the order, excluding tax.
TotalIncTax	Decimal	False		The total value of the order, including tax.
HandlingCostTax	String	True		The value of handling cost.
HandlingCostTaxClassId	Integer	True		The Id of handling cost.
ShippingCostTax	String	True		The value of shipping cost.
ItemsTotal	Decimal	False		The total number of items in the order.
ItemsShipped	Decimal	False		The total number of items that have been shipped.
PaymentMethod	String	False		The payment method for this order.
PaymentProviderId	String	False		The external Transaction ID/Payment ID within this order's payment provider (if a payment provider was used).
PaymentStatus	String	True		The payment status for this order.
RefundedAmount	Decimal	False		The amount refunded from this transaction.
GiftCertificateAmount	String	True		Gift Certificate Amount.
CurrencyId	String	True	Currencies. Id	The ID of the currency being used in this transaction.
CurrencyCode	String	True		The currency code of the display currency.
CurrencyExchangeRate	String	True		The exchange rate between the default currency and display currency of store.
DefaultCurrencyId	String	True	Currencies.Id	The ID of the default currency for the store.
DefaultCurrencyCode	String	False		The currency code of the transactional currency the shopper pays in.
StaffNotes	String	False		Any additional notes for staff.
CustomerMessage	String	False		Message that the customer entered.
DiscountAmt	Decimal	False		Amount of discount for this transaction.
EbayOrderId	String	False		Ebay order number if order is placed through Ebay.
GeoIpCountryIso2	String	False		The country where the customer made the purchase, in ISO2 format.
ShippingAddressCount	Decimal	True		The number of shipping addresses associated with this transaction.
CouponDiscount	String	True		Discount of the coupon.
OrderSource	String	True		Source of the order.
IsDeleted	Boolean	True		Indicates whether the order was deleted (archived).
OrderIsDigital	Boolean	False		Indicates whether this is an order for digital products.
ExternalSource	String	False		A value identifying the system used to generate the order (for orders submitted or modified via the API).
ExternalId	String	False		ID of the order in another system.
ExternalMerchantId	String	False		Id of the external merchant.
ChannelId	String	False		Shows where the order originated.
TaxProviderId	String	False		BasicTaxProvider - Tax is set to manual; AvaTaxProvider - This is for when the tax provider has been set to automatic and the order was NOT created by the API; (blank) - When the tax provider is unknown.
ProductsUrl	String	False		Url of the products.
ProductsResource	String	False		Resource of the products.
BillingFirstName	String	False		Addressee first name.
BillingLastName	String	False		Addressee last name.
BillingCompany	String	False		Addressee company.
BillingStreet1	String	False		Street address (first line).
BillingStreet2	String	False		Street address (second line).
BillingCity	String	False		Addressee city
BillingZip	String	False		ZIP or postal code
BillingCountry	String	False		Addressee's country
BillingCountryIso2	String	False		Addressee's country code
BillingState	String	False		The name of the state or province. Should be spelled out in full, for example, California.
BillingEmail	String	False		Recipient's email address.
BillingPhone	String	False		Recipient's telephone number.
BaseHandlingCost	String	False		The value of the base handling cost.
BaseShippingCost	String	False		The value of the base shipping cost.
BaseWrappingCost	String	False		The value of the base wrapping cost.

Pseudo-Columns

疑似カラムフィールドを使用して、ユーザーは新しいレコードの作成時に必要な読み取り不可のフィールドを挿入できます。


Name	Type	Description
LinkedProducts	String	Column for the aggregate table name holding order products.

OrderShipments

Returns shipments and their corresponding orders.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
OrderId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM OrderShipments WHERE Id = 1 

SELECT * FROM OrderShipments WHERE OrderId = 1428

Insert

To insert a order shipment, at least one shipment item must be attached to the INSERT query. First, populate a temporary ShipmentItems table with the items you want to include in the shipment. Then, use this table as the value for the LinkedProducts pseudo-column during insertion:

INSERT INTO ShipmentItems#TEMP (Id, Quantity) VALUES (2519, 1);
INSERT INTO OrderShipments (OrderId, OrderAddressId, LinkedProducts) VALUES (1106, 1007, ShipmentItems#TEMP);

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Numeric ID of this shipment within this order.
OrderId [KEY]	Integer	False	Orders.Id	Numeric ID of the associated order.
DateCreated	Datetime	True		Creation date for the shipment.
TrackingNumber	String	False		Tracking number of the shipment.
TrackingCarrier	String	False		Tracking carrier for the shipment.
TrackingLink	String	True		Returns a tracking link from the shipping service.
ShippingProvider	String	False		Enum of the BigCommerce shipping-carrier integration/module.
ShippingMethod	String	False		Enum of the BigCommerce shipping-carrier integration/module.
Comments	String	False		Comments the shipper wishes to add.
OrderAddressId	Integer	False	OrderShippingAddresses.Id	ID of this shipping address.
CustomerId	String	False		ID of this order’s customer.
ShippingProviderDisplayName	String	True		The human-readable name for the shipping_provider.
MerchantShippingCost	Decimal	True		The shipping merchant cost.
BillingAddressCity	String	True		The billing address city.
BillingAddressCompany	String	True		The billing address company.
BillingAddressCountry	String	True		The billing address country
BillingAddressCountryIso2	String	True		The billing address country iso2.
BillingAddressEmail	String	True		The email of the Addressee.
BillingAddressFirstName	String	True		Addressee's First Name.
BillingAddressLastName	String	True		Addressee's Last Name.
BillingAddressPhone	String	True		Addressee's Phone.
BillingAddressState	String	True		Addressee's State.
BillingAddressStreet1	String	True		Street address (first line).
BillingAddressStreet2	String	True		Street address (second line).
BillingAddressZip	String	True		ZIP or postal code.

Pseudo-Columns

疑似カラムフィールドを使用して、ユーザーは新しいレコードの作成時に必要な読み取り不可のフィールドを挿入できます。


Name	Type	Description
LinkedProducts	String	Column for the aggregate table name holding order products.

PriceListRecords

Returns a collection of price list records.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

CalculatedPrice supports the = comparison.
DateCreated supports the =, >, <, >=, and <= comparisons.
DateModified supports the =, >, <, >=, and <= comparisons.
ProductId supports the = comparison.
PriceListId supports the = comparison.
VariantId supports the = comparison.
Sku supports the = comparison.
Currency supports the = comparison.
Price supports the = comparison.
SalePrice supports the = comparison.
RetailPrice supports the = comparison.
MapPrice supports the = comparison.

The rest of the filter is executed client-side within the Cloud. For example, the following queries are processed server-side:

SELECT * FROM PriceListRecords WHERE PriceListId = 1 

SELECT * FROM PriceListRecords WHERE PriceListId = 1 AND SalePrice = 23

SELECT * FROM PriceListRecords WHERE PriceListId = 1 AND MapPrice = 17.99

SELECT * FROM PriceListRecords WHERE PriceListId = 1 AND VariantId = 361 AND Currency = 'USD'

Bulk Update

To update a price list record, specify the following columns: VariantId, Currency, Price, and PriceListId.

INSERT INTO PriceListRecords#TEMP (VariantId, Currency, Price, SalePrice, PriceListId) VALUES (361, 'USD', 27, 23, 1)
INSERT INTO PriceListRecords#TEMP (VariantId, Currency, Price, SalePrice, PriceListId) VALUES (362, 'USD', 28, 24, 1)
UPDATE PriceListRecords (VariantId, Currency, Price, SalePrice) SELECT VariantId, Currency, Price, SalePrice FROM PriceListRecords#TEMP

Bulk Update Using Aggregates:

INSERT INTO PriceListRecords#TEMP (VariantId, Currency, Price, SalePrice, PriceListId, BulkPricingTiers) VALUES (361, 'USD', 27, 23, 1,
                   '{ 
                    \"quantity_min\": 10," 
                    \"quantity_max\" : 12," 
                     \"type\" : \"percent\","
                      \"amount\": 1
                    }'
UPDATE PriceListRecords ( VariantId, Currency, Price, SalePrice, BulkPricingTiers) SELECT VariantId, Currency, Price, SalePrice, BulkPricingTiers FROM PriceListRecords#TEMP

Columns


Name	Type	ReadOnly	References	Description
CalculatedPrice	Double	True		The price of the variant as seen on the storefront if a price record is in effect
DateCreated	Datetime	True		The date of creation for the Price Entry.
DateModified	Datetime	True		The last modification date of the Price Entry.
ProductId	Integer	True		The id of the Product this Price Record's variant_id is associated with.
PriceListId [KEY]	Integer	True		The Price List ID with which this price set is associated
VariantId [KEY]	Integer	False		The variant with which this price set is associated.
Sku	String	False		The variant with which this price set is associated
Currency [KEY]	String	False		The 3-letter currency code with which this price set is associated
Price	Double	False		The list price for the variant mapped in a Price List
SalePrice	Double	False		The sale price for the variant mapped in a Price List
RetailPrice	Double	False		The retail price for the variant mapped in a Price List
MapPrice	Double	False		The MAP (Manufacturers Advertised Price) for the variant mapped in a Price List
BulkPricingTiers	String	False		The minimum quantity of associated variant in the cart needed to qualify for this tiers pricing
QuantityMin	Integer	False		The minimum quantity of associated variant in the cart needed to qualify for this tiers pricing
QuantityMax	Integer	False		The maximum allowed quantity of associated variant in the cart to qualify for this tiers pricing.
QuantityType	String	False		The type of adjustment that is made.
QuantityAmount	Double	False		The maximum allowed quantity of associated variant in the cart to qualify for this tiers pricing.

PriceLists

Returns a collection of price records.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
DateCreated supports the =, >, <, >=, and <= comparisons.
DateModified supports the =, >, <, >=, and <= comparisons.
Name supports the = and LIKE comparisons.

The rest of the filter is executed client-side within the Cloud. For example, the following queries are processed server-side:

SELECT * FROM PriceLists WHERE Id = 1 

SELECT * FROM PriceLists WHERE DateCreated = '2021-05-26'

SELECT * FROM PriceLists WHERE DateModified = '2021-05-26'

SELECT * FROM PriceLists WHERE Name LIKE 'Wholesale group1'

Insert

To insert a price list, specify the following columns: Name and Active.

INSERT INTO PriceLists (Name, Active) VALUES ('Wholesalegroup', false)

Update

To update a price list, specify the following columns: Name and Id.

UPDATE PriceLists SET Name = 'Wholesalegroup' WHERE Id = '4'

Delete

To delete a price list, specify the Id column.

DELETE From PriceLists WHERE Id = '4'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		ID of the PriceList.
Active	Boolean	False		The status of the PriceList.
DateCreated	Datetime	True		The date of creation for the PriceList.
DateModified	Datetime	True		The last modification date of the PriceList.
Name	String	False		The name of the PriceList.

ProductImages

Returns images registered for products.

Table Specific Information

Select

SELECT * FROM ProductImages

There are two alternatives to inserting new product images for BigCommerce, ImageFile and ImageUrl.

Insert using ImageFile

To insert a product image using ImageFile, you only need the ProductId and the local path of your ImageFile, which is written as a string using forward slashes as directory separators. BigCommerce does not allow for additional parameters when inserting an image using ImageFile. Therefore, including extra parameters returns an error.

INSERT INTO ProductImages (ProductId, ImageFile) VALUES (963, 'C:/dev/tests/v19/ProviderBigCommerce/DDHU.jpg');

Insert using ImageUrl

In addition to using ImageFiles, you can add product images by specifying image URLs of images on the web. When inserting image URLs, you can add additional fields to your query, such as Description, SortOrder, and IsThumbnail.

INSERT INTO ProductImages (ProductId, ImageUrl) VALUES (955, 'http://oi50.tinypic.com/kexbfq.jpg');

Update

UPDATE ProductImages SET Description = 'Testing123' WHERE Id = 265

Delete

DELETE FROM ProductImages WHERE Id = 3

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		The Id of the image.
ProductId [KEY]	Integer	False	Products.Id	The Id of the corresponding product.
ImageFile	String	False		The local path to the original image file uploaded to BigCommerce.
StandardUrl	String	False		Standard Image URL for the product.
ThumbnailUrl	String	False		The thumbnail URL for this image. By default, this is the image size used on the category page and in side panels.
IsThumbnail	Boolean	False		Flag for identifying whether the image is used as the product's thumbnail.
SortOrder	Integer	False		The order in which the image will be displayed on the product page.
Description	String	False		The description for the image.
DateModified	Datetime	True		The last modification date of the image.
TinyUrl	String	False		Tiny URL for the product.
ZoomUrl	String	False		Zoom URL for the product.

Pseudo-Columns

疑似カラムフィールドを使用して、ユーザーは新しいレコードの作成時に必要な読み取り不可のフィールドを挿入できます。


Name	Type	Description
ImageUrl	String	The local path to the original image file to be uploaded to BigCommerce.

ProductMetafields

Returns a list of Product Metafields.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the Id and ProductId columns, which support the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM ProductMetafields WHERE ProductId = 77
SELECT * FROM ProductMetafields WHERE ProductId = 77 and Id = 2

Insert

To insert a product metafield, specify at least the following columns: ProductId, Description, Namespace, Key, Value, and PermissionSet.

INSERT Into ProductMetafields(ProductId, Description, Namespace, Key, Value, PermissionSet) Values(88, test, Warehouse, location, 88, app_only)

Update

UPDATE ProductMetafields SET Description ='updatedtest' WHERE ProductId = 77 and Id = 3

Delete

DELETE FROM ProductMetafields WHERE ProductId = 77 and Id = 3

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Unique ID of the Metafield.
ProductId [KEY]	Integer	True	Products.Id	The Id of the corresponding product.
DateCreated	Datetime	True		Date and time of the metafield?s creation.
DateModified	Datetime	True		Date and time when the metafield was last updated.
Description	String	False		Description for the metafields.
Key	String	False		The name of the field.
Value	String	False		The value of the field,must enter a JSON formatted string for ShipperHQ metafields.
Namespace	String	False		Namespace for the metafield, for organizational purposes.
OwnerClientId	String	True		ID of metafield's creator.
PermissionSet	String	False		Determines the visibility and writeability of the field by other API consumers.Must be one of app_only, read, write, read_and_sf_access or write_and_sf_access. 使用できる値は次のとおりです。app_only, read, write, read_and_sf_access, write_and_sf_access
ResourceId	Integer	True		The ID of the resource with which the metafield is associated.
ResourceType	String	True		The type of resource with which the metafield is associated.

ProductOptions

Returns data from Products table.

Table Specific Information

Select

SELECT * FROM ProductOptions

Insert

To insert a product option, a set of option values are required to be inserted along with it. This can be done by populating a temporary ProductOptionValues table with the desired values for the option you are creating, and later using this table as a value for the LinkedOptionValues pseudo-column during insertion:

INSERT INTO ProductOptionValues#TEMP (Label, SortOrder, IsDefault) VALUES ('Classic', 0, true);
INSERT INTO ProductOptionValues#TEMP (Label, SortOrder, IsDefault) VALUES ('Elegance', 1, false);
INSERT INTO ProductOptionValues#TEMP (Label, SortOrder, IsDefault) VALUES ('Avantgarde', 2, false);
INSERT INTO ProductOptions (ProductId, DisplayName, Type, Name, SortOrder, LinkedOptionValues) VALUES (955, 'ModelSeries', 'dropdown', 'Series', 0, ProductOptionValues#TEMP);

Update

UPDATE ProductOptions SET DisplayName = 'Testing123' WHERE Id = 109

Delete

DELETE FROM ProductOptions WHERE Id = 3

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the option.
ProductId [KEY]	Integer	True	Products.Id	The ID of the product.
DisplayName	String	False		The name of the option shown on the storefront.
Name	String	False		The unique option name, auto-generated from the display name, a timestamp, and the product ID.
Type	String	False		The type of option, which determines how it will display on the storefront. Acceptable values: radio_buttons, rectangles, dropdown, product_list, product_list_with_images, swatch.
SortOrder	Integer	False		The order in which the option will be displayed on the product page.
ConfigDefaultValue	String	False		The default value. Shown on a date option as an ISO-8601–formatted string, or on a text option as a string.
ConfigCheckedByDefault	Boolean	False		Flag for setting the checkbox to be checked by default.
ConfigCheckboxLabel	String	False		Label displayed for the checkbox option.
ConfigDateLimited	Boolean	False		Flag to limit the dates allowed to be entered on a date option.
ConfigDateLimitMode	String	False		The type of limit that is allowed to be entered on a date option. 使用できる値は次のとおりです。earliest, range, latest
ConfigDateEarliestValue	String	False		The earliest date allowed to be entered on the date option, as an ISO-8601 formatted string.
ConfigDateLatestValue	String	False		The latest date allowed to be entered on the date option, as an ISO-8601 formatted string.
ConfigFileTypesMode	String	False		The kind of restriction on the file types that can be uploaded with a file upload option. Values: specific - restricts uploads to particular file types; all - allows all file types. 使用できる値は次のとおりです。specific, all
ConfigFileTypesSupported	String	False		The type of files allowed to be uploaded if the file_type_option is set to specific.
ConfigFileTypesOther	String	False		A list of other file types allowed with the file upload option.
ConfigFileMaxSize	Integer	False		The maximum size for a file that can be used with the file upload option. This will still be limited by the server.
ConfigTextCharactersLimited	Boolean	False		Flag to validate the length of a text or multi-line text input.
ConfigTextMinLength	Integer	False		The minimum length allowed for a text or multi-line text option.
ConfigTextMaxLength	Integer	False		The maximum length allowed for a text or multi line text option.
ConfigTextLinesLimited	Boolean	False		Flag to validate the maximum number of lines allowed on a multi-line text input.
ConfigTextMaxLines	Integer	False		The maximum number of lines allowed on a multi-line text input.
ConfigNumberLimited	Boolean	False		Flag to limit the value of a number option.
ConfigNumberLimitMode	String	False		The type of limit on values entered for a number option. 使用できる値は次のとおりです。lowest, highest, range
ConfigNumberLowestValue	Decimal	False		The lowest allowed value for a number option if number_limited is true.
ConfigNumberHighestValue	Decimal	False		The highest allowed value for a number option if number_limited is true.
ConfigNumberIntegersOnly	Boolean	False		Flag to limit the input on a number option to whole numbers only.
ConfigProductListAdjustsInventory	Boolean	False		Flag for automatically adjusting inventory on a product included in the list.
ConfigProductListAdjustsPricing	Boolean	False		Flag to add the optional product's price to the main product's price.
ConfigProductListShippingCalc	String	False		How to factor the optional product's weight and package dimensions into the shipping quote. 使用できる値は次のとおりです。none, weight, package
LinkedOptionValues	String	False		Column for the aggregate table name holding option values.

ProductOptionValues

Returns data from Products table.

Table Specific Information

Select

SELECT * FROM ProductOptionValues

Insert

To insert a product option value, specify at least the following columns: ProductId, OptionId, Label, SortOrder, along with one of the following: ValueDataImgUrl or ValueDataColors.

Inserting ProductOptionValues with ValueDataImgUrl:

INSERT INTO ProductOptionValues (OptionId,ProductId,Label, SortOrder,ValueDataImgUrl,ValueDataColors) VALUES ('143','77','testingexceptionfornow','2','https://cdn11.bigcommerce.com/s-dr2j9p39ga/product_images/attribute_value_images/357.preview.jpg?t=1696324267','#e35e22')

Inserting ProductOptionValues with ValueDataColors:

INSERT INTO ProductOptionValues (OptionId,ProductId,Label, SortOrder,ValueDataColors) VALUES ('143','77','toasting123','2','#e35e20')

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the option value.
OptionId [KEY]	Integer	True	ProductOptions.Id	The ID of the option.
ProductId [KEY]	Integer	True	Products.Id	The ID of the product.
Label	String	False		The label of the option value shown on the storefront.
IsDefault	Boolean	False		Whether this option value is the default for this option.
SortOrder	Integer	False		The order in which the option value will be displayed on the product page.
ValueDataColors	String	False		The colors contained in the value data.
ValueDataImgUrl	String	False		The image url contained in the value data.

ProductReviews

Returns reviews registered for products.

Table Specific Information

Select

SELECT * FROM ProductReviews

Insert

To insert a product review, specify at least the following columns: Title, Status, Id, ProductId, and DateReviewed.

INSERT INTO ProductReviews (Title, Status, Id, ProductId, DateReviewed) VALUES ('Car Interior121', 'approved', '6', '103', '2019-08-24T14:15:22Z')

Update

To update a product review, specify at least the following columns: ProductId, OptionId, Label, SortOrder, along with one of the following: ValueDataImgUrl or ValueDataColors.

UPDATE ProductReviews SET Title = 'Testing123' WHERE Id = 7

Delete

DELETE FROM ProductReviews WHERE Id = 16

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		The Id of the review.
ProductId [KEY]	Integer	True	Products.Id	The Id of the corresponding product.
Title	String	False		Title of the review.
Text	String	False		Text content of the review.
Status	String	False		The status of the product review. Must be one of approved, disapproved, or pending.
Rating	Integer	False		The rating of the product review. Must be one of 0, 1, 2, 3, 4, or 5.
Email	String	False		The email of the reviewer. Must be a valid email, or an empty string.
Name	String	False		The name of the reviewer.
DateCreated	Datetime	True		The date of creation for the review.
DateModified	Datetime	True		The last modification date of the review.
DateReviewed	Datetime	False		Date the product was reviewed.

Products

Returns data from products table.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the =, >=, >, <=, and < comparisons.
Name supports the = comparison.
Sku supports the = comparison.
Description supports the = comparison.
Price supports the = comparison.
IsVisible supports the = comparison.
IsFeatured supports the = comparison.
InventoryLevel supports the =, >=, >, <=, and < comparisons.
BrandId supports the = comparison.
DateModified supports the =, >=, >, <=, and < comparisons.
Condition supports the = comparison.
DateLastImported supports the =, >=, >, <=, and < comparisons.
Availability supports the = comparison.
Categories supports the = and IN comparisons.

The rest of the filter is executed client-side within the Cloud. For example, the following queries are processed server-side:

SELECT * FROM Products WHERE Id > 5 AND Id < 10

SELECT * FROM Products WHERE IsVisible = "true"

Insert

To insert a product, specify at least the following columns: Name, Type, Description, Price, Categories, Availability and Weight.

INSERT INTO Products (Name, Type, Description, Price, Categories, Availability, Weight) VALUES ("Plain T-Shirt", "physical", "This is a test description", 29.99, 18, "available", 0.5)

Inserting products with multiple variants using a temp table:

INSERT INTO ProductVariantValues#TEMP (Label, DisplayName, Id) VALUES ('Blue', 'Color', 1)
INSERT INTO ProductVariantValues#TEMP (Label, DisplayName, Id) VALUES ('Yellow', 'Color', 2)

INSERT INTO ProductVariants#TEMP (Sku, LinkedOptionValues, Id) VALUES ('SKU-AB', 'ProductVariantValues#TEMP', 1)
INSERT INTO ProductVariants#TEMP (Sku, LinkedOptionValues, Id) VALUES ('SKU-CD', 'ProductVariantValues#TEMP', 2)

INSERT INTO Products (Name, Type, Weight, Price, ProductVariants) VALUES ('BC-8', 'physical', 60, 5700, 'ProductVariants#TEMP')

Inserting products with multiple variants using aggregates:

INSERT INTO Products (Name, Type, Weight, Price, ProductVariants) VALUES ('BC-95', 'physical', 99, 5800, '[{"Sku": "SKU-MM","option_values": [{"option_display_name": "Song","Id": "1","label": "Mary"}]}, {"Sku": "SKU-DE","option_values": [{"option_display_name": "Song","Id": "2","label": "Jane"}]}]')

Inserting products with one variant:

INSERT INTO ProductVariantValues#TEMP (Label, DisplayName) VALUES ('Blue', 'Color')

INSERT INTO ProductVariants#TEMP (Sku, LinkedOptionValues) VALUES ('SKU-AB', 'ProductVariantValues#TEMP')

INSERT INTO Products (Name, Type, Weight, Price, ProductVariants) VALUES ('BC-8', 'physical', 60, 5700, 'ProductVariants#TEMP')

Bulk Update

To perform a bulk update on products, specify at least the following columns: Description, Id, Name, Sku, and RelatedProducts.

INSERT INTO Update#TEMP (Description, Id, Name, Sku, Categories, RelatedProducts, MetaKeywords, IsCustomized, Url) VALUES ('my_details', '80', 'hello123', 'OTL', '19, 23', '1, 2', '"pqr", "xyz"', false, '/orbit-terrarium-large/'
INSERT INTO Update#TEMP (Description, Id, Name, Sku, Categories, RelatedProducts, MetaKeywords, IsCustomized, Url) VALUES ('my_details1', '86', 'example', 'ABS', '23, 21', '3, 4', '"abc", "an"', false, '/able-brewing-system/'
UPDATE products (Description, Id, Name, Sku, Categories, RelatedProducts, MetaKeywords, IsCustomized, Url) SELECT Description, Id, Name, Sku, Categories, RelatedProducts, MetaKeywords, IsCustomized, Url FROM Update#TEMP

Bulk update using aggregates:

INSERT INTO Update#TEMP (Description, Id, Name, Sku, categories, RelatedProducts, MetaKeywords, CustomUrl) VALUES ('details1', '77', 'name4456', 'SLCTBS', '23, 18', '10', '"abcd", "ab"',
            '{
			  "is_customized": False,
			  "url" : "/fog-linen-chambray-towel-beige-stripe/"
             }')
UPDATE products (Description, Id, Name, Sku, Categories, RelatedProducts, CustomUrl) SELECT Description, Id, Name, Sku, Categories, RelatedProducts, CustomUrl FROM Update#TEMP

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The Id of the product.
Name	String	False		The product name.
Type	String	False		The product type.
Sku	String	True		User-defined product code/stock keeping unit (SKU)
Description	String	False		Product description, which can include HTML formatting.
SearchKeywords	String	False		A comma-separated list of keywords that can be used to locate the product when searching the store.
AvailabilityDescription	String	False		Availability text, displayed on the checkout page under the product title, telling the customer how long it will normally take to ship this product.
Price	Decimal	False		The products price.
CostPrice	Decimal	False		The products cost price.
RetailPrice	Decimal	False		The products retail cost.
SalePrice	Decimal	False		Sale price.
MapPrice	Decimal	False		Map price.
ProductTaxCode	String	False		Tax Codes.
CalculatedPrice	Decimal	True		Price as displayed to guests, adjusted for applicable sales and rules.
SortOrder	Integer	False		Priority to give this product when included in product lists on category pages and in search results.
IsVisible	Boolean	False		Flag to determine whether or not the product should be displayed to customers browsing.
IsFeatured	Boolean	False		Flag to determine whether the product should be included in the featured products panel for shoppers viewing the store.
RelatedProducts	String	False		Defaults to -1, which causes the store to automatically generate a list of related products.
InventoryLevel	Integer	False		Current inventory level of the product.
InventoryWarningLevel	Integer	False		Inventory Warning level for the product.
Warranty	String	False		Warranty information displayed on the product page.
Weight	Decimal	False		Weight of the product, which can be used when calculating shipping costs.
Width	Decimal	False		Width of the product, which can be used when calculating shipping costs.
Height	Decimal	False		Height of the product, which can be used when calculating shipping costs.
Depth	Decimal	False		Depth of the product, which can be used when calculating shipping costs.
FixedCostShippingPrice	Decimal	False		A fixed shipping cost for the product.
IsFreeShipping	Boolean	False		Flag used to indicate whether or not the product has free shipping.
InventoryTracking	String	False		The type of inventory tracking for the product.
RatingTotal	Integer	False		The total rating for the product.
RatingCount	Integer	False		The total number of ratings the product has had.
ReviewsRatingSum	Integer	True		The total (cumulative) rating for the product.
ReviewsCount	Integer	True		The number of times the product has been rated.
TotalSold	Integer	False		Total quantity of this product sold through transactions.
DateCreated	Datetime	False		The date of which the product was created.
BrandId	Integer	True		The products brand
ViewCount	Integer	False		The number of times the product has been viewed.
PageTitle	String	False		Custom title for the products page.
MetaKeywords	String	False		Custom meta keywords for the product page.
MetaDescription	String	False		Custom meta description for the product page.
LayoutFile	String	False		The layout template file used to render this product category.
IsPriceHidden	Boolean	False		The default false value indicates that this products price should be shown on the product page.
PriceHiddenLabel	String	False		By default, an empty string. If is_price_hidden is true, the value of price_hidden_label will be displayed instead of the price.
Categories	String	False		An array of IDs for the categories this product belongs to. When updating a product, if an array of categories is supplied, then all product categories will be overwritten.
DateModified	Datetime	False		The date that the product was last modified.
Condition	String	False		The products condition. 使用できる値は次のとおりです。new, used, refurbished
IsConditionShown	Boolean	False		Flag used to determine whether the products condition will be shown to the customer on the product page.
PreorderReleaseDate	Datetime	False		Pre-order release date.
IsPreorderOnly	Boolean	False		If set to false, the product will not change its availability from preorder to available on the release date.
PreorderMessage	String	False		Custom expected-date message to display on the product page.
OrderQuantityMinimum	Integer	False		The minimum quantity an order must contain in order to purchase this product.
OrderQuantityMaximum	Integer	False		The maximum quantity an order can contain when purchasing the product.
OpenGraphType	String	False		Type of product. 使用できる値は次のとおりです。product, album, book, drink, food, game, movie, song, tv_show
OpenGraphTitle	String	False		Title of the product. If not specified, the products name will be used instead.
OpenGraphDescription	String	False		Description to use for the product.
OpenGraphUseMetaDescription	Boolean	False		Flag to determine if product description or open graph description is used.
OpenGraphUseProductName	Boolean	False		Flag to determine if product name or open graph name is used.
OpenGraphUseImage	Boolean	False		Flag to determine if product image or open graph image is used.
IsOpenGraphThumbnail	Boolean	False		If set to true, the product thumbnail image will be used as the open graph image.
UPC	String	False		The product UPC code, which is used in feeds for shopping comparison sites.
GTIN	String	False		Global Trade Item Number.
OptionSetId	Integer	True		The ID of the option set applied to the product.
TaxClassId	Integer	True		The ID of the tax class applied to the product.
OptionSetDisplay	String	True		The position on the product page where options from the option set will be displayed.
BinPickingNumber	String	False		The BIN picking number for the product.
CustomUrl	String	False		Custom URL (if set) overriding the structure dictated in the stores settings.
CustomFields	String	False		200 maximum custom fields per product. 255 maximum characters per custom field.
ManufacturerPartNumber	String	False		Manufacturer Part Number.
IsCustomized	Boolean	False		Returns true if the URL has been changed from its default state (the auto-assigned URL that BigCommerce provides.
Url	String	False		Product URL on the storefront.
Availability	String	False		Availability of the product.
PrimaryImageId	Integer	True		Id of the primary image.
PrimaryImageProductId	Integer	True		ProductId of the primary image.
PrimaryImageIsThumbnail	Boolean	True		Primary image Is Thumbnail or not.
PrimaryImageSortOrder	String	True		Sort Order of the primary image.
PrimaryImageDescription	String	True		Description of the primary image.
PrimaryImageImageFile	String	True		Image file of the primary image.
PrimaryImageUrlZoom	String	True		Zoom Url of the primary image.
PrimaryImageStandardUrl	String	True		Standard url of the primary image.
PrimaryImageUrlThumbnail	String	True		Thumbnail url of the primary image.
PrimaryImageUrlTiny	String	True		Tiny url of the primary image.
PrimaryImageDateModified	Datetime	True		Modified Date of the primary image.
GiftWrappingOptionsType	String	True		Type of gift-wrapping options.
GiftWrappingOptionsList	String	True		Type of gift-wrapping option IDs.
BaseVariantId	String	True		Base Variant Id.
VideoURL	String	True		Returns the URL of the first video hosted on the site. To retrieve all video URLs, refer to the ProductVideos view.

Pseudo-Columns

疑似カラムフィールドを使用して、ユーザーは新しいレコードの作成時に必要な読み取り不可のフィールドを挿入できます。


Name	Type	Description
ProductVariants	String	Variants of the Products

ProductVariants

Returns data from Products table.

Table Specific Information

Select

SELECT * FROM ProductVariants

Insert

To insert a product variant, a set of option values must also be inserted. This can be done by populating a temporary ProductVariantValues table with the desired values for the option you are creating, and later using this table as a value for the LinkedOptionValues pseudo-column during insertion:

INSERT INTO ProductVariantValues#TEMP (Id, OptionId, Label, DisplayName) VALUES (181, 118, 'Elegance', 'Series');
INSERT INTO ProductVariants (ProductId, LinkedOptionValues, SKU) VALUES (955, ProductVariantValues#TEMP, 'DSFMGG');

Update

UPDATE ProductVariants SET Weight = '23' WHERE Id = 64

Delete

DELETE FROM ProductVariants WHERE Id = 3

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the product variant.
ProductId [KEY]	Integer	True	Products.Id	The ID of the product.
SKU	String	False		User defined product code/stock keeping unit (SKU).
SkuID	String	False		The ID for User defined product code/stock keeping unit (SKU).
Price	Decimal	False		The price of the product variant. The price should include or exclude tax, based on the store settings.
CalculatedPrice	Decimal	False		The variant’s calculated price on the storefront.
SalePrice	Decimal	False		The variant’s sale price on the storefront.
RetailPrice	Decimal	False		The variant’s retail price on the storefront.
MapPrice	Integer	False		The Minimum Advertised Price.
Weight	Decimal	False		This variant’s base weight on the storefront. If this value is null, the product’s default weight (set in the Product resource’s weight field) will be used as the base weight.
CalculatedWeight	Decimal	False		The variant’s calculated weight on the storefront.
Width	Decimal	False		Width of the variant, which can be used when calculating shipping costs.
Height	Decimal	False		The Height of the variant, which can be used when calculating shipping costs.
Depth	Decimal	False		The Depth of the variant, which can be used when calculating shipping costs.
IsFreeShipping	Boolean	False		The Flag used to indicate whether the variant has free shipping. If true, the shipping cost for the variant will be zero.
FixedCostShippingPrice	Decimal	False		The fixed shipping cost for the variant. If defined, this value will be used during checkout instead of normal shipping-cost calculation.
PurchasingDisabled	Boolean	False		Accepts AvaTax System Tax Codes, which identify products and services that fall into special sales-tax categories.
PurchasingDisabledMessage	String	False		Accepts AvaTax System Tax Codes, which identify products and services that fall into special sales-tax categories.
ImageUrl	String	False		The Image url for the variant.
CostPrice	Decimal	False		The cost price of the variant.
Upc	String	False		The UPC code used in feeds for shopping comparison sites and external channel integrations.
Mpn	String	False		The Manufacturer Part Number (MPN) for the variant.
Gtin	String	False		The Global Trade Item Number.
InventoryLevel	Integer	False		The Inventory level for the variant, which is used when the product’s inventory_tracking is set to variant.
InventoryWarningLevel	Integer	False		The Inventory warning level for the product
BinPickingNumber	String	False		Identifies where in a warehouse the variant is located.
LinkedOptionValues	String	False		The Option Value Id.

ShippingMethods

Lists all shipping methods.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
ZoneId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following query is processed server-side:

SELECT * FROM ShippingMethods

SELECT * FROM ShippingMethods WHERE ZoneId = 1

SELECT * FROM ShippingMethods WHERE ZoneId = 1 and Id = 1

Insert

To insert a shipping method, Name, Type, Settings, and ZoneId must also be inserted. Settings must be passed as a JSON object.

INSERT INTO ShippingMethods (Name, Type, Enabled, ZoneId, HandlingFeesFixedSurcharge, Settings) VALUES ('Flat Rate 2', 'perorder', true, 1, 0, '{ "rate" : 7 }')

Update

To update a ShippingMethod, Name, Type, Settings, ZoneId and Id are required.

UPDATE ShippingMethods SET Name = 'Flat Rate 1', Type = 'perorder', Settings = '{ "rate" : 7 }', HandlingFeesPercentageSurcharge = 1 WHERE Id = 9 and ZoneId = 1

Delete

To delete a shipping method, Id and ZoneId are required.

DELETE FROM ShippingMethods WHERE Id = 9 and ZoneId = 1

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Shipping Method Id.
Name	String	False		Shipping Method name.
Type	String	False		Shipping Method type.
Enabled	Boolean	False		Minimum subtotal of the free shipping.
HandlingFeesPercentageSurcharge	Decimal	False		Percentage surcharge of the handling fee.
HandlingFeesFixedSurcharge	Decimal	False		Fixed surcharge of the handling fee.
IsFallback	Boolean	True		Whether or not this shipping zone is the fallback if all others are not valid for the order.
Settings	String	False		Shipping method settings.
ZoneId	Integer	True		The Shipping Zone Id

ShippingZones

Lists all shipping zones.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the Id column, which supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following query is processed server-side:

SELECT * FROM ShippingZones WHERE Id = 1

Insert

To insert a shipping zone, a set of Locations must also be inserted. This can be done by populating a temporary ShippingZoneLocations table with the desired values for the option you are creating, and later using this table as a value for the Locations column during insertion:

INSERT INTO ShippingZoneLocations#Temp (CountryIso2, Zip) values ('US', '11103')
INSERT INTO ShippingZones (Name, Type, locations) VALUES ('United States', 'zip', 'ShippingZoneLocations#Temp')

Update

UPDATE ShippingZones SET Enabled=true WHERE Id = 7

Delete

DELETE FROM ShippingZones WHERE Id = 7

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Zone Id.
Name	String	False		Zone name.
Type	String	False		Zone type. 使用できる値は次のとおりです。zip, country, state, global
FreeShippingEnabled	Boolean	False		Indicator if free shipping is enabled.
FreeShippingMinimumSubTotal	Decimal	False		Minimum subtotal of the free shipping.
FreeShippingExcludeFixedShippingProducts	Boolean	False		Indicator whether or not to exclude fixed shipping on products.
HandlingFeesDisplaySeparately	Boolean	False		Indicator whether or not to display the handling fees separately.
HandlingFeesPercentageSurcharge	Decimal	False		Percentage surcharge of the handling fee.
HandlingFeesFixedSurcharge	Decimal	False		Fixed surcharge of the handling fee.
Enabled	Boolean	False		Whether this shipping zone is enabled.
Locations	String	False		Array of zone locations.

ビュー

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

CData Cloud - BigCommerce ビュー


Name	Description
Blogposts	Returns all blog posts.
BlogTags	Returns a list of Blog Tags.
CustomerGroups	Returns a list of customer groups.
DownloadConsignments	Returns consignments and their corresponding downloads.
EmailConsignments	Returns consignments and their corresponding emails.
GiftCertificates	Lists all gift certificates.
OrderCoupons	Lists all order coupons.
OrderItemOptionValues	Returns data from OrderItems table.
OrderMessages	Gets the messages associated with an order.
OrderRefunds	Returns a list of refunds.
OrderRefundsItems	List of Items refunded.
OrderRefundsPayments	Refund payments made to payment providers.
OrderShippingAddresses	Returns shipping addresses and their corresponding orders.
OrderStatuses	Returns order statuses.
OrderTaxes	Gets all order taxes related to an order.
Pages	Lists all pages.
PaymentMethods	Lists all payment methods.
PickupConsignments	Returns consignments and their corresponding pickups.
PriceListAssignments	Returns an array of Price List Assignments matching a particular Customer Group and Price List and Channel.
ProductBulkPricingRules	Returns bulk pricing rules applied to a product.
ProductCustomFields	Lists all tax classes.
ProductRules	Returns rules that modify the properties of a product, such as weight, price, and product image.
ProductVariantValues	Returns data from Products table.
ProductVideos	Returns Embedded videos displayed on product listings.
Redirects	Lists all redirect URLs.
ShipmentItems	Returns data from Items within a shipment.
ShippingConsignmentQuotes	Gets all shipping quotes persisted on an order for a shipping consignment.
ShippingConsignments	Returns consignments and their corresponding shipping orders.
ShippingZoneLocations	Lists all shipping zone locations
Stores	Lists all Stores.
TaxClasses	Lists all tax classes.
Transactions	Lists all transactions.

Blogposts

Returns all blog posts.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
Url supports the = comparison.
IsPublished supports the = comparison.

The rest of the filter is executed client-side within the Cloud. For example, the following queries are processed server-side:

SELECT * FROM Blogposts WHERE Id = "1234" 

SELECT * FROM Blogposts WHERE IsPublished = "true"

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the blogpost
Title	String		Title of this blog post.
URL	String		URL for the public blog post.
PreviewURL	String		URL to preview the blog post.
Body	String		Text body of the blog post.
Summary	String		Summary of the blog post.
IsPublished	Boolean		Whether the blog post is published.
PublishedDate	Datetime		Date when the blogpost was published.
PublishedTimezone	String		Timezone when the blogpost was published.
PublishedTimezoneType	String		Type of the timezone.
PublishedDateISO	String		Published date in ISO8601 format.
MetaDescription	String		Description text for this blog posts meta element.
MetaKeywords	String		Keywords for this blog posts meta element.
Author	String		Name of the blog posts author.
ThumbnailPath	String		Local path to a thumbnail image within the product_images folder to accompany the blog post.

BlogTags

Returns a list of Blog Tags.

Columns


Name	Type	References	Description
BlogIds [KEY]	String		Id of the blogpost
Tag [KEY]	String		Tag which belongs to the blog.

CustomerGroups

Returns a list of customer groups.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
Name supports the = and LIKE comparisons.
IsDefault supports the = comparison.
IsGroupForGuests supports the = comparison.
DateModified supports the =, <=, >=, <, and > comparisons.

The rest of the filter is executed client-side within the Cloud. For example, the following queries are processed server-side:

SELECT * FROM CustomerGroups WHERE Id = 1 

SELECT * FROM CustomerGroups WHERE IsDefault = "true"

SELECT * FROM CustomerGroups WHERE IsGroupForGuests = "true"

SELECT * FROM CustomerGroups WHERE DateModified = '2024-12-27 01:20:31.0'

SELECT * FROM CustomerGroups WHERE DateModified <= '2024-12-27 01:20:31.0'

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the customer group
Name	String		Name of the group
IsDefault	Boolean		Determines whether new customers are assigned to this group by default.
CategoryAccessType	String		Type of the category access.
CategoryAccessCategories	String		An array of category IDs.
IsGroupForGuests	Boolean		Describes whether the group is for guests. There can only be one customer group for guests at a time.
DateCreated	Datetime		Date on which the customer group was created.
DateModified	Datetime		Date on which the customer group was last modified.
DiscountRules	String		A collection of discount rules that are automatically applied to customers who are members of the group.

DownloadConsignments

Returns consignments and their corresponding downloads.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the OrderId column, which supports the = comparison.

For example, the following queries are processed server-side:

SELECT * FROM DownloadConsignments WHERE OrderId = 123

Columns


Name	Type	References	Description
OrderId	Integer	Orders.Id	Identifier for the BigCommerce Order with which this transaction is associated.
RecipientEmail	String		The recipient email of the digital consignment.
DownloadLineItemResources	String		URL where you can use a GET request to get the downloads line items for the order consignment.
DownloadLineItemUrl	String		Path where you can use a GET request to get the downloads line items for the order consignment.

EmailConsignments

Returns consignments and their corresponding emails.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the OrderId column, which supports the = comparison.

For example, the following queries are processed server-side:

SELECT * FROM EmailConsignments WHERE OrderId = 123

Columns


Name	Type	References	Description
OrderId	Integer	Orders.Id	Identifier for the BigCommerce Order with which this transaction is associated.
RecipientEmail	String		The recipient email of the gift certificate.
GiftCertificatesLineItemResources	String		URL where you can use a GET request to get the gift certificate line items for the order consignment.
GiftCertificatesLineItemUrl	String		Path where you can use a GET request to get the gift certificate line items for the order consignment.

GiftCertificates

Lists all gift certificates.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the =, >=, >, <=, and < comparisons.
Code supports the = comparison.
ToName supports the = comparison.
ToEmail supports the = comparison.
OrderId supports the = comparison.
FromEmail supports the = comparison.
FromName supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM GiftCertificates WHERE Id = 1 

SELECT * FROM GiftCertificates WHERE IsDefault = "true"

SELECT * FROM GiftCertificates WHERE IsGroupForGuests = "true"

Columns


Name	Type	References	Description
Id [KEY]	Integer		The ID of the gift certificate.
Code	String		A unique string that the customer can input to redeem the gift certificate.
Amount	Decimal		Value of the gift certificate.
Status	String		Status of the gift certificate.
Balance	Decimal		Remaining value of the gift certificate.
ToName	String		Name of the recipient.
ToEmail	String		Email of the recipient.
OrderId	Integer		The ID of the order.
Template	String		The email theme to use in the message sent to the recipient.
Message	String		Text that will be sent to the recipient, such as Congratulations.
FromName	String		Name of the customer who purchased the gift certificate.
FromEmail	String		Email of the customer who purchased the gift certificate.
CustomerId	Integer		The id of the customer placing the order.
ExpiryDate	Datetime		Date on which the gift certificate is set to expire.
PurchaseDate	Datetime		Date the gift certificate was purchased.
CurrencyCode	String		The currency code.

OrderCoupons

Lists all order coupons.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the OrderId column, which supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM OrderCoupons WHERE OrderId = 2;

Columns


Name	Type	References	Description
Id [KEY]	Integer		Numeric ID of this product within this order.
OrderId	Integer	Orders.Id	Numeric ID of the associated order.
CouponId	Integer		The coupon id.
Code	String		The code of the coupon.
Amount	Decimal		The amount of the coupon.
Type	Integer		The type of the coupon.
Discount	Decimal		The discount of the coupon.

OrderItemOptionValues

Returns data from OrderItems table.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

OrderId supports the = comparison.
OrderItemId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM [OrderItemOptionValues] WHERE OrderItemId = 2522;
SELECT * FROM [OrderItemOptionValues] WHERE OrderId = 100 and OrderItemId = 27;

Columns


Name	Type	References	Description
Id [KEY]	Integer		The numerical ID of the option value within the group of ordered items.
OptionId	Integer	ProductOptions.Id	The ID of the option from Product Options table.
OrderItemId [KEY]	Integer	OrderItems.Id	The ID of the product.
OrderItemOptionId	Integer		The numerical ID of the option within the group of ordered items.
DisplayName	String		The name of the option shown on the storefront.
DisplayValue	String		The value of the option shown on the storefront.
Value	String		The actual value of the option.
Name	String		The unique option name, auto-generated from the display name, a timestamp, and the product ID.
Type	String		The type of option, which determines how it will display on the storefront. Acceptable values: Checkbox \| Date field \| File Upload \| Multi-line text field \| Multiple choice \| Product Pick List \| Swatch \| Text field
DisplayStyle	String		The specific style which the order will be displayed as.
OrderId	Integer		Numeric ID of the associated order.
OrderProductId	Integer		Order product Id
DisplayNameCustomer	String		The product option name that is shown to customer in storefront.
DisplayNameMerchant	String		The product option name that is shown to merchant in Control Panel.
DisplayValueCustomer	String		The product option value that is shown to customer in storefront.
DisplayValueMerchant	String		The product option value that is shown to merchant in Control Panel.

OrderMessages

Gets the messages associated with an order.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the <=, <, >=, and > comparisons.
OrderId supports the = comparison.
CustomerId supports the = comparison.
Status supports the = comparison.
IsFlagged supports the = comparison.
DateCreated supports the =, <=, <, >=, and > comparisons.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM OrderMessages WHERE OrderId = 2

SELECT * FROM OrderMessages WHERE IsFlagged = "true"

SELECT * FROM OrderMessages WHERE DateCreated >= "2019-01-01" AND DateCreated <= "2020-01-01"

Columns


Name	Type	References	Description
Id [KEY]	Integer		Numeric ID of this product within this order.
OrderId	Integer	Orders.Id	Numeric ID of the associated order.
StaffId	Integer		The staff id.
CustomerId	Integer		The customer id.
Type	String		Type of the message.
Subject	String		Subject of the message.
Message	String		Message content.
Status	String		Status of the message.
IsFlagged	Boolean		Indicator if the message is flagged.
DateCreated	Datetime		Datetime when the message was first created.

OrderRefunds

Returns a list of refunds.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = and IN comparisons.
OrderId supports the = and IN comparisons.
Created supports the =, >, <, >=, and <= comparisons.

The rest of the filter is executed client-side within the Cloud. For example, the following queries are processed server-side:

SELECT * FROM OrderRefunds WHERE Id = 1 

SELECT * FROM OrderRefunds WHERE OrderId = 2

Columns


Name	Type	References	Description
Id [KEY]	Integer		Refund resource ID.
Created [KEY]	Datetime		Timestamp of when this refund was created.
OrderId	Integer		Reference to order id.
Reason	String		Reason for refund.
TotalAmount	Decimal		A non-negative 2 decimal place rounded value that represents the amount that can be charged/refunded via payment providers.
TotalTax	Decimal		Total tax amount refunded back to the shopper. Note: order_level_amount does not affect tax liability.
UserId	Integer		Reference to the user's id who create this refund.
UsesMerchantOverrideValues	Boolean		Whether refund amount and tax are provided explicitly by merchant override

OrderRefundsItems

List of Items refunded.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

OrderId supports the = and IN comparisons.
OrderRefundsId supports the = and IN comparisons.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM OrderRefundsItems WHERE OrderRefundId = 2

SELECT * FROM OrderRefundsItems WHERE OrderId = 2

Columns


Name	Type	References	Description
ItemId [KEY]	Integer		order_product.id corresponding to the item_types of PRODUCT, GIFT_WRAPPING. order_address.id corresponding to the item_types of SHIPPING, HANDLING. order.id corresponding to the item_type of ORDER.
OrderRefundId [KEY]	Integer	OrderRefunds.Id	The ID of the order refund.
OrderId	Integer	OrderRefunds.OrderId	Reference to order id.
ItemType	String		Type of item that was refunded.
Quantity	Integer		Quantity of item refunded. Note: this will only be populated for item_type PRODUCT
Reason	String		Reason for refunding an item.
RequestedAmount	Decimal		A non-negative 2 decimal place rounded value that represents the amount that can be charged/refunded via payment providers..

OrderRefundsPayments

Refund payments made to payment providers.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

OrderId supports the = and IN comparisons.
OrderRefundsId supports the = and IN comparisons.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM OrderRefundsItems WHERE OrderRefundId = 2

SELECT * FROM OrderRefundsPayments WHERE OrderId = 2

Columns


Name	Type	References	Description
Id [KEY]	Integer		The ID of the refund payment.
OrderRefundId	Integer	OrderRefunds.Id	The ID of the order refund.
OrderId	Integer	OrderRefunds.OrderId	Reference to order id.
ProviderId	String		Reference to payment provider.
Amount	Decimal		A non-negative 2 decimal place rounded value that represents the amount that can be charged/refunded via payment providers.
Offline	Boolean		Indicate whether payment was offline.
IsDeclined	Boolean		Indicate if this payment has been declined by payment provider.
DeclinedMessage	String		Message indicate why payment was declined.

OrderShippingAddresses

Returns shipping addresses and their corresponding orders.

Columns


Name	Type	References	Description
Id [KEY]	Integer		ID of this shipping address.
OrderId	Integer	Orders.Id	ID of the order.
FirstName	String		Addressee first name.
LastName	String		Addressee last name.
Company	String		Addressee company.
Street1	String		Street address (first line).
Street2	String		Street address (second line).
City	String		Addressee city.
Zip	String		ZIP or postal code.
Country	String		Addressee's country.
State	String		The name of the state or province. Should be spelled out in full, for example, California.
Email	String		Recipient's email address.
Phone	String		Recipient's telephone number.
BaseCost	Decimal		The base value of the order’s items.
BaseHandlingCost	Decimal		The base handling charge.
CostExTax	Decimal		The value of the order’s items, excluding tax.
CostIncTax	Decimal		The value of the order’s items, including tax.
CostTax	Decimal		The tax amount on the order.
CostTaxClassId	String		The ID of the tax class applied to the product.
CountryIso2	String		2-letter ISO Alpha-2 code for the country.
HandlingCostExTax	Decimal		The handling charge, excluding tax.
HandlingCostIncTax	Decimal		The handling charge, including tax.
HandlingCostTax	Decimal		The handling charge.
HandlingCostTaxClassId	String		A read-only value. Do not attempt to set or modify this value in a POST or PUT operation. (NOTE: Value ignored if automatic tax is enabled on the store.)
ItemsShipped	Integer		The number of items that have been shipped.
ItemsTotal	Integer		The total number of items in the order.
ShippingMethod	String		Text code identifying the BigCommerce shipping module selected by the customer.
ShippingZoneId	Integer		Numeric ID of the shipping zone.
ShippingZoneName	String		Name of the shipping zone.

OrderStatuses

Returns order statuses.

Columns


Name	Type	References	Description
Id [KEY]	Integer		The Id of the type of order status
Name	String		Name of the type of order status.
Systemlabel	String		System name of the type of order status.
CustomLabel	String		Custom order status label given in the Control Panel.
SystemDescription	String		System description of the order status.

OrderTaxes

Gets all order taxes related to an order.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

OrderId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM OrderTaxes WHERE OrderId = 2

Columns


Name	Type	References	Description
Id [KEY]	Integer		Numeric ID of this product within this order.
OrderId	Integer	Orders.Id	Numeric ID of the associated order.
OrderAddressId	Integer		The unique numeric identifier of the order address object associated with the order.
TaxRateId	Integer		The unique numeric identifier of the tax rate.
TaxClassId	Integer		The unique numeric identifier of the tax class object.
Name	String		The name of the tax class object.
Class	String		The name of the type of tax that was applied.
Rate	Decimal		The tax rate.
Priority	Decimal		The order in which the tax is applied.
PriorityAmount	Decimal		The amount of tax calculated on the order.
LineAmount	Decimal		The line amount.
OrderProductId	String		If the line_item_type is item or handling then this field will be the order product id. Otherwise the field will return as null.
LineItemType	String		Type of tax on item.

Pages

Lists all pages.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the Id column, which supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following query is processed server-side:

SELECT * FROM Pages WHERE Id = 1

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the page.
ChannelId	Integer	Channels.Id	Channel Id of the page.
Name	String		Name of the page.
Email	String		Email of the page.
MetaTitle	String		Title of the page.
Body	String		Body of the page.
IsVisible	Boolean		Indicator if page is visible.
ParentId	Integer		Parent id of the page.
SortOrder	Integer		Sort order of the page.
MetaKeywords	String		Keywords of the page.
Type	String		Type of the page.
MetaDescription	String		Description of the page.
IsHomepage	Boolean		Indicator if the page is homepage.
IsCustomersOnly	Boolean		Indicator if the page is customer only.
SearchKeywords	String		Search keywords of the page.
Url	String		Url of the page.

PaymentMethods

Lists all payment methods.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

OrderId supports the = comparison.
CheckoutId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM PaymentMethods WHERE OrderId = 2;
SELECT * FROM PaymentMethods WHERE CheckoutId = 2;

The following query retrieves results for the OrderIds that have a status = Incomplete.

SELECT * FROM PaymentMethods;

Columns


Name	Type	References	Description
Id [KEY]	String		Identifier for this payment method
Name	String		Name of this payment method
TestMode	Boolean		Whether this payment method is on test mode
StoredInstruments	String		Stored Instruments of payment method
SupportedInstruments	String		Supported Instruments of payment method
Type	String		Type to classify this payment method
OrderId	String		The ID of the subject order.
CheckoutId	String		The ID of the subject checkout; identical to the cart ID.
RowId [KEY]	String		A unique identifier for payment methods, generated by combining the OrderId (or CheckoutId) with the Id. When OrderId is present, the format is o_{OrderId}_{id}; otherwise, it is c_{CheckoutId}_{id}.

PickupConsignments

Returns consignments and their corresponding pickups.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the OrderId column, which supports the = comparison.

For example, the following queries are processed server-side:

SELECT * FROM PickupConsignments WHERE OrderId = 123

Columns


Name	Type	References	Description
Id	Integer		The ID of the pickup consignment to update.
OrderId	Integer	Orders.Id	Identifier for the BigCommerce Order with which this transaction is associated.
PickupMethodId	Integer		The ID of the pickup consignment to update.
PickupMethodDisplayName	String		A name for the pickup method that can be displayed to shoppers.
CollectionInstructions	String		A message for shoppers explaining how to collect their pickup order.
CollectionTimeDescription	String		A message for shoppers indicating the estimated time their pickup order will be ready for collection.
LocationId	Integer		ID of the location.
LocationName	String		The name of the pickup location.
LocationCode	String		The code of the pickup location.
LocationAddressLine1	String		Pickup location's Street address (first line)
LocationAddressLine2	String		Pickup location's Street address (second line).
LocationCity	String		Pickup location's city.
LocationState	String		Pickup location's state.
LocationPostalCode	String		Pickup location's postal code.
LocationCountryAlpha2	String		2-letter ISO Alpha-2 code for the country.
LocationEmail	String		Pickup location's email address
LocationPhone	String		Pickup location's phone number.
PickupsLineItemResources	String		URL where you can use a GET request to get the pickups line items for the order consignment.
PickupsLineItemUrl	String		Path where you can use a GET request to get the pickups line items for the order consignment.

PriceListAssignments

Returns an array of Price List Assignments matching a particular Customer Group and Price List and Channel.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
CustomerGroupId supports the = and IN comparisons.
PriceListId supports the = and IN comparisons.
ChannelId supports the = and IN comparisons.

The rest of the filter is executed client-side within the Cloud.

SELECT * FROM PriceListAssignments WHERE CustomerGroupId IN (1, 2)

SELECT * FROM PriceListAssignments WHERE PriceListId IN (1, 2)

SELECT * FROM PriceListAssignments WHERE PriceListId = 1

SELECT * FROM PriceListAssignments WHERE CustomerGroupId = 1

Columns


Name	Type	References	Description
Id	Integer		The ID of the PriceList Assignment.
CustomerGroupId	Integer		The ID of the customer group.
PriceListId	Integer		The ID of the PriceList.
ChannelId	Integer		ID of the Channel.

ProductBulkPricingRules

Returns bulk pricing rules applied to a product.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
ProductId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM ProductBulkPricingRules WHERE ProductId = 77

SELECT * FROM ProductBulkPricingRules WHERE ProductId = 77 AND Id = 1

Columns


Name	Type	References	Description
Id [KEY]	Integer		The ID of the bulk discount rule.
ProductId [KEY]	Integer	Products.Id	The ID of the product associated with this bulk discount rule.
Min	Integer		The minimum inclusive quantity of a product to satisfy this rule. Must be greater than or equal to zero.
Max	Integer		The maximum inclusive quantity of a product to satisfy this rule.
Type	String		Type of the discount.
TypeValue	Decimal		The value of the discount

ProductCustomFields

Lists all tax classes.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
ProductId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM ProductCustomFields WHERE Id = 1 

SELECT * FROM ProductCustomFields WHERE ProductId = 2

Columns


Name	Type	References	Description
Id [KEY]	Integer		Numeric ID of the custom field.
ProductId	Integer		Id of the product that the custom field belongs to.
Name	String		Name of the custom field.
Text	String		Value of the custom field.

ProductRules

Returns rules that modify the properties of a product, such as weight, price, and product image.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
ProductId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM ProductRules WHERE Id = 1 

SELECT * FROM ProductRules WHERE ProductId = 2

Columns


Name	Type	References	Description
Id [KEY]	Integer		The ID of the rule.
ProductId [KEY]	Integer	Products.Id	The ID of the product to which the rule belongs.
SortOrder	Integer		The order in which the rule will be displayed on the product page.
IsEnabled	Boolean		If set to true, the rule will be evaluated when a customer configures a products options.
IsStop	Boolean		If set to true and the rule evaluates to true, no more rules with a higher sort_order will be processed.
PriceAdjusterName	String		Price adjuster name.
PriceAdjusterValue	Decimal		Price adjuster value.
WeightAdjusterName	String		Weight adjuster name.
WeightAdjusterValue	Decimal		Weight adjuster value.
IsPurchasingDisabled	Boolean		If true this rule prohibits purchasing the product with the configured option values.
PurchasingDisabledMessage	String		The message to display if the rule disabled purchasing the product.
IsPurchasingHidden	Boolean		If true the rule hides the options on the product.
ImageFile	String		A path to an rule already uploaded via FTP in the import directory and the path should be relative from the import directory.

ProductVariantValues

Returns data from Products table.

Columns


Name	Type	References	Description
Id [KEY]	Integer		The ID of the option value.
VariantId [KEY]	Integer	ProductVariants.Id	The ID of the corresponding variant.
OptionId [KEY]	Integer	ProductOptions.Id	The ID of the option.
Label	String		The label of the option value shown on the storefront.
DisplayName	String		The label of the option value shown on the storefront.
ProductId	String	Products.Id	The Id of the product

ProductVideos

Returns Embedded videos displayed on product listings.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

Id supports the = comparison.
ProductId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM ProductVideos WHERE Id = 1 

SELECT * FROM ProductVideos WHERE ProductId = 1

Columns


Name	Type	References	Description
Id [KEY]	String		ID of this video.
ProductId [KEY]	Integer	Products.Id	ID of the associated product.
SortOrder	Integer		Sort order for this video.
Name	String		The title for the video.
Length	String		The duration of the video.
Description	String		The description for the video.
VideoId	String		The ID of the video on a host site.
Type	String		The video type (a short name of a host site)
VideoURL	String		The URL for the video.

Redirects

Lists all redirect URLs.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the Id column, which supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following query is processed server-side:

SELECT * FROM Redirects WHERE Id = 1

Columns


Name	Type	References	Description
Id [KEY]	Integer		Numeric ID of the redirect.
SiteId	Integer		Site ID of the redirect.
FromPath	String		The path from which to redirect.
ToType	String		The type of redirect.
ToEntityId	Integer		EntityId of the redirect.
ToURL	String		URL of the redirect.
URL	String		Full destination URL for the redirect. Must be explicitly included via URL parameter.

ShipmentItems

Returns data from Items within a shipment.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

OrderShipmentId supports the = comparison.
OrderId supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries are processed server-side:

SELECT * FROM ShipmentItems WHERE OrderShipmentId = 1 

SELECT * FROM ShipmentItems WHERE OrderId = 2

Columns


Name	Type	References	Description
Id [KEY]	Integer	OrderItems.Id	Id of the Product within the order.
ProductId [KEY]	Integer	Products.Id	Numerical Id of the product.
OrderShipmentId [KEY]	String	OrderShipments.Id	Id of the shipment.
Quantity	Integer		Quantity of product shipped.
OrderId	Integer		ID of the order associated with this shipment.

ShippingConsignmentQuotes

Gets all shipping quotes persisted on an order for a shipping consignment.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

OrderId supports the = comparison.
ShippingConsignmentId supports the = comparison.

For example, the following queries are processed server-side:

SELECT * FROM ShippingConsignmentQuotes WHERE OrderId = 123 and ShippingConsignmentId = 234

Columns


Name	Type	References	Description
Id	Integer		ID of the shipping quote.
OrderId	Integer	Orders.Id	Identifier for the BigCommerce Order with which this transaction is associated.
ShippingConsignmentId	Integer	ShippingConsignments.Id	ID of the shipping consignment.
UUID	String		UUID of the shipping quote.
Timestamp	String		Time the order was created in RFC 2822 format.
ProviderCode	String		Code of the shipping provider.
CarrierCode	String		Code of the shipping carrier.
RateCode	String		Type of delivery. This can vary based on shipping quote.
RateId	String		This can vary based on shipping quote.
MethodId	Integer		Shipping method ID.
ProviderQuoteRateValue	Integer		Provider quote's rate_value.
ProviderQuoteRateUnit	String		Provider quote's rate_unit.
ProviderQuoteName	String		Provider quote's name.
ProviderQuoteTransitTime	Integer		Provider quote's transitTime.
ProviderQuoteSignatureConfirmationFee	String		Provider quote's signatureConfirmationFee.
ProviderQuoteCarrierName	String		Provider quote's CarrierName.
ProviderQuoteDeliveryMessage	String		Provider quote's deliveryMessage.
ProviderQuoteLabelSizes	String		Provider quote's label sizes.
ProviderQuoteDates	String		Provider quote's dates.
ProviderQuoteInsuredMailFee	String		Provider quote's insuredMailFee.
ProviderQuoteRateId	String		Provider quote's rateId.
ProviderQuoteDescription	String		Provider quote's description.
ProviderQuoteAdditionalInfo	String		Provider quote's additionalInfo.

ShippingConsignments

Returns consignments and their corresponding shipping orders.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the Id column, which supports the = comparison.

For example, the following queries are processed server-side:

SELECT * FROM ShippingConsignments WHERE OrderId = 123

Columns


Name	Type	References	Description
Id	Integer		ID of the shipping consignment.
OrderId	Integer	Orders.Id	Identifier for the BigCommerce Order with which this transaction is associated.
FirstName	String		First name.
LastName	String		Last name.
Company	String		company.
Street1	String		Street address (first line).
Street2	String		Street address (second line).
Country	String		country
State	String		State
Email	String		Recipient?s email address.
Phone	String		Recipient?s telephone number.
ItemsTotal	Decimal		The total number of items in the order.
ItemsShipped	Decimal		The number of items that have been shipped.
ShippingMethod	String		Text identifying the BigCommerce shipping module selected by the customer.
BaseCost	Decimal		The base shipping cost value.
CostExTax	Decimal		The shipping cost value excluding tax.
CostIncTax	Decimal		The shipping cost value including tax.
CostTax	Decimal		The tax amount on the shipping cost.
CostTaxClassId	Integer		The ID of the tax class applied to the shipping cost. Ignored if automatic tax is enabled.
BaseHandlingCost	Decimal		The base handling charge.
HandlingCostExTax	Decimal		The handling charge, excluding tax.
HandlingCostIncTax	Decimal		The handling charge, including tax.
HandlingCostTax	Decimal		The handling charge, including tax number.
HandlingCostTaxClassId	Integer		The ID of the tax class applied to the handling charge. Ignored if automatic tax is enabled.
ShippingZoneId	Decimal		The numeric ID of the shipping zone.
ShippingZoneName	String		The name of the shipping zone.
ShippingQuotesResources	String		URL where you can use a GET request to get the shipping quotes for the order consignment.
ShippingQuotesUrl	String		Path where you can use a GET request to get the shipping quotes for the order consignment.
ZipCode	String		zip code of the shipping quotes.
ShippingLineItemResources	String		URL where you can use a GET request to get the shipping line items for the order consignment.
ShippingLineItemUrl	String		Path where you can use a GET request to get the shipping line items for the order consignment.
City	String		Path where you can use a GET request to get the shipping line items for the order consignment.
CountryIso	String		Path where you can use a GET request to get the shipping line items for the order consignment.

ShippingZoneLocations

Lists all shipping zone locations

Columns


Name	Type	References	Description
Id [KEY]	Integer		Location’s ID.
ShippingZoneId [KEY]	Integer		The Id of the Shipping Zone.
Zip	String		Location’s ZIP/postal code.
CountryIso2	String		2-letter ISO Alpha-2 code for the country.
StateIso2	String		ISO Alpha-2 code for the state.

Stores

Lists all Stores.

Columns


Name	Type	References	Description
Id [KEY]	String		Unique store identifier.
Domain	String		Primary domain name.
SecureUrl	String		Stores current HTTPS URL.
Status	String		Status of the store.
Name	String		Stores name.
FirstName	String		Primary contacts first name
LastName	String		Primary contacts last name
Address	String		Display address.
Country	String		Country where the store is located
CountryCode	String		Country code.
Phone	String		Display phone number.
AdminEmail	String		Email address of the store administrator/owner.
OrderEmail	String		Email address for orders and fulfillment.
FaviconUrl	String		URL of the favicon.
TimezoneName	String		A string identifying the time zone, in the format: /.
TimezoneRawOffset	Integer		A negative or positive number, identifying the offset from UTC/GMT, in seconds, during winter/standard time.
Language	String		Default language code.
Currency	String		Default currency code
CurrencySymbol	String		Default symbol for values in the currency.
DecimalSeparator	String		Default decimal separator for values in the currency.
ThousandsSeparator	String		Default thousands separator for values in the currency.
DecimalPlaces	Integer		Default decimal places for values in the currency.
CurrencySymbolLocation	String		Default position of the currency symbol (left or right).
WeightUnits	String		Default weight units (metric or imperial).
DimensionUnits	String		Default dimension units (metric or imperial).
DimensionDecimalPlaces	Integer		The number of decimal places.
DimensionDecimalToken	String		The symbol that separates the whole numbers from the decimal points.
DimensionThousandsToken	String		The symbol used to denote thousands.
PlanName	String		Name of the BigCommerce plan to which this store is subscribed.
PlanLevel	String		Level of the BigCommerce plan to which this store is subscribed.
Industry	String		Industry, or vertical category, in which the business operates.
LogoUrl	String		URL of the logo.
IsPriceEnteredWithTax	Boolean		A Boolean value that indicates whether or not prices are entered with tax.
FeaturesStencilEnabled	Boolean		Indicates whether a store is using a Stencil theme.
FeaturesSitewideHttpsEnabled	Boolean		Indicates if there is sitewide https.
FeaturesFacebookCatalogId	String		Id of the facebook catalog. If there is none, it returns an empty string.
FeaturesCheckoutType	String		What type of checkout is enabled on the store.

TaxClasses

Lists all tax classes.

Table Specific Information

Select

The Cloud uses the BigCommerce API to process WHERE clause conditions built with the Id column, which supports the = comparison.

The rest of the filter is executed client-side within the Cloud.

For example, the following queries is processed server-side:

SELECT * FROM TaxClasses WHERE Id = 1

Columns


Name	Type	References	Description
Id [KEY]	String		Numeric ID of the tax class.
Name	String		Name of the tax class.
DateCreated	Date		The date when tax class was created.
DateModified	Date		The data when tax class was modified.

Transactions

Lists all transactions.

Columns


Name	Type	References	Description
Id [KEY]	Integer		Unique identifier for the transaction.
OrderId	String		Identifier for the BigCommerce Order with which this transaction is associated.
Event	String		Store event that created the transaction.
Method	String		The payment method.
Amount	Decimal		Amount of money in the transaction.
Currency	String		Currency used for the transaction.
Gateway	String		The payment gateway.
Status	String		Status of the transaction.
GatewayTransactionId	String		The transaction ID returned by the payment gateway for this transaction item.
DateCreated	Datetime		The datetime of the transaction.
Test	Boolean		True if the transaction performed was a test, or if the gateway is in test mode.
FraudReview	Boolean		Result of gateway fraud review, if any.
ReferenceTransactionId	Integer		Identifier for an existing transaction upon which this transaction acts.
OfflineDisplayName	String		Display name for the offline payment.
CustomPaymentMethod	String		Custom payment from manual order.
PaymentMethodId	String		The payment method ID used for this transaction.
PaymentInstrumentToken	String		Internal BigPay token for stored card.
AVSResultCode	String		AVS code from the payment gateway.
AVSResultMessage	String		AVS message from the payment gateway.
AVSResultStreetMatch	String		AVS Code for street matching result.
AVSResultPostalMatch	String		AVS Code for postal matching result.
CVVResultCode	String		CVV Code from the payment Gateway.
CVVResultMessage	String		CVV Message from the payment Gateway.
CreditCardType	String		Type of credit-card.
CreditCardIIN	String		The IIN of a credit-card number.
CreditCardLast4	String		The last 4 digits of a credit-card number.
CreditCardExpiryMonth	Integer		The expiry month of a credit-card.
CreditCardExpiryYear	Integer		The expiry year of a credit-card.
GiftCertificateCode	String		The gift-certificate code.
GiftCertificateOriginalBalance	Decimal		The balance on a gift certificate when it was purchased.
GiftCertificateStartingBalance	Decimal		The balance on a gift certificate at the time of this purchase.
GiftCertificateRemainingBalance	Decimal		The remaining balance on a gift certificate.
GiftCertificateStatus	String		The status of a gift certificate.
StoreCreditRemainingBalance	Decimal		Remaining balance of shopper's store credit.

ストアドプロシージャ

ストアドプロシージャはファンクションライクなインターフェースで、BigCommerce の単純なSELECT/INSERT/UPDATE/DELETE 処理にとどまらずCloud の機能を拡張します。

ストアドプロシージャは、パラメータのリストを受け取り、目的の機能を実行し、プロシージャが成功したか失敗したかを示すとともにBigCommerce から関連するレスポンスデータを返します。

CData Cloud - BigCommerce ストアドプロシージャ


Name	Description
CreateBrandImage	Creates a brand image.
DeleteBrandImage	Deletes a brand image.

CreateBrandImage

Creates a brand image.

Stored Procedure Specific Information

You can create a brand image using an image file. For example:

EXECUTE CreateBrandImage BrandId ='19128157751697', ImageFileLocation = 'D:\Desktop\Test.png'

Input


Name	Type	Required	Description
BrandId	String	True	The Id of the Brand.
ImageFileLocation	String	False	The local path of the image file to be uploaded.
FileName	String	False	File name that is uploaded. If content is not empty

Result Set Columns


Name	Type	Description
Status	String	The Status of the create operation.
ImageUrl	String	The URL of the Image uploaded.

DeleteBrandImage

Deletes a brand image.

Stored Procedure Specific Information

You can delete the brand image of a particular brand. For example:

EXECUTE DeleteBrandImage BrandId ='19128157751697'

Input


Name	Type	Required	Description
BrandId	String	False	The Id of the Brand.

Result Set Columns


Name	Type	Description
Status	String	The Status of the delete operation.

システムテーブル

このセクションで説明されているシステムテーブルをクエリして、スキーマ情報、データソース機能に関する情報、およびバッチ操作の統計にアクセスできます。

スキーマテーブル

以下のテーブルは、BigCommerce のデータベースメタデータを返します。

sys_catalogs：利用可能なデータベースをリスト。
sys_schemas：利用可能なスキーマをリスト。
sys_tables：利用可能なテーブルおよびビューをリスト。
sys_tablecolumns：利用可能なテーブルおよびビューのカラムについて説明。
sys_procedures：利用可能なストアドプロシージャについて説明。
sys_procedureparameters：ストアドプロシージャパラメータについて説明。
sys_keycolumns：主キーおよび外部キーについて説明。
sys_indexes：利用可能なインデックスについて説明。

データソーステーブル

以下のテーブルは、データソースへの接続方法およびクエリ方法についての情報を返します。

sys_connection_props：利用可能な接続プロパティについての情報を返す。
sys_sqlinfo：Cloud がデータソースにオフロードできるSELECT クエリについて説明。

クエリ情報テーブル

次のテーブルは、データ変更クエリのクエリ統計を返します。

sys_identity：バッチ処理または単一の更新に関する情報を返す。

sys_catalogs

利用可能なデータベースをリストします。

次のクエリは、接続文字列で決定されるすべてのデータベースを取得します。

SELECT * FROM sys_catalogs

Columns


Name	Type	Description
CatalogName	String	データベース名。

sys_schemas

利用可能なスキーマをリストします。

次のクエリは、すべての利用可能なスキーマを取得します。

          SELECT * FROM sys_schemas

Columns


Name	Type	Description
CatalogName	String	データベース名。
SchemaName	String	スキーマ名。

sys_tables

利用可能なテーブルをリストします。

次のクエリは、利用可能なテーブルおよびビューを取得します。

          SELECT * FROM sys_tables

Columns


Name	Type	Description
CatalogName	String	テーブルまたはビューを含むデータベース。
SchemaName	String	テーブルまたはビューを含むスキーマ。
TableName	String	テーブル名またはビュー名。
TableType	String	テーブルの種類（テーブルまたはビュー）。
Description	String	テーブルまたはビューの説明。
IsUpdateable	Boolean	テーブルが更新可能かどうか。

sys_tablecolumns

利用可能なテーブルおよびビューのカラムについて説明します。

次のクエリは、Customers テーブルのカラムとデータ型を返します。

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

Columns


Name	Type	Description
CatalogName	String	テーブルまたはビューを含むデータベースの名前。
SchemaName	String	テーブルまたはビューを含むスキーマ。
TableName	String	カラムを含むテーブルまたはビューの名前。
ColumnName	String	カラム名。
DataTypeName	String	データ型の名前。
DataType	Int32	データ型を示す整数値。この値は、実行時に環境に基づいて決定されます。
Length	Int32	カラムのストレージサイズ。
DisplaySize	Int32	指定されたカラムの通常の最大幅（文字数）。
NumericPrecision	Int32	数値データの最大桁数。文字データおよび日時データの場合は、カラムの長さ（文字数）。
NumericScale	Int32	カラムのスケール（小数点以下の桁数）。
IsNullable	Boolean	カラムがNull を含められるかどうか。
Description	String	カラムの簡単な説明。
Ordinal	Int32	カラムのシーケンスナンバー。
IsAutoIncrement	String	カラムに固定増分値が割り当てられるかどうか。
IsGeneratedColumn	String	生成されたカラムであるかどうか。
IsHidden	Boolean	カラムが非表示かどうか。
IsArray	Boolean	カラムが配列かどうか。
IsReadOnly	Boolean	カラムが読み取り専用かどうか。
IsKey	Boolean	sys_tablecolumns から返されたフィールドがテーブルの主キーであるかどうか。
ColumnType	String	スキーマ内のカラムの役割または分類。可能な値は、SYSTEM、LINKEDCOLUMN、NAVIGATIONKEY、REFERENCECOLUMN、およびNAVIGATIONPARENTCOLUMN が含まれます。

sys_procedures

利用可能なストアドプロシージャをリストします。

次のクエリは、利用可能なストアドプロシージャを取得します。

          SELECT * FROM sys_procedures

Columns


Name	Type	Description
CatalogName	String	ストアドプロシージャを含むデータベース。
SchemaName	String	ストアドプロシージャを含むスキーマ。
ProcedureName	String	ストアドプロシージャの名前。
Description	String	ストアドプロシージャの説明。
ProcedureType	String	PROCEDURE やFUNCTION などのプロシージャのタイプ。

sys_procedureparameters

ストアドプロシージャパラメータについて説明します。

次のクエリは、SelectEntries ストアドプロシージャのすべての入力パラメータについての情報を返します。

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

パラメータに加えて結果セットのカラムを含めるには、IncludeResultColumns 擬似カラムをTrue に設定します。

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

Columns


Name	Type	Description
CatalogName	String	ストアドプロシージャを含むデータベースの名前。
SchemaName	String	ストアドプロシージャを含むスキーマの名前。
ProcedureName	String	パラメータを含むストアドプロシージャの名前。
ColumnName	String	ストアドプロシージャパラメータの名前。
Direction	Int32	パラメータのタイプに対応する整数値：input (1)。input/output (2)、またはoutput(4)。input/output タイプパラメータは、入力パラメータと出力パラメータの両方になれます。
DataType	Int32	データ型を示す整数値。この値は、実行時に環境に基づいて決定されます。
DataTypeName	String	データ型の名前。
NumericPrecision	Int32	数値データの場合は最大精度。文字データおよび日時データの場合は、カラムの長さ（文字数）。
Length	Int32	文字データの場合は、許可される文字数。数値データの場合は、許可される桁数。
NumericScale	Int32	数値データの小数点以下の桁数。
IsNullable	Boolean	パラメータがNull を含められるかどうか。
IsRequired	Boolean	プロシージャの実行にパラメータが必要かどうか。
IsArray	Boolean	パラメータが配列かどうか。
Description	String	パラメータの説明。
Ordinal	Int32	パラメータのインデックス。
Values	String	このパラメータで設定できる値は、このカラムに表示されるものに限られます。指定できる値はカンマ区切りです。
SupportsStreams	Boolean	パラメータがファイルを表すかどうか。ファイルは、ファイルパスとして渡すことも、ストリームとして渡すこともできます。
IsPath	Boolean	パラメータがスキーマ作成操作のターゲットパスかどうか。
Default	String	何も値が指定されていない場合に、このパラメータで使用される値。
SpecificName	String	複数のストアドプロシージャが同じ名前を持つ場合、それぞれの同名のストアドプロシージャを一意に識別するラベル。特定の名前を持つプロシージャが1つだけの場合は、その名前がここに単純に反映されます。
IsCDataProvided	Boolean	プロシージャがネイティブのBigCommerce プロシージャではなく、CData によって追加 / 実装されているかどうか。

Pseudo-Columns


Name	Type	Description
IncludeResultColumns	Boolean	出力にパラメータに加えて結果セットのカラムを含めるかどうか。デフォルトはFalse です。

sys_keycolumns

主キーおよび外部キーについて説明します。

次のクエリは、Customers テーブルの主キーを取得します。

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

Columns


Name	Type	Description
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前
IsKey	Boolean	カラムがTableName フィールドで参照されるテーブル内の主キーかどうか。
IsForeignKey	Boolean	カラムがTableName フィールドで参照される外部キーかどうか。
PrimaryKeyName	String	主キーの名前。
ForeignKeyName	String	外部キーの名前。
ReferencedCatalogName	String	主キーを含むデータベース。
ReferencedSchemaName	String	主キーを含むスキーマ。
ReferencedTableName	String	主キーを含むテーブル。
ReferencedColumnName	String	主キーのカラム名。

sys_foreignkeys

外部キーについて説明します。

次のクエリは、他のテーブルを参照するすべての外部キーを取得します。

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

カラム


名前	タイプ	説明
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前
PrimaryKeyName	String	主キーの名前。
ForeignKeyName	String	外部キーの名前。
ReferencedCatalogName	String	主キーを含むデータベース。
ReferencedSchemaName	String	主キーを含むスキーマ。
ReferencedTableName	String	主キーを含むテーブル。
ReferencedColumnName	String	主キーのカラム名。
ForeignKeyType	String	外部キーがインポート（他のテーブルを指す）キーかエクスポート（他のテーブルから参照される）キーかを指定します。

sys_primarykeys

主キーについて説明します。

次のクエリは、すべてのテーブルとビューから主キーを取得します。

         SELECT * FROM sys_primarykeys

Columns


Name	Type	Description
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前。
KeySeq	String	主キーのシーケンス番号。
KeyName	String	主キーの名前。

sys_indexes

利用可能なインデックスについて説明します。インデックスをフィルタリングすることで、より高速なクエリ応答時間でセレクティブクエリを記述できます。

次のクエリは、主キーでないすべてのインデックスを取得します。

          SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns


Name	Type	Description
CatalogName	String	インデックスを含むデータベースの名前。
SchemaName	String	インデックスを含むスキーマの名前。
TableName	String	インデックスを含むテーブルの名前。
IndexName	String	インデックス名。
ColumnName	String	インデックスに関連付けられたカラムの名前。
IsUnique	Boolean	インデックスが固有の場合はTrue。そうでない場合はFalse。
IsPrimary	Boolean	インデックスが主キーの場合はTrue。そうでない場合はFalse。
Type	Int16	インデックスタイプに対応する整数値：statistic (0)、clustered (1)、hashed (2)、またはother (3)。
SortOrder	String	並べ替え順序：A が昇順、D が降順。
OrdinalPosition	Int16	インデックスのカラムのシーケンスナンバー。

sys_connection_props

利用可能な接続プロパティと、接続文字列に設定されている接続プロパティに関する情報を返します。

次のクエリは、接続文字列に設定されている、あるいはデフォルト値で設定されているすべての接続プロパティを取得します。

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns


Name	Type	Description
Name	String	接続プロパティ名。
ShortDescription	String	簡単な説明。
Type	String	接続プロパティのデータ型。
Default	String	明示的に設定されていない場合のデフォルト値。
Values	String	可能な値のカンマ区切りリスト。別な値が指定されていると、検証エラーがスローされます。
Value	String	設定した値またはあらかじめ設定されたデフォルト。
Required	Boolean	プロパティが接続に必要かどうか。
Category	String	接続プロパティのカテゴリ。
IsSessionProperty	String	プロパティが、現在の接続に関する情報を保存するために使用されるセッションプロパティかどうか。
Sensitivity	String	プロパティの機密度。これは、プロパティがロギングおよび認証フォームで難読化されているかどうかを通知します。
PropertyName	String	キャメルケースの短縮形の接続プロパティ名。
Ordinal	Int32	パラメータのインデックス。
CatOrdinal	Int32	パラメータカテゴリのインデックス。
Hierarchy	String	このプロパティと一緒に設定する必要がある、関連のある依存プロパティを表示します。
Visible	Boolean	プロパティが接続UI に表示されるかどうかを通知します。
ETC	String	プロパティに関するその他のさまざまな情報。

sys_sqlinfo

Cloud がデータソースにオフロードできるSELECT クエリ処理について説明します。

SQL 構文の詳細については、SQL 準拠を参照してください。

データソースのSELECT 機能

以下はSQL 機能のサンプルデータセットです。 SELECT 機能のいくつかの側面がサポートされている場合には、カンマ区切りのリストで返されます。サポートされていない場合、カラムにはNO が入ります。


名前	説明	有効な値
AGGREGATE_FUNCTIONS	サポートされている集計関数。	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	COUNT 関数がサポートされているかどうか。	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	識別子をエスケープするための開始文字。	[
IDENTIFIER_QUOTE_CLOSE_CHAR	識別子をエスケープするための終了文字。	]
SUPPORTED_OPERATORS	サポートされているSQL 演算子。	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	GROUP BY がサポートされているかどうか。サポートされている場合、どのレベルでサポートされているか。	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	サポートされている外部結合の種類。	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	外部結合がサポートされているかどうか。	YES, NO
SUBQUERIES	サブクエリがサポートされているかどうか。サポートされていれば、どのレベルでサポートされているか。	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
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	サポートされている数値関数。	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	サポートされている日付および時刻関数。	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	レプリケーション中にスキップされたテーブルを示します。
REPLICATION_TIMECHECK_COLUMNS	レプリケーション中に更新判断のカラムとして使用するかどうかを、（指定された順に）チェックするカラムのリストを含む文字列の配列。
IDENTIFIER_PATTERN	識別子としてどの文字列が有効かを示す文字列値。
SUPPORT_TRANSACTION	プロバイダーが、コミットやロールバックなどのトランザクションをサポートしているかどうかを示します。	YES, NO
DIALECT	使用するSQL ダイアレクトを示します。
KEY_PROPERTIES	Uniform データベースを特定するプロパティを示します。
SUPPORTS_MULTIPLE_SCHEMAS	プロバイダー用に複数のスキームが存在するかどうかを示します。	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	プロバイダー用に複数のカタログが存在するかどうかを示します。	YES, NO
DATASYNCVERSION	このドライバーにアクセスするために必要な、CData Sync のバージョン。	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	このドライバーのCData Sync カテゴリ。	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	API で提供されている以上の、追加SQL 機能がサポートされているかどうか。	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	バッチ操作がサポートされているかどうか。	YES, NO
SQL_CAP	このドライバーでサポートされているすべてのSQL 機能。	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	使用したいcacheOptions を指定する文字列値。
ENABLE_EF_ADVANCED_QUERY	ドライバーがEntity Framework の高度なクエリをサポートしているかどうかを示します。サポートしていなければ、クエリはクライアントサイドで処理されます。	YES, NO
PSEUDO_COLUMNS	利用可能な疑似カラムを示す文字列の配列。
MERGE_ALWAYS	値がtrue であれば、CData Sync 内でMerge Model が強制的に実行されます。	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	レプリケート開始日時を返すSELECT クエリ。
REPLICATION_MIN_FUNCTION	サーバーサイドでmin を実行するために使用する式名を、プロバイダーが指定できるようになります。
REPLICATION_START_DATE	レプリケート開始日を、プロバイダーが指定できるようになります。
REPLICATION_MAX_DATE_QUERY	レプリケート終了日時を返すSELECT クエリ。
REPLICATION_MAX_FUNCTION	サーバーサイドでmax を実行するために使用する式名を、プロバイダーが指定できるようになります。
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	初回のレプリケートで、レプリケートをチャンクに分割しないテーブルのリスト。
CHECKCACHE_USE_PARENTID	CheckCache 構文を親キーカラムに対して実行するかどうかを示します。	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	スキーマファイルの生成に使用できる、ストアドプロシージャを示します。

次のクエリは、WHERE 句で使用できる演算子を取得します。

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

WHERE 句では、個々のテーブルの制限や要件が異なる場合がありますので注意してください。詳しくは、データモデルセクションを参照してください。

Columns


Name	Type	Description
NAME	String	SQL 構文のコンポーネント、またはサーバー上で処理できる機能。
VALUE	String	サポートされるSQL またはSQL 構文の詳細。

sys_identity

試行された変更に関する情報を返します。

次のクエリは、バッチ処理で変更された行のId を取得します。

         SELECT * FROM sys_identity

Columns


Name	Type	Description
Id	String	データ変更処理から返された、データベース生成Id。
Batch	String	バッチの識別子。1 は単一処理。
Operation	String	バッチ内の処理の結果：INSERTED、UPDATED、またはDELETED。
Message	String	SUCCESS、またはバッチ内の更新が失敗した場合のエラーメッセージ。

sys_information

利用可能なシステム情報を説明します。

次のクエリは、すべてのカラムを取得します。

SELECT * FROM sys_information

Columns


Name	Type	Description
Product	String	製品名。
Version	String	製品のバージョン番号。
Datasource	String	製品が接続するデータソースの名前。
NodeId	String	製品がインストールされているマシンの固有識別子。
HelpURL	String	製品のヘルプドキュメントのURL。
License	String	製品のライセンス情報。（この情報が利用できない場合、この項目は空白のままか「N/A」と表示されます。）
Location	String	製品のライブラリが格納されているファイルパスの場所。
Environment	String	製品が現在稼働している環境またはランタイムのバージョン。
DataSyncVersion	String	本コネクタを使用するために必要なCData Sync のティア。
DataSyncCategory	String	CData Sync 機能のカテゴリ（例：Source、Destination）。

接続文字列オプション

接続文字列プロパティは、接続を確立するために使用できるさまざまなオプションです。このセクションでは、本プロバイダーの接続文字列で設定できるオプションの一覧を示します。詳細については各リンクをクリックしてください。

Authentication

プロパティ	説明
AuthScheme	The type of authentication to use when connecting to BigCommerce.

OAuth

プロパティ	説明
OAuthClientId	カスタムOAuth アプリケーションに割り当てられたクライアントID（コンシューマーキーとも呼ばれます）を指定します。このID は、認証時にOAuth 認可サーバーにアプリケーションを識別させるために必要です。
OAuthClientSecret	カスタムOAuth アプリケーションに割り当てられたクライアントシークレットを指定します。この機密情報は、OAuth 認可サーバーに対してアプリケーションを認証するために使用されます。（カスタムOAuth アプリケーションのみ）
OAuthAccessToken	データソースへのリクエストを認証するために使用されるOAuth アクセストークンを指定します。このトークンは、OAuth 交換が正常に完了した後に認可サーバーから発行されます。
StoreId	The store hash for this BigCommerce account.
Scope	データへの適切なアクセスを確実にするために、認証ユーザーのアプリケーションへのアクセス範囲を指定します。カスタムOAuth アプリケーションが必要な場合は、通常、アプリケーションの作成時に指定します。

SSL

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続する際に、サーバーが受け入れ可能な証明書を指定します。

Logging

プロパティ	説明
Verbosity	ログファイルのVerbosity レベルを指定し、記録される情報の詳細度を制御します。サポートされる値の範囲は1から5までです。

Schema

プロパティ	説明
BrowsableSchemas	レポートされるスキーマを利用可能なすべてのスキーマのサブセットに制限するオプション設定。例えば、 BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

Miscellaneous

プロパティ	説明
CustomFieldDiscovery	Use this property to specify whether to merge customfields by name or create unique customfields by id to the Products table.
IncludeCustomFields	A boolean indicating if you would like to include custom fields in the column listing.
MaxRows	集計やGROUP BY を含まないクエリで返される最大行数を指定します。
PseudoColumns	テーブルカラムとして公開する擬似カラムを、'TableName=ColumnName;TableName=ColumnName' の形式の文字列で指定します。
RowScanDepth	テーブルで利用可能なカラムを探すためにスキャンする行数の最大値。
Timeout	provider がタイムアウトエラーを返すまでにサーバーからの応答を待機する最大時間を秒単位で指定します。

Authentication

このセクションでは、本プロバイダーの接続文字列で設定可能なAuthentication プロパティの全リストを提供します。

プロパティ	説明
AuthScheme	The type of authentication to use when connecting to BigCommerce.

AuthScheme

The type of authentication to use when connecting to BigCommerce.

Possible Values

OAuth, PersonalAccessToken

データ型

string

デフォルト値

"OAuth"

解説

OAuth: Set this when you want to autheticate using OAuth Credentials Created from BigCommerce App.
PersonalAccessToken: Set this when you want to autheticate using OAuthAccess Token generated from BigCommerce UI.

OAuth

このセクションでは、本プロバイダーの接続文字列で設定可能なOAuth プロパティの全リストを提供します。

プロパティ	説明
OAuthClientId	カスタムOAuth アプリケーションに割り当てられたクライアントID（コンシューマーキーとも呼ばれます）を指定します。このID は、認証時にOAuth 認可サーバーにアプリケーションを識別させるために必要です。
OAuthClientSecret	カスタムOAuth アプリケーションに割り当てられたクライアントシークレットを指定します。この機密情報は、OAuth 認可サーバーに対してアプリケーションを認証するために使用されます。（カスタムOAuth アプリケーションのみ）
OAuthAccessToken	データソースへのリクエストを認証するために使用されるOAuth アクセストークンを指定します。このトークンは、OAuth 交換が正常に完了した後に認可サーバーから発行されます。
StoreId	The store hash for this BigCommerce account.
Scope	データへの適切なアクセスを確実にするために、認証ユーザーのアプリケーションへのアクセス範囲を指定します。カスタムOAuth アプリケーションが必要な場合は、通常、アプリケーションの作成時に指定します。

OAuthClientId

カスタムOAuth アプリケーションに割り当てられたクライアントID（コンシューマーキーとも呼ばれます）を指定します。このID は、認証時にOAuth 認可サーバーにアプリケーションを識別させるために必要です。

データ型

string

デフォルト値

""

解説

このプロパティは2つのケースで必要となります：

カスタムOAuth アプリケーションを使用する場合。たとえば、Web ベースの認証フロー、サービスベースの認証、またはアプリケーションの登録が必要な証明書ベースのフローなどが該当します。
ドライバーが埋め込みOAuth 資格情報を提供しない場合。

（ドライバーが埋め込みOAuth 資格情報を提供する場合、この値はすでにCloud によって設定されており、手動で入力する必要がないことがあります。）

OAuthClientId は、認証付きの接続を構成する際に、OAuthClientSecret やOAuthSettingsLocation などの他のOAuth 関連プロパティと一緒に使用されるのが一般的です。

OAuthClientId は、ユーザーがOAuth 経由で認証を行う前に設定する必要がある、主要な接続パラメータの1つです。この値は、通常、ID プロバイダーのアプリケーション登録設定で確認できます。 Client ID、Application ID、Consumer Key などとラベル付けされた項目を探してください。

クライアントID は、クライアントシークレットのような機密情報とは見なされませんが、アプリケーションの識別情報の一部であるため、慎重に取り扱う必要があります。公開リポジトリや共有設定ファイルでこの値を露出させないようにしてください。

接続設定時にこのプロパティを使用する方法の詳細については、接続の確立を参照してください。

OAuthClientSecret

カスタムOAuth アプリケーションに割り当てられたクライアントシークレットを指定します。この機密情報は、OAuth 認可サーバーに対してアプリケーションを認証するために使用されます。（カスタムOAuth アプリケーションのみ）

データ型

string

デフォルト値

""

解説

このプロパティ（アプリケーションシークレットまたはコンシューマシークレットとも呼ばれます）は、安全なクライアント認証を必要とするすべてのフローでカスタムOAuth アプリケーションを使用する場合に必要です。たとえば、Web ベースのOAuth、サービスベースの接続、証明書ベースの認可フローなどが該当します。組み込みOAuth アプリケーションを使用する場合は必要ありません。

クライアントシークレットは、OAuth フローのトークン交換ステップで使用されます。このステップでは、ドライバーが認可サーバーにアクセストークンを要求します。この値が欠落しているか正しくない場合、認証はinvalid_client またはunauthorized_client エラーで失敗します。

OAuthClientSecret は、ユーザーがOAuth 経由で認証を行う前に設定する必要がある、主要な接続パラメータの1つです。この値は、OAuth アプリケーションを登録する際にID プロバイダーから取得できます。

Notes：

この値は安全に保管し、公開リポジトリやスクリプト、安全でない環境では決して公開しないようにしてください。
クライアントシークレットは、一定期間が経過すると有効期限が切れる場合もあります。アクセスを中断させないために、有効期限を常に監視し、必要に応じてシークレットをローテーションするようにしてください。

接続設定時にこのプロパティを使用する方法の詳細については、接続の確立を参照してください。

OAuthAccessToken

データソースへのリクエストを認証するために使用されるOAuth アクセストークンを指定します。このトークンは、OAuth 交換が正常に完了した後に認可サーバーから発行されます。

データ型

string

デフォルト値

""

解説

OAuthAccessToken は、保護されたリソースへのアクセスを認可する一時的な認証情報です。これは通常、ユーザーまたはクライアントアプリケーションがOAuth 認証フローを完了した後に、ID プロバイダーから返されます。このプロパティは、自動化されたワークフローや、トークンの処理をドライバーの外部で管理したいカスタムOAuth 実装で最も一般的に使用されます。

OAuth アクセストークンにはサーバーに依存する時間制限があり、ユーザーのアクセスを制限します。タイムアウトは、OAuthExpiresIn プロパティを使用して設定します。ただし、ユーザーが操作を続けている限り、リクエスト間で再発行することでアクセスを維持することができます。

InitiateOAuth がREFRESH に設定されている場合、OAuthExpiresIn とOAuthTokenTimestamp の両方を設定することを推奨します。 Cloud は、これらのプロパティを使用してトークンの有効期限を判断し、最も効率的にリフレッシュできるようにします。 OAuthExpiresIn およびOAuthTokenTimestamp が指定されていない場合、Cloud はトークンを直ちにリフレッシュします。

Note：アクセストークンは機密性の高い認証情報として扱い、安全に保管する必要があります。アクセス制御されていないログ、スクリプト、または設定ファイルでの公開は避けてください。

接続設定時にこのプロパティを使用する方法の詳細については、接続の確立を参照してください。

StoreId

The store hash for this BigCommerce account.

データ型

string

デフォルト値

""

解説

The store hash for this BigCommerce account.

Scope

データへの適切なアクセスを確実にするために、認証ユーザーのアプリケーションへのアクセス範囲を指定します。カスタムOAuth アプリケーションが必要な場合は、通常、アプリケーションの作成時に指定します。

データ型

string

デフォルト値

""

解説

スコープは、認証ユーザーがどのようなアクセス権を持つかを定義するために設定されます。例えば、読み取り、読み取りと書き込み、機密情報への制限付きアクセスなどです。システム管理者は、スコープを使用して機能またはセキュリティクリアランスによるアクセスを選択的に有効化できます。

InitiateOAuth がGETANDREFRESH に設定されている場合、要求するスコープを変更したい場合はこのプロパティを使用する必要があります。

InitiateOAuth がREFRESH またはOFF のいずれかに設定されている場合、このプロパティまたはScope 入力を使用して、要求するスコープを変更できます。

SSL

このセクションでは、本プロバイダーの接続文字列で設定可能なSSL プロパティの全リストを提供します。

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続する際に、サーバーが受け入れ可能な証明書を指定します。

SSLServerCert

TLS/SSL を使用して接続する際に、サーバーが受け入れ可能な証明書を指定します。

データ型

string

デフォルト値

""

解説

TLS/SSL 接続を使用している場合は、このプロパティを使用して、サーバーが受け入れるTLS/SSL 証明書を指定できます。このプロパティに値を指定すると、マシンによって信頼されていない他の証明書はすべて拒否されます。

このプロパティは、次のフォームを取ります：


説明	例
フルPEM 証明書（例では省略されています）	`-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----`
証明書を保有するローカルファイルへのパス。	C:\cert.cer
公開鍵（例では省略されています）	`-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----`
MD5 Thumbprint（hex 値はスペースまたはコロン区切り）	`ecadbdda5a1529c58a1e9e09828d70e4`
SHA1 Thumbprint（hex 値はスペースまたはコロン区切り）	`34a929226ae0819f2ec14b4a3d904f801cbb150d`

Note：'*' を使用してすべての証明書を受け入れるように指定することも可能ですが、セキュリティ上の懸念があるため推奨されません。

Logging

このセクションでは、本プロバイダーの接続文字列で設定可能なLogging プロパティの全リストを提供します。

プロパティ	説明
Verbosity	ログファイルのVerbosity レベルを指定し、記録される情報の詳細度を制御します。サポートされる値の範囲は1から5までです。

Verbosity

ログファイルのVerbosity レベルを指定し、記録される情報の詳細度を制御します。サポートされる値の範囲は1から5までです。

データ型

string

デフォルト値

"1"

解説

このプロパティは、Cloud がログファイルに含める詳細レベルを定義します。 Verbosity レベルを高くするとログに記録される情報の詳細が増えますが、ログファイルが大きくなり取り込まれるデータが増えるためパフォーマンスが低下する可能性があります。

デフォルトのVerbosity レベルは1で、通常の運用にはこれが推奨されます。より高いVerbosity レベルは主にデバッグを目的としています。各レベルの詳細については、ログを参照してください。

LogModules プロパティと組み合わせることで、Verbosity は特定の情報カテゴリに対するログの詳細度を調整できます。

Schema

このセクションでは、本プロバイダーの接続文字列で設定可能なSchema プロパティの全リストを提供します。

プロパティ	説明
BrowsableSchemas	レポートされるスキーマを利用可能なすべてのスキーマのサブセットに制限するオプション設定。例えば、 BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

BrowsableSchemas

レポートされるスキーマを利用可能なすべてのスキーマのサブセットに制限するオプション設定。例えば、 BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

データ型

string

デフォルト値

""

解説

利用可能なデータベーススキーマをすべてリストすると余分な時間がかかり、パフォーマンスが低下します。接続文字列にスキーマのリストを指定することで、時間を節約しパフォーマンスを向上させることができます。

Miscellaneous

このセクションでは、本プロバイダーの接続文字列で設定可能なMiscellaneous プロパティの全リストを提供します。

プロパティ	説明
CustomFieldDiscovery	Use this property to specify whether to merge customfields by name or create unique customfields by id to the Products table.
IncludeCustomFields	A boolean indicating if you would like to include custom fields in the column listing.
MaxRows	集計やGROUP BY を含まないクエリで返される最大行数を指定します。
PseudoColumns	テーブルカラムとして公開する擬似カラムを、'TableName=ColumnName;TableName=ColumnName' の形式の文字列で指定します。
RowScanDepth	テーブルで利用可能なカラムを探すためにスキャンする行数の最大値。
Timeout	provider がタイムアウトエラーを返すまでにサーバーからの応答を待機する最大時間を秒単位で指定します。

CustomFieldDiscovery

Use this property to specify whether to merge customfields by name or create unique customfields by id to the Products table.

Possible Values

ByID, ByName

データ型

string

デフォルト値

"ByID"

解説

ByID: Set this when you want to create a unique customfields name by comparing the Ids of the custom fields. There are different customfields added for the various products as the customfields Id is unique across products.
ByName: Set this when you want to create customfields by name.

IncludeCustomFields

A boolean indicating if you would like to include custom fields in the column listing.

データ型

bool

デフォルト値

true

解説

Setting this to true will cause custom fields to be included in the column listing, but may cause poor performance when listing metadata.

MaxRows

集計やGROUP BY を含まないクエリで返される最大行数を指定します。

データ型

int

デフォルト値

-1

解説

このプロパティのデフォルト値である-1 は、クエリに明示的にLIMIT 句が含まれていない限り、行の制限が適用されないことを意味します。（クエリにLIMIT 句が含まれている場合、クエリで指定された値がMaxRows 設定よりも優先されます。）

MaxRows を0より大きい整数に設定することで、クエリがデフォルトで過度に大きな結果セットを返さないようにします。

このプロパティは、非常に大きなデータセットを返す可能性のあるクエリを実行する際に、パフォーマンスを最適化し、過剰なリソース消費を防ぐのに役立ちます。

PseudoColumns

テーブルカラムとして公開する擬似カラムを、'TableName=ColumnName;TableName=ColumnName' の形式の文字列で指定します。

データ型

string

デフォルト値

""

解説

このプロパティを使用すると、Cloud がテーブルカラムとして公開する擬似カラムを定義できます。

個々の擬似カラムを指定するには、以下の形式を使用します。

Table1=Column1;Table1=Column2;Table2=Column3

すべてのテーブルのすべての擬似カラムを含めるには、次を使用してください：

*=*

RowScanDepth

テーブルで利用可能なカラムを探すためにスキャンする行数の最大値。

データ型

int

デフォルト値

100

解説

テーブルのカラムはテーブル行をスキャンすることで決定される必要があります。この値はスキャンされる行数の最大値を設定します。

大きい値を設定すると、パフォーマンスが低下する場合があります。小さい値を設定すると、特にnull データがある場合には、データ型を正しく判定できない場合があります。

Timeout

provider がタイムアウトエラーを返すまでにサーバーからの応答を待機する最大時間を秒単位で指定します。

データ型

int

デフォルト値

60

解説

タイムアウトは、クエリや操作全体ではなくサーバーとの個々の通信に適用されます。例えば、各ページング呼び出しがタイムアウト制限内に完了する場合、クエリは60秒を超えて実行を続けることができます。

タイムアウトはデフォルトで60秒に設定されています。タイムアウトを無効にするには、このプロパティを0に設定します。

タイムアウトを無効にすると、操作が成功するか、サーバー側のタイムアウト、ネットワークの中断、またはサーバーのリソース制限などの他の条件で失敗するまで無期限に実行されます。

Note： このプロパティは慎重に使用してください。長時間実行される操作がパフォーマンスを低下させたり、応答しなくなる可能性があるためです。

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

BigCommerce - CData Cloud

概要

主要機能

はじめに

BigCommerce への接続

CData Cloud サービスからデータにアクセス

接続の確立

BigCommerce への接続

BigCommerce トークン

トークンの生成

BigCommerce カスタムアプリの作成

デスクトップアプリ

ヘッドレスマシン

SSL の設定

SSL 設定のカスタマイズ

ファイアウォールとプロキシ

Firewall またはProxy 経由の接続

HTTP プロキシ

その他のプロキシ

データモデル

概要

主要機能

ビュー

ストアドプロシージャ

システムテーブル

テーブル

CData Cloud - BigCommerce テーブル

Brands

Table Specific Information

Select

Columns

Categories

Table Specific Information

Select

Insert

Update

Delete

Columns

CategoryTrees

Table Specific Information

Select

Insert

Update

Delete

Columns

ChannelListings

Table Specific Information

Select

Insert

Update

Columns

Channels

Table Specific Information

Select

Insert

Update

Columns

Coupons

Table Specific Information

Select

Insert

Update

Delete

Columns

Currencies

Table Specific Information

Select

Insert

Update

Delete

Columns

CustomerAddresses

Table Specific Information

Select

Insert

Update

Delete

Columns

Customers