Exploring Your DB With Eclipse 101 (Including Derby DBs!)
Posted By : todd sharp Posted At : November 19, 2007 8:51 AM Posted In: Eclipse, Apache Derby
15
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.
My problem with it was there is no GUI table editor (at least that I could find).
However, if you've got the latest DTP version (1.5.1) it does state that Eclipse 3.3.1 is a requirement.
http://www.eclipse.org/datatools/
It just doesn't show the derby libraries you show in your screen grabs.
http://www.eclipse.org/datatools/downloads.html
so that's what I did. But I mentioned being on 3.2.2 in case it might be a clue for anyone.
I might have upgraded to 3.3, but hearing John say he's there and has the same problem makes me wonder if that will really solve the problem. This is indeed curious that we're not seeing Derby listed where you did. Must be some difference somewhere. Perhaps others will chime in.
\eclipse\plugins\org.eclipse.datatools.source_1.5.1.200709251\src
And see if you see anything that mentions Derby?
Something like:
org.eclipse.datatools.connectivity.derby...
I'm guessing that is where the definitions are stored.
Oh...wait...I just remembered - Scott Stroz was having problems with this install too (we talked on IM) and he did an update through Eclipse and it fixed things for him. I don't remember if he was having this *exact* problem but give that a try first I'd say...
Help - Software Updates - Find and Install - check everything and update...
But yes, there are derby-based jars in the plug-ins dir, such as:
org.eclipse.datatools.connectivity.db.derby_1.0.0.200706071.jar
org.eclipse.datatools.connectivity.derby_1.0.0.200706071.jar
and a few others. That's why it surprised me that it's not showing up.
Perhaps using the source option would be different, and may well be the solution (but it sure seems one shouldn't expect that to make a difference, right?)
On a side note, which may also be worth blogging about, did you know that you can set up Aqua Data Studios for Derby? Right now it is just "Generic JDBC Support" - but it's something...
I hear they are working on adding Derby in a future release (which would give things like table editor, etc). Oh - and they give a free license to OS developers...
http://carehart.org/resourcelists/derby_for_CFers/...
If people here haven't seen it, there's a lot there on using Derby as a CFer.
I also list it on my tools resource page:
http://carehart.org/resourcelists/tools_to_conside...
I've been a fan of Aquadata for a while, and some of the others I've listed on these pages.
That said, Todd, you showed using the DPT tool to connect to a Derby DB that you were creating from within that tool. Did you (or anyone here) ever try connecting from that tool to a DB that was configured as an Embedded Derby DSN in a running CF engine? I'd think you'd be unable to, and would need to switch to using the Apache Derby Client approach and running the Derby Network Server, to permit multiple clients to access the DB. (Lest anyone confuse things, this single client limitation of the embedded derby driver is not about multiple CF users, but rather just multiple Java clients--CF and some query tool--both accessing the DB at the same time.)
This is all something that trips people up frequently. I've updated the "getting started" resource I listed above, to discuss these and many other details: http://www.carehart.org/resourcelists/derby_for_CF...