Last active
February 27, 2023 21:27
-
-
Save JamoCA/f18c868fcb2d8f23b797f9c7b2ee93e9 to your computer and use it in GitHub Desktop.
queryReturnRow UDF to convert a ColdFusion struct or array from a query row. Options to drop/replace NULL values. CF2016+
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<cfscript> | |
/* 2023-02-27 queryReturnRow() by SunStar Media https://www.sunstarmedia.com/ | |
Requires CF2016+ or Lucee | |
Inspired by https://cflib.org/udf/queryGetRow & https://docs.lucee.org/reference/functions/queryrowdata.html | |
GIST: https://gist.github.com/JamoCA/f18c868fcb2d8f23b797f9c7b2ee93e9 | |
TWEET: https://twitter.com/gamesover/status/1630291299097260032 | |
- Renamed function to something unique because queryGetRow() is a BIF (as of CF11) | |
- Updated to retain column order by default (using an ordered struct) | |
- Option to set key case (original/lower/upper) | |
- Option to return data in either an array or struct similar to Lucee's QueryRowData() BIF | |
- Option to drop NULL values from the returned array/struct | |
- Option to replace NULL values with a string (instead of empty which isn't the same as NULL) | |
*/ | |
public any function queryReturnRow(required query query, numeric row=0, string format="struct", string keyCase="original", boolean dropNull=false, string replaceNullString="") hint="Returns a ColdFusion struct or array with query columns as keys and their corresponding values w/option to drop or relabel NULL values" { | |
local.row = (val(arguments.row) gt 0) ? arguments.row : 1; | |
local.result = (arguments.format eq "array") ? [] : [:]; | |
if (arguments.query.recordcount lt local. Row) { | |
return local.result; | |
} | |
if (structkeyexists(server, "lucee")){ | |
local.cols = arguments.query.getColumnNames(); | |
} else { | |
local.cols = arguments.query.getMetaData().getColumnLabels(); | |
} | |
if (listfindnocase("lcase,lower", arguments.keyCase)){ | |
local.cols = listtoarray(lcase(arraytolist(local.cols))); | |
} else if (listfindnocase("ucase,upper", arguments.keyCase)){ | |
local.cols = listtoarray(ucase(arraytolist(local.cols))); | |
} | |
if (arguments.format eq "array"){ | |
for (local.col in local.cols){ | |
local.isCellNull = ((structkeyexists(server, "lucee") && isnull(arguments.query[local.col][local.row])) || (!structkeyexists(server, "lucee") && isnull(arguments.query.getField(arguments.query.CurrentRow, arguments.query.findColumn(local.col))))); | |
if (!arguments.dropNull || (arguments.dropNull && !local.isCellNull)){ | |
arrayappend(local.result, [ | |
"#local.col#": (local.isCellNull && len(arguments.replaceNullString)) ? arguments.replaceNullString : arguments.query[local.col][local.row] | |
]); | |
} | |
} | |
} else { | |
for (local.col in local.cols){ | |
local.isCellNull = ((structkeyexists(server, "lucee") && isnull(arguments.query[local.col][local.row])) || (!structkeyexists(server, "lucee") && isnull(arguments.query.getField(arguments.query.CurrentRow, arguments.query.findColumn(local.col))))); | |
if (!arguments.dropNull || (arguments.dropNull && !local.isCellNull)){ | |
local.result[local.col] = (local.isCellNull && len(arguments.replaceNullString)) ? arguments.replaceNullString : arguments.query[local.col][local.row]; | |
} | |
} | |
} | |
return local.result; | |
} | |
</cfscript> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment