Connecting to Caché with SQuirreL SQL, an external SQL client

The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.

SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.

Finding Caché’s JDBC driver JAR

The JAR file containing the Caché JDBC driver is installed automatically by the Caché installer when installing a full Caché instance or when installing client components only. It can be found in the lib directory under the main installation directory.

For this Caché client installation on a Windows PC, the JAR file can be found at this path:
C:\InterSystems\CACHEClient\lib\cachejdbc.jar

Installing SQuirreL SQL

SQuirreL SQL can be downloaded from the main SQuirreL website:
http://www.squirrelsql.org/

Follow the instructions on the website to install SQuirreL SQL and verify that it works.

Adding a driver entry in SQuirreL SQL

Open SQuirreL SQL and select the “Drivers” tab on the left side of the window. Click the “+” icon to create a new driver entry.

In the “Add Driver” dialog box, select the “Extra Class Path” tab, and click “Add” to add a new entry for the the Caché JDBC driver JAR file.

At the top of the “Add Driver” window, enter a name for the driver such as “Caché 2016.1”.  

The Example URL will be displayed when creating a connection to a database and is a convenient reference. For “Example URL”, enter this sample URL:
jdbc:Cache://127.0.0.1:56772/Samples 

Alternately, you can use a URL that labels each part of the URL:
jdbc:Cache//[HOST NAME OR IP]:[SUPERSERVER PORT]/NAMESPACE

Click the “List Drivers” button on the right side of the “Add Drivers” window, and then select “com.intersys.jdbc.CacheDriver” from the Class Name dropdown list.

For more details about JDBC connection settings and properties for the Caché JDBC driver, consult the following documentation:
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=BGJD_connecting#BGJD_connecting_url

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=BGJD_connecting#BGJD_connecting_connprops

Click OK to save the new driver entry.

Adding a connection (Alias) entry

In SQuirreL SQL, a connection profile for a specific database server is called an Alias. Select the “Aliases” tab on the left side of the main window and click the “+” icon to add a new alias.
 

In the “Add Alias” window, enter a name for this Alias. Select our newly created driver from the dropdown menu.

After selecting the driver, the URL box will be automatically populated using the example URL we added to the driver config. Edit this URL to use the correct host name or IP address and superserver port number for the Caché server, and set the correct namespace.

Enter the username and password for a Caché user that has appropriate SQL privileges.
 

Click the “Test” button and verify that the connection is successful.
 

Click OK to save the new Alias.

Connecting to Caché

You can now connect to the database server by double-clicking on the entry from the Alias tab in the main window.

For instructions on how to use SQuirreL SQL to execute queries, view schema information, and perform other tasks, view the application documentation on the SQuirreL SQL website.