mergeQbSqlBindings - ColdFusion/CFML UDF to be used with QB parameterized SQL string & binding array to generate reusable SQL

mergeQbSqlBindingsUDF.cfm

<cfscript>
/* 20221222 mergeQbSqlBindings UDF
by James Moberg / SunStar Media.
Tested w/MSSQL. To be used with QB parameterized SQL string & binding array to generate reusable SQL.
NOTE: Using Query Builder (QB) does not required ColdBox or CommandBox.
GIST: https://gist.github.com/JamoCA/bb681afd2eb1a0d6d380f3b714ccc138
TWEET: https://twitter.com/gamesover/status/1606008360976781312
GITHUB: https://github.com/coldbox-modules/qb
DOCS: https://qb.ortusbooks.com/query-builder/building-queries/parameters-and-bindings
USAGE: 
  sql_without_params = mergeQbSqlBindings(q.toSql(), q.getBindings());
*/
function mergeQbSqlBindings(required string sql, array bindings=[]) hint="Merges QB SQL string with bindings array data" {
	local.sql = javacast("string", arguments.sql);
	local.varcharTypes = ["CF_SQL_VARCHAR":0, "CF_SQL_LONGVARCHAR":0];
	local.nvarcharTypes = ["CF_SQL_NVARCHAR":0, "CF_SQL_LONGNVARCHAR":0, "CF_SQL_SQLXML":0, "CF_SQL_NCHAR":0];
	local.numericTypes = ["CF_SQL_BIGINT":0, "CF_SQL_BIT":0, "CF_SQL_DECIMAL":0, "CF_SQL_DOUBLE":0, "CF_SQL_FLOAT":0, "CF_SQL_INTEGER":0, "CF_SQL_MONEY4":0, "CF_SQL_MONEY":0, "CF_SQL_NUMERIC":0, "CF_SQL_REAL":0, "CF_SQL_SMALLINT":0, "CF_SQL_TINYINT":0];
	local.dateTypes = ["CF_SQL_TIMESTAMP":0, "CF_SQL_DATE":0, "CF_SQL_TIME":0];
	for (local.binding in arguments.bindings){
		if (local.binding.null){
			local.sql = replace(local.sql, "?", "NULL", 1);
		} else if (structkeyexists(local.varcharTypes, local.binding.cfsqltype)){
			local.sql = replace(local.sql, "?", "'#replace(local.binding.value,"'", "''", "all")#'", 1);
		} else if (structkeyexists(local.nvarcharTypes, local.binding.cfsqltype)){
			local.sql = replace(local.sql, "?", "N'#replace(local.binding.value,"'", "''", "all")#'", 1);
		} else if (structkeyexists(local.numericTypes, local.binding.cfsqltype)){
			if (structkeyexists(local.binding, "scale") && val(local.binding.scale) gt 0){
				local.sql = replace(local.sql, "?", "CAST #javacast("string", local.binding.value)# AS DECIMAL(38, #local.binding.scale#)");
			} else {
				local.sql = replace(local.sql, "?", javacast("string", local.binding.value), 1);
			}
		} else if (structkeyexists(local.dateTypes, local.binding.cfsqltype)){
			local.sql = replace(local.sql, "?", "'#dateformat(local.binding.value,'yyyy-mm-dd')# #timeformat(local.binding.value,'HH:mm:ss.ll')#'", 1);
		}
	}
	return local.sql
}
</cfscript>