Table of Contents
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.
russ@taliesin: ~/Downloads> java -jar squirrel-sql-3.1-install.jar
russ@taliesin:~/dev> java -jar squirrel-sql.jar
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.
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:
C:\Documents and Settings\russ\My Documents\Downloads> java -jar squirrel-sql-3.1-install.jar
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.
This set up is for MySQL as an illustration.
Windows -> View Aliases.
How did I know this? Because when I wrote my first sample SQL application years ago and learned how to use MySQL's JDBC connector in Java code, this was the class I used. (If I stumble upon more cogent advice for this step, I promise to come back and add it.)
Windows -> View Aliasesand 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 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, ...
If you want this setting to be permanent, choose File -> Save Preferences.
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.
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.
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...
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).
If you see error codes, you can visit Oracle Error Codes for a handy look-up and explanation of them.
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
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