Bob's Blog

Today I had a need to connect to and mess around with one of the Derby databases that ships with ColdFusion. I don't have RDS set up, plus I'm fairly certain that RDS does not allow you to modify data in tables, so I needed a solution. I had no idea where to start, so of course I simply Googled "derby database client tools" and one of the first links I saw was an article entitled Using SQuirreL SQL Client with Derby, by Susan Cline. I recalled that SQuirreLSQL is a client that runs on OS X, so I clicked.

Boy was I glad that I did. The article covers just about everything you need to know to get up and running with Derby on OS X, and a whole lot more, and I highly recommend reading it. If you don't feel like seeing the details, I've included a simple step-by-step guide below:

1. Install SQuirreLSQL
   1. Download SQuirreLSQL from the downloads page. I clicked the link labelled Install jars (and source) of SQuirreL 3.1 for MacOS X.
   2. Open up a terminal window and change to the folder where the downloaded jar file is located.
   3. Run the install package by typing the following command:

      java -jar squirrel-sql-3.1-MacOSX-install.jar

      where squirrel-sql-3.1-MacOSX-install.jar is the name of the file you downloaded.
   4. Tell the installer where to put the app file (I chose /Applications/SQuirreLSQL.app), and choose the plugins to install. I chose a whole bunch of plugins as I wanted to see what they do, but make sure you choose the Derby plugin.
2. Configure SQuirreLSQL to Use Derby
   1. Start SQuirreLSQL by running the app file that you just installed.
   2. Click the Drivers tab.
   3. You should see Apache Derby Embedded as one of the drivers listed. If it has a blue check mark beside it then SQuirreLSQL is ready to access Derby databases, and you can continue with Configuring SQuirreLSQL to Use Your Database, below. On the other hand, if, like me, you see a red x beside it, then you need to continue with the following steps.
   4. Select Apache Derby Embedded in the list and click the pencil icon, which allows you to edit the driver.
   5. Click the Extra Class Path tab and then click the Add button.
   6. Browse to a folder that contains the derby.jar file. It should be located in the /lib folder of your ColdFusion server. For example, on my machine it's in /Developer/CF9/servers/cfusion/cfusion-ear/cfusion-war/WEB-INF/cfusion/lib. Select the derby.jar file and click the Choose button.
   7. Now click the List Drivers button which should populate the Class Name select box.
   8. Select org.apache.derby.jdbc.EmbeddedDriver from the Class Name select box, and click the OK button.
   9. You should be returned to the main SQuirreLSQL window and see the message

      Driver class org.apache.derby.jdbc.EmbeddedDriver successfully registered for driver definition: Apache Derby Embedded

      at the bottom of the screen. The Apache Derby Embedded driver should now have a blue check mark beside it.
3. Configuring SQuirreLSQL to Use Your Database
   1. Click on the Aliases tab.
   2. Click the blue + symbol to add an alias.
   3. Give your alias a name. I chose the name of the database, so I put cfartgallery into the Name text box.
   4. Choose Apache Derby Embedded from the Driver select box.
   5. In the URL text box, replace the text <database> with the location of your database. I went to the datasource information in the ColdFusion Administrator and copied the contents of the Database Folder text box from there. The value I used was

      /Developer/CF9/servers/cfusion/cfusion-ear/cfusion-war/WEB-INF/cfusion/db/artgallery

   6. Check the Auto logon check box.
   7. Click the Test button. You should see a dialog pop up saying "Connection successful".
   8. Click the OK button. You should now be able to connect to the cfartgallery database!

Once again I just want to point out that all of this is covered by Susan Cline in her excellent guide. If by any chance she ever reads this, I'd like to thank her for this wonderful and well written resource.