Browsing Apache Derby databases

It can be fun to run complex queries on databases by command-line tools, but sometimes the beckon of a graphical aid is just too strong. Since migrating the tXtFL leagues/teams/players/etc from a convoluted structure of individual files to a simple database, I've enjoyed using the OpenOffice.org/LibreOffice Base application for browsing data. I use LibreOffice anyway for much of my office needs, and setup for database grazing was a mere 11 steps.

There were a few shortcomings, however, that propelled me to explore a few database browsing alternatives. LibreOffice isn't the lightest of programs, both in terms of disk and RAM consumption as well as startup time, so taking a quick peek at the data can take longer than expected. Moreover, LibreOffice locks the database until the entire office suite is closed, preventing me from accessing the db from the tXtFL app until I've closed LibreOffice--including any other LibreOffice apps still open.

So what are the alternatives? Browsing the Wikipedia database tool comparison table, I found a number of open-source alternatives that support JDBC connections, which are required for connecting to the Apache Derby database that my app uses. My goal was to test out the ease of setup and connection to the database both via embedded and network modes. In embedded mode, only one program can access the database at a time, whereas in network (client/server) mode, my app hosts the db for a concurrent connection from the db browser.

As setup wasn't always completely straightforward for each of the apps, I've outlined the steps below using the scenario of connecting to the tXtFL database.

Getting the database driver (Apache Derby)

For all of these programs, I ended up needing to manually add the database drivers. You can pick them up from the Apache Derby download page. I used the latest current release (10.9.1.0), and the lib or bin packages should be fine. All you'll need is the derby.jar file, plus the derbyclient.jar file if you're connecting to a Derby db in network mode.

LibreOffice

If you already use LibreOffice for its excellent word processor and spreadsheet capabilities, why not give it a shot for database management? I've previously described the process but wanted to update it hear in a more streamlined fashion.

1. In Tools > Options > Java, click on Class Path > Add Archive. Browse to and select the derby.jar file you downloaded from the Derby website, then click OK.
2. In File > New > Database, choose "Connect to existing database," select JDBC, and press Next.
3. For the Datasource URL, choose your path, such as derby:C:\Users\[username]\.txtfl3\FootballDB for the tXtFL db.
4. For the JDBC driver class, enter org.apache.derby.jdbc.EmbeddedDriver.
5. Enter your username/password (or skip it for tXtFL) and save the file.
6. Click on Tables to start browsing your data!

For connecting to Derby in networking mode, you can repeat the same steps, with the following adjustments:

• Add the derbyclient.jar file to the Class Path list.
• Point the Datasource URL to derby://localhost:1527/C:/Users/[username]/.txtfl3/FootballDB (or whatever your server address is).
• If you're running tXtFL, make sure that in the tXtFL Options pane, "Open database as server to allow multiple connections" is checked and that the program is running!

Unfortunately I cannot post a current screenshot of the database browser in action as it failed to load. I have Java 64-bit installed, and apparently 32-bit Java is required for LibreOffice on Windows. Another quirk is that when scrolling data, the table only advances line-by-line, which can be slow. Pressing the arrow to advance to the end of the table and then backing up to the line of interest can speed the process. Nonetheless, LibreOffice has served my db browsing very well for several years, and I will certainly be watching for continued updates to the suite.

SquirrelSQL

I learned about SquirrelSQL from Apache Derby's own website. At about 35 MB, the app weighs in at a much more economical size than LibreOffice does while still providing all of the functionality as a db browser. And although startup time wasn't any snappier, a patch from way back in 2007 ensured that disconnecting from the database released the lock, without having to shut down the entire program. That means that you can flip back and forth with ease between viewing your db and running your program that depends upon it. Of all the db browsers I tried, only this program appeared to employ the Derby command to fully shutdown the database without shutting down the program.

1. When installing the program, I used the squirrel-sql-x.y.z-install.jar installer. Make sure to choose the Derby plugin during installation, as apparently this plugin brings the crucial patch to disconnect the db completely.
2. In the Aliases pane, press "+" to add a new alias.
3. Press New to add the Derby driver. In the Extra Class Path tab, press Add to pull derby.jar into the list, and then press List Drivers to automatically load in the drivers. I tried using the supplied derby.jar from the program's plugins folder but could not get it to load successfully.
4. For the URL, use the same paths as above or wherever you've stored your own db.
5. Press OK to open the db.
6. In the tree, open TXTFL > TABLE to find all the tXtFL tables.
7. Wait, what happened to all the entries after 100? In File > New Session Properties, open the Object Tree tab and uncheck the "Contents - Limit rows" box to see your complete tables.

Again, you can repeat the steps but with derbyclient.jar and the network URL address to connect to the db in network mode. Note that for some reason, a username/password must be supplied even if your db does not have or use one. Fortunately, I simply supplied a single letter for both fields (eg "a" for username and password) and connected without a hitch.

DBEdit2

I loved using DBEdit2 only because it's so pleasantly minimalistic. It's but a fraction of the size of LibreOffice, starts up happily snappily, and does away with all but the bare necessities in its interface.

1. Before opening the program, drop derby.jar into the DBEdit2 program folder (eg C:\Program Files (x86)\DBEdit 2), replacing the older version (10.7) that's there.
2. In Connections, choose Add, then Derby.
3. As far as I can tell, the Input dialog box doesn't actually create a file in that location and may actually just be for naming your new connection.
4. In the Connection dialog, add the URL as above. For network connections, the username/password can be filled as for SquirrelSQL (eg "a" for each field).
5. Once you've made your connection, you're presented with a totally blank screen. Fear not! To browse your data, you can directly input sql commands. As an extra aid, the Help menu helpfully includes an "Insert 'select * from'" shortcut to do just that for you in the sql text area, and then you can click on the icon on the far upper right to browse your db schema and double click on tables to drop their name into the sql command. When you're ready to roll, press the blue "Play" icon to run the command. Voila!

Clearly this option may not be for all, but I do plan to experiment further with it. Reminds me a bit of the beloved SQLiteBrowser, or an even simpler version of it.

Orbada

Perhaps the king of the pack in terms of full-on complexity is Orbada. It's on par with SquirrelSQL in size and startup time as well as walloping with features and a complex interface, but with an extra splash of color to liven things up a bit and a flurry of additional wizards to make you feel as if setup is easier.

1. In the Program > New connections... dialog, press Drivers, then New.
2. Name the driver as you like, and then browse to your derby.jar file using the button next to the Source of driver drop-down. Press the refresh button next to the Class drop-down to pick up the autoloaded driver. Press OK, then Close.
3. Back in the connections dialog, press New to define your connection.
4. Name it as you like (regardless of the prompting), then choose your derby driver.
5. The series of text fields are supposedly supposed to walk you through the URL syntax, but I found it easier just to copy and paste in the URL from above into the Connection string field. Note that username/password is not required, even for networked connections, in this app.
6. Once you've connected, you can write sql commands as in DBEdit2, or you can be lazy and go to View > Browse tables (JDBC) to view them in all their colorful glory.

As with LibreOffice and DBEdit2, the entire program must be shut down when connected to an embedded db to free it for access from another program. In network mode, the database can be disconnected without shutting down the program, but make sure to commit (Connection > Commit changes) prior to disconnecting.

So there you have it, four free alternatives to view your Apache Derby database (or many others). I had been hoping to find a one-download, one-click option, but instead I found 4 options that required at least 5-6 steps. Well I suppose that if the steps here reduce the number of steps to manually query and debug the database, the investment may be worthwhile. Certainly the price of the software was.