WebDevelopment, ColdFusion, Railo, JS, Database and Tech-related by the Co-Founder and CEO of tunesBag.com

Wednesday, 29 October 2008

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.setString( 1, 'Max' );
prep.setString( 2, 'Mustermann' );

// insert!

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

// quit
a_conn = 0;

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


Leif said...

Thanks for this post!

I've been looking for a cleaner way to get SQLite database files from a MySQL database. Being able to control the creation process with ColdFusion in the manner you describe here is very nice.

Egnaro said...

I've more or less got this working but I've run into a problem. When I replace the strings with variables in the prepareStatement methods it stops working. ie. if I do the following:
myvar1 = 'Max';
myvar2 = 'Mustermann';
prep.setString( 1, myvar1 );
prep.setString( 2, myvar2 );

when I run it I get a syntax error. I tried using JavaCast("String",myvar1) to explicitly make it a string but that didn't change anything.

I'm sure I'm missing something simple, but I don't usually work with Java in CF so I'm not sure where to look to get this working.

Egnaro said...

Nevermind, got it working. I had changed some other code that messed this part up. Seems to be good now.

Thanks for this post btw, it's been super helpful!

zojx said...

It's easier if you add a new datasource in your CF administrator and write SQL statements in "cfquery" tag.