[Looking for Charlie's main web site?]

CF/Derby: Some common questions about Derby's suitability for use

Note: This blog post is from 2008. Some content, links and indeed comments from others may be outdated--though not necessarily. Corrections are welcome, in the comments. I may revise the content if necessary.
Some of the most common questions people wonder as they first consider using Derby (the embedded database in CF8) are the following: is it a development-only DB? Does it perform and scale well? Isn't it only a single-user DBMS? Is it the same database embedded in AIR? There's a lot of misinformation out there and there are surprisingly positive answers to all these potential criticisms, which I address these below.

I'll also address these points and more in my talk at Adobe Max, in my session "Using Apache Derby, the Open Source Database Embedded in ColdFusion 8", on Tuesday, November 18, from 4:30 pm - 5:30 pm. If you've not yet booked that slot, or are on the fence, I think you'll be VERY surprised to hear all that Derby can do. I'll share some tidbits over coming days.

Is it a development-only DB? How does it perform and scale?

No, it's not just a development DB. First, yes, of course you can build production applications with it (though redistribution would be according to the Apache license). But as for its performance capability, there's in fact a PDF of a presentation comparing Derby to MySQL and others. Still another is available from IBM.

I've heard that Derby is a single-user DBMS

That's a common misconception, and some of it stems from the fact that it can run on its own, embedded in another Java application or server (like ColdFusion), or it can operate using another feature called the Derby Network Server.

Let's look first at the simpler embedded form: Derby itself provides no communications abilities of its own, which helps keep it lightweight. As such, it can't be communicated with from outside applications, indeed it can accept requests from within that same JVM process. Does that mean it's single-user? Of course not. CF (like most web app servers) is multi-user, and IT handles that multi-user processing. Derby itself has no problem processing multiple requests within the CF process. It just can't receive them from outside of CF.

So why might a CF user care about the Network Server feature? Well, if you wanted to talk to Derby from another Java process, then you need it. You may think this applies only to other Java applications on the server or clients, but here's one that may trip you up: an IDE. The ij tool (mentioned later), for example, can't talk to your embedded database without enabling the Network Server.

That latter point, and indeed more on this whole question of "is it a multi-user database or not?" is covered in Chapter 1 of an available Derby book I'll discuss in a later entry, and that chapter is available online (and in pdf form). The discussion of interest here is on the next to last page. I'd like to quote a bit:

When developers refer to Apache Derby as an embeddable database, they are referring to the fact that the Apache Derby database runs within a JVM process. Without the Apache Derby network server, there would be no networking services, data access outside of the embedded JDBC driver in the database engine, or other infrastructure requirements; this accounts for its small footprint.

Understanding what the embedded concept entails is critical when developing applications. For example, one common misconception that developers have when they work with Apache Derby as a standalone database is that it's only a single-user database and does not have communication capabilities. They believe that it is a single-user, single-connection, single-threaded system and develop their applications accordingly. This is not true. Apache Derby as a standalone database can support as many connections as desired, so long as they are established from the same JVM hosting the Apache Derby engine.

For an Apache Derby database to be accessed from a process that resides outside the hosting JVM that loaded the Apache Derby database initially (even if the JVM process resides on the same server), you need to load the Apache Derby network server. Read that last sentence twice to ensure you understand it because it is often a source of confusion for Apache Derby developers when multiple JVMs reside on the same machine. The Apache Derby network server allows for communications between JVM processes. This means that this communication infrastructure isn't solely required to communicate between machines; it is needed even if two different JVM processes reside on the same machine and want to talk to the same database.

Is this the same database engine that's embedded in Adobe Air?

No, that's yet another open source (indeed, public domain) DB, called SQLite (not a typo: it's spelled with one "L"). One may ask why the different Adobe teams chose different open-source embedded DBMSs. It could be that the different groups weren't aware of each other's decision and simply chose what seemed best for them. (Is Derby embedded in CF the same way SQLite is in Air? Well, that seems semantics. Yes, Derby is embedded in CF. The full DBMS is there. Nothing to install. Yes, you have to create a DSN (more later), but that's it.)

Could Derby have been used for Air? Sure. Could SQLite have been used as the embedded DB for CF? It seems so. The two are very similar in being small, embedded, yet highly functional multiuser database engines.

There's a comparison of the two on the SQLite site, though obviously it has a SQLite perspective. For instance, it says "Derby only allows a single process to have the database open at a time in its embedded mode. However, Derby also offers a full client/server mode." That's a common misconception, as discussed in my previous point above.

Signup for my Derby talk at Max

Again, I'll have lots more to say on Derby. But I realize it can be hard to keep up on any blog entry or series of entries. If I've piqued your interest at all, or if you want to show your support for CF (and Derby) talks at Max, consider signing up for my talk. I'll have more to say and show there, for sure. Also, if you have colleagues who maybe don't read blogs, let them know that Derby is a lot more than they may have been led to believe, and have them sign up. The Max folks use signups to measure interest in topics. This may be one that slipped under the radar of many considering talks. I'm just trying to help promote it, while sharing more Derby goodness along the way.

For more like this:

Do you have an ongoing discussion anywhere on the site about how to use Derby? I'm just getting my feet wet, and I'm curious about a few things.

1. I was able to create an embedded instance and create a table, but only through CFQUERY. I was trying to use the RDS dataview that comes with Adobe's extensions for Eclipse (awesome by the way) but I wasn't able to create tables or anything like that via the dataview tool.

2. I misspelled one of my column names. I tried using RENAME COLUMN in the RDS dataview and in a CFQUERY tag and wasn't able to change it. I got an error that said "Encountered 'COLUMN' at line 1, column 8" Any ideas?

Sorry if this is the wrong place for this discussion. I wish I could make it to Max this year. I have another training engagement. :(
# Posted By Kevin Slane | 9/26/08 9:29 AM
Kevin, there are several such places.

First, I created a Derby community in the coldfusioncommunity.org site: http://www.coldfusio... You can discuss things with folks there.

Before you do, though, check out a getting started guide I've got (and from which I'm offering these recent blog entries), at http://carehart.org/...

As for using the tool with the Eclipse-based RDS DataView, that does work, as I hope you've seen, with the Embedded server version (not with the client/server version). But that interface has never been able to create tables (nor have the RDS-based database viewing features of Dreamweaver, CF Studio, or HomeSite+).

I'm afraid Adobe (and MM before them, and Allaire before them) just didn't envision empowering the RDS-based data viewing feature (the interface for showing tables, columns, etc.) with the ability to create or modify them. They just don't support DDL (the SQL for modifying schemas).

Of course, you can enter DDL you create yourself, in the RDS Query Viewer, which can be opened form the RDS DataView tab. That's different. And surely you should be able to do a rename.

But your inability to do it there isn't about the RDS tools. It's that it's just not supported in the version of Derby running in CF (10.2). It's been added in 10.3. Yeah, that's a bummer. But hey, SQL Server doesn't support RENAME COLUMN either: you have to use an SP to do it.

Of course, this raises the inevitable question of whether one can update the version of Derby running in CF. The answer would be, yes one could, but it wouldn't be supported. So for now, Kevin, the answer is that you can't do a rename. Sorry.

Speaking of IDEs to do DML and DDL, though, there are, of course, other visual query IDE tools that generally support such things, and many work with Derby. I discuss that in a specific question of my guide above, at http://carehart.org/...

Unfortunately, all those other tools then will not work with Derby as an embedded DB, because they are then operating in their own JVM, and they need to have direct access to the DB themselves, or you need to use the client/server mode and point them (and CF) at that.

It's unfortunate. But I discuss it all in more depth there, and of course will mention many of these things in the talk at Max. In the meantime, ask away on the coldfusioncommunity derby discussion area, or in comments here.
# Posted By Charlie Arehart | 9/26/08 6:32 PM
Your response pretty much confirmed what I was thinking. I could use another IDE, but not with the embedded db.

That's OK. I'm still very interested and I'm headed over to check out the links you sent me. Oddly enough, I just registered on coldfusioncommunity.org this week. I've been a CF developer since 1998, so I thought I might attempt to finally contribute. :)

On an unrelated note, I enjoyed the presentation you gave at the Tulsa CFUG last year. Very informative.

# Posted By Kevin Slane | 9/28/08 8:27 PM
Thanks, Kevin.
# Posted By Charlie Arehart | 9/29/08 10:30 AM
Copyright ©2019 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting