Page 2 of 2   « previous page

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.

Page 2 of 2   « previous page