ODBC Driver for BigCommerce

Build 24.0.9060

Products

Returns data from products table.

Table Specific Information

Select

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

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

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

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

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

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

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.

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.

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) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060