Eleven Coldfusion-ish tips from the field

I’ve had this running list of Coldfusion tips on my wall for the last few years and it’s time to get these online.  All of the items in this list came from Coldfusion projects over the last few years, but a good portion of these could easily be considered tips for server programmers.  I definitely run into the same items when programming Asp.NET.

There is no rhyme or reason here, just some things I felt need to be repeated.

1. PreserveSingleQuotes()

This one came in really handy on a project requiring large text files to be imported into a MySQL database.  I used Coldfusion to upload and read the files into large INSERT chunks using MySQL’s multi – row INSERT syntax. Code built the VALUES portion of the SQL, then I just fed the data into a function for insertion.

[sql]
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
[/sql]
[coldfusion]
<cfquery name="insert_data" result="insert_result" DATASOURCE="#request.dsn#" USERNAME="#request.dbuser#" PASSWORD="#request.dbpswd#">
INSERT INTO table
( column1, column2, column3, column4, column5, column6, column7 )
VALUES
#PreserveSingleQuotes( insert_values )#
</cfquery>
[/coldfusion]

2. What if GENERATED_KEY doesn’t work?

If you’re using MySQL and the GENERATED_KEY property of your cfquery objects isn’t populating, you can use LAST_INSERT_ID instead.

[sql]SELECT LAST_INSERT_ID();[/sql]

3. Use ArrayAppend when building strings

Classic performance tuning tip for just about any programming language.  Here I’ll give a Coldfusion example and keep it dead simple.  If you ever have to concatenate strings in code, user ArrayAppend instead.  Here are two loops that do the same thing.  If you run this code, you should notice loop1 takes forever, and loop2 is smoking fast.

slow…..

[coldfusion]
<cfscript>
xx = 100000;
insertString = "";

// do the loop
while( xx > 0 ) {
insertString &= xx & " ";
xx–;
}

WriteOutput( insertString);
</cfscript>
[/coldfusion]

FAST!

[coldfusion]
<cfscript>
xx = 1000000;
insertArray = ArrayNew(1);
// do the loop
while( xx > 0 ) {
ArrayAppend( insertArray, xx & " " );
xx–;
}

WriteOutput( ArrayToList( insertArray, " " ) );
ArrayClear( insertArray );
</cfscript>
[/coldfusion]

4. If CSV, then CHR

This one is simple, if you find yourself creating CSV or any other text file, use special characters when dealing with single and double quotes, etc.

  • chr(9) = Tab
  • chr(34) = ” double quote
  • chr(39) = ‘ single quote

And if you’re not sure of the correct code for the character you’re looking to use, just wrap that character in ASC() and WriteOutput to the page.

5. Use CFMail with GMail

This is a no brainer, but with how difficult sending email can be with other languages, I’m mentioning it here.

Application.cfc

[coldfusion]
<cfscript>
APPLICATION.mail.server = "smtp.gmail.com";
APPLICATION.mail.port = "465";
APPLICATION.mail.ssl = true;
APPLICATION.mail.user = "gmailAccount";
APPLICATION.mail.pswd = "gmailPasssword";
</cfscript>
[/coldfusion]

Emailer.cfm

[coldfusion]
<cfmail to="work@ericfickes.com"
bcc=""
from="web@master.com"
subject="sending mail is easy with Coldfusion"
server="#application.mail.server#"
useSSL="#application.mail.ssl#"
port="#application.mail.port#"
username="#application.mail.user#"
password="#application.mail.pswd#">
#emailBody#
</cfmail>
[/coldfusion]

6. CFSCRIPT doesn’t know NULL?

Another tip from the land of importing and exporting data.  While working with query objects in CFScript, for some reason I could never accurately detect for NULL values.  I tried all sorts of detection schemes and ended up just writing a hacky fail safe.  Please, if you have a better suggestion for *easy* NULL detection in CFSCript, add it in the comments below.

utils.cfc

[coldfusion]
<!— Simple value getter with try / catch to get around NULL values
This function originated in a script where we always needed a " " even if
the value from the database was null.
—>
<cffunction name="qryGetString" access="public" returntype="string">

<cfargument name="data" type="string">

<cftry>
<cfscript>
return #data# & " ";
</cfscript>

<cfcatch type="Any">
<cfreturn " "/>
</cfcatch>
</cftry>

</cffunction>
[/coldfusion]

Exporter.cfm

[coldfusion]
// largeish loop
tab = chr(9);
for( xx = 1; xx <= queryObj.RecordCount; xx++ )
{
// start the row
this_row = utils_cfc.qryGetString( queryObj.FirstName[xx] ) & tab &
utils_cfc.qryGetString( queryObj.MiddleName[xx] ) & tab &
utils_cfc.qryGetString( queryObj.LastName[xx] ) & tab &
utils_cfc.qryGetString( queryObj.Suffix[xx] ) & tab &
utils_cfc.qryGetString( queryObj.MedicalTitle[xx] ) & tab &
utils_cfc.qryGetString( queryObj.email[xx] ) & tab &
utils_cfc.qryGetString( queryObj.practice_phone[xx] ) & tab &
utils_cfc.qryGetString( queryObj.practice_fax[xx] ) & tab &
utils_cfc.qryGetString( queryObj.practice_name[xx] ) & tab &
utils_cfc.qryGetString( queryObj.practice_address1[xx] ) & tab &
utils_cfc.qryGetString( queryObj.practice_address2[xx] ) & tab &
utils_cfc.qryGetString( queryObj.practice_city[xx] ) & tab &
utils_cfc.qryGetString( queryObj.practice_state[xx] ) & tab &
utils_cfc.qryGetString( queryObj.practice_zipcode[xx] ) & tab &
utils_cfc.qryGetString( queryObj.hospital_affiliation[xx] ) & tab;

// do stuff with the data
}
[/coldfusion]

7. How I find list items

Ever notice the different behavior in the Coldfusion ListFind commands?  I ended up writing my own ListHasValue function in order to find exact pattern matching in a list.  I had a list of role ids in a list, and just couldn’t get the built in functions to tell me when my id was in the list without also matching on other ids.  This one makes sense when you run some code.

The top of this sample as my custom ListHasValue() command, and the lower half does three simple loops counting from 1 to 100, and using ListFind, ListContains, and ListHasValue for number checking against the same list.

[coldfusion]
<!—
Use this to do an exact pattern check for a value in a list.
This is useful inside of a loop checking numbers against a number list.

EX :

list = "91, 92"

if you loop from 1 – 100, ListFind and related CF functions will match
on 1, 2, and 9. Not just 91 and 92

Use this when you’re looping and you ONLY want to match on 91, or 92

—>
<cffunction name="ListHasValue" access="public" returntype="boolean">

<cfargument name="list" required="yes" type="string">
<cfargument name="value" required="yes" type="any">

<cfscript>
// clean up to be safe
list = trim( toString( list ) );

// check to see if we have a *possible* match
position = ListContains( list, value ) ;

if( position > 0 )
{
// NOTE : KEEP THE TRIM AND TOSTRING
found_value = trim( toString( ListGetAt( list, position ) ) );

if( Compare( value, found_value ) == 0 )
{
return true;
}
}

// no match for you!
return false;
</cfscript>

</cffunction>

<cfscript>
list = "1, 11, 21, 31, 41, 51, 61, 71, 81, 91, 99";
xx = 1;

WriteOutput( "List = " & list & "<hr>");

while( xx < 100 ) {
if( ListContains( list, xx ) > 0 )
{
WriteOutput( "ListContains found " & xx & "<br>" );
}
xx++;
}

WriteOutput("<hr />");

xx = 1;
while( xx < 100 ) {
// ListFind
if( ListFind( list, xx ) > 0 )
{
WriteOutput( "ListFind found " & xx & "<br>" );
}
xx++;
}

WriteOutput("<hr />");

xx = 1;
while( xx < 100 ) {
// Eric’s ListHasValue
if( ListHasValue( list, xx ) )
{
WriteOutput( "ListHasValue found " & xx & "<br>" );
}
xx++;
}
</cfscript>
[/coldfusion]

If you run this code on your Coldfusion server, you should notice the following results.  ListContains matches single digits from the loop that do not really exist in the list.  ListFind only finds the number 1?  And finally, my function does exactly what I needed it to do.  Tell me when a specific number exists in a list.

CF ListFind function comparison

Coldfusion ListFind functions don't always behave how I want them to


8. Make PDFs faster

This could easily be it’s own topic, but I’ll say one thing about making PDFs faster with CFDocument.  Only put final content between <cfdocument> and </cfdocument>.  That is, if you have any processing code, cfqueries, cfloops, inside of your cfdocument tag, your cfml page is running slower than it needs to be.  Here’s a simple example of one of my cfml pages that has only final content in the cfdocument tags.

The key to this example is moving all of my content creation code into an external file, then including at the top of my page. I always do a check for my main PDF_BODY variable, and then spit out my PDF document.

[coldfusion]
<cfinclude template="code/export_pdf_codefile.cfm">

<cfif PDF_BODY NEQ "">

<cfdocument name="provider_profile"
format="PDF"
pagetype="A4"
mimetype="application/pdf"
orientation="portrait"
margintop="0"
marginbottom="0.2"
marginleft="0.2"
marginright="0.2"
>
<cfoutput>#PDF_BODY#</cfoutput>
</cfdocument>

<!— send directly to client —>
<cfheader name="Content-Disposition" value="attachment; filename=#filename#">
<cfcontent type="application/pdf" variable="#provider_profile#">

<cfelse>
No PDF content found
</cfif>
[/coldfusion]

9. Use parameterized queries

This is a tip for all server side programmers whether you use Coldfusion, ASP, JSP, PBJ.  Use parameterized queries when doing any database interaction.  It’s too easy not to use, and you get protection from SQL Injection, as well as enforcing proper data types when speaking to your database.  This is something all server programmers should do regardless of your language, the sample below is for Coldfusion.

BAD

[coldfusion]
<cfquery name="tblInsert" datasource="myDb">
INSERT INTO myTable
( col1, col2, col3, col4 )
VALUES
( ‘#Form.field1#’, ‘#Form.field2#’, ‘#Form.field3#’ )
</cfquery>
[/coldfusion]

GOOD

[coldfusion]
<cfset val1 = Form.field1>
<cfset val2 = Form.field1>
<cfset val3 = Form.field1>

<cfquery name="tblInsert" datasource="myDb">
INSERT INTO myTable
( col1, col2, col3, col4 )
VALUES
(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#val1#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#val2#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#val3#" />
)
</cfquery>
[/coldfusion]

10. Where’d the time go?

For the good programmers already using parameterized queries, ever insert a timestamp into your database and find out the date is correct, but the time is always 12:00:00?  Take a closer look at the cfsqltype in your cfqueryparam, I had this exact problem and here’s what happened.

Using cf_sql_date does not include the full date and timestamp, just the date.

[coldfusion]
<cfqueryparam cfsqltype="cf_sql_date" value="#paymentDate#" />
[/coldfusion]

 

Using cf_sql_timestamp includes the full date and timestamp I was looking for.

[coldfusion]
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#paymentDate#" />
[/coldfusion]

11. Stored Procedures are a little different

This last one isn’t much of a tip, but more of a reminder to myself.  I do so much database work that stored procedures are just queries to me, but not so to Coldfusion and the CFQuery tag.  If you want to get data from a stored procedure, you need to use the CFStoredproc tag.  Here’s a sample of passing one argument into a stored procedure, and how to get the resulting data.

[coldfusion]
<cfstoredproc datasource="myDb" procedure="GetDataFaster">

<cfprocparam type="in" cfsqltype="cf_sql_integer" value="#inputVar#" />

<!— specify sproc result here, cfstoredproc res != returned recordset —>
<cfprocresult name = sprocResult>

</cfstoredproc>

<cfreturn #sprocResult.ColumnFromQuery#>
[/coldfusion]

10 thoughts on “Eleven Coldfusion-ish tips from the field

  1. Eric, very nice set of tips..

    Can you give some advice on automatically adding a range of IP's into MySQL database?

    I have a Form called addIPs.cfm with the following text fields:
    – StartIP
    – EndIP
    – Status
    – Group

    How do I use your Insert Code above to "loop thru" and add the "range of IP's", where for example the
    – Form.StartIP = 10.1.5.0
    – Form.EndIP = 10.1.5.255
    – Form.Status = 1
    – Form.Group = 8

    • Hey jlig, this could really go a number of ways, but here’s one possible solution. This code takes your ip range and creates one large INSERT statement using MySQL’s multi-value insert feature. Hopefully this helps in some way. I’ve broken it out into two simple files.

      form.cfm
      [coldfusion]
      < form action="handler.cfm" method="post" >
      StartIP <input type="text" name="startIP" value="10.1.5.0" />
      <br /><br />

      EndIP <input type="text" name="endIP" value="10.1.5.255" />
      <br /><br />

      Status <select name="ipStatus">
      <option>1</option>
      <option>2</option>
      <option>3</option>
      <option>4</option>
      </select>
      <br /><br />

      Group <input type="text" name="groupName" value="workgroup" />
      <br /><br />

      <input type="submit" value=" GO " />
      </form>
      [/coldfusion]

      handler.cfm
      [coldfusion]
      <cfscript>
      // NOTE : just assuming all data is correct from the form
      startIP = form["startIP"];
      endIP = form["endIP"];
      ipStatus = form["ipStatus"];
      groupName = form["groupName"];

      // setup vars for loop processing
      xx = listToArray( startIp, ".")[4]; // first IP in range
      _endBit = listToArray( endIp, ".")[4]; // last IP in range
      _ipPrefix = Replace( startIp, ".#xx#", "" ); // trim off the final .999 to get ipPrefix
      insertArray = ArrayNew(1); // temp array to hold insert values

      // loop through ip range and create VALUES list for SQL INSERT
      while( xx <= _endBit )
      {
      // append each row’s value to array
      ArrayAppend( insertArray, "( ‘#groupName#’, ‘#_ipPrefix#.#xx#’, #ipStatus# )" );
      xx++;
      }
      // convert array to comma delimited list
      insert_values = ArrayToList( insertArray, "," );

      // cleanup
      ArrayClear( insertArray );
      </cfscript>

      <cfoutput>
      < cfquery name="insert_data" result="insert_result" DATASOURCE="#request.dsn#" USERNAME="#request.dbuser#" PASSWORD="#request.dbpswd#" >
      INSERT INTO GROUP_IPS ( group_id, ip_address, status )
      VALUES
      #PreserveSingleQuotes( insert_values )#
      </ cfquery >
      </ cfoutput >
      [/coldfusion]

      • Eric, This worked perfectly..! and I learned a bit more about Arrays & Loops..
        Here is an image of my form: http://cerberus.clearwave.com/jerry/ipAddForm.jpg

        But one question:
        – Is it possible to add a second Array to this code so that a field called (ipOrdinal) gets filled as well?
        – ipOrdinal is an INT value from 0 to 255
        – It is equal to the value of the last part of the IP address (fourth octet)
        – For example, when the code runs, it would insert the "Range of IP's" as well as the "Range of ipOrdinals) It would not need to be listed on the add form, as it could just get it's value from the IP's..

        ipAddress ipOrdinal
        10.1.4.0 0
        10.1.4.1 1
        10.1.4.2 2

        Thanks again,
        jlig

      • If you're looking to store each ordinal in a 2nd array, that should be a simple addition. Create a new array under this line "insertArray = ArrayNew(1); // temp array to hold insert values" for your ordinals. Then under this line "ArrayAppend( insertArray, "( '#groupName#', '#_ipPrefix#.#xx#', #ipStatus# )" ); ", put the same thing only for your new array.

        Something like this "ArrayAppend( ordinalArray, #xx# ); ".

        Hope that helps.

      • Yes, you created the Array correctly. However, you created a 2 dimensional array when you probably only need a single dimension array.

        That is, I would change line 97 to read like this : "ordinalArray = ArrayNew(1);"

        As for your error about column count not matching, that's exactly what the problem is. Look at your INSERT statement. You have five columns listed, but the values only contain four columns. It appears that you've added ipOrdinal to the INSERT statement, but not tot he VALUES portion of the statement. Hope that makes sense.

  2. Eric, See my updated code & errors..
    – Looking at the SQL error, It looks like the Ordinal values are not being passed properly to the query..
    – In trying to understand your Array, I do not see how the 0 thru 255 values are getting down to the Insert Query?
    – I would think that I need to add something to this section..?
    // convert array to comma delimited list
    insert_values = ArrayToList( insertArray, "," );

    Here is my updated code: http://cerberus.clearwave.com/jerry/Array_Image2….
    Here is the error that I'm getting: http://cerberus.clearwave.com/jerry/Array_Image_E

    Thanks again,
    jlig

  3. Eric, I figured out my problem & everything is working fine!
    Thanks so much for your excellent code & assistance..

    – I ended up not even needing the second Array, I just added the #xx# variable to the end of the first Array.
    – Then added the extra "ipOrdinal" Value to my Insert Query

    Here is my updated code: http://cerberus.clearwave.com/jerry/Array_Image_C

    Thanks again,
    jlig

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s