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.
Leave a Reply