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
prep.setString( 1, 'John' );
// 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!
