ColdFusion – CFchimp

How to use the Microsoft JDBC Driver for SQL Server in ColdFusion

December 20, 2023 by Chris Simmons

I recently had an issue where the datasources using the Microsoft SQL Server Driver in ColdFusion were failing. The error was:

java.sql.SQLException: Timed out trying to establish connection

There was no change with the database server. For some reason the driver was just not connecting. This led to an exploration of connecting using JDBC both with the Adobe jar included with ColdFusion and by downloading the Microsoft JDBC Driver for SQL Server.

Using the Microsoft JDBC Driver for SQL Server

First you must obtain the driver and make it available to ColdFusion:

Download Microsoft JDBC Driver for SQL Server from: https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16
Unzip the download and place the driver jar(s) into the following directory on the server (for Windows): C:\ColdFusion2021\cfusion\lib
Restart the ColdFusion service

To create a JDBC data source to connect to an MS SQL Server database in ColdFusion:

Login to CFADMIN
Navigate to the Data & Services tab in CFADMIN
Enter a Datasource Name: developmentServerJDBC
For Driver choose: Other
Click: Add

On the ensuing page enter the additional information (change to your info):

CF Data Source Name: developmentServerJDBC
JDBC URL: jdbc:sqlserver://developmentServer:databaseName=developmentDatabase;Port=1433;encrypt=false;
Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Driver Name: mssql-jdbc
User name: developmentUser
Password: ************
Description (optional): Uses Microsoft jar file

Using ColdFusion’s Microsoft SQL Server Driver

To create a data source to connect to an MS SQL Server database in ColdFusion:

Login to CFADMIN
Navigate to the Data & Services tab in CFADMIN
Enter a Datasource Name: developmentServer
For Driver choose: Microsoft SQL Server
Click: Add

On the ensuing page enter the additional information (change to your info):

CF Data Source Name: developmentServer
Database: developmentDatabase
Server: developmentServer
Port 1433
User name: developmentUser
Password: ************
Description (optional): Uses ColdFusion's Microsoft SQL Server Driver

BONUS: Using ColdFusion’s Microsoft SQL Server Driver with JDBC

To create a JDBC data source to connect to an MS SQL Server database in ColdFusion:

Login to CFADMIN
Navigate to the Data & Services tab in CFADMIN
Enter a Datasource Name: developmentServerMicrosoftJDBC
For Driver choose: Other
Click: Add

On the ensuing page enter the additional information (change to your info):

CF Data Source Name: developmentServerMicrosoftJDBC
JDBC URL: jdbc:sqlserver://developmentServer:databaseName=developmentDatabase;Port=1433;encrypt=false;
Driver Class: macromedia.jdbc.MacromediaDriver
Driver Name: macromedia-jdbc
User name: developmentUser
Password: ************
Description (optional): Uses Adobe jar file

ACF Bug CF-4219847 (GENERATEDKEY and IDENTITYCOL are not returned)

December 20, 2023November 17, 2023 by Chris Simmons

ACF Bug CF-4219847 (GENERATEDKEY and IDENTITYCOL are not returned when performing an insert using MSSQL Symmetric Keys)

Filed as ACF bug CF-4219847.

This is not a bug in Lucee and functions as expected.

This demo illustrates that GENERATEDKEY and IDENTITYCOL are not returned when performing an insert using MSSQL Symmetric Keys.

ColdFusion 2023

ColdFusion 2021

Lucee 6

Code to Reproduce:

ACF Bug CF-4219348 (cfdirectory Filter: The importance of filter order)

December 5, 2023September 18, 2023 by Chris Simmons

UPDATE

This bug has been FIXED by Adobe in Build No: 2023.0.0.330651!

Filed as ACF bug CF-4219348.

This is not a bug in Lucee and functions as expected.

This demo illustrates the importance of filter order for cfdirectory in ACF.

A directory contains the following files:

this-is-a-doc.doc
this-is-a-docm.docm
this-is-a-docx.docx
this-is-a-xls.xls
this-is-a-xlsm.xlsm
this-is-a-xlsx.xlsx

The filter attribute of cfdirectory accepts a pipe delimited list. When a 3 digit file extension precedes a 4 digit file extension in the filter attribute of cfdirectory (and the extensions share the same first 3 characters) only files with the 3 digit extension are included. You could use an astericks () on the front and the back of the extension (.doc*) to retrieve both .doc and .docx, but this could also include additional files you don’t want to retrieve such as .docm files. The same is true for .xls, .xlsx, and .xlsm files.

Option 1: Filter (.doc|.docx|.xls|.xlsx): Returns 2 files: this-is-a-doc.doc, this-is-a-xls.xls

Option 2: Filter (.docx|.doc|.xlsx|.xls): Returns 4 files: this-is-a-doc.doc, this-is-a-docx.docx, this-is-a-xls.xls, this-is-a-xlsx.xlsx

Option 3: Filter(.doc|.xls): Returns 6 files: this-is-a-doc.doc, this-is-a-docm.docm, this-is-a-docx.docx, this-is-a-xls.xls, this-is-a-xlsm.xlsm, this-is-a-xlsx.xlsx

Code to Reproduce:

Using cfpm in CommandBox to List packages, Install a package, Export package list

September 18, 2023February 8, 2023 by Chris Simmons

Method 1

This example assumes you are running ColdFusion 2021 via CommandBox.

Launch CommandBox and run the following command(s):

> set CFPM_SERVER=YOUR_SERVER_NAME
> cfpm list
> cfpm install <cfpackagename>
> cfpm export path/to/packages.txt
> env clear CFPM_SERVER

Method 2

This example assumes you are NOT running ColdFusion via CommandBox. It assumes you are running a default installation of ColdFusion 2021 (Path is for Windows).

Launch CommandBox and run the following command(s):

> !C:\ColdFusion2021\cfusion\bin\cfpm.bat list
> !C:\ColdFusion2021\cfusion\bin\cfpm.bat install <cfpackagename>
> !C:\ColdFusion2021\cfusion\bin\cfpm.bat C:\path\to\packages.txt

Run CommandBox directly inside VSCode Terminal

September 18, 2023August 17, 2022 by Chris Simmons

Initially I looked at the CommandBox documentation for running it inside VSCode: https://commandbox.ortusbooks.com/ide-integrations/visual-studio-code. However, the Shell Launcher extension was deprecated in favor of Terminal Profiles in the Integrated Terminal (VSCode >= v1.55). See this article: https://code.visualstudio.com/updates/v1_55#_terminal-profiles.

Set up a Terminal Profile for CommandBox:

Open VSCode Preferences > Settings
Search for terminal.integrated.profiles.osx (Replace osx with windows or linux based on your os)
Click “edit in settings.json”
Add the following under terminal.integrated.profiles.osx

    "CommandBox": {
        "source": "path/to/box"
    }

Close and save settings.json

Launch CommandBox from VSCode terminal

Press Cmd + Shift + P or Ctrl + Shift + P to launch the Command Palette
Begin typing Terminal
When you see Create New Terminal (With Profile) press Enter
Under Select the terminal profile to create you should see the CommandBox profile you created. Select it with the down arrow key and press Enter
CommandBox should launch in the terminal window

Atom support for Emmet in ColdFusion .cfm and .cfc files

September 18, 2023May 17, 2016 by Chris Simmons

I’m late to the party, but I’m trying out a new editor: GitHub’s Atom. My mission when I try out a new editor is to see if I can get the following 3 items set up properly (because if I can’t the editor is unfortunately not going to work for me).

Language Support for ColdFusion
An Emmet package
Support for Emmet functionality within a .cfm, .cfc file

Setting up Language support for ColdFusion is easy. Simply install the language-cfml package. To install a package in Atom:

From the Atom editor menu, navigate to Atom -> Preferences
Click the Install button
Type language-cfml in the Search Packages field and click the Packages button
Click the Install button for the language-cfml package

Setting up Emmet is easy. Simply install the emmet package (see package install instructions above).

Setting up Emmet support for ColdFusion .cfm and .cfc files requires editing your Keymap. This step was derived from the emmet-atom Tab key documentation

Open the Keymap file (keymap.cson): Atom > Keymap...
Add the following to the keymap.cson file (proper indention counts):

'atom-text-editor[data-grammar="text html cfml"]:not([mini])':
    'tab': 'emmet:expand-abbreviation-with-tab'

Processing ColdFusion using .htm and .html files with Lucee

September 18, 2023February 11, 2015 by Chris Simmons

If you want Lucee to use the .htm and .html file extensions instead of (or in addition to) .cfm and .cfml you can set this up in 3 quick steps.

1. Stop Lucee
2. Edit the web.xml file located at

/conf/web.xml

From:


     CFMLServlet
     *.cfc
     *.cfm
     *.cfml
     /index.cfc/*
     /index.cfm/*
     /index.cfml/*

To:


     CFMLServlet
     *.cfc
     *.cfm
     *.cfml
     *.htm
     *.html
     /index.cfc/*
     /index.cfm/*
     /index.cfml/*

3. Start Lucee

Connecting ColdFusion 10 to FoxPro 9

September 18, 2023October 14, 2014 by Chris Simmons

My company has a legacy application built in FoxPro 9 which contains a table that I need to extract data from on a recurring basis. I have never worked with FoxPro. Apparently in FoxPro each table has a corresponding .dbf file. For this example we will say that the table file is persons.dbf, which resides at C:\legacyapp\data\.

The first step for connecting ColdFusion to the FoxPro database was to find an acceptable driver. I initially tried to use some ODBC drivers but abandoned this route. Not only was it just not working, but I do my development on a Mac (whereas production is Windows) and I really wanted a solution that did not involve a Windows and a Mac configuration.

So the following steps describe how I set up a jdbc connection to FoxPro 9 from ColdFusion.

Download the StelsDBF JDBC Driver
Place the dbfdriver.jar file in {ColdFusion-Home}/cfusion/lib aka C:\ColdFusion10\cfusion\lib\
Restart the ColdFusion services
Login to the CFADMIN and set up a Data Source
- For ‘Data Source Name’ enter ‘legacyapp’
- For ‘Driver’ select ‘Other’
- Click ‘Add’
- For JDBC URL enter ‘jdbc:jstels:dbf:c:/legacyapp/data’
- For Driver Class enter ‘jstels.jdbc.dbf.DBFDriver2’
- Click ‘Submit’
When using cfquery to select data use the file name as the table name:
```
SELECT 
    LASTNAME, FIRSTNAME
FROM
    persons
```

It was actually not too bad to set up once I located the driver.

Using a ColdFusion Ternary Operator for an Optional Tag Attribute

September 18, 2023February 7, 2014 by Chris Simmons

Today I had to write a script to process a form and send an email as part of the processing. The form allows the user to specify an email address to bcc, but it’s not required. I’m a big fan of using the Ternary Operator for doing either/or stuff like css classes. So I decided to extend that fandom to set the bcc attribute of the <cfmail> tag if the email specified in the form was valid.

Assumptions for this example:

1. You have validated form.bccEmail as a valid email.
2. You have a default mail server specified in Application.cfc or CFADMIN.
3. You may see more utility in using the Ternary Operator for other conditional cases.

The “magic” is in the bcc attribute below:

    
	    Message Body here...

ColdFusion function returns space before value

September 18, 2023March 5, 2013 by Chris Simmons

I had an annoying issue this morning where a function I wrote that rounds and formats values for use in an internal financial app kept sticking a single space before the value returned. This was obviously not good for a financial app. After spending 10-15 minutes tearing the function’s innards apart it turns out what was going on in the function was not the cause. The cause was simple.

In a tag based ColdFusion function you need to be sure to include the ‘output=”no”‘ attribute.

 
     ... MAGIC STUFF HERE ...