QuerySetCell(), StructInsert(), And Other Silly ColdFusion Functions
Posted August 21, 2006 at 3:32 PM by Ben Nadel
I just came across QuerySetCell() in some code I was updating... I see this a lot and it's got me thinking.... I see a lot of strange code in ColdFusion applications, but sometimes I see code I just flat-out don't understand. For instance, why would people use the functions QuerySetCell() and StructInsert()? This functions seem to server no special purpose and only provide extra overhead to straight-forward features.
Let's take QuerySetCell(). As a function, it sets a cell in a query to a value. If no row number is specified, it assumes you are talking about the last row. So, instead of doing this:
- <cfset qGirls[ "hot_factor" ][ qGirls.RecordCount ] = 10 />
... you could do:
- <cfset QuerySetCell( qGirls, "hot_factor", 10 ) />
I guess this is convenient in that you do not have to pass in the row number. But, 9 times out of 10, when I see this function being used, the row number IS sent:
- <cfset QuerySetCell( qGirls, "hot_factor", 10, qGirls.RecordCount ) />
This to me defeats the purpose. You are basically doing the same thing as using structure notation, except that you add a function-call overhead. Yes, this function does hide errors from you by returning a boolean value rather than an error, but I NEVER see it used to handle errors in any way at all (ie. using the FALSE return value to do other logic).
StructInsert() to me is the same way. Instead of doing something simple like:
- <cfset objGirl.Name = "Sarah" />
... you could do:
- <cfset StructInsert( objGirl, "Name", "Sarah" ) />
The only difference here is that StructInsert() allows you to throw an error if you cannot overwrite the value (optional fourth argument). But again, I NEVER see it being used this way! So, again I ask, why do it this way??? All it does is add overhead.
Now admittedly, I am not grand master ColdFusion developer, but I have been around the block a few times, and cannot for the life of me figure out how these functions offer any advantage over the more straight forward approaches.
Reader Comments
QuerySetCell: Hmm, I tend to use it as it just feels right after doing a queryAddRow. Can you use struct notation WITHOUT first adding a row? What I mean is - if you are adding new rows, don't you need to add the row first?
As for QuerySetCell, I used to agree with you. Then, in one particularly hairy debugging session, I found that CF will occasionally forget that the default is the LAST row, and somehow the default would be the CURRENT row. Long story short, if I didn't explicitly add the last RecordCount parameter, I got a query that had "N" rows, but "N-1" were blank, leaving only the first row filled in with the last set of data. It was really kooky. It was also a few years ago, so that particular bug may have been fixed, but ... I still explicitly put in the RecordCount parameter these days anyway. I figure it's probably worth it for the readbility. And I still to this day run into problems with trying to set cells manually:
qFoo.Bar[1]="quux"
qFoo["Bar"][1]="quux"
Every now and then CF will throw some really esoteric error until I convert it into a QuerySetCell. I don't get it, but I can play the cargo cultist with the best of them.
However .. for StructInsert I definitely agree. I don't use it at all.
Ray,
Yes, I add a Row before setting values as I think this needs to be done no matter what. But even without adding a row, let's say I was looping through a query and updating values (perhaps based on mirrored physical file existence), QuerySetCell() would seemingly add no value over structure notation, not to mention that it is additional processing for the server via a method call.
Rick,
That's interesting. I have not run into that error with weird errors getting thrown. As I never use QuerySetCell() I cannot relate to the forgetting of last row vs. first row. I wonder if the errors or setting cells manually are related to the data types. Hmmm....
I'm an experienced developer but new to ColdFusion
I found this page googling for "coldfusion structinsert", because I wasn't expecting the default to be "throw an error if already set"-- I expected it to act more like HashMaps in other languages
Anyway, the reason I'm using StructInsert is that I don't quite get the . syntax CF uses, maybe I'm not familiar with it.
For instance, I want to pull a list of roles out of a database, and set up a struct where the keys are the rolenames, and the value is just something simple like "on"... the variable roles is my struct, roleid is a variable holding the roleid, but <cfset role.#roleid#="on"> doesn't work, and I'm not sure how to do that escaping, to use variable contents as a struct's key.
Kirk,
Let's assume that you have a query qRoles coming out of the database with the column "roleid". You could create the structure like :
<cfset objRole = StructNew() />
<cfloop query="qRoles">
...<cfset objRole[ qRole.roleid ] = true />
</cfloop>
This will create a structure, objRole, with keys corresponding the records returned from the database. Part of the issue you were having was with the use of # signs. You don't need , nor should you, use # signs as part of a variable name (but it might not throw an error, not sure). You can refer to structure values in one of two ways:
DOT notation as in
objStructure.Key = Value
and object notation (not sure if that is the official name, also called array notation sometimes) as in
objStructure[ "Key" ] = Value
The object notation is GREAT for creating dyamic keys as in
objStructure[ "K" & "ey" ] = Value
Here we create the key "Key" by concatenation K and ey.
Does that help clear anything up?
Ok, I get the concept; 3 notations for inserting, foo.bar, foo["bar"], StructInsert(foo,"bar") ...
I guess I should just be grateful no one has suggested "plus you should also be able to say foo.setBar()" to add a 4th setting option, keeping w/ Java's bean notation...
Yeah, that pretty much sums it up. I personally go with the Struct.Key unless I need to reference a vaiable value as a key, in which case I use object notation.
... plus you should also be able to say foo.setBar() ...
No, really.
A CFC is a ColdFusion Component that is compiled down to a Java Class and acts just like a structure. So, especially when deally with scaffolding frameworks like Reactor, setXYZ() methods can be automagically added to CFCs ... which can then be used like Structs.
Just so you didn't think you were completely sane. ;-)
Oh, and one more thing. StructInsert() bombs if there is already a value for the given key. But what if you wanted to go the other way - set it once and only once, but not bomb after?
<cfparam name="foo.bar" default="#otherthing#">
It's cheating, but it works.
I realise this post's not fresh, but I found a use for QuerySetCell(). I ran into a strange error message in a cffunction "Query objects cannot be modified, they can only be displayed".
http://phillhowson.com/blog/index.cfm?mode=entry&entry=4B03A447-1372-5B4B-88BFB8C78988A945
The solution was QuerySetCell(), though I don't know why using dot notation version created an error.
@Phill,
That is a tricky problem. When you set a query cell value, you need to give it a column and row number:
<cfset qData[ col ][ row ] = "value" />
The reason QuerySetCell() works without the row number is that it assumes that you are referring to the last row of the query. A bit subtle but that is why your dot notation wasn't working (as far as I know).
Ironically, I use QuerySetCell to create a rather large query in order to use your POI Utility to create an excel sheet of data (or 10).
@Lee,
Whatever gets the job done :)
Here I come a billion years later... but I wanted to make this point regarding structinsert(). Here is something you cannot do with dot notation:
<cfset structinsert(somestruct,"1.5","3")>
I actually tried this for grins: <cfset somestruct."1.5"="3"> but CF8 wasn't happy with this.
@Brien,
True, you cannot do that with dot notation, but you can do it with bracket notation:
<cfset somestruct[ "1.5" ] = 3 />
Perhaps another reason to avoid this (or at least a reason to do it right):
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=using_recordsets_8.html
