Hooking up ColdFusion and SQLite

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> </code>

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 developer advocate. He focuses on JavaScript, serverless 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

Comments