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:
   view plainprintabout
1  <cfsetting enablecfoutputonly="true">
2  <cfset wordList = "/Users/ray/Desktop/word_list_moby_freq_broad_corpus-flat/word_list_moby_freq_broad_corpus.flat.txt">
3  
4  <cfloop index="word" file="#wordList#">
5      <cfset word = trim(word)>
6      <cfif len(word) gt 2>
7          <cfoutput>#word#<br>
8          </cfoutput>
9          <cfquery datasource="sqllitetest">
10          insert into WORDS(word)
11          values(<cfqueryparam cfsqltype="cf_sql_varchar" value="#word#">)
12          </cfquery>
13          <cfflush>
14      </cfif>
15  </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:
   view plainprintabout
1  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.