Which custom queries are supported for ODBC?

(This article refers to a feature that is currently being previewed by certain users. If you're interested in joining this feature's preview group, please reach out to our support team. We'll follow up when the preview group is expanded!)


*ShipStation ODBC is an advanced tool that connects ShipStation to an existing ODBC database. If you are not the developer that set up your database, it is advised that you not attempt to make this connection. Please confer with your IT adviser to see if this would be the best fit for your business's needs. 

If you are the developer/system admin in charge of setting up this ODBC database: As with any other major configuration, we highly advise backing up all necessary data before proceeding with connecting your ODBC database to ShipStation.

By default, ShipStation ODBC only imports orders from a single table or file. However, this standard query may not be the best option for your business needs. This can be the case if your data is in multiple tables or you need to specify WHERE or GROUP BY clauses to improve performance or if you need to restrict the data that is being imported. In such cases the better solution may be to create a "View" in your database. It is important to keep in mind that any issues caused by custom queries fall beyond our scope of troubleshooting. So if you decide to use ODBC beyond its default queries, ShipStation will be unable to troubleshoot any issues with order data not properly transmitting or errors this causes on your database.

Query Types


The following Query Types are supported:

  • BatchOrder: If you plan to import orders into Batch, this is a required query.
  • CountOrders: During import, this query is used to determine how many orders will be loaded into the software. This query is recommended as performance can deteriorate if this query is not specified.
  • PostBack: This query is used to post back status for imported orders. This query is optional if you do not wish to post back the tracking number, status, etc.

 

Missing Query


If you attempt to import an order using Custom Queries and the required query type does not exist, the import will fail and you will receive an error. To resolve this issue, either disable Custom Queries, or update your query to include the missing query type.

 

Updating Queries in Batch


  • On ShipStation ODBC's Manage Stores modal, select the ODBC store needing a custom query configured and click Edit.
  • On the window that appears, check the checkbox for Custom Queries and click Browse to locate the query you wish to customize.
  • An explorer window will open to a directory containing a readme (also attached below) and sample queries.  Your queries must be located in this directory.  For more information about the ShipStation custom query variables, import DB column names, and postback values, consult the Readme.txt file contained in this directory.

Please note that the following query names are the only available options:

  • BatchOrder.sql
  • CountOrders.sql
  • PostBack

And if you ever need to return to the standard queries, simply uncheck the Custom Queries checkbox. 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request