Early releases of Cold Fusion (under Allaire) focused on the Windows market and it was common to include example Access databases. It was common for entry-level developers to take this example to heart and build systems on Access back-ends. One didn't have to install, set up, admin, or most importantly pay for anything. With the beta release of ColdFusion 8, Adobe is providing support for an array of open source databases. Now using PostgreSQL, MySQL, or Apache Derby (formerly IBM Cloudscape) has been made that simpler for CF developers since JDBC drivers for all of them are included. Any of these solutions can be used for the free distribution of databases on either Windows or *nix. The same can't be said of Access. Adobe has made moving to open source more convenient by including Derby in the base install of Scorpio. It will now likely become common for systems to be distributed with Derby databases because of their operating system independence, royalty-free distribution, small footprint, and ease of installation. This article attempts to help early adopters try Derby out with ColdFusion 8.
Terminology
Apache Derby is a pure Java database that supports almost all of the SQL-92 standard. It fits in about 2MB of memory, a nice efficient footprint for memory-constrained environments like mobile applications (or your desktop masquerading as a server). With ColdFusion it runs on J2SE 1.6 and the shipping driver is a native protocol, a pure Java (level 4) driver. There are two flavors of Derby: embedded and network. Simply put, embedded is a direct-connect (you have to run it in the same JVM as the code connecting to it), single-client database while network adds networking and multi-client support.
The Embedded Database
Once you install Scorpio and find your way to the Data Sources section of the Administrator, you'll see four Derby Embedded example databases (and zero Access databases). The example databases are installed in /ColdFusion8/db. (Note: File references in this article assume that ColdFusion is installed at the root of your filesystem. If you're on Windows, the default install path is C:\ColdFusion8 and the references below should be changed accordingly.)
One of these examples is the CFBookClub database. One can query this database in the same way as any other in ColdFusion, using the CFQuery, CFQueryParam, CFStoredProc, and CFProcParam tags. Create a new page called testDatabase.cfm in your webroot and code:
<cfquery name="test" datasource="cfbookclub" >
select books.title
from app.books books
inner join app.authors authors on books.authorid=authors.authorid
where authors.lastname =
<cfqueryparam cfsqltype="cf_sql_varchar" value="Miro" />
</cfquery>
<cfdump var="#test#" />
Run it and you should see CF8's new query dump format which gives you metadata about the query including execution time.
Creating Embedded Databases
To create a new embedded database, enter the directory where you want the database files kept. Do not create a new directory yourself and then browse to it. Select or enter the directory you'd like to contain the database subdirectory. Now write the subdirectory name you'd like after this. For example, Browse to "C:\ColdFusion8\db\" then enter "mytestdatabase" in the entry box. The Database Folder field now reads "C:\ColdFusion8\db\mytestdatabase". (Figure 1)
If you're creating a new Derby database, open the Advanced tab and enter 'create=true' in the Connection String field. If the directory specified already exists and doesn't contain a Derby database, datasource creation will fail writing an error 'XBM0J' to the Derby log file (in /ColdFusion8/logs). (Figure 2)
It's not great interface design to have the file browser come up to help you locate the target directory when the activity fails if the directory already exists and doesn't contain a Derby database. The only time this will work is when you point to a directory that does contain a Derby database. If you want to create a datasource for a Derby database someone has given you, you can point the file browser at the directory that contains it. You must still open the Advanced tab and enter 'create=true' in the Connection String field. (Figure 3)
The ColdFusion server doesn't seem to like to let go of Derby embedded databases once you connect to them. Restarting the CF service is very slow after creating and verifying the new embedded Derby database. This is a bit problematical since it currently appears that the Advanced Options "Maintain connections across client requests," "Timeout," and "Interval" don't work: connections are maintained indefinitely. Restarting the server is the only way to release a connection. You can't have multiple connections open to an embedded database, so you can't use other tools (such as Eclipse or ij, discussed below) to work with it while running your CF application. This is one of many good reasons to think about using Derby Network instead.
Creating Network Databases
Creating a Derby network database is just as easy. First start up the Derby server: "java -jar /ColdFusion8/lib/derbynet.jar -start." Now go to CF Administrator and create a new Derby client datasource. There's currently a bug in this dialog, so in the Advanced/Connection String box, you'll have to start with a semicolon ";create=true" to get it to work. Hit submit and your new database directory will be created in the same directory in which the server started up (/ColdFusion8/lib). There's nothing to say that you have to run the Derby server on the same box as CF, in fact if you want your app to scale across multiple servers it's better not to.
Installing a Derby Network Server
While CF8 ships with the latest Derby (10.2.2), Adobe's not going to notify you when new features or minor patches come out. You'll have to go to Apache to keep up-to-date. (http://db.apache.org/derby/derby_downloads.html.) Download the latest version and unpack it on the server of your choice. Set the JAVA_HOME environment variable to the location of your Java runtime. If you want to use the runtime shipping with the CF server (JavaSE 1.6) to try this out, run 'set JAVA_HOME = [coldfusion install dir]\runtime' (Windows) or 'export JAVA_HOME="[coldfusion install dir]/runtime"' (*nix). (If you're smart and installing this on another machine, you should have downloaded the latest JRE from Sun and installed it there already.) Navigate to the Derby bin subdirectory and run the setNetworkServerCP script, which adds Derby's info to your CLASSPATH environment variable then run the startNetworkServer script. Scripts are included for *nix and Windows. Your Derby server will now tell you that it's listening on port 1527 and is ready to accept connections. When you use the ColdFusion Datasources dialog to tell the Derby server to make new databases (with the create=true directive), Derby will create new subdirectories in its startup directory (/Derby/bin directory) by default. When you want to stop your new server - you guessed it, run stopNetworkServer.
Working with the Network Server Using ij
If you're
really interested in using Derby, you'll probably want to install its
tools. Adobe includes the tools classes in the base install, but
doesn't include the nice scripts that are part of the Derby
distribution and make using the tools that much easier. If you've
downloaded Derby, you can copy the contents of its bin directory into
ColdFusion's. This gives you scripts like sysinfo and dblook that you
can use to get information about your Derby installation and its
databases. Also included is ij, a SQL*Net-like console for
interactively sending SQL commands to either an embedded or network
database.
Navigate to the bin directory where you installed the Derby scripts. Open a command prompt and run setNetworkClientCP. Now run ij. The program will echo its version and give you a new command prompt:
ij Version 10.2
ij>
Now connect to the database you created with the ColdFusion Administrator (named test2 in the screenshot above) by typing this at the command prompt: CONNECT 'jdbc:derby://localhost:1527/test2;create=true'; Ij will return a prompt and you're connected. You can now start creating tables and having lots of fun. To be honest, unless you're used to interacting with your database on the command line, you'd be better off sending your CREATE TABLEs through CFQuery or better yet, using IBM's Eclipse plug-in.
Using IBM Cloudscape Workbench Plug-in for Eclipse
IBM provides an excellent plug-in for working with databases. You can
use it to connect to DB2, Oracle, SQL Server, MySQL, Sybase, Informix,
and oh, yeah, Derby. You can then use a nifty tree-type browser to
navigate the objects in your database. There's no visual query builder
or simple table creation dialog, but if you want to explore a database,
sample table data, or test your queries inside of Eclipse it's not bad.
Get started by visiting IBM DeveloperWorks and downloading the CS
Workbench plug-ins (skip the StandAlone version): www.ibm.com/developerworks/db2/downloads/csworkbench/.
Unpack the csworkbench_plugins_1.0.zip archive into your Eclipse
installation and restart Eclipse if necessary. Go to the Window
menu->Show View->Other... and select Data->Database Explorer.
Now you have a new View open at the bottom of your screen with a folder
labeled Connections. Right-clicking on this let you create a New
Connection. You can give it a whirl with one of the example embedded
databases. You have to tell Eclipse the Database Location and the
location of the Derby driver (which is in derby.jar). (Figure 4)
Remember that once you connect to an embedded database with Eclipse, ColdFusion won't be able to until you Disconnect. If you so much as Verify your Datasource in ColdFusion, you won't be able to connect with Eclipse until you restart the CF server. If you created a network database and have the Derby server running, you can connect to that without worrying about locking the connection. Select Derby Client JDBC Driver from the list in the New Connection dialog instead. The screenshot below shows the Derby Client dialog. It looks like a resource bundle got misplaced in my install, so NO_RESOURCE_FOUND is showing where 'JDBC Driver Class,' 'Class Location,' and 'Connection URL' should appear. In this case 'Class Location' should indicate the location of derbyclient.jar. Even if you didn't specify a password when you created your database, you'll have to specify one here.
Here you can see the database browser contents for the Book Club example database. Right-clicking on a table gives you the option to browse the table data.
Packaging Your Database for Distribution
One of
the prime reasons for using Derby is to make it easy for other
ColdFusion users to install your system. You know the target database
platform exists on all ColdFusion 8 servers, regardless of operating
system. So how do you package your application for deployment on the
machines of your potential clients? Use the Packaging and Deployment
wizard in ColdFusion Administrator. There isn't enough space here to
cover this feature fully, but it's easy enough to figure out. If you
want to package up your CF files, Derby database, and Datasource,
simply add the directory containing your application and the directory
containing your Derby database under "Assoc. Files/Dirs" in the wizard.
Next add the Datasource. Build your Archive and send it out to your
client. They should be able to use the same wizard in reverse. If the
database files are placed in the same location relative to Derby, the
Datasource should continue to work. If the deployer changes the
database deployment path, he will have to update the Datasource to
reflect this change.
Conclusion
Derby is very well documented and you can find stacks of reading material to fill your free time at http://db.apache.org/derby/.
This article is a basic introduction to get you started using Derby as
installed with ColdFusion 8. For more technical details on performance,
SQL standard compatibility, and Derby's Java API, head to Apache. Since
Derby's whole API is exposed in Java, it plays very nicely with CF. You
can do all sorts of crazy things directly to the database - imagine
creating a whole new database (in two lines of CFScript), then
populating it, zipping it up, and sending it to the browser embedded in
an applet. It's not hard to think of a system that creates and packages
offline systems... sounds like a good article.
References
http://db.apache.org/derby/
http://wiki.apache.org/db-derby/SQLvsDerbyFeatures
www.ibm.com/developerworks/db2/downloads/csworkbench/
http://squirrel-sql.sourceforge.net/index.php?page=faq
At press time, ColdFusion 8 was still in beta and the documentation incomplete.