Blog

Brad Wood

February 21, 2017

Spread the word


Share your thoughts

One of the cool things that CommandBox allows you to do is run CFML code directly from the command line via the execute command, or on Unix as a natively-executable hash bang script. This opens up all sorts of doors for scheduling cron jobs and whatnot that you can write in CFML much faster and esaier than using bash or shell scripts.

How do I access the DB?

One common question is how to access the database from one of these scripts. Your code is executed on Lucee Server (version 4.5 at the time of this writing) which is the version of Lucee that the core CLI runs on. The CLI has the full power of a Lucee server running under the covers, but there's no web-based administrator for you to acess to do things like adding datasources for your scripts to use. I would consider that poor form anyway since standalong scripts are best if they're self-contained and don't have external dependencies like server settings neccessary to run.

Lucee allows datasource to be a struct

So the easiest way to accompish this is simply to exploit a little known but very cool feature of Lucee that allows the datasource attribute of most tags to be not only a string which contains the name of the datasource, but also a struct that contains the definitiion of the datasource. This will create an on-the-fly connection to your database without any server config being neccessary whcih is perfect for a stand-alone script. Here is what that looks like. Note, I'ms using queryExecute(), but it would work just as well in a cfquery tag.

ds = {
	class: 'org.gjt.mm.mysql.Driver',
	connectionString: 'jdbc:mysql:  //localhost:3306/bradwood?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true',
	username: 'root',
	password: 'encrypted:bc8acb440320591185aa10611303520fe97b9aa92290cf56c43f0f9f0992d88ba92923e215d5dfd98e632a27c0cceec1091d152cbcf5c31d'
};

var qry = queryExecute( sql='select * from cb_role', options={ datasource : ds } );

for( var row in qry ) {
	echo( row.role & chr( 10 ) );
}

So, the first block simply declares a struct that represents a datasource connection. Then I use that struct as my datasource. You might be thinking, "where the heck did he get that struct??". Glad you asked. Start up a Lucee 4 server, edit a datasource that has the connection properties you want and then at the bottom of the edit page you'll see a code sample you can just copy and paste from. This is the code for an Application.cfc, but you can re-use the same struct here.

Get DS definition from the Lucee administrator

Another method

So, there are a couple tags inside Lucee that don't support this just yet. <CFDBInfo> is one of them. Ticket Here In this case, you need a "proper" datasource defined that you can refernce by name. Lucee has some more tricks up its sleeve for this. You can simulate the same thing that happens when you add a datasource to your Application.cfc with the following code. This will define a datasource for the duration of the time the CLI is running in memory, but it will be gone the next time you start the CLI.

appSettings = getApplicationSettings();
dsources = appSettings.datasources ?: {};
dsources[ 'myNewDS' ] = {
	class: 'org.gjt.mm.mysql.Driver',
	connectionString: 'jdbc:mysql:  //localhost:3306/bradwood?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true',
	username: 'root',
	password: 'encrypted:bc8acb440320591185aa10611303520fe97b9aa92290cf56c43f0f9f0992d88ba92923e215d5dfd98e632a27c0cceec1091d152cbcf5c31d'
};
application action='update' datasources=dsources;

var qry = queryExecute( sql='select * from cb_author', options={ datasource : 'myNewDS' } );

for( var row in qry ) {
	echo( row.firstName & chr( 10 ) );
}

So lets break this down real quick. First we get the current settings of the CLI Lucee context and the list of current databases (may be null). Then we simply add the same datasource definition as above to the struct with the name we wish to use to reference this datasource. And finally we update the application with the new struct of datasoruces. Now we can use this datasource name just we would in a "normal" web application.

Notes

The internal CLI of CommandBox still runs on Luce 4.5 so make sure you copy the data source definitions from a Lucee 4.5 server, and not a 5.0 server. Also, you'll note I used encryped passwords above (extra credit if anyone can figure out what it is). You can also just put the plain text password in. Just omit the encrypted: text like so:

username: 'root',
password: 'clear text password'

Add Your Comment

Recent Entries

CBWIRE 3.0.0 Released

CBWIRE 3.0.0 Released

We are very excited to announce the release of version 3 of CBWIRE, our ColdBox module that makes building modern, reactive apps a breeze. This version brings with it a new component syntax, 19 enhancements and bug fixes, and improved documentation. Our biggest goal with this release was to improve the developer experience and to provide a low barrier to entry to getting started with CBWIRE.

Grant Copley
Grant Copley
May 22, 2023
ColdBox 7.0.0 Released

ColdBox 7.0.0 Released

Introducing ColdBox 7: Revolutionizing Web Development with Cutting-Edge Features and Unparalleled Performance

We are thrilled to announce the highly anticipated release of ColdBox 7, the latest version of the acclaimed web development HMVC framework for ColdFusion (CFML). ColdBox 7 introduces groundbreaking features and advancements, elevating the development experience to new heights and empowering developers to create exceptional web applications and APIs.

Designed to meet the evolving needs of modern web development, ColdBox 7 boasts a range of powerful features that streamline the development process and enhance productivity. With its robust HMVC architecture and developer-friendly tools, ColdBox 7 enables developers to deliver high-performance, scalable, and maintainable web applications and APIs with ease.

Esme Acevedo
Esme Acevedo
May 16, 2023
TestBox v5.0.0 Released!

TestBox v5.0.0 Released!


We are excited to announced the release of Testbox version 5, which brings a host of new features and improvements for developers. TestBox is a powerful and flexible tool that helps developers write comprehensive BDD/TDD tests for their applications, ensuring code quality and reducing the likelihood of bugs and errors. With TestBox v5, developers can take advantage of new features such as batch code coverage testing, improved reporting capabilities, method spies, and better integration with other tools in the Ortus suite.

These new features make TestBox even more versatile and user-friendly, and provide developers with a powerful tool for building high-quality, reliable applications.

 

Luis Majano
Luis Majano
May 11, 2023