CommandBox : CLI, Package Manager, REPL & More
5.3.0
5.3.0
  • Introduction
  • About This Book
  • Authors
  • Overview
  • Release History
    • 5.x Versions
      • What's New in 5.3.1
      • What's New in 5.3.0
      • What's New in 5.2.1
      • What's New in 5.2.0
      • What's New in 5.1.1
      • What's New in 5.1.0
      • What's New in 5.0.0
    • 4.x Versions
      • What's new in 4.8.0
      • What's New in 4.7.0
      • What's New in 4.6.0
      • What's New in 4.5.0
      • What's New in 4.4.0
      • What's New in 4.3.0
      • What's New in 4.2.0
      • What's New in 4.1.0
      • What's New in 4.0.0
    • 3.x Versions
      • What's New in 3.9.0
      • What's New in 3.8.0
      • What's New in 3.7.0
      • What's New in 3.6.0
      • What's New in 3.5.0
      • What's New in 3.4.0
      • What's New in 3.3.0
      • What's New in 3.2.0
      • What's New in 3.1.1
      • What's New in 3.0.1
      • What's New in 3.0.0
    • 2.x Versions
      • What's New in 2.2.0
      • What's New in 2.1.1
      • What's New in 2.1.0
      • What's New in 2.0.0
    • 1.x Versions
      • What's in 1.0.0
  • Getting Started Guide
  • Setup
    • Requirements
    • Download
    • Installation
    • Light and Thin Binaries
    • Non-Oracle JREs
    • Upgrading
    • Common Errors
  • Usage
    • Execution
      • Recipes
      • CFML Files
        • Using a DB in CFML scripts
      • OS Binaries
      • CFML Functions
      • Exit Codes
    • Commands
    • Parameters
      • Escaping Special Characters
      • File Paths
      • Globbing Patterns
      • Piping into Commands
      • Expressions
    • Command Help
    • Environment Variables
    • System Settings
    • System Setting Expansion Namespaces
    • Ad-hoc Command Aliases
    • Default Command Parameters
    • REPL
    • Tab Completion
    • Interactive Shell Features
    • forEach Command
    • watch Command
    • jq Command
    • printTable Command
    • sql Command
    • Auto Update Checks
    • Bullet Train Prompt
    • 256 Color Support
    • A Little Fun
  • IDE Integrations
    • Sublime Text
    • Visual Studio Code
  • Config Settings
    • Module Settings
    • Proxy Settings
    • Endpoint Settings
    • Server Settings
    • JSON Settings
    • Misc Settings
    • Task Runner Settings
    • Env Var Overrides
  • Embedded Server
    • Multi-Engine Support
    • Server Versions
    • Start HTML Server
    • Offline Server Starts
    • Debugging Server Starts
    • Server Processes
    • Manage Servers
    • FusionReactor
    • Server Logs
    • Configuring Your Server
      • Server Profiles
      • Server Rules
        • Baked in Rules
        • Allowed Static Files
        • Rule Language
        • Custom Predicates/Handlers
        • Rule Examples
        • Debugging Server Rules
      • JVM Args
      • Server Port and Host
      • SSL Certs
      • HTTPS Redirect/HSTS
      • URL Rewrites
      • Aliases
      • Custom Error Pages
      • Welcome Files
      • Basic Authentication
      • Custom Java Version
      • Adding Custom Libs
      • GZip Compression
      • REST Servlet
      • Performance Tuning
      • Undertow Options
      • Custom Tray Menus
      • Env Var overrides
      • Server Home
      • Experimental Features
    • External Web Server
    • Starting as a Service
    • Single Server Mode
    • Server.json
      • Working with server.json
      • Packaging Your Server
      • Using Multiple server.json Files
  • Package Management
    • Installing Packages
      • Installation Path
      • Installation Options
      • Advanced Installation
    • Private Packages
    • System Modules
    • Code Endpoints
      • ForgeBox
      • HTTP(S)
      • File
      • Folder
      • Git
      • Java
      • S3
      • CFLib
      • Jar (via HTTP)
      • Lex (via HTTP or File)
      • Gist
    • Package Scripts
    • Dependencies
    • Semantic Versioning
    • Updating Packages
    • Creating Packages
      • Editing Package Properties
      • Publishing Lucee Extensions to ForgeBox
    • Artifacts
    • Box.json
      • Basic Package Data
      • Extended Package Data
      • Package URLs
      • Installation
      • Embedded Server
      • Dependencies
      • TestBox
    • Managing Version
  • Task Runners
    • Task Anatomy
    • BaseTask Super Class
    • Task Target Dependencies
    • Passing Parameters
    • Using Parameters
    • Task Output
      • Printing tables
    • Lifecycle Events
    • Threading/Async
    • Task Interactivity
    • Shell Integration
    • Downloading Files
    • Running Other Commands
    • Error Handling
    • Hitting Your Database
    • Sending E-mail
    • Interactive Jobs
    • Watchers
    • Property Files
    • Running other Tasks
    • Loading Ad hoc Jars
    • Loading Ad-hoc Modules
    • Cancel Long Tasks
    • Progress Bar
    • Caching Task Runners
  • Helpful Commands
    • Token Replacements
    • Checksums
    • Code Quality Tools
  • Deploying CommandBox
    • Docker
    • Heroku
    • Amazon Lightsail
  • TestBox Integration
    • Test Runner
    • Test Watcher
  • Developing For CommandBox
    • Modules
      • Installation and Locations
      • Configuration
        • Public Properties
        • Configure() Method
        • Lifecycle Methods
      • Conventions
      • User Settings
      • Linking Modules
    • Commands
      • Aliases
      • Using Parameters
        • Using File Globs
        • Dynamic Parameters
      • Command Output
      • Tab Completion & Help
      • Interactivity
      • Watchers
      • Shell integration
      • Running Other Commands
      • Error handling
      • Watchers
      • Loading Ad hoc Jars
    • Interceptors
      • Core Interception Points
        • CLI Lifecycle
        • Command Execution Lifecycle
        • Module Lifecycle
        • Server Lifecycle
        • Error Handling
        • Package Lifecycle
      • Custom Interception Points
    • Injection DSL
    • Example Project
    • FusionReactor for the CLI
  • ForgeBox Enterprise
    • Introduction
    • Storage
    • Commands
      • List
      • Register
      • Login
      • Set Default
      • Remove
    • Usage
Powered by GitBook
On this page
  • Lucee allows datasource to be a struct
  • Another method
  • Notes

Was this helpful?

Edit on GitHub
Export as PDF
  1. Usage
  2. Execution
  3. CFML Files

Using a DB in CFML scripts

PreviousCFML FilesNextOS Binaries

Last updated 2 years ago

Was this helpful?

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. It would considered poor form anyway since standalone scripts are best if they're self-contained and don't have external dependencies like server settings necessary to run.

Lucee allows datasource to be a struct

So the easiest way to accomplish 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 _definition_ of the datasource. This will create an on-the-fly connection to your database without any server config being necessary which is perfect for a stand-alone script. Here is what that looks like. Note, I'm 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

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 let's 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 datasources. 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 encrypted passwords above. You can also just put the plain text password in. Just omit the `encrypted:` text like so:

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

There are a couple tags inside Lucee that don't support this just yet. `<CFDBInfo>` is one of them. [ In this case, you need a "proper" datasource defined that you can reference 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.

Ticket Here]