JDBC Driver for BigCommerce

Build 22.0.8462

Products

Returns data from products table.

Table Specific Information

Select

The driver will use the BigCommerce API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the driver.

  • Id supports the '=, >=, >, <=, <' comparison.
  • 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 '=, >=, >, <=, <' comparison.
  • DateCreated supports the '=, >=, >, <=, <' comparison.
  • BrandId supports the '=' comparison.
  • DateModified supports the '=, >=, >, <=, <' comparison.
  • Condition supports the '=' comparison.
  • DateLastImported supports the '=, >=, >, <=, <' comparison.
  • Availability supports the '=' comparison.

For example, the following queries are processed server side: 

SELECT * FROM Products

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

SELECT * FROM Products WHERE IsVisible = "true" AND DateCreated >= "2020-01-01" AND DateCreated <= "2020-02-01"

Insert

To insert a new Product, you need to 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 more than one Variants using Temp table or Aggregegates. 

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')

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


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

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

Current inventory level of the product.

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

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 Int[] 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.

EventDateFieldName String False

Name of the field to be displayed on the product page when selecting the event/delivery date.

EventDateType String False

Event Date type.

EventDateStart Datetime False

When the product requires the customer to select an event/delivery date, this date is used as the after date.

EventDateEnd Datetime False

When the product requires the customer to select an event/delivery date, this date is used as the before date.

MYOBAssetAccount String False

MYOB Asset Account.

MYOBIncomeAccount String False

MYOB Income Account.

MYOBExpenseAccount String False

MYOB Expense/COS Account.

PeachtreeGlAccount String False

Peachtree General Ledger Account.

Condition String False

The products condition.

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.

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.

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.

DateLastImported Datetime False

The date on which the product was last imported using the bulk importer.

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.

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.

PrimaryImageStandardUrl String True

Standard url of the primary image.

Pseudo-Columns

Pseudo column fields are used to enable the user to INSERT Fields that are non-readable but required during creation of new records.

Name Type Description
ProductVariants String

Variants of the Products

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462