Using Custom Tags for Migration from Older Versions to ColdFusion 2023
When working on migration projects to ColdFusion 2023, one of the most common updates is to bulk replace instances of ‘cf_sql_int’ with ‘cf_sql_integer’. In some projects, data validation for variables may have been overlooked, such as using float data or commas in numeric values.
To address these issues comprehensively, we can employ custom tags like ‘cf_query’ and ‘cf_queryparam’ to handle required conversions and data validation. An excellent reference for this approach is Adam Cameron’s 2012 article on ‘Custom tags: nesting’: https://blog.adamcameron.me/2012/11/custom-tags-nesting.html
Below is a code snippet inspired by Cameron’s work, specifically focusing on modifying the ‘cf_queryparam’ custom tags:
<cfscript>
// if we’re here, we’ve got a legit queryparam tag
if (thisTag.executionMode == “START”){
include “helpers.cfm”; // this just abstracts out some mocked functionality to keep this file to-the-point
validateParam(attributes); // (UDF) will raise an exception if everything ain’t legit for this param
// the query tag will have worked out which DB we’re dealing with, so get a DB-specific object to help “translate” the param from CF-speak to JDBC-speak
dbConnector = getBaseTagData(“cf_query”).dbConnector;
jdbcParam = dbConnector.createJdbcParam(attributes); // (UDF)
// CFASSOCIATE is not as granular as it could be, so get rid of ALL the attributes and just pop back in the one we actually want to give back to the calling code
attributes = jdbcParam;
if (attributes.CFSQLTYPE == “CF_SQL_INT”){
attributes.CFSQLTYPE = “CF_SQL_INTEGER”;
}
if (attributes.CFSQLTYPE == “CF_SQL_INTEGER”){
attributes.VALUE = int(ReReplaceNoCase(attributes.VALUE,”[^0-9.]”,””,”ALL”));
}
cfassociate(basetag=”cf_query”, datacollection=”queryparams”); // (UDF)
// put in a parameter placeholder in place of the tag
writeOutput(“?”);
}
// there is no closing tag, so no “ELSE”
</cfscript>
We use this code to update deprecated ‘queryparm’ value:
if (attributes.CFSQLTYPE == “CF_SQL_INT”){
attributes.CFSQLTYPE = “CF_SQL_INTEGER”;
}
This is for data manipulation/validation:
if (attributes.CFSQLTYPE == “CF_SQL_INTEGER”){
attributes.VALUE = int(ReReplaceNoCase(attributes.VALUE,”[^0-9.]”,””,”ALL”));
}
This solution can be extended to handle other data issues or conversions before executing database queries or even creating custom ‘queryparam’ types.