Project Home • Forums • Known Issues • Contact Project
I found myself needing to quickly generate SQLite databases for a project, and did not want to go through the trouble of installing the JDBC driver on the server and requiring the setup of a new CF datasource in the CF Administrator.
Instead, I wanted to just feed a file path to a component, and write SQL against it. There is a method (executeSql) to execute SQL directly against the SQLite db. For select statements it returns CF query, for all other statements it returns the connection object for further reuse.
The other thing is does, rather well, is convert a CF recordset to a table in a SQLite db. This is very useful when caching query results and creating 'on the fly' dbs. Pass a cf query to the convertQueryToTable method, along with a comma-separated list of primary keys and numeric columns.
There's also a convertQueryToCSV method that works as well, though it's not really the focus of this component. It uses the StringBuilder/StringBuffer Java class for performance reasons. As a rule, performance is the foremost consideration in the development of this CFC.
See test.cfm to confirm that it is working correctly for you (you will need to change some paths).
New methods were recently added, convertCSVToQuery and convertCSVToTable. I used Ben Nadel's excellent regex algorithm for parsing CSV files. http://www.bennadel.com/index.cfm?dax=blog:976.view These methods will read a CSV file and write it out to a cf query or directly to a SQLite table. Examples, as always, provided in the test.cfm file.
There are also now custom tags that can be used to run queries against SQLite dbs, like so:
&lt;sqlite:query name="MyQuery" dbName="MySite"&gt;
SELECT * FROM myTable WHERE id = &lt;sqlite:queryparam value="#form.id#" /&gt;
NOTE: If you specify closeConnection=false so that the connection from convertQueryToTable or executeSql is not automatically closed, it is your responsibility to call the close() method on the instantiated connection object. Forgetting to do so can cause memory leaks in your application, you might even want to ensure that the connection is closed with a try/catch, in case of errors. closeConnection is 'true' by default, but for many concurrent queries you will want to reuse one shared connection.
Special thanks to ArcStone Technologies for allowing me to post this work, as part of it was definitely completed on their dime. You can see more of our work at http://www.arcstone.com
1.1.1a - (04/20/2012) - Updated version based on real-world use. Also including the most recent SQLite JDBC driver from Xerial. Should have better performance.
1.1.1 - (02/10/2010) - Identified an issue with executeSql on Railo, since coldfusion.sql.QueryTable isn't really used in the same way (it's an interface, not a class). This method should now work properly both in Adobe CF as well as Railo. More details here: http://goo.gl/SqUy
1.1 - (01/28/2010) - For date exports, added isDate() checking to format dates as yyyy-mm-dd HH:mm:ss for both convertQueryToCSV and convertQueryToTable
1.1a - (01/16/2010) - Added convertCSVToQuery and convertCSVToTable methods for reading CSV files directly into SQLite or query format. Added new custom tags to the /tags folder (based on the Transfer ORM TQL custom tags).
1.01 - (01/11/2010) - No update to the CFC, added more examples to test.cfm, including prepared Statements.
1.01 - Bugfix: convertQueryToTable was replacing all quotes with single-quotes. JavaCast to the rescue.
1.00 - Initial Release
CFMX 7+, Railo 3.1+
(Railo 3.0 might work, as well as CF 6, untested on these platforms)
Uses Mark Mandel's JavaLoader as a proxy for the sqlitejdbc.jar file in the lib folder. Will attempt to register the driver on the server (if you already have it installed), but otherwise will use the included jdbc driver.
To enter issues for this (or any other) project, you must be logged in.
Adobe and the Adobe product names are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States and/or other countries.