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!

mySQL - cfqueryparam - Comments - "No value specified for parameter"

I always try to add comments to complex queries against our mysql server and in order to see the comments in the debug output as well, I'm always using the /* comment syntax */ offered by SQL. The other day I added some comments to a rather complex query again and suddenly I received a strange error message all the time telling me "No value specified for parameter 4 / SQL State 07001".
After some playing around I found out that you should now use question marks in the SQL comments - it seems as CF or mysql is interpreting this char and assuming a parameter, even if it's contained in a comment.