ODBC guide

Reading data in an ODBC application

In this section we will learn how to configure and use Shopify Connector in the API Driver to extract data from the Shopify.

Create data source using Shopify ODBC Driver

Video instructions

Watch this quick walkthrough to see how to configure your Shopify ODBC data source, or scroll down for the step-by-step written guide.

While this video uses the OData Connector as an example, the core concepts and setup process are exactly the same for the Shopify Connector.

Step-by-step instructions

To get data from Shopify using , we first need to create an ODBC data source. We will later read this data in . Perform these steps:

  1. Download and install ODBC PowerPack (if you haven't already).

  2. Search for odbc and open the ODBC Data Sources (64-bit):

    Open ODBC Data Source

  3. Create a User data source (User DSN) based on the ZappySys API Driver driver:

    ZappySys API Driver
    Create new User DSN for ZappySys API Driver
    • Create and use a User DSN if the client application runs under a User Account. This is the ideal option at design time (e.g., when developing in Visual Studio). Use it for both types of applications (64-bit and 32-bit).
    • Create and use a System DSN if the client application runs under a System Account (e.g., as a Windows Service). This is usually the required option in a production environment. If your Windows Service is a 32-bit application, you must use the 32-bit ODBC Data Source Administrator to configure this

  4. When the Configuration window appears give your data source a name if you haven't done that already, then select "Shopify" from the list of Popular Connectors. If "Shopify" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:

    ShopifyDSN
    Shopify
    ODBC DSN Template Selection

  5. Select your authentication scenario below to expand connection configuration steps to:

    • Configure the authentication in Shopify.
    • Enter those details into the ZappySys API Driver data source configuration.

    Admin Access Token (Custom App)

    Shopify authentication

    Method: Admin API Access Token (Recommended for Single-Store Connections)

    This method uses a permanent Admin API Access Token generated from a "Custom App" within your Shopify Admin. This is the simplest way to connect if you are only integrating with your own store.

    Quick Setup Summary:

    • Log in to your Shopify Admin and click Settings (the gear icon in the bottom left).
    • Select Apps from the left-side settings menu.
    • Click the Develop apps button at the top right of the page.
    • Open your app (or create a new one) and configure the necessary Admin API access scopes.
    • Click Install app. Once installed, navigate to the API credentials tab.

    Important: Your Admin API access token (shpat_xxx) is only revealed once. Please copy and store it securely immediately after clicking "Reveal token once."

    Note: If you are using the Shopify Dev Dashboard (via a Partner Account), you must use the OAuth Authorization Code Grant method, as those apps do not issue permanent 'shpat' tokens.

    Video Tutorial: How to Get Admin API Access Token in Shopify?

    Part 1: Create and Configure the Shopify App

    1. Visit https://accounts.shopify.com and log into your Shopify store account.
    2. After logging in, select the store you wish to connect to.
    3. Select Settings (gear icon in the lower-left corner) to launch the Settings screen.
    4. On the left menu panel, select Apps. (Note: Sales channels are now managed in a separate menu item).
    5. On the Apps page, select Develop apps (near the top right of the screen).
      Note: If this is your first time accessing this section, you may need to click "Allow custom app development" to proceed.
    6. On the App development screen, select Create an app.
    7. Give the app a name (e.g., "ZappySys Connector"), select the appropriate developer from the drop-down, and select Create app.
    8. Click Configure Admin API scopes.
    9. In the Admin API access scopes screen, select the checkboxes based on which features you plan to use:
      • For Customers: read_customers, write_customers
      • For Orders: read_orders, write_orders
      • For Products: read_products, write_products
      • For Inventory: read_inventory, write_inventory
      • For Locations: read_locations
      • For Payouts/Analytics: read_all_orders or read_analytics

      Important: If you select fewer scopes than required, some connector features will not work. To ensure full functionality, select both read and write permissions for the resources you need to access.

    10. After selecting your scopes, click Save at the top or bottom of the page.
    11. Install the app by clicking the Install app button (upper-right corner). If prompted, confirm by selecting Install.

    Part 2: Get Admin API Access Token

    1. Once the app is installed, you will be taken to the API credentials tab.
    2. Look for the Admin API access token section. Select Reveal token once to show the token (it starts with shpat_).

    3. IMPORTANT: Copy and save this token immediately in a secure location. You cannot view it again after leaving this page.

      Reveal Token Screenshot

      Note: You may also see an API Key or Secret Key in this section; these are not required for this specific authentication method. Use only the Admin API access token.

    Part 3: Configure the ZappySys Connector

    1. Open the ZappySys connector configuration screen.
    2. Enter Subdomain: Enter the unique subdomain of your Shopify store into the Subdomain parameter textbox.

      How to find your Subdomain:

      If you use a custom domain (e.g., www.my-store.com), you must still use your internal Shopify subdomain:

      • Method 1 (Admin URL): While logged into Shopify, check your browser address bar.
        • If the URL is admin.shopify.com/store/my-unique-slug, your subdomain is my-unique-slug.
        • If the URL is my-unique-slug.myshopify.com/admin, your subdomain is my-unique-slug.
      • Method 2 (Domains Setting): Go to Settings > Domains. Look for the "Primary domain for the online store" or the fallback domain ending in .myshopify.com. The part before the dots is your subdomain.
    3. Enter Access Token: Paste the shpat_xxx token you saved in Step 14 into the Admin API Access Token textbox.
      Tip: Click the ellipses (...) button inside the textbox to open a larger dialog for easier pasting.
    4. Click Test Connection to verify the settings.
    5. If the test is successful, click OK to save the configuration.
    API Connection Manager configuration

    Just perform these simple steps to finish authentication configuration:

    1. Set Authentication Type to Admin Access Token (Custom App) [Http]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. Finally, hit OK button:
    ShopifyDSN
    Shopify
    Admin Access Token (Custom App) [Http]
    https://[$Subdomain$].myshopify.com/admin/api/2025-01
    Required Parameters
    Sub-domain Fill-in the parameter...
    Admin API Access Token Fill-in the parameter...
    Optional Parameters
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429
    RetryCountMax 5
    RetryMultiplyWaitTime True
    ODBC DSN HTTP Connection Configuration
    Find full details in the Shopify Connector authentication reference.

    OAuth Authorization Code Grant (3-legged) - RECOMMENDED

    Shopify authentication

    Authenticate using OAuth 2.0 Authorization Code Flow (3-legged). Use this for apps built in the Dev Dashboard (https://dev.shopify.com). This is the primary OAuth flow supported for public apps and apps created in the Dev Dashboard.

    Method: OAuth Authorization Code Grant (3-Legged)

    This method is for apps built in the Shopify Dev Dashboard. Use this if you are a Partner or developer building a multi-store integration.

    Note: If you are connecting to your own single store, the Admin Access Token method is significantly simpler. Use this OAuth method only if your integration specifically requires a Client ID and Secret.

    Part 1: Create and Configure App in Dev Dashboard

    1. Log in to the Shopify Dev Dashboard using your Partner account.
    2. Click Apps in the left menu, then click Create app.
    3. Select Start from Dev Dashboard (manual setup) and give your app a name.
    4. Step 1: Configure Scopes and URLs via "Versions"
      • Click the Versions tab at the top of the app screen.
      • Click Create version (or edit the Draft).
      • In the URLs section, enter https://zappysys.com/oauth into Allowed redirection URL(s).
      • Select your Admin API scopes based on which features you plan to use:
        • For Customers: read_customers, write_customers
        • For Orders: read_orders, write_orders
        • For Products: read_products, write_products
        • For Inventory: read_inventory, write_inventory
        • For Locations: read_locations
        • For Payouts/Analytics: read_all_orders or read_analytics

        Important: If you select fewer scopes than required, some connector features will not work. To ensure full functionality, select both read and write permissions for the resources you need to access.

      • Click Release (top right). Note: Settings only become active once a version is Released.
    5. Step 2: Set Distribution Method
      • Click Distribution in the left-side menu.
      • Select Custom Distribution.
      • Enter your Shopify store URL (e.g., your-store.myshopify.com) and click Generate link.
      • Action Required: Copy the generated install link, open it in your browser, and follow the prompts to install/unlock the app for your store. You must complete this step before testing the connection in ZappySys.

    Part 2: Get Client Credentials

    1. Click on Settings in the left-side navigation of your app.
    2. In the Client credentials section, copy the Client ID and Client Secret (click the eye icon to reveal).

    Part 3: Configure the ZappySys Connector

    1. Open the ZappySys connector and select OAuth Authorization Code Grant (3-legged).
    2. Subdomain: Enter your store handle (the part before .myshopify.com).
    3. Client ID / Secret: Paste the credentials from Steps 6 and 7.
    4. Redirect URI: Enter https://zappysys.com/oauth.
    5. Click Test Connection. You will be prompted to authorize the app in your browser.

    Note on Tokens: Successful authorization generates an "Offline" Access Token (starting with shpca_). This token does not expire and does not require a refresh token.

    Troubleshooting: If you see "App no longer available," ensure you have completed the Distribution step and followed the Install Link in Step 5 to unlock the app for your store.

    API Connection Manager configuration

    Just perform these simple steps to finish authentication configuration:

    1. Set Authentication Type to OAuth Authorization Code Grant (3-legged) - RECOMMENDED [OAuth]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. Press Generate Token button to generate the tokens.
    5. Finally, hit OK button:
    ShopifyDSN
    Shopify
    OAuth Authorization Code Grant (3-legged) - RECOMMENDED [OAuth]
    https://[$Subdomain$].myshopify.com/admin/api/2025-01
    Required Parameters
    Redirect URI (***Must match App Settings***) Fill-in the parameter...
    ODBC DSN OAuth Connection Configuration
    Find full details in the Shopify Connector authentication reference.

  6. Once the data source connection has been configured, it's time to configure the SQL query. Select the Preview tab and then click Query Builder button to configure the SQL query:

    ZappySys API Driver - Shopify
    Read and write Shopify data effortlessly. Integrate, manage, and automate customers, orders, products, and inventory — almost no coding required.
    ShopifyDSN
    Open Query Builder in API ODBC Driver to read and write data to REST API

  7. Start by selecting the Table or Endpoint you are interested in and then configure the parameters. This will generate a query that we will use in to retrieve data from Shopify. Hit OK button to use this query in the next step. 

    SELECT * FROM Products
    Configure table/endpoint parameters in ODBC data source based on API Driver
    Some parameters configured in this window will be passed to the Shopify API, e.g. filtering parameters. It means that filtering will be done on the server side (instead of the client side), enabling you to get only the meaningful data much faster.

  8. Now hit Preview Data button to preview the data using the generated SQL query. If you are satisfied with the result, use this query in :

    ZappySys API Driver - Shopify
    Read and write Shopify data effortlessly. Integrate, manage, and automate customers, orders, products, and inventory — almost no coding required.
    ShopifyDSN
    SELECT * FROM Products
    API ODBC Driver-based data source data preview
    You can also access data quickly from the tables dropdown by selecting <Select table>.
    A WHERE clause, LIMIT keyword will be performed on the client side, meaning that the whole result set will be retrieved from the Shopify API first, and only then the filtering will be applied to the data. If possible, it is recommended to use parameters in Query Builder to filter the data on the server side (in Shopify servers).

  9. Click OK to finish creating the data source.

Reading Shopify data from client application

  1. Firstly, to get data from ODBC data source based on ZappySys ODBC driver, in your client application, you would need to connect to ODBC source and then from the list select the data source.

  2. Finally, to read the data just read tables/views in your app or enter a SQL statement to extract data, e.g.:

    SELECT * FROM Customers

Shopify Connector Examples

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.

Click here to find more Shopify Connector examples designed for seamless integration with the ZappySys API ODBC Driver under ODBC Data Source (36/64) or ZappySys Data Gateway, enhancing your ability to connect and interact with Prebuilt Connectors effectively.

Consume Data inside your App / Programming Language

Once you know how to load data from Shopify Connector, you can click on one of the below links to learn the steps how to consume data inside your App / Programming Language from Shopify Connector.

ODBC inside ETL / Reporting / BI Tools

ODBC inside Programming Languages

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.

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

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

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

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

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

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