CData JDBC Driver for Magento 2019 - Online Help
Questions / Feedback?

Products

CData JDBC Driver for Magento 2019 - Build 19.0.7354

Create, update, delete, and query the available products in Magento.

Select

Server-Side Query Support

The driver will use the Magento API to filter the results by the following columns and operators while the rest of the filter is executed client side within the driver.

  • EntityId, Price, Weight, TaxClassId, Occasion, and AttributeSetId support the following operators: >, >=, <, <=, =, IN, and NOT IN.
  • TypeId, Sku, Description, ShortDescription, and Name support the following operators: >, >=, <, <=, =, IN, NOT IN, LIKE, and NOT LIKE.

For example, the following query is processed server side:

SELECT * FROM Products WHERE EntityId >= 200 AND EntityId <=500 AND Name LIKE '%d%' AND Visibility = 4 LIMIT 50 ORDER BY Size DESC
You can turn off client-side execution by setting SupportEnhancedSQL to false, in which case any search criteria that refers to any other columns or operators will cause an error.

Accessing Unique Product Attributes

Based on the attribute set associated with a product, Magento will return more columns. For example, clothing products will also have Gender, Size, and Sleeve_Length attributes in addition to the standard columns.

The number of columns returned will depend on the connection properties TypeDetectionScheme and RowScanDepth. By default, these properties are set to "RowScan" and "15", respectively. This means that before executing your SELECT query, the first 15 products will be scanned, and any additional columns that are found will be applied to all the results of your query.

Note: Unique columns are not available for products exceeding the RowScanDepth limit. In that case, RowScanDepth must be set to a higher value.

Insert

To add a product, specify the TypeId, AttributeSetId, Sku, Name, Price, Weight, Visibility, TaxClassId, Description, ShortDescription, and Status fields.

INSERT INTO Products (TypeId,AttributeSetId,Sku,Name,Price,Weight,Visibility,TaxClassId,Description,ShortDescription,Status) VALUES ('simple',13,'T1256','Food',100,10,4,2,'Food desc','Food short desc',1)

Update

Most columns can be updated by providing the EntityId in the WHERE clause.

UPDATE Products SET Price = 100 WHERE EntityId = 231

To update products in a specific store, provide EntityId and StoreId in the WHERE clause.

UPDATE Products SET SpecialPrice = 99 WHERE EntityId = 231 AND StoreId=2

Delete

Products can be deleted by providing the EntityId of the product and issuing a DELETE statement.

DELETE FROM Products WHERE EntityId = 138

Columns

Name Type ReadOnly Description
EntityId [KEY] String True

The entity Id of the product.

TypeId String False

Product type. For example: simple.

AttributeSetId Integer False

Attribute set for the product.

Sku String False

Product SKU.

Status Integer False

The status of the product. Can have the following values: 1- Enabled or 2 - Disabled.

The allowed values are 1, 2.

Visibility Integer False

Product visibility. Can have the following values: 1 - Not Visible Individually, 2 - Catalog, 3 - Search, or 4 - Catalog, Search.

The allowed values are 1, 2, 3, 4.

TaxClassId Integer False

Product tax class. Can have the following values: 0 - None, 2 - taxable Goods, 4 - Shipping, etc., depending on the created tax classes.

Name String False

Product name.

ShortDescription String False

Product short description.

Description String False

Product description.

Price Double False

Product price.

SpecialPrice Double False

Product special price.

Weight Double False

Product weight.

Qty Double False

Quantity of in-stock items for the current product.

MinQty Double False

Quantity for in-stock items to become out of stock.

UseConfigMinQty Boolean False

Choose whether the Config settings will be applied for the MinQty column.

IsQtyDecimal Boolean False

Choose whether the product can be sold using decimals (e.g, you can buy 2.5 of the product).

The allowed values are False, True.

Backorders Boolean False

Defines whether the customer can place the order for products that are out of stock at the moment.

The allowed values are False, True.

UseConfigBackorders Boolean False

Choose whether the Config settings will be applied for the Backorders column.

The allowed values are False, True.

MinSaleQty Double False

Minimum number of items in the shopping cart to be sold.

UseConfigMinSaleQty Boolean False

Choose whether the Config settings will be applied for the MinSaleQty column.

The allowed values are False, True.

MaxSaleQty Double False

Maximum number of items in the shopping cart to be sold.

UseConfigMaxSaleQty Boolean False

Choose whether the Config settings will be applied for the MaxSaleQty column.

The allowed values are False, True.

IsInStock Boolean False

Defines whether the product is available for selling.

The allowed values are False, True.

NotifyStockQty Double False

The number of inventory items below which the customer will be notified.

ManageStock Boolean False

Choose whether to view and specify the product quantity and availability and whether the product is in stock management.

The allowed values are False, True.

UseConfigManageStock Boolean False

Choose whether the Config settings will be applied for the ManageStock column.

The allowed values are False, True.

IsInUseConfigQtyIncrements Boolean False

Choose whether the Config settings will be applied for the IsInQtyIncrements column.

The allowed values are False, True.

IsInQtyIncrements Double False

The product quantity increment value.

UseConfigEnableQtyInc Boolean False

Choose whether the Config settings will be applied for the EnableQtyIncrements column.

The allowed values are False, True.

EnableQtyIncrements Boolean False

Defines whether the customer can add products only in increments to the shopping cart.

The allowed values are False, True.

IsDecimalDivided Boolean False

Defines whether the stock items can be divided into multiple boxes for shipping.

The allowed values are False, True.

UrlKey String False

A friendly URL path for the product.

CountryOfManufacture String False

The country the product was manufactured in.

Msrp Double False

The price that a manufacturer suggests to sell the product at.

MsrpEnabled String False

The Apply MAP option. Defines whether the price in the catalog in the frontend is substituted with a Click for Price link.

MsrpDisplayActualPriceType String True

Defines how the price will be displayed in the frontend. Can have the following values: In Cart, Before Order Confirmation, and On Gesture.

GiftMessageAvailable String False

Defines whether a gift message is available for the product.

GiftWrappingAvailable Boolean False

Defines whether gift wrapping is available for the product.

GiftWrappingPrice Double False

Defines the gift wrapping price.

NewsFromDate Datetime False

Date starting from which the product is promoted as a new product.

NewsToDate Datetime False

Date till which the product is promoted as a new product.

SpecialToDate Datetime True

Date till which the special price will be applied to the product.

SpecialFromDate Datetime True

Date from which the special price will be applied to the product.

OptionsContainer String False

Defines how the custom options for the product will be displayed. Can have the following values: Block after Info Column or Product Info Column.

The allowed values are Info Column, Product Info Column.

AttributeSetColumn1 String False

Additional attribute related to the product's attribute set. If TypeDetectionScheme is not set to 'RowScan', this column will not be available.

AttributeSetColumn2 String False

Additional attribute related to the product's attribute set. If TypeDetectionScheme is not set to 'RowScan', this column will not be available.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
CategoryId String

Filter by category.

StoreId String

Update a product in a store with this Id.

 
 
Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 19.0.7354.0