Using Apache Derby - The built-in CF8 Database
Scott Stroz

Who am I?
Senior Software Architect for Alagad.
Web developer for over 7 years.
ColdFusion Developer since ColdFusion 5.
Adobe Community Expert
Author of Flogr
A Flex based tool for viewing ColdFusion log files.

What Will We Cover?
What is Derby?
Advantages of Derby
Advantages of Using Derby With ColdFusion
When Would You Use Derby?
Creating an Embedded Derby Database.
Creating a Network Derby Database.

What is Derby?
Derby is a sub-project of the Apache DB Project.
The DB Project is charged with the creation and maintenance of commercial-quality, open-source, database solutions for distribution at no charge to the public.
Derby is an open source relational database implemented entirely in Java and available under the Apache License, Version 2.0.

What is Derby?
There are 2 different types of Derby databases:
Embedded - A direct-connect (you have to run it in the same JVM as the code connecting to it), single-client database.
Network (client) - adds networking and multi-client support.
With embedded databases, only one client can connect to database at any one time.

Advantages of Derby
Derby has a small footprint
About 2 megabytes for the base engine and embedded JDBC driver.
Derby is based on the Java, JDBC, and SQL standards.
Derby provides an embedded JDBC driver that lets you embed Derby in any Java-based solution.
Derby is easy to install, deploy, and use.
Databases are easily transferable to other systems.

Advantages of Using Derby with ColdFusion
No installation of Derby necessary.
Derby is installed when you install ColdFusion 8.
No need to install a DBMS
No need to do a restore or import
Simply set up a data source pointing to the database and off you go.
Changes made to data or schema are automatically updated
Guaranteed compatibility regardless of operating system.

When Would You Use Derby?
To provide code examples where database access is necessary.
Such as in blog posts, online articles, presentations.
Prototyping applications
In some cases it might be more efficient to create a Derby database for prototyping rather than creating a process to stub data.
This is NOT to say Derby is for development only.
Product development
You would not need to develop for more than one RDBMS.
Could avoid customers having to use a RDBMS they normally would not.

Creating an Embedded Derby Database
There are several ways you can create a Derby database to use with ColdFusion
Using ColdFusion Administrator
Creating a Derby data source allows you the option to create the database.
Pros:
You have to create a data source anyway.
Quick, easy, familiar.
Cons:
You still need to create the database schema.
Using an IDE
Using CF code (blech)

Creating an Embedded Derby Database
DerbyCFC
Developed by Sam Farmer to allow for easy creation and basic manangent of Derby databases
Pros:
No need to worry about using CF Administrator.
Can be done programmatically
Quick, easy and familiar
Cons:
You still need to create the database schema.
Using an IDE
Using CF code (blech)

Creating an Embedded Derby Database
Using and “IDE”
Squirrel
Data Tools Plugins for Eclipse
Aqua-Data Studio
Using Generic JDBC connection
RazorSQL (my favorite)
Pros:
You can create the database and database schema using tools similar to other database management.
You can create the database in your project root and point to it when creating data source in CF Admin.
Cons:
You still need to create the data source in CF Administrator
Some of the “IDE”s are expensive.
Configuring connections (or whatever the product calls them) could be confusing if you are not familiar with JDBC connections.

Creating a Network Derby Database
First, you will need to start the Derby “server”
You can run the derbynet.jar that comes with ColdFusion using
java -jar /ColdFusion8/lib/derbynet.jar start
You can download, “install” and run Derby.

Creating a Network Derby Database
Using CF Administrator, create a data source of type ‘Apache Derby Client’.
Add “;create=true” to the ‘Connection String’ box under ‘Advanced Settings’
A new folder will be created under the ‘bin’ directory where you started Derby.
You can now connect to your DB using CF and an IDE at the same time.

Creating a Network Derby Database
You can easily copy the files created with a network database to an embedded database.
This make development a bit easier as you can modify database as needed without CF interfering.
This will make it easier to deploy your application without having to worry about setting up a Derby server.

Summary – What we learned.
Apache Derby is a Java based database
Derby can be run wit ColdFusion as an embedded database or a network database.
When you might consider using a Derby database.
How to create and manage embedded and network databases.
You can move the data files between an embedded and network database.

Questions?

Resources
Apache Derby - http://db.apache.org/derby/
Documentation - http://db.apache.org/derby/docs/10.2/ref/
DerbyCFC - http://derbycfc.riaforge.org/