JDBC Driver for Shopify

Build 23.0.8839

Orders

Create, update, delete, and query orders.

Table-Specific Information

Select

The driver uses the Shopify API to process search criteria that refer to the Id, FinancialStatus, FulfillmentStatus, CreatedAt, ProcessedAt and UpdatedAt columns. The supported SQL operators are '=' and 'IN' for Id, '=' for FinancialStatus, and FulfillmentStatus, and '>' and '<' for the CreatedAt, ProcessedAt, and UpdatedAt columns. The driver processes other filters client-side within the driver.

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

SELECT * FROM Orders WHERE Id = '123'

SELECT * FROM Orders WHERE FinancialStatus = 'pending'

SELECT * FROM Orders WHERE CreatedAt > '2017-10-25'

Insert

  • Create a simple order with only a product variant Id using aggregates.

    INSERT INTO Orders (LineAggregate) VALUES ('[{\"variant_id\":\"4236041945111\",\"quantity\":2}]')

  • Create a simple order with only a product variant Id using temporary table. The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For instance:

    INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('123', 2)

    Once your temporary table is populated, it is now time to insert to the actual table in Shopify. You can do this by performing an INSERT to the actual table and setting as a value for LinesAggregate the name of the temporary table. For instance:

    INSERT INTO Orders (LineAggregate) VALUES ('OrdersItems#TEMP')

  • Create a simple order, sending the order receipt and fulfillment receipt to the customer using aggregates.

    INSERT INTO Orders (Email, FulfillmentStatus, SendReceipt, SendFulfillmentReceipt, LineAggregate) VALUES ('[email protected]', 'fulfilled', true, true, '[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]')

  • Create a simple order, sending the order receipt and fulfillment receipt to the customer using temporary table.

    INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236041945111', 2)
    INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236069011479', 3)
    INSERT INTO Orders (Email, FulfillmentStatus, SendReceipt, SendFulfillmentReceipt, LineAggregate) VALUES ('[email protected]', 'fulfilled', true, true, 'OrdersItems#TEMP')

  • Create a simple order, with taxes using aggregates.

    INSERT INTO Orders (LineAggregate, TaxAggregate) VALUES ('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]', '[{\"price\":10.2,\"rate\":0.06,\"title\":\"State Tax\"}]')

  • Create a simple order, with taxes using temporary table.

    INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236041945111', 2)
    INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236069011479', 3)
    INSERT INTO TaxItems#TEMP (TaxItemPrice, TaxItemRate, TaxItemTitle) VALUES (10.2, 0.06, 'State Tax')
    INSERT INTO Orders (LineAggregate, TaxAggregate) VALUES ('OrdersItems#TEMP', 'TaxItems#TEMP')

  • Create a simple order, with multiple taxes using aggregates.

    INSERT INTO Orders (LineAggregate, TaxAggregate) VALUES ('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]', '[{\"price\":10.2,\"rate\":0.06,\"title\":\"State Tax\"},{\"price\":4.25,\"rate\":0.025,\"title\":\"County Tax\"}]')

  • Create a simple order, with multiple taxes using temporary table.

    INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236041945111', 2)
    INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('4236069011479', 3)
    INSERT INTO TaxItems#TEMP (TaxItemPrice, TaxItemRate, TaxItemTitle) VALUES (10.2, 0.06, 'State Tax')
    INSERT INTO TaxItems#TEMP (TaxItemPrice, TaxItemRate, TaxItemTitle) VALUES (4.25, 0.025, 'County Tax')
    INSERT INTO Orders (LineAggregate, TaxAggregate) VALUES ('OrdersItems#TEMP', 'TaxItems#TEMP')

  • Create a more comprehensive order using aggregates.

    INSERT INTO Orders (LineAggregate) VALUES ([{\"title\":\"Big Brown Bear Boots\",\"price\":74.99,\"grams\":1300,\"quantity\":3}])

  • Create a more comprehensive order using temporary table.

    INSERT INTO OrdersItems#TEMP (ItemTitle, ItemPrice, ItemGrams, ItemQuantity) VALUES ('Big Brown Bear Boots', 74.99, 1300, 3)
    INSERT INTO Orders (LineAggregate) VALUES ('OrdersItems#TEMP')

  • Create a pending order with an existing customer using aggregates.

    INSERT INTO Orders (LineAggregate, CustomerId, FinancialStatus) VALUES ('[{\"variant_id\":\"123\",\"quantity\":3}]', '456', 'pending')

  • Create a pending order with an existing customer using temporary table.

    INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('123', 3)
    INSERT INTO Orders (LineAggregate, CustomerId, FinancialStatus) VALUES ('OrdersItems#TEMP', '456', 'pending')

  • Create a partially paid order with a new customer and addresses using aggregates.

    INSERT INTO Orders (LineAggregate, CustomerFirstName, CustomerLastName, CustomerEmail, FinancialStatus) VALUES ('[{\"variant_id\":\"123\",\"quantity\":3}]', 'Paul', 'Norman', '[email protected]', 'partially_paid')

  • Create a partially paid order with a new customer and addresses using temporary.

    INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('123', 3)
    INSERT INTO Orders (LineAggregate, CustomerFirstName, CustomerLastName, CustomerEmail, FinancialStatus) VALUES ('OrdersItems#TEMP', 'Paul', 'Norman', '[email protected]', 'partially_paid')

  • Close an order (you must specify the Id of the order).

      INSERT INTO Orders (Id, Operation) VALUES ('123', 'Close')
      

  • Reopen an order (you must specify the Id of the order).

      INSERT INTO Orders (Id, Operation) VALUES ('123', 'Open')
      

  • Cancel an order (you must specify the Id of the order).

      INSERT INTO Orders (Id, Operation) VALUES ('123', 'Cancel')
      

Update

Only columns BuyerAcceptsMarketing, Email, Phone, Note, Tags, ShippingAddressFirstName, ShippingAddressLastName, ShippingAddressAddress1, ShippingAddressAddress2, ShippingAddressPhone, ShippingAddressCity, ShippingAddressCompany, ShippingAddressZip, ShippingAddressProvince, ShippingAddressCountry, ShippingAddressLatitude, ShippingAddressLongitude, ShippingAddressName, ShippingAddressCountryCode, ShippingAddressProvinceCode and ShippingAddressDefault can be updated.

  • Update the shipping address of an existing order.

    UPDATE Orders SET ShippingAddressAddress1 = '123 Ship Street', ShippingAddressCity = 'Shipsville' WHERE Id = '123'

  • Update an order's tags.

    UPDATE Orders SET Tags = 'External, Inbound, Outbound' WHERE Id = '123'

Delete

You must specify the Id of the order when deleting an item from this table.

DELETE FROM Orders WHERE Id = '123'

Columns

Name Type ReadOnly References Description
Id [KEY] Long True

A unique numeric identifier for the order.

Number Long True

A unique identifier for the order, used by the shop owner and customer.

CustomerId Long False

Customers.Id

A unique numeric identifier for the customer.

CustomerFirstName String False

The first name of the customer.

CustomerLastName String False

The last name of the customer.

Currency String False

The three letter code (ISO 4217) for the currency used for the payment.

Email String False

The customer's email address.

Name String True

The customer's order name as represented by a number.

TotalDiscounts Decimal True

The total amount of the discounts to be applied to the price of the order.

TotalOrderItemsPrice Decimal True

The sum of all the prices of all the items in the order.

TotalPrice Decimal True

The sum of all the prices of all the items in the order, taxes and discounts included.

SubtotalPrice Decimal True

Price of the order before shipping and taxes.

TotalTax Decimal False

The sum of all the taxes applied to the order.

TotalWeight Int True

The sum of all the weights of the line items in the order, in grams.

BrowserIp String True

The IP address of the browser used by the customer when placing the order.

BuyerAcceptsMarketing Bool False

Indicates whether or not the person who placed the order would like to receive email updates from the shop.

CancelReason String True

The reason why the order was canceled.

CartToken String True

Unique identifier for a particular cart that is attached to a particular order.

OrderStatusUrl String True

The URL pointing to the order status web page.

Token String True

Unique identifier for a particular order.

TaxesIncluded Bool False

States whether or not taxes are included in the order subtotal.

SourceName String True

Where the order originated.

ReferringSite String True

The website that the customer clicked on to come to the shop.

Phone String False

The customer's phone number.

Note String False

The text of an optional note that a shop owner can attach to the order.

LocationId Long True

The unique numeric identifier for the physical location at which the order was processed.

LandingSite String True

The URL for the page where the buyer landed when entering the shop.

Tags String False

Additional short descriptors.

FulfillmentStatus String False

The fulfillment status of the order. Valid values during SELECT: shipped, partial, unshipped, any, unfulfilled. Valid values during INSERT: fulfilled, null, partial, restocked.

FinancialStatus String False

The financial status of the order. Valid values during SELECT: authorized, pending, paid, partially_paid, refunded, voided, partially_refunded, any, unpaid. Valid values during INSERT: authorized, pending, paid, partially_paid, refunded, voided, partially_refunded.

CustomerLocale String True

The customer locale of the order.

InvoiceSentAt Datetime True

This auto-generated property is the date and time when the invoice was emailed to the customer. Only available for draft orders.

InvoiceUrl String True

The URL for the invoice. Only available for draft orders.

TaxExempt Bool True

States whether or not taxes are exempt for this order. Only available for draft orders.

DiscountCodes String True

Applicable discount codes that can be applied to the order.

LineAggregate String False

A JSON aggregate of line items associated with the order.

TaxAggregate String False

A JSON aggregate of tax line items associated with the order. Note, these taxes are applied on the order, not on individual items. If you want them to be applied on individual items, use them in LineAggregate. Tax lines must be associated with either order or line item but not both.

ShippingAggregate String False

A JSON aggregate of shipping line items associated with the order.

NoteAttributesAggregate String False

A JSON aggregate of note attributes associated with the order.

DiscountApplicationsAggregate String True

An ordered list of stacked discount applications.

BillingAddressFirstName String False

The first name of the person associated with the payment method.

BillingAddressLastName String False

The last name of the person associated with the payment method.

BillingAddressAddress1 String False

The street address of the billing address.

BillingAddressAddress2 String False

An optional additional field for the street address of the billing address.

BillingAddressPhone String False

The phone number at the billing address.

BillingAddressCity String False

The city of the billing address.

BillingAddressCompany String False

The company of the person associated with the billing address.

BillingAddressZip String False

The zip or postal code of the billing address.

BillingAddressProvince String False

The name of the state or province of the billing address.

BillingAddressCountry String False

The name of the country of the billing address.

BillingAddressLatitude Double False

The latitude of the billing address.

BillingAddressLongitude Double False

The longitude of the billing address.

BillingAddressName String False

The full name of the person associated with the payment method.

BillingAddressCountryCode String False

The two-letter code for the country of the billing address.

BillingAddressProvinceCode String False

The two-letter abbreviation of the state or province of the billing address.

BillingAddressDefault Bool False

Whether this address is the default one or not.

ShippingAddressFirstName String False

The first name of the person associated with the shipping method.

ShippingAddressLastName String False

The last name of the person associated with the shipping method.

ShippingAddressAddress1 String False

The street address of the shipping address.

ShippingAddressAddress2 String False

An optional additional field for the street address of the shipping address.

ShippingAddressPhone String False

The phone number at the shipping address.

ShippingAddressCity String False

The city of the shipping address.

ShippingAddressCompany String False

The company of the person associated with the shipping address.

ShippingAddressZip String False

The zip or postal code of the shipping address.

ShippingAddressProvince String False

The name of the state or province of the shipping address.

ShippingAddressCountry String False

The name of the country of the shipping address.

ShippingAddressLatitude Double False

The latitude of the shipping address.

ShippingAddressLongitude Double False

The longitude of the shipping address.

ShippingAddressName String False

The full name of the person associated with the shipping method.

ShippingAddressCountryCode String False

The two-letter code for the country of the shipping address.

ShippingAddressProvinceCode String False

The two-letter abbreviation of the state or province of the shipping address.

ShippingAddressDefault Bool False

Whether this address is the default one or not.

AppliedDiscountTitle String False

The title of the applied discount for this order. Only available for draft orders.

AppliedDiscountDescription String False

The description of the applied discount for order. Only available for draft orders.

AppliedDiscountValue Decimal False

The value of the applied discount for this order. Only available for draft orders.

AppliedDiscountValueType String False

The value type of the applied discount for order. Only available for draft orders.

AppliedDiscountAmount Decimal False

The amount of the applied discount for this order. Only available for draft orders.

PaymentTermsAmount Long True

The amount that is owed according to the payment terms.

PaymentTermsCurrency String True

The presentment currency for the payment.

PaymentTermsPaymentTermsName String True

The name of the selected payment terms template for the order.

PaymentTermsPaymentTermsType String True

The type of selected payment terms template for the order.

PaymentTermsDueInDays Int True

The number of days between the invoice date and due date that is defined in the selected payment terms template.

PaymentTermsPaymentSchedules String True

An array of schedules associated to the payment terms.

ProcessedAt Datetime True

The date and time when the order was imported, in ISO 8601 format.

CreatedAt Datetime True

The date and time when the order was created.

ClosedAt Datetime True

The date and time when the order was closed.

CancelledAt Datetime True

The date and time when the order was canceled.

UpdatedAt Datetime True

The date and time when the order was last modified.

SendReceipt Bool False

Determines whether an order confirmation will be sent to the customer.

SendFulfillmentReceipt Bool False

Determines whether a fulfillment confirmation will be sent to the customer.

InventoryBehaviour String False

Determines which inventory updating behavior is used. The following values are available: default, decrement_ignoring_policy, decrementobeying_policy

Operation String False

An operation to apply to the Order. Valid values for order: Close, Open, or Cancel.

TotalShippingPriceSetShopMoneyAmount Double True

The amount of the shop money in the total shipping price set.

TotalShippingPriceSetShopMoneyCurrencyCode String True

The currency code of the shop money in the total shipping price set.

TotalShippingPriceSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the total shipping price set.

TotalPriceSetShopMoneyAmount Decimal True

The amount of the shop money in the total price set.

TotalPriceSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the total price set.

TotalTaxSetShopMoneyAmount Decimal True

The amount of the shop money in the total tax set.

TotalTaxSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the total tax set.

CheckoutId Long True

The checkout id.

CheckoutToken String True

A unique value when referencing the checkout that's associated with the order.

EstimatedTaxes Bool True

Estimated taxes of the order.

Test Bool True

Test boolean for the order.

TotalOutstanding Decimal True

The outstanding sum of all the items in the order.

TotalTipReceived Decimal True

The total tip for the order.

PresentmentCurrency String True

The currency code of the presentment money.

TotalLineItemsPriceSetShopMoneyAmount Decimal True

The amount of the shop money in the total line items price set.

TotalLineItemsPriceSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the total line items price set.

TotalDiscountsSetShopMoneyAmount Decimal True

The amount of the shop money in the discounts set.

TotalDiscountsSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the discounts set.

TotalSubtotalSetShopMoneyAmount Decimal True

The amount of the shop money in the subtotal.

TotalSubtotalSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the subtotal.

OrigTotalAdditionalFeesSetShopMoneyAmount Decimal True

The amount of the shop money in the original total additional fees set.

OrigTotalAdditionalFeesSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the original total additional fees set.

CurTotalDiscountsSetShopMoneyAmount Decimal True

The amount of the shop money in the current total discounts set.

CurTotalDiscountsSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the current total discounts set.

CurSubtotalPriceSetShopMoneyAmount Decimal True

The amount of the shop money in the current subtotal price set.

CurSubtotalPriceSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the current subtotal price set.

CurrentTotalTaxSetShopMoneyAmount Decimal True

The amount of the shop money in the current total tax set.

CurrentTotalTaxSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the current total tax set.

CurTotalAdditionalFeesSetShopMoneyAmount Decimal True

The amount of the shop money in the current total additional fees set.

CurTotalAdditionalFeesSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the current total additional fees set.

CurrentTotalPriceSetShopMoneyAmount Decimal True

The amount of the shop money in the current total price set.

CurrentTotalPriceSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the current total price set.

OriginalTotalDutiesSetShopMoneyAmount Decimal True

The amount of the shop money in the original total duties set.

OriginalTotalDutiesSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the original total duties set.

CurrentTotalDutiesSetShopMoneyAmount Decimal True

The amount of the shop money in the current total duties set.

CurrentTotalDutiesSetPresentmentMoneyAmount Decimal True

The amount of the presentment money in the current total duties set.

PaymentGatewayNames String True

The list of payment gateway names

FulfillmentAggregate String True

A JSON aggregate of fulfillments associated with the order. Note, these fulfillments are applied on the order, not on individual items.

RefundAggregate String True

A JSON aggregate of refunds associated with the order. Note, these refunds are applied on the order, not on individual items.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, to offer a more granular control over the tuples that are returned from the data source, or as parameters in INSERT statements.

Name Type Description
Status String

Filter orders by their status. Valid values are: open: show only open orders, closed: show only closed orders, cancelled: show only canceled orders, any: show orders of any status,default: open.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839