Open source software security

Using Squirrel SQL to connect to your MySQL database

30 November -0001
October 13, 2003

Well, my quest to find a good MySQL gui eventually led me to explore the possibilities of using Squirrel SQL client. I've used Squirrel to connect to an Oracle database (after far too much frustration with SQL+, which is the command line tool distributed with Oracle). Small bitch: Oracle costs tens of thousands of dollars, and its beyond me why they distribute such a piece of crab CLI (command line interpreter) connection program. SQL+ doesn't even have command memory, making it less primitive in many ways to MS-DOS. Anyways, back to the topic at hand. Squirrel is an open source project to provide a near universal database management GUI tool. Because its based on Java, Squirrel can run almost anywhere and connect to every database that I've ever used. Of course, because its based on Java, installation isn't all that easy :) and I didn't find any good documentation so I decided to write some up real quick (using AbiWord of course, to stick with the whole Linux theme). Ok, so first step is to install Java. Now, I know that sounds daunting, but its really not nearly as difficult as it used to be. Navigate over to http://java.sun.com and grab the latest release of the Java 2 SDK (development kit) from the J2SE (Java 2 standard edition) which has all the advanced tools you'll need. Don't get the JRE (which is the runtime environment) or J2EE (which is the Enterprise Environment and entirely overkill for what we need). Now, on a Linux platform you're going to want to download the RPM version in self extracting format. This should be a *rpm.bin looking file. Download it to wherever you want it and extract it using './thename.rpm.bin'. Then you'll have to change to root using 'su' and install the rpm with 'rpm -ivh *.rpm'. Once you do this you can locate your new java installation in '/usr/java'. The next thing to do (and this is always a kicker) is to add the java executable to your $PATH environmental variable. To do this you simply need to edit your .bash_profile in your home directory to include the following line:

PATH=$PATH:/usr/java/j2sdk1.4.2_01/bin

Note that your sdk version might be different from mine. You basically want to add the directory where you find your 'java' and 'javac' executables. When you log back in you can check to see if the $PATH variable got updated correctly by opening a command line and typing in 'javac -v' which should throw an error about a bad flag. If you get a 'command not found' error or something else then your computer isn't finding the java program where its looking for it and you've got to do some more updates. I'm going to assume everything is working fine from this point though. Next step is to download Squirrel SQL from their website at: http://squirrel-sql.sourceforge.net. You're going to want to download the latest stable .jar (java archive, a-la tar (tape archive)). At the time of this writing you'll be looking for squirrel-sql-1.1final1-install.jar. Just download this file and execute it using Java by typing 'java -jar squirrel-sql-1.1final1-install.jar' and the installer should fire up and guide you through the rest of the installation.

Now that you've got Squirrel installed you might want to create a shortcut to it on your desktop. In KDE you need to right click on the desktop an select 'New Link to Application', then in properties set the executable to whatever directory Squirrel installed to (in my case it was '/home/jkeane/SQuirreL SQL Client/squirrel-sql.sh'). Now clicking on the link will fire up Squirrel SQL. This will give you a rather bland looking list of connectors (with red x's next to them meaning they aren't functioning) and a blank list of connections. Its not very inspiring and pretty daunting to see everything that isn't working with Squirrel when you first fire it up, but have no fear. Your next step is to install the right Java drivers so that Squirrel can connect to MySQL. Its actually easier than you might think. Simply download the drivers from MySQL.com (http://www.mysql.com/downloads/api-jdbc.html). After you download the appropriate driver you have to unpack and untar it, which should leave you with a full directory structure and the driver file you want: mysql-connector-java-3.0.9-stable-bin.jar (your version may be different). To install this you have to copy this file into your Java working directory's /ext directory, in my case this was: /usr/java/j2sdk1.4.2_01/jre/lib/ext. Then fire up Squirrel and you should see a check box next to MySQL and you're good to go.

SQuirreL in Action<

One snafu I notice in my distribution was that Squirrel tries by default to connect to MySQL over port 3360 instead of the default port 3306, so be sure to fix this when adding a new alias.