Skip to main content
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Marc Grabanski
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Marc Grabanski ( @1Marc )

Creating On-The-Fly Datasource Connections In Lucee CFML

By
Published in , Comments (1)

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 CFQuery tag 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:

The CFDump of a CFQuery tag result showing two columns, hello and now.

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

15,728 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

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel