Quod Scripsi, Scripsi

SQLite with Coldfusion via JDBC

I used the drivers here: Precompiled JDBC SQLite drivers

The source is here, though it may not be the same: Source

I dropped the Linux JDBC and .so files into [cfmx7root]/lib/ and restarted coldfusion. Then I created a database using SQLiteQuery on my Mac and added a table, column and single value. Then I copied the db file to a test folder on my server and just ran a select * from table.

My datasource setting is as follows:

JDBC URL: jdbc:sqlite:\var\www\html\sqlite_test\cfmltest
Driver Class: org.sqlite.JDBC
Driver Name: sqlitejdbc.jar

At that point, running the code threw an error that the table didn’t exist. Permissions were all okay, so I executed a CREATE TABLE via the .cfm file with a different name, column and value. That table showed up. Turns out that the table I created on the Mac only shows up on the Mac via the GUI tool, and the table I created via CF only shows up for CF, even if I query the master table to get a table listing. I’m thinking that has to be something to do with driver versions.

If I get a chance to test further, I’ll run some speed tests versus reading and writing files, XML, etc. to see what the speed is like. This is also obviously insecure, you don’t want your db file to be readable by the web server.

UPDATE: now that CF8 includes the Derby java database, this is somewhat moot. Though it does give a way to interact with existing sqlite databases. I still don’t know why some tables don’t show up, again probably a version/driver issue.