Installing Apache Derby Database on ColdFusionMX 7.0.2

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.

CFC 101: Presentation to CFUG Auckland

I made a presentation on CFCs to CFUG Auckland a few weeks back. Most of the CFUG users already had some experience with CFCs.

The presentation was fairly short. But the discussion was lively. A need was expressed to understand CFC best practices as applied to particular problems. There was also an element of sounding off experiences. And that made it quite interesting.

I’ve attached a copy of the presentation and the sample code used.

Fixing error: The XML-RPC service is not available

Late Friday evening, I was developing a small application on my laptop that looped over a list of webservice URLs and fired off a given function on each of the webservices to retrieve some information. The application worked beautifully and life was good!

But on Monday morning, I restarted the CFMX 7 Server, and found that I couldn’t log into to CF Administrator. I was getting the following error:

The XML-RPC service is not available.
This exception is usually caused by service startup failure. Please check your server configuration.

The error occurred in Application.cfm: line 82
-1 : Unable to display error’s location in a CFML template.

This is definitely not an error that you want to be staring at first thing on Monday morning. Here is how I fixed it.Mr Google brought up another blog that mentioned the same error. Apparantly a missing neo-xmlrpc.xml on a CFMX 7 server can cause this.

So, I opened \WEB-INF\cfusion\lib\ directory where the file should exist. And it was there. So it wasn’t missing in my case. But date modified value pointed that it had been changed on Friday.

Opened the file an XML editor. Noticed that it contained a WDDX packet containing links to the webservices I was accessing using my application. Interesting. So next, I tried to validate whether the XML doc was well formed or not. And it turned out that it wasn’t. CF Server had added characters into the XML that it didn’t like.

It contained nodes like:

<var name=’http://remotesite.co.nz<;char code=’0d’/>/folder/webservice.cfc?wsdl’>

Notice that there is an extraneous:

<char code=’0d’/>

This is what was breaking the xml. Interestingly, the character code ‘0d’ is equivalent to carriage return.

Once I removed all such references and the XML validated as well-formed, life was back to normal!

It would be interesting to figure out why the CFMX server inserted these characters in the first place. However, that is a mission for another day.

On with Monday now…

Lessons in customizing BlogCFC

After debating for a couple days and trying out Blogger.com and WordPress.com, I have finally decided to use BlogCFC from Raymond Camden. Thanks Ray.

Adam Lapsley @ Lapsley Media set me up with great CF hosting. Thanks Adam. If you are looking for expert ColdFusion development and hosting in further-down-from-down-under, i.e., in New Zealand, you might want to have a chat with Adam.

There were a couple of interesting things I had to do with BlogCFC to get it to go.

  • I wanted to use the styles from my earlier blog at Blogger.com. Customizing BlogCFC was fairly painless. Having said that, it did take a couple of hours.
  • I didn’t want to use index.cfm as the default document. I know I could have continued to use index.cfm, but default.cfm was what I wanted to use! To get this going, I had to do a search and replace on all instances of index.cfm with default.cfm. There a couple of places where the code was looking for the last nine characters (number of characters in index.cfm). I had to change itto look for 11 characters (for default.cfm).It would be interesting to see how long it will take to use a new version of BlogCFC as references to “index.cfm” as the default document are hard-coded at quite a few places.
  • I wanted to not use the search-engine safe (SES) urls. (I don’t know if that term continues to hold meaning as spidering engines now seem to be intelligent enough to follow query strings.) Changing that was as simple as deleting values in “alias” column in tblBlogCategories and tblBlogEntries.
  • I wanted to pass username and password in all the tags. Again, I had to do a search and replace to add these attributes to the tag.

All in all, it was fairly straightforward to customize BlogCFC to look as well as behave the way I wanted to! Nice 🙂

Now that I’m all setup, I can devote my energy to posting the sample Cairngorm contacts application on the blog. See my previous blog entry for more on this.