[coldbox-4.1.0] Raw SQL with dynamic variables

Hi Folks,
complete newbie here, so please forgive me if I make any dumb mistakes or annoy anyone for any reason. As a rule, I try solve my own problems and answer my own questions. This time, I just can’t seem to get any closer no matter how much time I spend on it. So I’m asking for help. Thank you in advance for any insights you can share.

SITUATION
I have a number of legacy CFML sites that I want to convert to ColdBox. The existing sites use CFquery tags to retrieve product listings from a pre-existing MySQL database. The SQL code uses Application variables to dynamically fill in parts of the query with predefined “settings” sitewide. For example, setting a minimum price, or checking that the product is not in a restricted list. Here is a typical query in SQL:

SELECT productNumber, listPrice, productName
FROM inventory
WHERE listPrice > #var.min_price#
AND productType NOT IN (#var.restricted_list#)

In other cases, the URL and Form scopes are merged into the var scope, and query is done based on user input. For example:

SELECT *
FROM inventory
WHERE productNumber =

QUESTION
How can I use existing raw sql queries, like those above, in my event handlers?

Thank you for your time and attention!

You’ll most likely need to drop the “var” pattern. Not really because of ColdBox, but creating your own little “scopes” in Application.cfm and letting them just flow through to all your .cfm pages in the “variables” scope doesn’t really work in an OO app using Application.cfc. Mostly for good reason. It’s poor encapsulation to do that even though it was made popular years ago in FuseBox.

  1. Move your site-wide settings to your config/Coldbox.cfc config file in the settings struct.
  2. Inject the settings in any CFC (handler, or model) like so:
    proprerty name=“restricted_list” inject=“coldbox:setting:restricted_list”;
  3. Or access the settings in any handler, interceptor, layout, or view as
    #getSetting( “restricted_list” )#
    or #getSetting( name=“restricted_list”, defaultValue=“default if not exists” )#
  4. Form and URL scopes are merged into a struct called "rc" (request collection) and is passed into any handler, layout or view as.
    #rc.productNumber#
    or #event.getValue( “productNumber”, “default if not exists” )#

Thanks!

~Brad

ColdBox Platform Evangelist
Ortus Solutions, Corp

E-mail: brad@coldbox.org
ColdBox Platform: http://www.coldbox.org
Blog: http://www.codersrevolution.com

Wow, thank you so much Brad! This is very helpful. I’ve actually printed it out so I can keep referring to it while I work on creating a small proof of concept using services, DAO, and business objects following section 7.7 “Coding: Solo Style” of the ColdBox manual.

Thanks agin!
nt