Posted in ColdFusion | Posted on 09-24-2009 | 5,936 views
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:
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:
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.
http://www.nodans.com/index.cfm/2007/10/31/Fixed-e...
DW
"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
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/cfsqlite . To anyone who wants to use an SQLite databse in a CF app, I hope this helps!
Done. (But I expect it won't appear until it makes its way through the submission queue.) Thanks for prompting me to submit it.
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
[Add Comment] [Subscribe to Comments]