Using the H2 database in ColdFusion

Railo is delivered with an embedded database called H2. The description on the homepage says:

Welcome to H2, the Java SQL database. The main feature of H2 are:

* Very fast, open source, JDBC API

* Embedded and server modes; in-memory databases

* Browser based Console application

* Small footprint: around 1 MB jar file size

Such a database fit's perfectly for a current task at tunesBag - the streaming servers should store some data on their own machines and mysql or postgresql just would be overkill for this task. So I decided to give H2 a look and it looks pretty nice.

On my local machine I'm working with ColdFusion 8 as well - the configuration to add H2 support for CF is very easy, just follow the following steps:

• Download the latest version of H2
• Drop the h2*.jar file in the WEB-INF/lib directory of your ColdFusion server (directory depends on your type of setup)
• Restart CF
• Add a new datasource
• JDBC URL = jdbc:h2:file:%Path to Database file on your disk%
• Driver Class = org.h2.Driver
• Driver name = default
• User name = sa

That's it - the files holding the data will be created automatically for you. Take a look at the H2 tutorial in order to find out more (e.g. server mode etc).

"Corrupt table" errors (ColdFusion 8, mysql)

We have to do a lot of caching in order to keep the performance of tunesBag.com, so cached queries are very important for our website. During the last two days a lot of "corrupt tables" errors showed up in the log and first I thought about a mysql database issue of course. It's a bug in ColdFusion, however, so in case you run into the same error use this hotfix: http://kb.adobe.com/selfservice/viewContent.do?externalId=kb402583

I've updated our live servers and I hope we won't see this error again.

Enhance performance of Joins / Queries: Make sure all columns share the same collation

At tunesBag.com, we're storing most of the data in UTF-8 / Unicode because we've customers from all over the world. Some internal data like userkeys, entrykeys etc are stored in latin1 because it needs less space. I recently came accross a query which was very slow without any obvious reason and after some research I found out that in a left join, the column A from table A has a different collation than column B from table A.
I changed the collation for column B to the same as column A - and the query executive time dropped from about 1300 msec to 100 msec!

So always make sure you're using the same collation if possible when performing joins!

Create & Connect to databases (mysql, SQLite etc) on the fly with ColdFusion

Working with databases in ColdFusion is very easy - you have to set up your datasources in the CFAdmin (or by code using the CFAdmin API) and you can start writing SQL using CFQUERY. But what if you want to connect to a database on the fly without creating a datasource?
In this case it comes in handy that ColdFusion is a Java-based product, so you can use all the power JDBC is offering. At the online music hub tunesBag I recently had to create SQLite databases on the fly - it's quite easy in fact as you can see below:

1) Add the JDBC Driver (or use an existing one)
You can use any JDBC driver, e.g. mysql, MS-SQL etc
In my case: Download the SQLite JDBC Driver from zentus.com and place it in wwwroot/WEB-INF/lib (sqlitejdbc-v053.jar)

a_sqlite = createObject( 'java', 'org.sqlite.JDBC' )
a_prop = createObject( 'java', 'java.util.Properties' )
db_filename = '/tmp/sqlite_' & CreateUUID() & '.db'

2) Write the code (this is an example for sqlite)

a_conn = a_sqlite.connect( 'jdbc:sqlite:' & a_db_filename, a_prop.init() )
a_statement = a_conn.createStatement()
a_res = a_statement.execute( 'BEGIN;')

Create the table
a_res = a_statement.execute( 'CREATE TABLE test (id INTEGER PRIMARY KEY, firstname TEXT, surname TEXT);');

Insert data
prep = a_conn.prepareStatement( 'INSERT INTO test (firstname,surname) VALUES (?, ?);' );

prep.setString( 1, 'John' );
prep.setString( 2, 'Doe' );

// you can add multiple inserts ...
prep.addBatch();

prep.setString( 1, 'Max' );
prep.setString( 2, 'Mustermann' );
prep.addBatch();

// insert!
prep.executeBatch();

// save the changes
a_res = a_statement.executeUpdate( 'END;');

// quit
a_conn.close();
a_conn = 0;

Where to go from here
For more details, check out the official JDBC documentation provided by SUN!