DEV Community

James Moberg
James Moberg

Posted on

1

Identify if ColdFusion Query Row is "empty"

I'm using the Handsontable javascript gid component. It provides my web applications the ability to copy/paste columnar data, review and edit before importing. During the online "save" process, the SheetClip plugin stringifies the data and it's posted it to the ColdFusion application server where it's converted it into a query-of-queries (using a CSV-to-Query UDF that I have) and then the individual rows are inserted into the database.

I've also encountered strange issues where multiple empty rows are retained after reading an Excel file via cfspreadsheet.

I was trying to determine the easiest way to identify whether the query row was empty so I wouldn't have to perform any inline if/then/else logic on all of the columns. I didn't find any pre-existing UDF at CFLib.org, so I sat down and wrote this.

Live Demo via TryCF.com

https://www.trycf.com/gist/796c63867cb426b5f5fad791cdcfa4b2

Source Code

<!--- 20200122 ColdFusion UDF to identify whether passed query + row has any data in any of the rows.
(Used during import to eliminate empty rows)
Blog: https://dev.to/gamesover/identify-if-coldfusion-query-row-is-empty-227l
Gist: https://gist.github.com/JamoCA/796c63867cb426b5f5fad791cdcfa4b2
TryCF: https://www.trycf.com/gist/796c63867cb426b5f5fad791cdcfa4b2
--->
<cfscript>
boolean function isQueryRowEmpty(required query theQuery, required integer rowNumber){
var dataLen = 0;
var rowData = QueryGetRow(arguments.theQuery, arguments.rowNumber);
var key = "";
var value = "";
structEach(rowData,function(key, value) {
dataLen += len(trim(value));
});
return javacast("boolean", not dataLen);
}
</cfscript>
<cfset TestQuery = QueryNew("id,name,email", "integer,varchar,varchar")>
<cfset queryAddRow(TestQuery)>
<cfset querySetCell(TestQuery, "id", javacast("int", 1))>
<cfset querySetCell(TestQuery, "name", "John Smith")>
<cfset querySetCell(TestQuery, "email", "John@Smith.com")>
<cfset queryAddRow(TestQuery)>
<cfset queryAddRow(TestQuery)>
<cfdump var="#TestQuery#">
<cfoutput query="TestQuery">
<div><b>Row #currentRow# of #recordCount#:</b> isQueryRowEmpty = #isQueryRowEmpty(TestQuery, currentRow)#</div>
</cfoutput>

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs