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

JDBC, ODBC, SQL, Caché

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.

 
 
  • + 7
  • 0
  • 3364
  • 8

Comments

I'm attempting to use Squirrel on a Windows system with a locally-installed version of Caché/Ensemble 2014.1. Unfortunately, the version of the JRE on that system is 1.6, with which Squirrel is incompatible. Updating the JRE to 1.8 allows Squirrel to work, but breaks the JDBC gateway and client functionality in Caché. Both applications check the JRE version in the Windows registry on initialization, so messing around with PATH and CLASSPATH don't solve the problem.

Any thoughts?

 

PS. Upgrading to a more current version of Caché is not currently an option; this is a customer system.

This is more of a Squirrel question so I can't offer specific steps, but on Windows, Java desktop apps are generally started by a .bat file or shortcut which include logic to find the location of the JRE. Usually this just checks if the environment variable JAVA_HOME is set, which I believe is also what Caché does. You could try hardcoding the Squirrel .bat file/shortcut to point to a different JRE.

According to the Caché log file, it's looking at the registry to get the version as well.

 

I've "hacked" around the issue by creating two registry import files, one that sets the version of the JRE to 1.8 and the other to 1.6. I import the 1.8 version (regedit /s jre18.reg) in Squirrel's startup batch file just before Squirrel is launched, and the 1.6 version after (regedit /s jre16.reg). It all happens fairly seamlessly, although I did have to insert a short delay in the batch file before the return to version 1.6 using the Win 7 'timeout' (timeout /t 1) command.

Nice recommendation! 

Another SQL external client is DataGrip from JetBrains https://www.jetbrains.com/datagrip/

The connection method is the exactly the same used in Squirrelsql.

The interface and tools are great !

DataGrip is a tool that at least worth a try!

Currently use it, and it's awesome. It also supports connection over ssh.

One of the really cool features of SQuirreL is the ability to have multiple different drivers of the same vendor. E.g. if you need to work with drivers from 2010.* to 2016.* you can have different drivers for each connection. 

In Java, it is a little bit of a challenge to use different versions of the same JDBC driver simultaneously, SQuirreL does that job very conveniently.