I recently re-discovered Apache Derby, a Java-based database that seems like a promising alternative to MySQL. I downloaded it a few months back but for some reason or the other never persisted with it. Anyway, this time around there were other factors so I went through the motions of understanding how it works and setting it up.
My main goal was to get Derby to work with ColdFusionMX. And after a few minutes of looking at the documentation it was fairly easy to figure out what I needed to do. Derby can be installed in two environments:
- Client/Server: where it runs like any other RDBMS like MySQL Server, MS SQL Server or Oracle. External applications/clients like ColdFusion applications can connect using JDBC/ODBC drivers (or any other mechanism). When Derby runs in client/server environment, multiple applications can access the Derby server over network
- Embedded: Only a single application can access the database at a time. The reason why it is called “Embedded” is because Derby starts within the instance of an application in which it is embedded. Network access is not available when Derby runs in embedded enviornment.
I chose the embedded option. It seemed the easier of the two, seemed to satisfy the reasons why I wanted to use it (as an alternative to a MySQL database for a sample application I was working on).
And easy it was.
After downloading zip file for the latest release (10.1.3.1), unzipping it in a folder, I copied lib/derby.jar into the /WEB-INF/lib directory of the ColdFusionMX server instance. I then restarted the ColdFusionMX server, and theoretically the Derby database server should now be “embedded” in the ColdFusionMX instance.
To test that I logged into ColdFusionMX administrator, and setup a datasource with the following details:
Datasource Name : derbyDemo
JDBC URL: jdbc:derby:derbyDemo;create=true
Driver Class: org.apache.derby.jdbc.EmbeddedDriver
Driver Name: Derby Database Driver
Username: [left this blank]
Password: [left this blank]
Notice the “create=true” attribute passed as part of the JDBC url. That tells the embedded Derby database server to create the database in case it doesn’t exist. Upon clicking submit (and waiting for a few seconds), ColdFusionMX administrator informed that the datasource was successfully setup. Voila!
The next step was to create some tables, add some data and query the database. As Derby does not ship with a visual client tool, I wrote a 1-minute Derby Query Manager 🙂
<html><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><title>1-Minute Derby Query Manager</title></head>
<body>
<cfparam name="form.sql" default="">
<cfoutput><h2>Query:</h2><form action="index.cfm" method="post"> <textarea name="sql" cols="60" rows="10">#form.sql#</textarea> <br/> <input type="submit" name="btnQuery" value="Query!"/></form></cfoutput>
<cfif len(trim(form.sql))> <h2>Results:</h2> <cfset myquery = queryNew("")> <cftry> <cfquery name="myquery" datasource="derbydemo"> #preserveSingleQuotes(form.sql)# </cfquery> <cfcatch type="database"> <cfdump var="#cfcatch#"> </cfcatch> </cftry> <cfdump var="#myquery#"></cfif>
</body></html>
Using this, I issued the following three queries to create a database, populate it and query it.
<!--- Create a table called products ---><cfquery name="qCreate" datasource="derbyDemo"> CREATE TABLE products ( itemNumber INT NOT NULL, price DECIMAL(5, 2), stockDate DATE, description VARCHAR(128) )</cfquery>
<!--- Insert some dummy values ---><cfquery name="qInsert" datasource="derbyDemo"> INSERT INTO products(itemNumber, price, stockDate, description) VALUES (4, 29.95, '2006-02-10', 'Male bathing suit, blue'), (5, 49.95, '2006-02-20', 'Female bathing suit, one piece, aqua'), (6, 9.95, '2006-01-15', 'Child sand toy set'), (7, 24.95, '2005-12-20', 'White beach towel'), (8, 32.95, '2005-12-22', 'Blue-striped beach towel'), (9, 12.95, '2006-03-12', 'Flip-flop'), (10, 34.95, '2006-01-24', 'Open-toed sandal')</cfquery>
<!--- Query the table and dump results ---><cfquery name="qGet" datasource="derbyDemo"> Select * From products</cfquery>
<cfdump var="#qGet#" label="Derby Database Test Results">
All worked as expected. The first query created a table, the second populated it, and the third queried it and retrieved records. Wonderful! I love it when technology works as one expects.
If you want to a slightly more robust tool, Derby ships with a command-line, interactive tool called, ij (in the frameworks/embedded/bin/ directory). There is also a third-party application called Squirrel SQL Client, a Java-based UI to work with various database platforms, including Apache Derby.
I’m in the process of writing a little more useful Flex 2.0 based tool at work. I’ll post a link to it when I’m done.
All in all, it was very easy to get Apache Derby to work with ColdFusionMX. Once I have a decent Flex-based tool, I’m quite sure I’ll be using much more of Derby in the projects I work on.