After chatting with some folks after my Derby presentation, I came to a realization that Derby, the Apache database, is still woefully misunderstood within the ColdFusion community. As Charlie Arehart describes, “Derby is the ColdFusion of the database world.” What he means by that is that Derby is misunderstood and underutilized. In this post, I am going to address some recurring ‘myths’ I have heard people discuss about Derby.
Myth #1 – Derby is just like SQLite
Derby is NOT SQLite. SQLite its the ‘lightweight’ database that is included as part of Adobe Integrated Runtime, Derby is the full featured database included with ColdFusion. To put it in terms that might make a bit more sense, Derby is SQL Server, SQLite is an Excel spreadsheet. I know that is not a true comparison, but in terms of functionality, its pretty close.
Myth #2 – Derby has limited functionality
With Derby you have a database that can go toe to toe with the big databse systems in terms of functionality. Derby supports user defined functions, stored procedures, triggers, views and constraints. To get a better idea of what Derby is capable of, take a look at the online documentation.
Myth #3 – You can only use Derby with ColdFusion 8
You can connect to Derby using JDBC drivers, so you can easily use Derby databases with ColdFusion going back to ColdFusion MX.
While there are other topics I have discussed with people, these are pretty much the “big 3” that I hear most frequently.
I plan to offer other posts that will shed somemore light on Derby and show it is a viable option for most web applications.
DougHughes.net 
Comments on: "Derby != SQLite and Other Derby Myths" (12)
I would like to add, that I wrote a test application for pagination in mySQL and MSSQL last year. The site I went to upload this too, had a requirement that it works with Derby.
Guess what, Derby at that time had no support for getting top n rows or such that could be used for pagination.
This limitation to me was enough for me to keep an eye on the product, but to not take it serious at the moment.
Sure it has the triggers, it has the SP’s, views and constraints. But if it can’t do simple pagination in SQL code, then I am not amused!
Seriously though, it is a young product and I will say that it has extremely huge potential.
LikeLike
@Andrew – The way Derby supports limiting the number of rows returned is through the standard JDBC java.sql.Statement.setMaxRows() method. I believe (and I am requesting verification on this) this is accomplished via the maxRows attribute of .
LikeLike
Derby supports pagination in much the same way SQL 2005 does with the “ROW_NUMBER” function:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER () AS R,
T.*
FROM T
) AS TR
WHERE R > 10
AND R < 20;
Here is the doc page on it:
http://db.apache.org/derby/docs/10.4/ref/rreffuncrownumber.html
LikeLike
Hey Scott, readers interested in your points may get value also out of this resource I created:
Getting Started with the Apache Derby Open Source Database in CF8
http://carehart.org/resourcelists/derby_for_CFers/
It’s equivalent to a 5 page article on the subject topic, with lots of clarifications like yours, and pointers to objective assessments by others, as well as many other resources.
Great to see someone else wave the Derby flag in the CF community. 🙂
LikeLike
The Row limit is nice to see. My biggest outstanding issue is the case sensitivity of Derby. I prefer this to be configurable but to date I have not found a note on how to do this. In fact most the time I see the suggestion of ucase everything…blarg.
LikeLike
I really like Derby, but the lack of a way to limit the number of rows returned *in SQL* is a pretty serious limitation. I like to be able to get the first record matching certain conditions in a subquery and I can’t do that in Derby. (if others can, I would love to find out how).
LikeLike
@Gus, Well I stand corrected. When I first used Derby that was not an option at that time. I had blogged this issue a very long time ago, and I can tell you there where many users who suffered the same fate.
Also for others SQL 2005 has a much better an elegant way of doing it now. And it is one query not a subselect of a select.
I would prefer that method over this.
select * from TableOne
where ( recordID not in
(
select top {TotalRecords} recordID
from TableOne as newTable
}
}
The downside to the above pagination code, is that if you need to do filtering on the query. You need to had your where conditions in both selects.
However if one is lucky enough to be using SQL Server 2005+ then I would prefer to use the ROW_NUMBER() ability.
LikeLike
Gus,
Not sure how I missed your comment (or failed to find that SQL after a lot of searching prior to today), but that is really cool!
I am definitely looking forward to using that.
Thanks for the tip.
LikeLike
Hmm. Interesting. Thanks, I never even gave Derby a chance. Now I’ll consider it for some of my future apps.
LikeLike
This week I had a cool essay on Java / Derby . I haven’t used Derby before . I start orgininizing my efforts with my similaro knowledge on MySql , MsSql etc …
After 2-3 days I found some MAJOR limitations of Derby:
Derby (until its current v 10.5) does not support something like ‘select top xx’ thing (Yes the standard answer ‘You can do that from Java’) but what if I want to use 3-4 sub selects with ‘select top xx’
Derby cannot save a view if there’s is an ‘order by’ !!
Actuall Derby cannot have a sub select with ‘order by’ .
After all theese I went straight to Sqlite to see if i had those limitations … unbelievable … Sqlite have none of the above obstacles .
I start thinking that all these guys talking about Derby , didn’t seriously used it .
Ms Access 95 (!) , or 250KB Sqlite have some more capabilities of Derby !.
LikeLike
Derby 1.4.2 emulates the select top with some 7-8 lines of code !
but Moreover Derby CANNOT do something like ‘select top xxxxx order by nnn’ that Sqlite and ALL other SQL do !.
LikeLike
Mike X, you are right but it is obvious that you ignore some Derby’s benefits (roles, client/server mode and more)…
Please read this one, http://www.sqlite.org/cvstrac/wiki?p=SqliteVersusDerby
LikeLike