VOOZH about

URL: https://www.cdata.com/kb/tech/xero-jdbc-squirrel-sql.rst

⇱ How to connect to Xero Data with Squirrel SQL client

How to connect to Xero Data with Squirrel SQL client

👁 Jerod Johnson
Jerod Johnson
Director, Technology Evangelism
Connect to Xero data and execute queries in the Squirrel SQL Client.

The CData JDBC Driver for Xero enables you to execute queries to Xero data in tools like Squirrel SQL Client. In this article, you will create a JDBC data source for Xero data and execute queries.

About Xero Data Integration

Accessing and integrating live data from Xero has never been easier with CData. Customers rely on CData connectivity to:

  • Connect to Xero Accounts and both US and Australian Payroll APIs.
  • Read, write, update, and delete Xero objects like Customers, Transactions, Invoices, Sales Receipts and more.
  • Use SQL stored procedures for actions like adding items to a cart, submitting orders, and downloading attachments.
  • Work with accounting, payroll, file, fixed asset, and project data.

Customers regularly integrate their Xero data with preferred tools, like Tableau, Qlik Sense, or Excel, and integrate Xero data into their database or data warehouse.


Getting Started


Add the JDBC Driver for Xero

Follow the steps below to add the driver JAR.

  1. In Squirrel SQL, click Windows -> View Drivers.
  2. Click the plus icon to open the Add Driver wizard.
  3. In the Name box, enter a user-friendly name for the driver; for example, CData JDBC Driver for Xero.
  4. In the Example URL box, enter jdbc:xero:
  5. In the Extra Class Path tab, click Add.
  6. In the file explorer dialog that opens, select the JAR file for the driver, located in the lib subfolder of the installation directory.
  7. Click List Drivers to populate the Class Name menu with the class name for the driver, cdata.jdbc.xero.XeroDriver.
👁 The definition for the JDBC data source. (Salesforce is shown.)

Define Connection Properties

Follow the steps below to save connection properties in the driver alias.

  1. Click Windows -> View Aliases.
  2. In the pane that lists the aliases, click the plus icon.

  3. In the Add Alias wizard that opens, the following fields are required for the JDBC driver:

    • Name: Enter a name for the alias; for example, CData Xero Source.
    • Driver: Select the CData JDBC Driver for Xero.
    • URL: Enter jdbc:xero:
    👁 The alias definition, containing the connection parameters. (Salesforce is shown.)
  4. If you want to define any additional properties, click Properties.
  5. In the Driver properties tab of the dialog that appears, select the Use driver properties checkbox.
  6. In the Specify column, select the checkboxes for the required connection properties.

    To connect, set the Schema connection property in addition to any authentication values. Xero offers authentication for private applications, public applications, and partner applications. Set the XeroAppAuthentication property to PUBLIC, PRIVATE, or PARTNER, depending on the type of application configured. To connect from a private application, you will additionally need to set the OAuthAccessToken, OAuthClientId, OAuthClientSecret, CertificateStoreType, CertificateStore, and CertificateStorePassword.

    To connect from a public or partner application, you can use the embedded OAuthClientId, OAuthClientSecret, and CallbackURL, or you can register an app to obtain your own OAuth values.

    See the "Getting Started" chapter of the help documentation for a guide to authenticating to Xero.

    Below is a typical connection string:

    jdbc:xero:InitiateOAuth=GETANDREFRESH;
    👁 Connection properties automatically detected by Squirrel SQL. (Salesforce is shown.)
  7. In the dialog that appears after you click OK, click connect to test the connection.

Discover Schemas and Query Xero Data

After the metadata has loaded, a new tab for the Xero data source is displayed. On the Objects subtab, you can discover schema information, such as the available tables and views.

👁 Column metadata for a table. (Salesforce is shown.)

To view table data, select the table on the Objects tab. The table data is then loaded in a grid on the Content tab.

👁 Tables can be edited on the Content tab. (Salesforce is shown.)

To execute an SQL query, enter the query on the SQL tab and then click Run SQL (the runner icon). For example: 

SELECT Name, SUM(QuantityOnHand) FROM Items GROUP BY Name
👁 A query and the results. (Salesforce is shown.)

Ready to get started?

Download a free trial of the Xero Driver to get started:

 Download Now

Learn more:

👁 Xero Icon
Xero JDBC Driver

Complete read-write access to Xero accounting enables developers to search (Customers, Transactions, Invoices, Sales Receipts, etc.), update items, edit customers, and more, from any Java/J2EE application.