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.
I might have a go with this one
"I’m in the process of writing a little more useful Flex 2.0 based tool at work"
I just started using Derby and was thinking of the same thing.
"I’ll post a link to it when I’m done"
can you use any help?
Hi Barry… I did write a simple Flex based front-end that let me query Derby. However, I found that the reason for my writing the tool was to be able to introspect the database and issue queries. And then I found that SQuirrel SQL let me do that nicely so the project fell by the wayside. Try SQuirrel SQL if you haven’t.
Hi Indy… I am new to Flex. I want to learn to use it to access Derby DB but up to now I got a mess in my brain. Like you wrote "a simple Flex based front-end to query Derby"… could you help me?
Thanks in advance.
Gilberto
I’ll just throw out, for those reading this in the future, that Derby is now included in CF8. It’s listed as one of the available Database drivers when setting up a new DSN (both embedded and client), and more important the database engine is embedded within CF8. Yes, it works in all editions.
For those interested in more on this, see the new Derby group (and discussion area) created at http://www.coldfusioncommunity.org/group/derby/.
Once thing not explained very well is WHY we even need Derby. I want to design apps for Android and use Flash Builder 3, but why do I need Derby for that? We run MSSQL and MYSQL, so why do I need yet another db?
This is a six year old post. Since then a huge number of changes have taken place in the database space, including mainstreaming of No-SQL databases, etc.
Why you need another database totally depends on what your needs are. I wanted a standalone app with a built-in database in a J2EE container. Therefore I played with Derby. But you also now have HSQL, SQLLite, etc. available as well. It all depends on what you want to do with it.