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.
Archived Comments
Ray, I think you forgot an end [/code] tag
Thanks. Remind me not to blog until I've had my 5 cups of coffee.
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
Yeah, definitely, and thanks Dan, I should have made it clear that AIR had no problems doing prepared crap with SQLite.
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
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.
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!
You should submit it to RIAForge too.
> 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.
I know the guy who runs RIAForge. I'll yell at em.
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