Exploring Your DB With Eclipse 101 (Including Derby DBs!)

I started looking into the Apache Derby Embedded database late last week as an alternative for future open source projects. The first major issue I had with them was the lack of a GUI tool for managing my schema. I feel pretty comfortable with SQL but I still like having a query browser/server administrator that lets me quickly edit/add tables and columns with a graphical user interface.

At first I downloaded and tried SQuirreL since several folks had commented that that is what they use to manage a Derby DB. I personally was left a little disappointed. I decided to look a little further when I discovered the Eclipse Data Tools Platform (DTP) project. This project is still a bit new, but it seems to be a very good start for working with Derby DBs.

Here is a quick tool for getting started with DTP. First you'll have to download the plugin and install it. Please notice there are some other dependencies listed at the top of the download for each version! You must have those other plugins for this to work.

Once you've installed the plugins and restarted Eclipse you're ready to set up DTP to access a Derby DB.

First select Window -- Preferences -- Connectivity -- Driver Definitions. Within the Driver Definitions dialog scroll down to Derby, select the proper version and click 'Add':

At the next dialog scroll down and select 'Derby Embedded JDBC Driver' and choose OK.

Now you need to point DTP to the Derby JDBC driver. You'll find the driver that ships with CF8 under lib/. Your location will vary but it will be something like:

• C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\lib\derby.jar
or
• C:\ColdFusion8\lib\derby.jar

First clear the existing driver (click 'Clear') then click 'Add' and navigate to the proper driver.

You're now ready to point DTP at an existing Derby DB - or use DTP to connect to and create a new Derby DB. First make sure you are in the Database Development perspective (not CFEclipse!) and then right click on Database and select 'New'.

Select Derby Embedded Database and choose Next.

Give your connection a name (and a description if you want) and click Next.

At the next screen give your database a name and choose a location where it will be stored. Note - if you select a location such as your desktop make sure you enter a folder name at the end of the path or your DB will not be created!

Check the Create Database (if required) button (if you are creating a new db that is). Enter login/password (write these down, you'll need them when setting up your CF DSN). Enter any other optional parameters and click Next/Finish. Your connection is now created.

Now right click your new connection and click Connect.

You can now navigate your schema via the Database Explorer.

My next post will show how to use the Database Explorer to create a table and how to set up your ColdFusion DSN to point to this new Derby DB.

Comments (15) | Print | Send | del.icio.us | Linking Blogs | 7357 Views