Creating On-The-Fly Datasource Connections In Lucee CFML
In yesterday's post on connecting to SQLite databases using JDBC in Lucee CFML, I was creating and consuming a new, user-specific datasource on every page request. In order to do this, I made use of a technique that I only just learned about from the CommandBox Book written by Ortus Solutions. Apparently, in Lucee CFML, you can provide the CFQuery datasource attribute as a struct instead of a string.
Normally, when using the CFQuery tag to connect to the database, we can either rely on the implicit default datasource; or, provide the name of a datasource that's been configured elsewhere (either in the ColdFusion administrator or in the this.datasources property within the Application.cfc).
But, in Lucee CFML, we can provide the database connection settings directly to the CFQuery tag as a struct. When doing so, Lucee CFML will create an on-the-fly datasource (named __temp__) and make it available in the context of the given query.
Note: If you're curious to see what Lucee CFML is doing under the hood, here's the
CFQuerytag implementation which is checking for a struct. And, if it receives a struct, it then creates a temporary datasource.
To demonstrate, I'm going to create an on-the-fly JDBC connection to a temporary, in-memory SQLite database:
<!---
In Lucee CFML, you can define temporary datasources by supplying the database
configuration as a "struct" in lieu of a datasource "name". The available settings in
the struct match those that you'd use elsewhere (such as in the "this.datasources"
property in the Application.cfc ColdFusion component).
--->
<cfset onTheFlyDatasource = {
class: "org.sqlite.JDBC",
connectionString: "jdbc:sqlite::memory:"
} />
<cfquery name="results" datasource="#onTheFlyDatasource#">
SELECT
( 'world' ) AS hello,
datetime( 'now' ) AS now
;
</cfquery>
<cfdump
label="On The Fly Query"
var="#results#"
/>
As you can see, we're passing a struct to the datasource attribute of the CFQuery tag. We don't have any database tables to work with in this demo; but, the in-memory SQLite database gives us a context in which to execute arbitrary SQL statements. And, when we run the above Lucee CFML code, we get the following output:
In this case, I'm using a SQLite database; but, to be clear, this works with any type of database that ColdFusion would normally connect to. In fact, the toDataSources() Java code that Lucee CFML is calling under the hood here is the same method that gets called when setting up the Application.cfc datasources.
This is a cool technique! But, it's probably not something you should use in a production setting. Since the generated datasource is temporary, you don't get to take advantage of robust features like connection pooling. However, it's great to know that this exists for the edge-cases in which it can make life easier.
Want to use code from this post? Check out the license.
Reader Comments
Re: Connection Pooling
In the post, I say that using the on-the-fly datasource doesn't let you use connection pooling. This was an assumption that I made from looking at the Lucee CFML source code. And, it wasn't an assumption that I could really test.
But, it seems that my assumption isn't true. This morning, I looked at using temporary, in-memory SQLite databases:
www.bennadel.com/blog/4655-creating-in-memory-sqlite-databases-using-jdbc-in-lucee-cfml.htm
These in-memory database persist across page refreshes, which could only be true if the connection was being held-open by the connection pool. As such, I must believe that the on-the-fly datasource is doing something clever and allowing for connection pooling to work.
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →