JDBC Driver for Magento

Build 22.0.8462

Orders

Orders is an auto generated table

Select

The Orders table supports server-side processing for all columns in the WHERE clause. Most of these columns support server-side processing for the >, >=, <, <=, =, IN, NOT IN and LIKE operators.

The following examples show the types of queries processed server side.

  • Retrieve all orders from your Magento server: 
    SELECT * FROM Orders
  • Retrieve a single order: 
    SELECT * FROM Orders WHERE EntityId = '1'
  • Filter by status: 
    SELECT * FROM Orders WHERE GroupId = 1
  • Filter by created date: 
    SELECT * FROM Orders WHERE CreatedAt > '2017-08-01'
  • Example query with multiple filters: 
    SELECT * FROM Orders WHERE EntityId IN (1,2) AND CustomerFirstname LIKE '%i%' AND OrderCurrencyCode='USD' LIMIT 50

Insert

You can insert items into an order using an Insert statement. The 'Items' column can be specified using either the temporary table called 'OrderItems#Temp' or as a simple string aggregate. 'Payment' aggregate column is required for Insert and Update operations on this table.

  • Using the temporary table: 
    INSERT INTO OrderItems#Temp (BasePrice, Sku) VALUES (299.99, '8915A40K4')
INSERT INTO OrderItems#Temp (BasePrice, Sku) VALUES (100, '7115AP0Z8')
INSERT INTO Orders (GrandTotal, Items, Payment) VALUES (399.99, OrderItems#Temp, '{...your payment aggregate here...}')
  • Using a text aggregate: 
    INSERT INTO Orders (GrandTotal, Items, Payment) VALUES (399.99, '{...Items aggregate here...}', '{...Payment aggregate here...}')
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.

Columns

Name Type ReadOnly Description
AdjustmentNegative String False

Negative adjustment value.

AdjustmentPositive String False

Positive adjustment value.

AppliedRuleIds String False

Applied rule IDs.

BaseAdjustmentNegative String False

Base negative adjustment value.

BaseAdjustmentPositive String False

Base positive adjustment value.

BaseCurrencyCode String False

Base currency code.

BaseDiscountAmount String False

Base discount amount.

BaseDiscountCanceled String False

Base discount canceled.

BaseDiscountInvoiced String False

Base discount invoiced.

BaseDiscountRefunded String False

Base discount refunded.

BaseDiscountTaxCompensationAmount String False

Base discount tax compensation amount.

BaseDiscountTaxCompensationInvoiced String False

Base discount tax compensation invoiced.

BaseDiscountTaxCompensationRefunded String False

Base discount tax compensation refunded.

BaseGrandTotal String False

Base grand total.

BaseShippingAmount String False

Base shipping amount.

BaseShippingCanceled String False

Base shipping canceled.

BaseShippingDiscountAmount String False

Base shipping discount amount.

BaseShippingDiscountTaxCompensationAmnt String False

Base shipping discount tax compensation amount.

BaseShippingInclTax String False

Base shipping including tax.

BaseShippingInvoiced String False

Base shipping invoiced.

BaseShippingRefunded String False

Base shipping refunded.

BaseShippingTaxAmount String False

Base shipping tax amount.

BaseShippingTaxRefunded String False

Base shipping tax refunded.

BaseSubtotal String False

Base subtotal.

BaseSubtotalCanceled String False

Base subtotal canceled.

BaseSubtotalInclTax String False

Base subtotal including tax.

BaseSubtotalInvoiced String False

Base subtotal invoiced.

BaseSubtotalRefunded String False

Base subtotal refunded.

BaseTaxAmount String False

Base tax amount.

BaseTaxCanceled String False

Base tax canceled.

BaseTaxInvoiced String False

Base tax invoiced.

BaseTaxRefunded String False

Base tax refunded.

BaseToGlobalRate String False

Base-to-global rate.

BaseToOrderRate String False

Base-to-order rate.

BaseTotalCanceled String False

Base total canceled.

BaseTotalDue String False

Base total due.

BaseTotalInvoiced String False

Base total invoiced.

BaseTotalInvoicedCost String False

Base total invoiced cost.

BaseTotalOfflineRefunded String False

Base total offline refunded.

BaseTotalOnlineRefunded String False

Base total online refunded.

BaseTotalPaid String False

Base total paid.

BaseTotalQtyOrdered String False

Base total quantity ordered.

BaseTotalRefunded String False

Base total refunded.

BillingAddress String False

This is an auto-generated column

BillingAddressId Int False

Billing address ID.

CanShipPartially Int False

Can-ship-partially flag value.

CanShipPartiallyItem Int False

Can-ship-partially-item flag value.

CouponCode String False

Coupon code.

CreatedAt Datetime False

Created-at timestamp.

CustomerDob String False

Customer date-of-birth (DOB).

CustomerEmail String False

Customer email address.

CustomerFirstname String False

Customer first name.

CustomerGender Int False

Customer gender.

CustomerGroupId Int False

Customer group ID.

CustomerId Int False

Customer ID.

CustomerIsGuest Int False

Customer-is-guest flag value.

CustomerLastname String False

Customer last name.

CustomerMiddlename String False

Customer middle name.

CustomerNote String False

Customer note.

CustomerNoteNotify Int False

Customer-note-notify flag value.

CustomerPrefix String False

Customer prefix.

CustomerSuffix String False

Customer suffix.

CustomerTaxvat String False

Customer value-added tax (VAT).

DiscountAmount String False

Discount amount.

DiscountCanceled String False

Discount canceled.

DiscountDescription String False

Discount description.

DiscountInvoiced String False

Discount invoiced.

DiscountRefunded String False

Discount refunded amount.

DiscountTaxCompensationAmount String False

Discount tax compensation amount.

DiscountTaxCompensationInvoiced String False

Discount tax compensation invoiced amount.

DiscountTaxCompensationRefunded String False

Discount tax compensation refunded amount.

EditIncrement Int False

Edit increment value.

EmailSent Int False

Email-sent flag value.

EntityId Int False

Order ID.

ExtCustomerId String False

External customer ID.

ExtensionAttributes String False

This is an auto-generated column

ExtOrderId String False

External order ID.

ForcedShipmentWithInvoice Int False

Forced-shipment-with-invoice flag value.

GlobalCurrencyCode String False

Global currency code.

GrandTotal String False

Grand total.

HoldBeforeState String False

Hold before state.

HoldBeforeStatus String False

Hold before status.

IncrementId String False

Increment ID.

IsVirtual Int False

Is-virtual flag value.

OrderCurrencyCode String False

Order currency code.

OriginalIncrementId String False

Original increment ID.

Payment String False

This is an auto-generated column

PaymentAuthExpiration Int False

Payment authorization expiration date.

PaymentAuthorizationAmount String False

Payment authorization amount.

ProtectCode String False

Protect code.

QuoteAddressId Int False

Quote address ID.

QuoteId Int False

Quote ID.

RelationChildId String False

Relation child ID.

RelationChildRealId String False

Relation child real ID.

RelationParentId String False

Relation parent ID.

RelationParentRealId String False

Relation parent real ID.

RemoteIp String False

Remote IP address.

ShippingAmount String False

Shipping amount.

ShippingCanceled String False

Shipping canceled amount.

ShippingDescription String False

Shipping description.

ShippingDiscountAmount String False

Shipping discount amount.

ShippingDiscountTaxCompensationAmount String False

Shipping discount tax compensation amount.

ShippingInclTax String False

Shipping including tax amount.

ShippingInvoiced String False

Shipping invoiced amount.

ShippingRefunded String False

Shipping refunded amount.

ShippingTaxAmount String False

Shipping tax amount.

ShippingTaxRefunded String False

Shipping tax refunded amount.

State String False

State.

Status String False

Status.

StatusHistories String False

Array of status histories.

StoreCurrencyCode String False

Store currency code.

StoreId Int False

Store ID.

StoreName String False

Store name.

StoreToBaseRate String False

Store-to-base rate.

StoreToOrderRate String False

Store-to-order rate.

Subtotal String False

Subtotal.

SubtotalCanceled String False

Subtotal canceled amount.

SubtotalInclTax String False

Subtotal including tax amount.

SubtotalInvoiced String False

Subtotal invoiced amount.

SubtotalRefunded String False

Subtotal refunded amount.

TaxAmount String False

Tax amount.

TaxCanceled String False

Tax canceled amount.

TaxInvoiced String False

Tax invoiced amount.

TaxRefunded String False

Tax refunded amount.

TotalCanceled String False

Total canceled.

TotalDue String False

Total due.

TotalInvoiced String False

Total invoiced amount.

TotalItemCount Int False

Total item count.

TotalOfflineRefunded String False

Total offline refunded amount.

TotalOnlineRefunded String False

Total online refunded amount.

TotalPaid String False

Total paid.

TotalQtyOrdered String False

Total quantity ordered.

TotalRefunded String False

Total amount refunded.

UpdatedAt Datetime False

Updated-at timestamp.

Weight String False

Weight.

XForwardedFor String False

X-Forwarded-For field value.

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
Id_p Int

The order ID.

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