More On Scorpio And Apache Derby

Earlier this week I mentioned that ColdFusion Scorpio would be including Apache Derby, and since that post I have been tinkering with the two on and off.
Apache Derby is a Java based open source DBMS, and it is generally used in one of two ways:

  • As a client server DBMS, Derby runs on a variety of Java servers, and multiple clients can connect to it (much like MySQL or SQL Server etc.).
  • As a local engine embedded in another application, allowing that application access to a local data store (kind of like an Access MDB file, although it is actually a set of folders and files as opposed to a single file).

ColdFusion Scorpio comes with two Apache Derby drivers, one for each of the deployment types. The “Apache Derby Client” driver connects to a Derby server, and it needs a host name as well as login credentials. The “Apache Derby Embedded” driver uses a version of Derby that is embedded within ColdFusion itself to access local Derby data stores. Local data stores created and used by ColdFusion are located in a folder named “db” under the ColdFusion root. (The example applications that come with ColdFusion all use local Apache Derby data stores in Scorpio).
So far so good, but then things get tricky. As an embedded database engine, Derby has no client and no UI. Rather, it has APIs and is intended for use by developers within their Java applications. So, if you wanted to create a new Derby data store for use as an embedded Derby database, how would you create it? Creating tables is easy enough, once you have a database you can use CREATE TABLE and so on, but how do you create the database in the first place?
The Apache Derby Embedded driver accepts an optional connection string attribute named “create”, which, if set to “true”, creates the specified data store. So …
To create a new Derby data store, use the “Apache Derby Embedded” driver, name your data source, specify a folder name in the Database Folder field (just provide the folder name, not the full path, and you’ll probably want to keep this the same as the data source name), then click the “Show Advanced Settings” button and in the Connection String field enter “create=true” and then just submit the form. ColdFusion will attempt to connect to Derby, passing “create=true” which creates your new data store (in the ColdFusion “db” folder) which you can then use. You can leave that Connection String in there, if the data store already exists it is just ignored.
(When the final ColdFusion 8 ships we hope to include a Create checkbox, so you’ll not need to mess with connection strings at all).
Now that you have a data store and datasource created you can use CREATE TABLE to create your tables, and get to work using your new database.
Oh, one last thing. If you want to distribute a database for use you’ll want to create it, and then ZIP up the entire data store folder for distribution. Users will then only need to unzip the file in the DB folder and then add the data source in ColdFusion using the “Apache Derby Embedded” and specifying the name of the data store folder.
UPDATED 07/07/2007 with simplified creation instructions.

9 responses to “More On Scorpio And Apache Derby”

  1. barry.b Avatar

    reminds me of years ago where I had to make a proof-of-concept webapp with Windows98, it’s IIS add-on, notepad and an ODBC connection. MSAccess wasn’t installed but the ODBC connection allowed me to create an empty Access97 database. everything else was hand coded with trial-and-error.
    back to the future.

  2. Joshua Avatar

    This opens up some very interesting possibilities. Here is a link to a PDF outlining some performance comparisons of mysql, postgres and Derby.

  3. David Avatar

    Very cool!
    though I assume we shouldn’t name any Derby DSNs "SequeLink Setup" or "slserver54" or "sybase" 🙂

  4. charlie arehart Avatar
    charlie arehart

    Ben, given the complexity of the setup, yet the value of demonstrating to people the option of having an embedded DB, has thought been given to creating a demo DB so that people can indeed just use the simpler driver to refer to it and begin playing, without need to go through the creation step? (Sorry if there’s one there. Just haven’t had a look at this yet, though I’ve been looking forward to the possibilities it opens.)

  5. Ben Forta Avatar
    Ben Forta

    Charlie, we’re going to try to make it a whole lot easier, stay tuned. 😉
    — Ben

  6. Ben Forta Avatar
    Ben Forta

    Good news, we’re going to add a checkbox to the form you fill into create an "Apache Derby Embedded" datasource that will tell CF to create the new empty database, which will get rid of the need for the workaround at the end of this post.
    — Ben

  7. Ben Forta Avatar
    Ben Forta

    I’ve updated this post with simpler creation instructions.
    — Ben

  8. Rob Huddleston Avatar
    Rob Huddleston

    This seems like a great feature. One concern I have from just reading this post (and this is the first I’ve heard or read about this) is that you say that the DB files are all stored in the CF root. Is that correct? The issue that would raise for me is one of file space. If CF is installed on a smallish partition with, say, the OS, figuring that it doesn’t need a lot of space since files are somewhere else and then mapped in and DBs are off on their own server altogether, then that is going to limit the usefulness of Derby. Also, I can see it becoming an issue with web hosts, who may not want thousands of client’s DBs stored on this one drive. I guess what I’m trying to ask here is if the functionality exists to allow the Derby DB to be stored someone other than the CF root.
    It does certainly open the door for developers to write online aps to provide a GUI for Derby development, similar to what PHP developers have done for MySQL.

  9. Ben Forta Avatar
    Ben Forta

    Rob, that is the default location, but you can provide a fully qualified path and point to paths outside of the CF root.
    — Ben

Leave a Reply