Hooking up ColdFusion and SQLite

This post is more than 2 years old.

Need to hookup ColdFusion to SQLite? I did. I normally only use SQLite for AIR development, but I had a situation where I needed to seed a database with a large set of data. While AIR can easily do this (it can create the database, the tables, and initial data), I thought it would be nicer if the database was simply prepoulated.

I began by first creating my SQLite database by using Lita. This is an AIR based SQLite editor. I had a few issues with it, but eventually I got it working right. I created the file and my table to get it ready for ColdFusion.

My Google search for JDBC drivers turned up SQLiteJDBC. I downloaded the first JAR file and save it to my ColdFusion classes folder. I restarted ColdFusion and then went and create my DSN. I selected Other for the type and used the following settings:

JDBC URL: jdbc:sqlite:/Users/ray/Documents/words.db

This value should begin with jdbc:sqlite: and then end with the full path to the file.

Driver Class: org.sqlite.JDBC

And then everything else, but the name of course, was blank. I confirmed the datasource connected ok in both the ColdFusion Admin and in ColdFusion Builder's RDS panel.

Ok, now for the fun part. I wanted to use ColdFusion to seed the database with a set of words that came from a text file. I used the following code, which I assume is self-explanatory and not really an important part of the blog entry:

<cfsetting enablecfoutputonly="true"> <cfset wordList = "/Users/ray/Desktop/word_list_moby_freq_broad_corpus-flat/word_list_moby_freq_broad_corpus.flat.txt">

<cfloop index="word" file="#wordList#"> <cfset word = trim(word)> <cfif len(word) gt 2> <cfoutput>#word#<br> </cfoutput> <cfquery datasource="sqllitetest"> insert into WORDS(word) values(<cfqueryparam cfsqltype="cf_sql_varchar" value="#word#">) </cfquery> <cfflush> </cfif> </cfloop>

This threw an interesting error though: statement is not executing. I didn't quite know what to make of that, but on a whim, I changed my queryparam line to this:

values('#word#')

And all of a sudden - it worked. So either the driver doesn't support bound parameters or I set up something wrong, but this did the trick. For another example, check this blog entry that was shared with me on Twitter.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Sean Coyne posted on 9/24/2009 at 5:41 PM

Ray, I think you forgot an end [/code] tag

Comment 2 by Raymond Camden posted on 9/24/2009 at 5:42 PM

Thanks. Remind me not to blog until I've had my 5 cups of coffee.

Comment 3 by Dan Wilson posted on 9/24/2009 at 5:59 PM

Odd. It must be the driver. SQLLite (when used in an AIR context) supports prepared statements. Here is a code sample of how to do that:

http://www.nodans.com/index...

DW

Comment 4 by Raymond Camden posted on 9/24/2009 at 6:02 PM

Yeah, definitely, and thanks Dan, I should have made it clear that AIR had no problems doing prepared crap with SQLite.

Comment 5 by Terry Collinson posted on 4/25/2011 at 4:58 PM

I get this error when trying to create the datasource
"java.sql.SQLException: No suitable driver available for Test, please check the driver setting in resources file, error: org.sqlite.JDBC The root cause was that: java.sql.SQLException: No suitable driver available for Test, please check the driver setting in resources file, error: org.sqlite.JDBC".
I am a bit confused what to do with jar file. The classes directory contains classes but the lib directory jar files. As ever late to the show and struggling to throw a six

Comment 6 by Raymond Camden posted on 4/25/2011 at 5:27 PM

Try going to your CF Admin, Server Settings - Java and JVM - and adding a folder to your ColdFusion Class Path. Put the jar there. Restart CF. Try again.

Comment 7 by Brendan Kidwell posted on 5/22/2011 at 12:11 AM

Raymond, thanks for sharing this trick to get me started.

I wrote a library that handles setting up and mapping a SQLite database filename to a ColdFusion data source -- you can call it from the Application.onRequestStart() event. https://github.com/bkidwell... . To anyone who wants to use an SQLite databse in a CF app, I hope this helps!

Comment 8 by Raymond Camden posted on 5/22/2011 at 1:12 AM

You should submit it to RIAForge too.

Comment 9 by Brendan Kidwell posted on 5/23/2011 at 4:09 AM

> You should submit it to RIAForge too.

Done. (But I expect it won't appear until it makes its way through the submission queue.) Thanks for prompting me to submit it.

Comment 10 by Raymond Camden posted on 5/23/2011 at 7:12 AM

I know the guy who runs RIAForge. I'll yell at em.

Comment 11 by Shawn Grigson posted on 4/19/2012 at 7:17 AM

Thanks for the notice of cfsqlite, Brendan! As it turns out, I started the project on RIAForge of SQLiteCFC for a slightly different purpose: Directly connecting to sqlite dbs without a datasource, and also for converting CF queries into sqlite tables (several convenience functions for that). It also reads CSV files and converts them to sqlite. I find SQLite very convenient as a caching mechanism using the sqlitecfc--far better performance than JSON or WDDX, and more queryable (obviously).

I encourage you to check it out. It's fun, say, reading from a view on a database, dumping the records into a sqlite file, and then downloading it to export discrete slices of data for mobile purposes. The loose datatyping of sqlite is also handy and fits in handily with the loose datatyping of CF, too.

http://sqlitecfc.riaforge.org