Using SQuirreL

Russell Bateman
13 April 2010
last update: 21 November 2011

Table of Contents

How to set up SQuirreL on Linux
How to set up SQuirreL on Windows
How to launch SQuirreL and set up MySQL
SQuirreL and those pesky commas
Optimization to handle large databases
Where to go from here?
Appendix: Setting up Oracle JDBC
Oracle error codes
Appendix: Setting up PostgreSQL JDBC
Appendix: Setting up MongoDB JDBC
Appendix: Setting up SQLite JDBC

Introduction

This article explains how to set up and use SQuirreL, an SQL viewer and editor. SQuirreL is a solution for any platform since it's written in Java. It is also an excellent browser since it supports nearly every RDBMS in existence, in particular, MySQL, which I use here as my illustration. The advantage of using SQuirreL over the native software that ships with each database is that the interface—and therefore your work habits—can remain the same. This isn't important if you ONLY work with one database. I have worked with 4 or 5 over the last few years and 3 simultaneously when I wrote this how-to.

Note that the more I use SQuirreL, the more I like it. I even use it just a little for MongoDB, though it's too silly to do much with there.

How to set up SQuirreL on Linux

  1. Download SQuirreL installation JAR by clicking on the icon at the top of this page.
    2.  
  2. Run the installation JAR: 
    
    russ@taliesin: ~/Downloads> java -jar squirrel-sql-3.1-install.jar
  3. Follow the installation steps as you normally would. Note that there are many options you'll want to include that depend on how you'll be using it.
    4.  
  4. If you prefer, create a launcher for your desktop or menu pane. The JAR to run is on the path, <installation-path>/squirrel-sql.jar. (There is an "acorn" icon under subfolder icons to use if you like.) Otherwise, you'll launch SQuirreL using the command: 
    
    russ@taliesin:~/dev> java -jar squirrel-sql.jar

Alternate method

If you have trouble because of an error such as the one below, ... 


russ@russ-elite-book:~/bin> java -jar squirrel-sql-3.2.1-install.jar
java.awt.HeadlessException
	at java.awt.GraphicsEnvironment.checkHeadless(GraphicsEnvironment.java:173)
	at java.awt.Window.(Window.java:437)
	at java.awt.Frame.(Frame.java:419)
	...

...then do something like the following instead.

  1. Go to the SQuirreL site.
    2.  
  2. In the left column, look for and click Download and Installation.
    3.  
  3. Look for and click Plain zip format of SQuirreL 3.2.1.
    4.  
  4. Choose the tarball download with a name like squirrel-sql-3.2.1-standard.tar.gz. Since this is a utility, I'm going to download it to ~/bin.
    5.  
  5. Then explode it in place: 
    
russ@russ-elite-book:~/bin> tar -xzf squirrel-sql-3.2.1-standard.tar.gz

    This will leave a subdirectory squirrel-sql-3.2.1-standard. You can then create a launcher using the script file inside:

How to set up SQuirreL on Windows

  1. Download SQuirreL installation JAR by clicking on the icon at the top of this page.
    2.  
  2. Run the installation JAR:
    • If on an earlier version of Windows than 7, go to the download directory and run the downloaded installation JAR: 
          C:\Documents and Settings\russ\My Documents\Downloads> java -jar squirrel-sql-3.1-install.jar
    • If on Windows 7, launch as just noted, but do not attempt to install on C:\Program Files\squirrel-sql-3.1\ as proposed, but on a path that belongs to you (such as C:\Users\russ\dev\SQuirreL\).
     
  3. Follow the installation steps as you normally would. Note that there are many options you'll want to include that depend on how you'll be using it such as MySQL, Oracle, PostgreSQL, etc.
    4.  
  4. Create a shortcut for your desktop and/or start menu. Then edit the shortcut's properties to set the Target: field to C:\Windows\System32\java.exe -jar C:\Users\russ\dev\SQuirreL\squirrel-sql.jar if not already set to that.

    Change the Start in: path if you like. I set mine to the root of my development area (C:\Users\russ\dev).

    There is an "acorn" icon under subfolder icons to use if you like.

    My experience is that this is necessary on Windows 7. I haven't set up SQuirrel on previous versions of Windows since I principally use Linux anyway.

How to launch SQuirreL and set up MySQL

This set up is for MySQL as an illustration.

  1. Launch SQuirreL as implied in the above instructions.
    2.  
  2. Go to Windows -> View Aliases.
    3.  
  3. Click the blue plus sign to add an alias.
    4.  
  4. Name the alias (MySql-dvdcatalog).
    5.  
  5. Change the driver to MySQL Driver.
    6.  
  6. Change the URL to the one being used in your Java program, e.g.: jdbc:mysql://localhost/DvdCatalog
    7.  
  7. Change the username and password.
    8.  
  8. If you choose, click the Auto logon and/or Connect at Startup boxes. That way, it will come right up at launch on the database you're working on.
    9.  
  9. Click Test to see if it works.
    10.  
  10. If your driver is missing, (which it will be)...
    1. Go to Windows -> View Drivers.
    2. Double-click the (MySQL) driver.
    3. Fill out the dialog:
    4. Click OK.
    11.  
  11. Return to Windows -> View Aliases and double-click the alias you've already set up (to use MySQL). You should now see the database particulars (assuming you have a database already set up and working), for example,

SQuirreL and those pesky commas

SQuirreL displays IDs (long integer numbers) using commas which makes them a lot easier to read in terms of understanding how long they are (in groups of 3). However, in practical development situations where you're copying and pasting what's in those columns, it quickly gets old having to remove the commas (which Java, C/C++, etc. find asyntactic). To rid SQuirreL of this, ...

  1. Open File -> Global Preferences.
  2. Click Data Type Control.
  3. Scroll down near bottom to FLOAT, REAL, DOUBLE, NUMERIC.
  4. Click User default format.

If you want this setting to be permanent, choose File -> Save Preferences.

Optimization to handle large databases

When you connect to a database in SQuirreL, it reads and loads the schema into memory. In the case of large databases this can, in theory, take a long time. I haven't felt to complain about it, but you do get a message to the effect that you can optimize this. If you use the offered button to explore it, you learn that you can have it cache the schema. If you do this, and your schema changes, there's an option elsewhere to refresh it.

If you're just working experimentally with a tiny, start-up database for your project, it's not worth worrying about this. I've used SQuirreL on a really big database and I haven't found its speed to be questionable. Still, you can imagine it's very annoying not to see it react to new schema you put in because you didn't refresh it.

I find the alert I have to dismiss about solving this more annoying than any wait I've ever experienced.

Where to go from here?

The base assumption is that you already know how to peruse a database to look at its tables, rows and cells. If this is not the case, you might instead use MySQL's browser and follow the information at MySQL: The Database Browser. Or learn using other databases and database tutorials.

In this case, I just wanted a glimpse inside my database to see its tables as I built them from my application code. Comparing the previous illustration and this one here, you'll see how this is done.

Appendix: Setting up Oracle JDBC

I've had occasion to set up SQuirreL for use with a local, Oracle database. I use the JDBC thin client. I created the alias thus:

(Yours will have an actual name in a couple of places; I've erased my alias name since I aligned it with my commercial database and its identity is no one's business, but my own.)

Of course, when I went to set up the driver, it wasn't there. The driver's name was correctly guessed by SQuirreL, but I needed to go to the Oracle site to download the driver.

(Note: To do this, you have to be signed up with Oracle.)

I went to Oracle's Software Downloads page, then I...

  1. clicked Database EE and XE on the right,
  2. down under More Database Downloads, I clicked on JDBC Drivers
  3. to find Oracle Database 10g Release 2; I clicked that.
  4. Then, I accepted the license agreement and scrolled down to Oracle Database 10g Release 2 (10.2.0.1.0) JDBC Drivers—what I needed.
  5. I chose ojdbc14.jar to download because I wanted Java 1.4/1.5 or later support (and didn't care, for now, about whether it was debuggable or not because I'm only using it for SQuirreL and not in my product).

I downloaded this driver (JAR) to /home/russ/dev/downloads/oracle-jdbc-driver. Then, I clicked on the Extra Class Path tab, clicked the Add button, and navigated to my new driver, to add it. When I clicked OK, SQuirreL was very happy and life went on just as if I were using the kinder, gentler database (MySQL).

Oracle error codes

If you see error codes, you can visit Oracle Error Codes for a handy look-up and explanation of them.

Appendix: Setting up PostgreSQL JDBC

By now, you can figure this out; I followed the MySQL instructions above and it worked right off. Here's what I used to look at a local database named "acme". 


URL:           jdbc:postgresql://localhost[:5432]/acme
JDBC driver:   postgresql-9.1-901.jdbc4.jar
Driver class:  org.postgresql.Driver

Appendix: Setting up MongoDB JDBC

Seems bizarre, but it's useful for a quick look, easier sometimes than the MongoDB command-line shell. Incidentally, you don't need a username and password for the connection (if that doesn't make sense—and it usually doesn't). 


URL:           jdbc:mongodb://localhost[:27017]/any-database-name
JDBC driver:   mongojdbcdriver-0.0.2.jar
Driver class:  net.sf.mongodb_jdbc.MongoDbDriver

Illustrations:

Appendix: Setting up SQLite JDBC

SQuirreL (still in 2015) doesn't support SQLite natively. Here's how to do that:

Install and launch SQuirreL. Set up the SQLite driver by downloading it.

Set up the driver

  1. Download SQLite JDBC driver from here.
  2. Click the Drivers tab.
  3. Click the blue + sign.
  4. Enter Name: SQLite.
  5. Click New to get the Add Driver dialog.
  6. Enter Example URL: something like: jdbc:sqlite://<hostname>[:port]/<db>[;<attribute>=<value>]. This is just a reminder when creating the alias what the URL/path should look like.
  7. Though unnecessary, enter Website URL: as the place where you (may have) got the driver: https://bitbucket.org/xerial/sqlite-jdbc/downloads.
  8. Click on Extra Class Path because SQLite isn't supported.
  9. Click Add, navigate to, select and Open the JDBC driver you downloaded in the first step.
  10. Fill in Class Name: with org.sqlite.JDBC.
  11. Click OK.

Set up an alias to your database

Armed with a fresh SQLite driver for SQuirreL, set up to browse your database.

  1. Click on the Aliases tab.
  2. Type Name: as whatever you like to remind you of the database you're going to browse.
  3. Select Driver: as the SQLite driver you just set up.
  4. In URL:, type in the URL or filesystem path. What's in there now is the Example URL you typed in when you created the driver. This is a hint. You'll actually type something like this filesystem path I'm using right now as I set this up, jdbc:sqlite:/home/russ/sandboxes/scorecard-extension.v60_release.dev/run/data/db/appliance.db
  5. Enter your User Name:
  6. Enter your Password:
  7. If you want to start SQuirreL next time with the browser already launched on your database, click Auto Logon and Connect at Startup.
  8. Click OK.
  9. The next time you start SQuirreL, unless it's already auto-launched, simply double-click your alias to get browsing again.