Upgrading to ColdFusion 2021

 If you're thinking of upgrading to CF21 and you use cfspreadsheet in your code, don't.

Cfspreadsheet, when doing read, randomly locks files anywhere from minutes to hours. The only way to get rid of the lock if you want to move or delete the file is to restart the main instance even if the file was locked by another instance.

On July 22nd, Adobe said that the issue is fixed in Update 2, but gave no eta from when Update 2 will be released.

https://tracker.adobe.com/#/view/CF-4212149

ColdFusion - using a colon in sql with queryexecute

queryexecute doesn't seem to allow escaping colons in sql.
Using MySQL regexp character classes will throw an error if entered in the sql directly.
A workaround is to add the regex expression as a queryparam.

The following will throw an error:
queryexecute("SELECT [column] REGEXP '[[:character-class:]]'...");

Here's the workaround:
queryexecute("SELECT [column] REGEXP :regex...",{regex:{value:"'[[:character-class:]]'"}});

ColdFusion email validation regex timeout

Once upon a time, before ColdFusion 9 introduced isvalid(), I made my own function using regex I found online for validating emails.

([A-Za-z0-9]|.|_|\-)+@([A-Za-z0-9]|_|\-)+[.]{1}([A-Za-z]|.|_|\-)+

Validation worked fine. Sometimes the form was slow, for seemingly no reason with there being no queries or anything which would slow things down. As it was occasional and seemingly unreplicatable, I ignored it.
Yesterday, a particular form, which was prepopped with a new member's email, stopped working. After submit, and a few minutes waiting, there was a timeout error with error pointing at cfmail. Commenting out cfmail just moved the line reported to a cfoutput tag. After some collaborative troubleshooting, we figured out that the issue was the long email.
I replace my custom function with isvalid(), which fixed the problem. However, the timeout still made no sense, so I took a close look at the regex I was using. The issue was that the + was outside the parenthesis, instead of inside. The longer the email, the longer ColdFusion would spend capturing all the possible matches. Adding ?: did not help. Moving the + inside, did. This is the correct regex to use if not using isvalid().

([A-Za-z0-9\._\-]+)@([A-Za-z0-9_\-]+)[.]{1}([A-Za-z\._\-]+)

ColdFusion: Passing encrypted values in url

If you're using encrypt / decrypt on url variables, urlencodedformat doesn't always work. Instead, use tobase64() after encoding and tostring(tobinary()) before decoding. This will ensure that only alphanumeric characters are passed in the url.

Undocumented features in ColdFusion 10

1. If you're using url rewrite, your cgi.path_info will be empty.
2. cffile and fileupload are now not supporting wildcards for mime type.  image/* will now throw an error , The MIME type or the Extension of the uploaded file image/jpeg was not accepted by the server, if a jpeg is uploaded.
3. cfimage info will now throw an error if you try to use it with https.

ColdFusion: Capitalize every word using regex

Forget about loops, left, right, mid and writing multiline custom functions.  Since ColdFusion MX, all you need is rereplace.
To capitalize ever word in a string: rereplace(string,"\b(\w)","\u\1","all")

ColdFusion - Query column overwrites URL structure

Just ran into an interesting bug. If you have a cfloop of a query which has a column named url, it will overwrite the url struct but only when using conditionals.

For output, query.url and url.urlparam output correctly. However, using isdefined("url.urlparam") returns false, while using structkeyexists(url,"urlparam") throws an error about trying to use a string as a structure with members.

Circularly stepping through query, array or array of arrays using mod

Let's say you have a menu with a "next" link and would like to step through the menu in a way that when you're on the last item, clicking next will bring you back to the top.  You can use mod, instead of if/else and, in most cases, have everything on only one line.

Stepping through a list of patterns returned by a query:

request.next = "?pattern=" & q_patterns.id[listfindnocase(valuelist(q_patterns.id),url.pattern) mod q_patterns.recordcount + 1]

Stepping through an array of keywords:

request.next = "?keywords=" & arrsearch[listfindnocase(arraytolist(arrsearch),url.keywords) mod arraylen(arrsearch) +1]

Stepping through an array of arrays of secondary categories:

for (i=1; i<=arraylen(arrcat); i++) {
     catposition = listfindnocase(arraytolist(arrcat[i]),url.secondary);
     if (catposition)
           request.next = "?secondary=" & arrcat[i][catposition mod arraylen(arrcat[i])+1]
}

ColdFusion - Breaking output into columns using mod

Say you have a long list which you want to output, but you want to break it up into several columns. The way to do this is to use mod, which gives you the remainder from a division operation. The formula is very simple:

<ul><cfloop query="q_myquery">
<li>[output goes here]</li><cfif not q_myquery.currentrow mod ceiling(q_myquery.recordcount / [number of columns] )>
</ul>
<ul></cfif></cfloop>
</ul>

In this example, ul (unordered list) is used to create columns. You'll also need to set width and float in css, and can get rid of the bullets if they're not needed.

Make sure to put the statement after the main output, not before.

ColdFusion 9: ByteArray error

If you've upgraded to ColdFusion 9 with MySQL, be prepared to get a "ByteArray objects cannot be converted to strings." error. This error occurs because the JDBC driver for MySQL included with the ColdFusion 9 install converts TinyInt values to a Byte Array if they're used in a function or a CASE WHEN. You will not get this error if you're simply including a TinyInt column in a SELECT statement, but you will get it if you're using it inside a function or a CASE WHEN.

One solution is to encapsulate your function in a CONVERT([function] USING latin1), but you will have to do it in every query where this condition occurs.

Another solution is to revert to an earlier driver. ColdFusion 9 install includes mysql-connector-java-commercial-5.1.7-bin.jar. You can try downloading the latest driver from here, however, version 5.1.13 still had this bug. If you're still having a problem after downloading the newest driver, revert to the version which was included with ColdFusion 8, mysql-connector-java-commercial-5.0.5-bin.jar.

Fixing Cart Weaver's Archived Products page

By default, cw2 and cw3 only have search and pagination on the Active Products page. Archived Products page outputs all archived product. If you have a lot of products, this page becomes slow and useless.

To add search and paging to Archived Products page, edit cwincproductsearch.cfm and replace tbl_products.product_Archive = 0 with tbl_products.product_Archive = <cfif findnocase("archive",cgi.script_name)>1<cfelse>0</cfif> in the query at the top of the page and replace action="ProductActive.cfm" in form with action="<cfoutput>#request.ThisPage#</cfoutput>".

Now edit the productarchive.cfm and remove the rsProductsSearch query. Then add the following right below the divMainContent div:

<cfinclude template="CWIncProductSearch.cfm">

<cfoutput>#PagingLinks#</cfoutput>

ColdFusion - Getting the total for a query column

Here's how to get the total for any number column in a query without using loops:

total = arraysum(listtoarray(valuelist(query.column)));

Optional Parameters in cfscript functions

Ever get the The parameter [parameter] to function [function] is required but was not passed in?

Here's how to get around that:

function [function name]([param1]) {
       var [param2] = [default value];
       if (arraylen(arguments) gt 1)
              [param2] = arguments[2];
...
}

ColdFusion - Getting ID of inserted row

How to get ID of row inserted using CFQUERY:

MySQL:

<cfquery result="insertrow" datasource="#application.ds#">
INSERT INTO [table]([column1], [column2]...)
VALUES([value1], [value2]...)
</cfquery>
ID: insertrow.generated_key

T-SQL

<cfquery name="insertrow" datasource="#application.ds#">
INSERT INTO [table]([column1], [column2]...)
VALUES([value1], [value2]...)

SELECT @@IDENTITY AS id
</cfquery>
ID: insertrow.id

Notice that when using MySQL, you use result, while when using T-SQL you use name.

ColdFusion - Stripping tags with rereplacenocase

When you want to output a part of an RSS feed or content that contains tags, you'll want to at least strip out the image tags. If you know that all tags are lowercase, you can use rereplace, otherwise, use rereplacenocase.

To get rid of all tags:

rereplacenocase("<[^<]*>",variable,"all")

This regular expression searches for all patterns that begin with a <, have something in the middle that's not a < and end with a >.

To get rid of image tags only:

rereplacenocase("<img[^<]*>",variable,"all")

This expression is same as previous, except it searches for patterns beginning with <img.

To get rid of two or more specific tags:

rereplacenocase("<(img|/?div|br)[^<]*>",variable,"all")

This expression searches for patterns beginning with <img, <div, </div or <br. The reason I'm using * instead of a + is because closing tags don't have characters between the beginning pattern and the ending pattern.