SQL examples for ODBC
The ZappySys API Driver is a user-friendly interface designed to facilitate the seamless integration of various applications with the Shopify API. With its intuitive design and robust functionality, the ZappySys API Driver simplifies the process of configuring specific API endpoints to efficiently read or write data from Shopify.
On this page you will find some SQL examples which can be used for API ODBC Driver or Data Gateway API Connector.
Get list of products
SELECT * FROM ProductsLearn more about this SQL query.
Get a specific product by its ID
SELECT * FROM Products WITH Id=1111111111111Learn more about this SQL query.
Get multiple specific products by their IDs
SELECT * FROM Products WITH(ids='1111111111111,2222222222222,3333333333333')Learn more about this SQL query.
Create a new product
This example shows how to insert a new Shopify product. It also sets Variants
INSERT INTO Products
(
Title
,Status
,BodyHtml
,UrlHandle
,Vendor
,ProductType
,Variants
,Options
,Tags
,Metafields
,Images
)
VALUES
('Ice Cream'
,'draft'
,'<strong>Very yummy ice cream!</strong>'
,'ice-cream'
,'Burton'
,'Snowboard'
,'[
{"price":10.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":100},
{"price":10.5, "option1":"Chocolate","option2":"Medium","sku":"ICE-CHO-MED","inventory_quantity":100},
{"price":11.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":210}
]'
--you must set variants and use atlease one value from the below list in option1, option2 or option3 in any variant entry else it will fail.
,'[
{"name":"Color","values":["Chocolate","Vanilla"]},
{"name":"Size","values":["Small","Medium"]}
]'
,'["Frozen","Seasonal","Dad''s Fav"]'
--adding metadata (custom fields) - metadata fields must be created before setting it
--below are 2 system fields for SEO Title / SEO Description (you dont need to create them unlike custom metadata). These values appears on SEO section
,'[
{"key":"title_tag","value":"Yum Ice Cream SEO Title", "namespace":"global","type":"single_line_text_field"},
{"key":"description_tag","value":"Yum Ice Cream SEO description", "namespace":"global","type":"single_line_text_field"}
]'
--first image becomes main image if you supply multiple images
--upload multiple images from URL (set "src")
, '[
{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
]'
--OR upload multiple local image files (set "attachment")
--, '[
-- {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
-- {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
-- ]'
)Learn more about this SQL query.
Update an existing product
This example shows how to update an existing product. Update product title, description (body html), images, variants and more
UPDATE Products
SET Title='Ice Cream - Updated'
, Status='draft' --active, archived, draft
, BodyHtml='<strong>Very yummy ice cream - updated!</strong>'
--first image becomes main image if you supply multiple images
--upload multiple images from URL (set "src")
, Images='[
{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
]'
--OR upload multiple local image files (set "attachment")
--, Images='[
-- {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
-- {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
-- ]'
, Variants='[
{"price":20.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":300},
{"price":21.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":110}
]'
, PublishedScope='global' --or web
, Vendor ='IceGlobal'
, ProductType ='Cold Food'
, Tags ='["Frozen","Seasonal","Dad''s Fav"]'
--Update SEO URL
,UrlHandle='ice-cream-51'
--Update SEO title / description
, SEOTitle='Yum Ice Cream SEO Title-update'
, SEODescription='Yum Ice Cream SEO description-update'
Where Id=7348335771748Learn more about this SQL query.
Delete an existing product
This example shows how to delete an existing product.
DELETE FROM Products
Where Id=7348335771748Learn more about this SQL query.
Get list of all product variants
SELECT * FROM ProductVariantsLearn more about this SQL query.
Get all product variants by a specific product ID
SELECT * FROM ProductVariants Where ProductId='1111111111111'Learn more about this SQL query.
Get all product variants by multiple specific product IDs
SELECT * FROM ProductVariants WITH(ids='1111111111111,2222222222222,3333333333333')Learn more about this SQL query.
Create a new product variant
This example shows how to create a new product variant.
INSERT INTO ProductVariants (ProductId, Option1, Option2,SKU,Price,CompareAtPrice,Position,Weight,WeightUnit,ImageId)
Values(7348335771748, 'Chocolate', 'Medium', 'ICE-CHO-MED', 195.5, 200.5, 3, 20.5, 'lb', 31900013854820)Learn more about this SQL query.
Update product variant price, image, weight
This example shows how to update product variant price, image, weight and other attributes.
Update ProductVariants
SET
,Option1='Chocolate'
,Option2='Large'
,SKU='ICE-CHO-SML'
,Price=90.45
,CompareAtPrice=100.45
,Position=2
,Weight=10.5
,WeightUnit='lb'
,ImageId=31900013854820 --use available images from Products table
Where Id=42564507992164Learn more about this SQL query.
Delete an existing product variant
This example shows how to delete an existing product variant by Variant Id.
DELETE FROM ProductVariants
WHERE Id=31900013854820Learn more about this SQL query.
Get list of customers
SELECT * FROM CustomersLearn more about this SQL query.
Get a specific customer by its ID
SELECT * FROM Customers Where Id=12345Learn more about this SQL query.
Get multiple specific customers by their IDs
SELECT * FROM Customers
WITH (ids='1111111111111,2222222222222,3333333333333')Learn more about this SQL query.
Insert a new customer record
INSERT INTO Customers
(FirstName, LastName, Email, Phone, Password, PasswordConfirmation, SendWelcomeEmail, MultipassIdentifier, Note, Tags, TaxExempt, TaxExemptions, DefaultAddressFirstName, DefaultAddressLastName, DefaultAddressCompany, DefaultAddressLine1, DefaultAddressLine2, DefaultAddressCity, DefaultAddressProvince, DefaultAddressCountry, DefaultAddressZip, DefaultAddressPhone, DefaultAddressName, DefaultAddressProvinceCode, DefaultAddressCountryCode, DefaultAddressCountryName)
VALUES
('John', 'Doe', 'john.doe@gmail.com', '7705553543', 'myNewP@ssword123', 'myNewP@ssword123', 1, null, 'This is a note on the customer account.', null, 0, null, 'John', 'Doe', 'John Doe Corp.', '123 Main Street', null, 'Atlanta', 'Georgia', 'United States', '30135', '7705553543', 'John Doe', 'GA', 'US', 'United States')Learn more about this SQL query.
Insert a new customer record using RAW JSON Body (special column _rawdoc_)
Sometimes you have need to INSERT or UPDATE certain arrtibutes for which input columns not defined. In this case you can supply entire BODY JSON as input using special column name _rawdoc_
INSERT INTO Customers(_rawdoc_)
VALUES('{"customer":{"first_name":"John","last_name":"Doe","email":"a.doe@gmail.com","phone":"7705553111"}}')Learn more about this SQL query.
Insert customers in BULK (read from external MS SQL database)
In this example we are reading customer Name, Email, Phone from external source system (Microsoft SQL Server) and sending it to Shopify. Your column name must match with Input columns of the table. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection.
INSERT INTO Customers(FirstName, LastName, Email, Phone)
SOURCE('MSSQL'
,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select ''John'' as FirstName, ''Doe'' as LastName, ''a.doe@gmail.com'' as Email, ''7705553111'' as Phone'
)Learn more about this SQL query.
Insert customers in BULK using RAW JSON Body (read from external MS SQL database)
In this example we are reading customer Name, Email, Phone from external source system (Microsoft SQL Server) and sending it to Shopify. Your column name must match with Input columns of the table. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection.
INSERT INTO Customers
SOURCE('MSSQL'
,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select ''{"customer":{"first_name":"Cust1","last_name":"Doe1","email":"a.doe@gmail.com","phone":"7705553111"}}'' as _rawdoc_
UNION
select ''{"customer":{"first_name":"Cust2","last_name":"Doe2","email":"b.doe@gmail.com","phone":"7705553222"}}'' as _rawdoc_
'
)Learn more about this SQL query.
Update an existing customer record
UPDATE Customers SET
Email = 'john.doe2@gmail.com',
Phone = '7705553445',
Note= 'This is a new note that needed to be added later.'
WHERE Id=1111111111111Learn more about this SQL query.
Update an existing customer record using RAW JSON Body (special column _rawdoc_)
Sometimes you have need to INSERT or UPDATE certain arrtibutes for which input columns not defined. In this case you can supply entire BODY JSON as input using special column name _rawdoc_
UPDATE Customers
SET _rawdoc_='{"customer":{"first_name":"John_new","last_name":"Doe_new","email":"a_new.doe@gmail.com","phone":"7705553111"}}'
WHERE Id=1111111111111Learn more about this SQL query.
Update an existing customer record
UPDATE Orders SET
FulfillmentStatus = 'john.doe5@gmail.com',
Phone = '7705553111',
Note= 'This is a new note that needed to be added to the order later.'
WHERE Id=1111111111111Learn more about this SQL query.
Update customers in BULK (read from external MS SQL database)
In this example we are reading customer Ids, Email, Notes from external source system (Microsoft SQL Server) and sending it to Shopify. Your column name must match with Input columns of the table you trying to update. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection.
UPDATE Customers
SOURCE('MSSQL'
,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select 111 as Id, ''a@a.com''Email , ''SOLD'' as Note,0 as [$$ContineOn404Error]
UNION
select 222 as Id, ''b@b.com''Email , ''SOLD'' as Note,0 as [$$ContineOn404Error]
'
)Learn more about this SQL query.
Delete a customer record
DELETE Customers WHERE Id=1111111111111Learn more about this SQL query.
Delete a customer record (throw error if not found)
DELETE Customers WHERE Id=1111111111111 (ContineOn404Error=0)Learn more about this SQL query.
Delete customers in BULK (read Id from external MS SQL database)
In this example we are reading customer Ids from external source system (Microsoft SQL Server) and sending it to Shopify. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection.
DELETE FROM Customers
SOURCE('MSSQL'
,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select 111 as Id,1 as [$$ContineOn404Error]
UNION
select 222 as Id,1 as [$$ContineOn404Error]
'
)Learn more about this SQL query.
Get all orders
SELECT * FROM OrdersLearn more about this SQL query.
Get open orders
SELECT * FROM Orders WITH (Status='open') --also try 'any', 'open', 'closed', 'cancelled'Learn more about this SQL query.
Get a specific order by its ID
SELECT * FROM Orders Where Id=1111111111111Learn more about this SQL query.
Get multiple specific orders by their IDs
SELECT * FROM Orders WITH(ids='1111111111111,2222222222222,3333333333333')Learn more about this SQL query.
Delete an order record
DELETE Orders WHERE Id=1111111111111Learn more about this SQL query.
Delete an order record (throw error if not found)
DELETE Orders WHERE Id=1111111111111 (ContineOn404Error=0)Learn more about this SQL query.
Get line items for all orders
SELECT * FROM OrderItemsLearn more about this SQL query.
Get line items for a specific order by the order ID
SELECT * FROM OrderItems Where OrderId=1111111111111Learn more about this SQL query.
Get line items for multiple specific orders by their order IDs
SELECT * FROM OrderItems WITH(ids='1111111111111,2222222222222,3333333333333')Learn more about this SQL query.
Insert a new order record
INSERT INTO Orders (BillingAddressLine1, BillingAddressLine2, BillingAddressCity, BillingAddressCompany, BillingAddressCountry, BillingAddressFirstName, BillingAddressLastName, BillingAddressPhone, BillingAddressProvince, BillingAddressZip, BillingAddressName, BillingAddressProvinceCode, BillingAddressCountryCode, BuyerAcceptsMarketing, LineItems, CustomerId, Email, EstimatedTaxes, FinancialStatus, FulfillmentStatus, Name, Note, Phone, Currency, PresentmentCurrency, ProcessedAt, ReferringSite, ShippingAddressLine1, ShippingAddressLine2, ShippingAddressCity, ShippingAddressCompany, ShippingAddressCountry, ShippingAddressFirstName, ShippingAddressLastName, ShippingAddressPhone, ShippingAddressProvince, ShippingAddressZip, ShippingAddressName, ShippingAddressProvinceCode, ShippingAddressCountryCode, Tags, TaxesIncluded, TotalWeight, SendReceipt, SendFulfillmentReceipt)
VALUES
('123 Main Street', 'Suite #54', 'Memphis', 'Acme, Inc.', 'United States', 'John', 'Doe', '4045559876', 'Tennessee', '38101', 'John Doe', 'GA', 'US', 1, '[{"title":"Super Strong Glue","price":24.99,"grams":"100","quantity":1,"tax_lines":[{"price":13.5,"rate":0.06,"title":"State tax"}]}]', 5945175474276, 'johndoe2@gmail.com', 1, 'pending', null, '#40294', 'This order needs to be expedited, so register it in the system as so.', '4045559876', 'USD', 'USD', '2023-02-27T11:00:00', 'https://referringsite.com', '123 Main Street', 'Suite #54', 'Memphis', 'Acme, Inc.', 'United States', 'John', 'Doe', '4045559876', 'Tennessee', '38101', 'John Doe', 'GA', 'US', NULL, 1, 20, 1, 1)Learn more about this SQL query.
Get inventory levels for all locations
Query inventory levels for all locations. If you get URL Too long error then manually supply location ids in the query (see other example)
select * from InventoryLevels
--WITH(location_ids='43512280416356, 44648752676964, ..... upto 300 to 500 more - until you hit URL limit error')Learn more about this SQL query.
Get inventory level for multiple item inventory id(s)
If you get URL Too long error then reduce inventory_item ids in the query (approx 300-400 ids per call allowed)
select * from InventoryLevels WITH (inventory_item_ids='43512280416356, 44648752676964')Learn more about this SQL query.
Get inventory level for specific location id(s) (i.e. Physcical Store / POS )
If you get URL Too long error then reduce location ids in the query (approx 300-400 ids per call allowed)
select * from InventoryLevels WITH (location_ids='43512280416356, 44648752676964')Learn more about this SQL query.
Get inventory level for specific inventory / location id(s)
select * from InventoryLevels WITH (inventory_item_ids='43512280416356, 44648752676964' , location_ids='111100034, 111100055')Learn more about this SQL query.
Adjust inventory level for a specific inventory / location id(s)
Adjusts the inventory level of an inventory item at a single location
UPDATE InventoryLevels
SET AvailableAdjustment=488,
LocationId=25801916516
WHERE InventoryItemId=43512276942948
WITH(
Action='Adjust' --or set or connect
, ContineOn404Error=0
)Learn more about this SQL query.
Set / insert inventory with a specific inventory item and location id
Sets the inventory level for an inventory item at a location. If the specified location is not connected, it will be automatically connected first. When connecting inventory items to locations
UPDATE InventoryLevels
SET LocationId=25801916516
,Available=488
WHERE InventoryItemId=43512276942948
WITH(
Action='set' --or adjust or connect
, ContineOn404Error=0
)
--OR--
/*
INSERT INTO InventoryLevels (InventoryItemId,LocationId,Available)
VALUES(43512276942948, 25801916516, 488)
--WITH( ContineOn404Error=0 )
*/Learn more about this SQL query.
Connects an inventory item to a location
Connects an inventory item to a location by creating an inventory level at that location.
UPDATE InventoryLevels
SET LocationId=25801916516
WHERE InventoryItemId=43512276942948
WITH(
Action='connect' --or adjust or set
, ContineOn404Error=0
)Learn more about this SQL query.
Set inventory with a specific inventory item and location id - generic API
If you get URL Too long error then reduce location ids in the query (approx 300-400 ids per call allowed)
SELECT * FROM generic_request
WITH (
URL='/inventory_levels/set.json'
--OR Use full URL
--URL='https://MY-STORE-HERE.myshopify.com/admin/api/2023-01/inventory_levels/set.json'
, RequestMethod='POST'
, Body='{"location_id":25801916516,"inventory_item_id":43512280416356,"available":42}' --needed if you call PUT, POST
, Filter='$.inventory_level' --change table name here
, Headers='Content-Type: application/json'
, Meta='inventory_item_id:long; location_id:long; available:int; updated_at: datetime'
)Learn more about this SQL query.
Get inventory item by id
You can find Inventory Item Id in ProductVariants table. ProductVariant has One-to-One mapping with InventoryItems table
select * from InventoryItems Where Id=43512280416356Learn more about this SQL query.
Get inventory item by id
You can find Inventory Item Id in ProductVariants table. ProductVariant has One-to-One mapping with InventoryItems table
select * from InventoryItems Where Id=43512280416356Learn more about this SQL query.
Get inventory items by multiple Ids
Query multiple InventoryItems by Ids (Comma separated list). You can find Inventory Item Id in ProductVariants table. ProductVariant has One-to-One mapping with InventoryItems table
select * from InventoryItems WITH(Ids='43512280416356, 43512280449124')Learn more about this SQL query.
Update an existing inventory item cost and other attributes
UPDATE InventoryItems
SET Cost='25.55'
WHERE Id=43512280416356Learn more about this SQL query.
Generic Query using Shopify admin GraphQL API
This example shows how to invoke GraphQL query for very generic data read/write. For more information on GraphQL API visit this link https://shopify.dev/docs/api/admin/getting-started
SELECT * FROM generic_request
WITH (
URL='/graphql.json'
--OR Use full URL
--URL='https://MY-STORE-HERE.myshopify.com/admin/api/2023-10/graphql.json'
, RequestMethod='POST'
, Filter='$.data.products.nodes' --change table name here e.g. products
, Headers='Content-Type: application/json'
--change table name and columns below here e.g. products... and id, title etc
-- change pagesize if needed (i.e. max 250)
, Body='{
"query" : "<<{
products(first: 250 [$tag$])
{
nodes {
id
title
createdAt
}
pageInfo {
hasNextPage
endCursor
}
}
},FUN_JSONENC>>"
}'
, NextUrlAttributeOrExpr='$.data.products.pageInfo.endCursor' --change table name
, NextUrlEndIndicator='false'
, StopIndicatorAttributeOrExpr='$.data.products.pageInfo.hasNextPage' --change table name
, UseConnection='True'
, EnablePageTokenForBody='True'
, HasDifferentNextPageInfo='True'
, NextPageBodyPart='after: \"[$pagetoken$]\"'
--Use metadata to speed up execution. To get Metadata Run query without Meta clause.
-- Then click View Metadata button found in Botttom Result Grid Toolbar. Get Compact format and paste below
--, Meta='id:String(255); title:String(255); createdAt:DateTime; '
)Learn more about this SQL query.
Generic Query using Shopify admin REST API
This example shows how to invoke pretty much any REST API for generic data read/write. For more information on REST API visit this link https://shopify.dev/docs/api/admin/getting-started
SELECT * FROM generic_request
WITH (
URL='/products.json'
--OR Use full URL
--URL='https://MY-STORE-HERE.myshopify.com/admin/api/2023-01/products.json'
, RequestMethod='GET'
, Body='{}' --needed if you call PUT, POST
, Filter='$.products[*]' --change table name here
, Headers='Content-Type: application/json'
, PagingMode='ByResponseHeaderRfc5988'
--Use metadata to speed up execution. To get Metadata Run query without Meta clause.
--Then click View Metadata button found in Botttom Result Grid Toolbar. Get Compact format and paste below
, Meta='id:String(255); title:String(255); created_at:DateTime; '
)Learn more about this SQL query.
Getting Started with Examples
ZappySys API Driver is a powerful software solution designed to facilitate the extraction and integration of data from a wide range of sources through APIs. Its intuitive design and extensive feature set make it an essential asset for any organization dealing with complex data integration tasks.
To get started with examples using ZappySys API Driver, please click on the following applications:
Key features of the ZappySys API Driver include:
The API ODBC driver facilitates the reading and writing of data from numerous popular online services (refer to the complete list here) using familiar SQL language without learning complexity of REST API calls. The driver allows querying nested structure and output as a flat table. You can also create your own ODBC / Data Gateway API connector file and use it with this driver.
Intuitive Configuration: The interface is designed to be user-friendly, enabling users to easily set up the specific API endpoints within Shopify without requiring extensive technical expertise or programming knowledge.
Customizable Endpoint Setup: Users can conveniently configure the API endpoint settings, including the HTTP request method, endpoint URL, and any necessary parameters, to precisely target the desired data within Shopify.
Data Manipulation Capabilities: The ZappySys API Driver allows for seamless data retrieval and writing, enabling users to fetch data from Shopify and perform various data manipulation operations as needed, all through an intuitive and straightforward interface.
Secure Authentication Integration: The driver provides secure authentication integration, allowing users to securely connect to the Shopify API by inputting the necessary authentication credentials, such as API tokens or other authentication keys.
Error Handling Support: The interface is equipped with comprehensive error handling support, ensuring that any errors or exceptions encountered during the data retrieval or writing process are efficiently managed and appropriately communicated to users for prompt resolution.
Data Visualization and Reporting: The ZappySys API Driver facilitates the seamless processing and presentation of the retrieved data from Shopify, enabling users to generate comprehensive reports and visualizations for further analysis and decision-making purposes.
Overall, the ZappySys API Driver serves as a powerful tool for streamlining the integration of applications with Shopify, providing users with a convenient and efficient way to access and manage data, all through a user-friendly and intuitive interface.