querySetCell to NULL when copying data?

Question

So here is my situation.

I have to work with some legacy code. This code gives me a query object back that I must append some rows to. Basically, I need to duplicate 1 row N times (N is not a constant) and then manually adjust some data.

Now, at least IMO what I'm doing really is not the right way to do things. I would much rather return a query object that needs no modifications on the CF side and generates the proper dataset on the database. But alas, I have no access to the original query :(

So here's my crack at. I decided to write a generic function that duplicates a query row, and then I can manually set whatever cells I need once I have the copy appended.

Please see below:

private function duplicateQueryRow(
    required query originalQuery,
    required numeric rowNum
) {
    queryAddRow(arguments.originalQuery, 1);
    for (local.i = 1; local.i LTE listLen(originalQuery.columnList); local.i += 1) {
        columnName = listGetAt(originalQuery.columnlist, i);
        querySetCell(originalQuery, columnName, originalQuery[columnName][rowNum], originalQuery.recordcount);
    }
}

The problem:

Suppose some of the columns are NULL. For example, suppose the value of column ACOLUMN is NULL at row 5. When I do this, the last row's ACOLUMN value will be "" due to CF not handling NULLs. Now if I do a select distinct ACOLUMN from QUERY_NAME I'll get row 5 AND the latest row since NULL <> [empty string].

I did have a solution and it's below:

private function duplicateQueryRow(
    required query originalQuery,
    required numeric rowNum
) {
    queryAddRow(arguments.originalQuery, 1);
    for (local.i = 1; local.i LTE listLen(originalQuery.columnList); local.i += 1) {
        columnName = listGetAt(originalQuery.columnlist, i);           
        if (originalQuery[columnName][rowNum] NEQ "") {
            querySetCell(originalQuery, columnName, originalQuery[columnName][rowNum], originalQuery.recordcount);
        }
    }
}

That works, for now since NULLs will not be copied, but I just dont have confidence that this is correct....

Was wondering if someone had a better solution? Or at the least see if something might be problematic with this function. The requirement I suppose is for a function that given a query and a rowNumber to copy will return the query with a copy of query's rowNumber row at the end.

Thanks in advance!

Answer 1

Or at the least see if something might be problematic with this function.

The obvious issue that comes to mind is that it does not differentiate between actual empty strings and nulls. So columns that contain actual empty strings would be considered null according to that logic.

I took a look around and unfortunately could not find any built in functions for this scenario. I thought IsNull might do it, but it does not seem to handle query columns the way you might expect. If you pass in a query column:

        isNull( queryName["columnName"][ rowNumber ] ) 

.. it always returns "NO". Most likely because the query reference evaluates to an empty string, instead of the real value ie null.

However, you might be able to use a little undocumented functionality. (I would not normally recommend it, but in this case I do not see any alternatives). CF query objects have an internal method called getField(rowIndex, columnIndex) that returns the underlying query value. If you use that value, instead of the one obtained using associative array notation, it should preserve the nulls.

   targetRow = queryAddRow(originalQuery, 1);
   columnNames = originalQuery.getColumnList();

   // note: colIndex refers to the position within the original SQL
   for (colIndex = 1; colIndex <= arrayLen(columnNames); colIndex++) {
      realValue = originalQuery.getField( sourceRow, columnIndex );
      originalQuery[ columnNames[colIndex] ][ targetRow ] = realValue;
   }