Ask Ben: Updating Columns In An Existing ColdFusion Query Object
Posted September 14, 2009 at 8:17 AM by Ben Nadel
Hi Ben, I have a question:), Is there a way to update a result set that was generated using QuerySetCell. I have a recordset that gets some null values. After the temporary table has been generated, I want to update the empty strings with 0. Is there a way to update query of queries or tables generated by querySetCell method. Thanks.
I'm really glad you asked me this as it got me thinking deeply about ColdFusion query of queries and some of the limitations that they have. What would be really nice (which I experimented with) is if ColdFusion query of queries supported UPDATE and DELETE SQL statements. However, currently, as they do not, the easiest way to solve your problem is to manually loop over the query and explicitly update the appropriate query cell values. To demonstrate this, let's first build a ColdFusion query object from scratch:
- <!--- Create the query object. --->
- <cfset girls = queryNew( "" ) />
-
- <!--- Add the name column. --->
- <cfset queryAddColumn(
- girls,
- "name",
- "cf_sql_varchar",
- listToArray( "Sarah,Libby,Jill,Kim" )
- ) />
-
- <!---
- Add the "is cute" column. When we add this column, let'
- s not supply any default values. This will put NULL
- value / empty strings in the cells.
- --->
- <cfset queryAddColumn(
- girls,
- "is_cute",
- "cf_sql_integer",
- arrayNew( 1 )
- ) />
-
-
- <!--- Output the query as it stands now. --->
- <cfdump
- var="#girls#"
- label="Incomplete Girls"
- />
Notice that we are providing default values for the "name" column, but not for the "is_cute" column. As such, the "is_cute" column will be filled with NULL values (represented on the output as empty strings):
| | | | | |
| |  | | ||
| | | |
Now, we have to loop over the query and update the appropriate cells. While we know at this point that all the "is_cute" values will be NULL, I'm going to include a conditional check just so you can see how you might do this if you knew less about the query:
- <!---
- Now, we want to set the NULL values to zero as the default
- value. Because the CFQuery query of queries does not support
- UPDATE statements, we will have to manually loop over the
- query to find the rows that need to be updated.
- --->
- <cfloop query="girls">
-
- <!---
- Check to see if this "is cute" column is null.
- NOTE: We know that they all will be at this point, but I
- wanted to demonstrate the conditional check.
- --->
- <cfif !isNumeric( girls.is_cute )>
-
- <!---
- Since "is cute" is not numeric, we know that it needs
- to get our default value. When setting this value, it
- is IMPORTANT that we use JavaCast() so that the
- unerlying Java object understands what type of value
- we are setting. Not doing this can lead to query of
- query errors later on.
- --->
- <cfset girls[ "is_cute" ][ girls.currentRow ] = javaCast(
- "int",
- 0
- ) />
-
- </cfif>
-
- </cfloop>
-
-
- <!--- Output the query as it stands now. --->
- <cfdump
- var="#girls#"
- label="Defaulted Girls"
- />
As we loop over the row, we can use the internal index provided implicitly by the CFLoop tag when checking the "is_cute" value; however, when we go to update the row, we need to reference the row index explicitly; as such, updating requires us to use array notation with both the column name and the row index (which we get using the CurrentRow property). As a general rule, you always want to do your best to provide the most accurate Java type when updating a query cell value so as to prevent any odd query of query results later on.
When we run the above code, we get the following output:
| | | | | |
| |  | | ||
| | | |
As you can see, the "is_cute" column now has its default zeros. I hope that helps.
Reader Comments
Thanks Ben. As usual, a very useful post!
thanx,
very useful info
Hi Ben,
I have also an Query, which I want to convert to a Json-Object.
Within my Query, there is a String(for example "0000" or "0001"). Unfortunately, this String is after the SeriealizeJson a number in the Json(0,1 ...).
I get an error when calling the functions with jQuery because the Json-Object isnt valid. The quotation marks are fail:
{
"COLUMNS": [
"Test1",
"Test2",
"Test3"
],
"DATA": [
[
"AA ",
0000,
"collecteur d'échappement "
]
]
}
I already tried so many things ... Can you help me?
Here my Code:
<cffunction name="getData" access="remote" returntype="any" returnformat="plain">
SQL....
<cfset result = SerializeJSON(result)>
<cfreturn result>
</cffunction>
Thanks a lot Ben.
Morpheus - I was having a similar problem with CF JSON truncating strings with leading zeros and here's how I handled it. I know the string length should be 6 - so it could be 000000, or A12345 etc.
- <cfif StructKeyExists(LOCAL.returnQry,"group_id")>
- <cfif LEN(LOCAL.returnQry.group_id) LT 6>
- <cfset LOCAL.returnQry["group_id"][LOCAL.returnQry.CurrentRow] = NumberFormat(LOCAL.returnQry.group_id,'000000')/>
- </cfif>
- </cfif>
If my group_id begins with a letter it's fine, if not I know it's 6 digits numeric and I just need to preserve the leading zeros.
