Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2008 (Washington, D.C.) with: Qasim Rasheed

Ask Ben: Changing ColdFusion Query Column Names

By Ben Nadel on

I want to change the names of the fields in a CFQUERY result set after I have retrieved the results from the database (ie. I can't use "AS" in the original SQL statement). I can think of 2 ways to do this - use the query-a-query (CFSQL) feature or just build and populate a new query object. Do you know which of these would be more efficient? And is there a better way, whereby I can manipulate the names of the columns in the original query (ie. so that I can rename the "CustName" field to "Name" and then use "CustList.Name" directly)?

Both ways you talk about are completely valid. Even more than that, they are both officially supported features of ColdFusion. What I am about to show you utilizes the underlying Java methods of the ColdFusion query object. These are not *supported* per say, but from everything I have been told, it is not crazy to use them.

The method that I would like to use to do this is called SetColumnNames(). This takes an array of string column names. The query will take the array and name each query column appropriately. Now here's the catch: it seems you can't rename just a single column AND the order of the column names in the array passed in has to line up with the internal order of the column in the underlying Java object.

If you look at what gets returned from Query.ColumnList, you will see that the list is always in alphabetical order. You might be tempted to use this order, however, you cannot. The "actual" order of the columns in the underlying structure does NOT change even when the column names are changed. Therefore, we have to get the actual columns from the query's underlying Java method: GetColumnNames(). This returns an array-like object of column names in their underlying order.

So, let's put that all together into a function: QueryChangeColumnName():

  • <cffunction
  • name="QueryChangeColumnName"
  • access="public"
  • output="false"
  • returntype="query"
  • hint="Changes the column name of the given query.">
  •  
  • <!--- Define arguments. --->
  • <cfargument
  • name="Query"
  • type="query"
  • required="true"
  • />
  •  
  • <cfargument
  • name="ColumnName"
  • type="string"
  • required="true"
  • />
  •  
  • <cfargument
  • name="NewColumnName"
  • type="string"
  • required="true"
  • />
  •  
  • <cfscript>
  •  
  • // Define the local scope.
  • var LOCAL = StructNew();
  •  
  • // Get the list of column names. We have to get this
  • // from the query itself as the "ColdFusion" query
  • // may have had an updated column list.
  • LOCAL.Columns = ARGUMENTS.Query.GetColumnNames();
  •  
  • // Convert to a list so we can find the column name.
  • // This version of the array does not have indexOf
  • // type functionality we can use.
  • LOCAL.ColumnList = ArrayToList(
  • LOCAL.Columns
  • );
  •  
  • // Get the index of the column name.
  • LOCAL.ColumnIndex = ListFindNoCase(
  • LOCAL.ColumnList,
  • ARGUMENTS.ColumnName
  • );
  •  
  • // Make sure we have found a column.
  • if (LOCAL.ColumnIndex){
  •  
  • // Update the column name. We have to create
  • // our own array based on the list since we
  • // cannot directly update the array passed
  • // back from the query object.
  • LOCAL.Columns = ListToArray(
  • LOCAL.ColumnList
  • );
  •  
  • LOCAL.Columns[ LOCAL.ColumnIndex ] = ARGUMENTS.NewColumnName;
  •  
  • // Set the column names.
  • ARGUMENTS.Query.SetColumnNames(
  • LOCAL.Columns
  • );
  •  
  • }
  •  
  • // Return the query reference.
  • return( ARGUMENTS.Query );
  •  
  • </cfscript>
  • </cffunction>

As you can see, we have to both get and set the column names via the underlying Java record set. Notice that the function returns the query object itself. You don't have to do this. Since ColdFusion query objects are passed by reference, you don't have to return anything. I like to return the query object so that I can chain my methods:

  • <cfset QueryChangeColumnName(
  • QueryChangeColumnName(
  • qData,
  • "x",
  • "y"
  • ),
  • "a",
  • "b"
  • ) />

Notice that in one swoop, we change two columns (a to b and x to y). Returning the query object allows us the flexibility to chain our actions in this way.

Ok, but you asked about speed an efficiency. Well, this method above is gonna be the fastest thing to do as it doesn't manipulate the query data, just the meta data. But, to demonstrate this, I did some speed tests. As always, I had to start by building a query object:

  • <!--- Create the girls query. --->
  • <cfset qGirls = QueryNew(
  • "first_name, last_name",
  • "VARCHAR, VARCHAR"
  • ) />
  •  
  • <!--- Add query rows. --->
  • <cfset QueryAddRow(
  • qGirls,
  • 50000
  • ) />
  •  
  • <!--- Build the query. --->
  • <cfloop
  • index="intI"
  • from="1"
  • to="#qGirls.RecordCount#"
  • step="1">
  •  
  • <!--- Set cell values. --->
  • <cfset qGirls[ "first_name" ][ intI ] = JavaCast(
  • "string",
  • "Julia"
  • ) />
  •  
  • <cfset qGirls[ "last_name" ][ intI ] = JavaCast(
  • "string",
  • ("Niles" & intI)
  • ) />
  •  
  • </cfloop>

Notice that I am using JavaCast() so set the column values. Always an important thing to do for data integrity. Now, let's test the above query against the two other methods you suggested, the query of query and the query duplication:

  • <!--- Test are Java methods. --->
  • <cftimer label="QueryChangeColumnName" type="outline">
  •  
  • <!--- Change last name to given name. --->
  • <cfset QueryChangeColumnName(
  • qGirls,
  • "last_name",
  • "family_name"
  • ) />
  •  
  • </cftimer>
  •  
  •  
  • <!---
  • Test the ColdFusion query of queries method in which we
  • alter the name of column by selecting it AS another name.
  • --->
  • <cftimer label="ColdFusion Query of Query" type="outline">
  •  
  • <cfquery name="qGirls2" dbtype="query">
  • SELECT
  • first_name,
  • (
  • family_name
  • ) AS last_name
  • FROM
  • qGirls
  • </cfquery>
  •  
  • </cftimer>
  •  
  •  
  • <!---
  • Test the speed of manually building a completely new query
  • and just setting the new column to the old column value.
  • --->
  • <cftimer label="QueryNew()" type="outline">
  •  
  • <!--- Define the new query. --->
  • <cfset qGirls2 = QueryNew(
  • "first_name, last_name",
  • "VARCHAR, VARCHAR"
  • ) />
  •  
  • <!--- Query records. --->
  • <cfset QueryAddRow(
  • qGirls2,
  • qGirls.RecordCount
  • ) />
  •  
  • <!---
  • Loop over the original query and get at the data. We
  • are doing an indexed loop as opposed to a query loop
  • as the index loop has some speed advantages.
  • --->
  • <cfloop
  • index="intI"
  • from="1"
  • to="#qGirls.RecordCount#"
  • step="1">
  •  
  • <cfset qGirls2[ "first_name" ][ intI ] =
  • qGirls[ "first_name" ][ intI ]
  • />
  •  
  • <cfset qGirls2[ "last_name" ][ intI ] =
  • qGirls[ "family_name" ][ intI ]
  • />
  •  
  • </cfloop>
  •  
  • </cftimer>

As with anything in ColdFusion (which is such an awesome, kick ass language) with a small record/column set everything executes in 0 ms. To get a really good speed difference going, I pumped this up to 50,000 records. With this large record set, the QueryChangeColumnName() method executes instantaneously (0 ms). The ColdFusion Query of Queries method executes in about 220 ms on average. And, the manual building of a new query executes in a sluggish 1,100 ms on average.

So, not only was our QueryChangeColumnName() much faster (infinitely faster), it also is a better approach as it does not duplicate any information. Both the other methods have to actually touch the underlying data, and in both cases have to DUPLICATE the record set in some way. This is just going to take time and eat of system resources. The QueryChangeColumnName() method does not touch the query data, just the query meta data, which is always a small set of data.

I was a little surprised that the ColdFusion query of queries was faster than the manually built query. In my experience, query of queries is generally slower for duplicating a query. But I guess that just goes to show there is more to speed testing than simple iteration.



Reader Comments

Did the initial respondant ask you this directly, or did you just spot it on the CF forums @ Adobe. I ask because a seemingly verbatim issue has been raised there: http://tinyurl.com/yjrnec.

Either way, it might be useful to post your response to the forums too? Any help answering questions there would be appreciated! :-)

If you are - as I suspect - just finding interesting questions elsewhere and choosing to answer them on your blog (fair enough: an answer is an answer, and answers are good), it's a bit disingenuous to suggest someone is asking YOU this question, and you should perhaps cite where/how you came across the question in the first place.

--
Adam

Reply to this Comment

One caveat to consider here is this.

When investigating this sort of thing myself a while back, I suggested to some Macromedia bods that they properly documented the "under the hood" methods available to the various Java classes they use for query objects and the like. They categorically said "NO WAY", the reason being they did not want to imply any contract that the methods exposed by those classes would necessarily be maintained in any way that would suggest backwards compatibility between CF versions. They're not really intended for popular usage.

I have seen these methods change behaviour between CF updaters (ie: not just point releases or major releases: updaters), and have accordingly steered clear of them for production code or anything more than really curio value.

This is a pity, but I see where they're coming from.

--
Adam

Reply to this Comment

Adam,

This question was sent to me last night via my "Ask Ben / Contact Form". I don't make up questions, which is why i have large "dry spells" of answering... it's not that I don't have time, people just don't ask very much.

I have never been on the Adobe forums before. I try to keep the stuff I have to concentrate on smaller. That is why I pretty much stick to full as a good and feed-squirrel.

As far as accessing Java methods, as I posted in the entry, I have gone back a forth with Ben Forta a bit and he suggested that using String methods was totally fine, but the rest was questionable (but not totally bad as the response that you got).

As far as citing who I got the question from, this is my methodology. I get the question. I try to answer it the best that I can. I post to the site AND I email the person a link to the answer. Then, if they choose, they can leave the "Thank you" as a comment and identify themselves.

Most of the time people don't identify themselves, and I don't care enough to ask them if they care. I just like answering stuff :)

Reply to this Comment

Ben,

That was awesome and just what I needed. I'm trying to implement Verity Search with the cfgrid type=html AJAX feature. Verity limits field names and thus I used it to change "CUSTOM1..." to my grid column names. Worked like a charm.

Have you messed with Verity much? If so, have you tried to post results to a cfgrid? I seem to recall your name in a post when I was searching for answers related to Verity, but it may have been anti-Verity.

Reply to this Comment

@Brian,

I haven't used Verity much at all. I used some existing indexes a while back, but I hardly remember.

Reply to this Comment

Have you tried this with BlueDragon?

BlueDragon doesn't seem to know what to do with "GetColumnNames()".

Any ideas?

Reply to this Comment

I'm newbie can you tell me how to implement it in coldfusion?
I'm using Dreamweaver 8 in you don't mind can you give me step-by-step to implement it using DW 8

This is (i think) what i'm looking for.
Thanks

Regards

Reply to this Comment

I was evaluating this script and determined that it doesn't work with Railo due to the java methods GetColumnNames() or SetColumnNames().

Is there another way to do this using documented ColdFusion methods that are available in third-party ColdFusion engines?

Reply to this Comment

@James,

You could do a ColdFusion query of queries in which the target column name changes:

<cfquery name="foo" dbtype="query">
SELECT
a,
b AS (new_b),
c
FROM
foo
</cfquery>

Here, we are changing the column "b" to be "new_b". Of course, there is a lot more overhead to do this (since it has to execute a query), but this will work with any engine that supports query of queries.

Reply to this Comment

How cleanly can that be done in a CFC like you've created? I was checking out another article by you regarding importing a CSV file to a query and then using this script to rename the columns.

I wanted the script I was developing to work in plug-and-play CF-based environment (or else I'd use a CFX tag).

I finally settled on using CFHTTP. It was infinitely faster when it came to reading a CSV file and converting it into a query. How come there isn't any CF Function for doing this with a "local" file?

Reply to this Comment

@James
this is how you can do it with railo:
<cffunction name="QueryChangeColumnName" access="public" output="false" returntype="query"
hint="Changes the column name of the given query.">
<cfargument name="Query" type="query" required="true"/>
<cfargument name="ColumnName" type="string" required="true"/>
<cfargument name="NewColumnName" type="string" required="true"/>
<cfset var KeyImpl=createObject('java','railo.runtime.type.KeyImpl')>
<cfset arguments.Query.rename(KeyImpl.getInstance(arguments.ColumnName),KeyImpl.getInstance(arguments.NewColumnName))>
<cfreturn arguments.query>
</cffunction>

a example with switch for ACF/railo you can find here:
http://dev.railo.ch/download/QueryChangeColumnName.cfm.zip

we will add support for the function setColumnNames() and setColumnName() in one of your next version, that bens exampe will work without changes on Railo as well.

to get the columns it is better to use the special column "columnlist", simply because it is faster (no hava reflection) and it works in every cfml engine.

example:
var LOCAL = StructNew();
LOCAL.ColumnList = ARGUMENTS.Query.columnlist;
LOCAL.Columns = ListToArray(LOCAL.ColumnList);

greetings micha

Reply to this Comment

The last version of Railo (3.1.2) implements getColumnNames() and setColumNames() methods.
More info: http://www.railo.ch/blog/index.cfm/2009/11/26/Release-notes-Railo-312.

Greetings.

Reply to this Comment

Has anyone else experienced NULL values in a query changing to "0" when changing column names in a query this way?

Reply to this Comment

@David,

I have not (although I haven't tried this approach in a while). I feel like I was just talking to someone last week or the week before about using this with success; and part of that was that it handled NULL values with success.

Does the column have a default value in the schema? Not that would necessarily make a difference. I just don't really have any other insight.

Reply to this Comment

@Ben Nadel,

The field(s) are actually VarChar fields with default of NULL.

I have a theory that it might be some kind of QofQ crazyness and that the datatypes of the fields might be changing as I tweak the query manually. I'll do some more in depth experimentation and see what I can figure out.

Reply to this Comment

@David,

Query of queries are definitely an odd beast. They data types are kind of evaluated as needed; they don't seem to be set in stone (which causes comparison errors). Let me know if you find anything.

Reply to this Comment

Thanks Ben This function saved my butt. This works really well in conjunction with the POI utility.

Used to swap out the column names from COLUMN1 to the actual column names from the excel sheet.

<cfloop from="1" to="#arraylen(objSheet.columnnames)#" index="n">
<!---swap query column name from COLUMN1 to the index in the column names array--->
<cfset qObj = QueryChangeColumnName(objSheet.Query,'COLUMN#n#',objSheet.columnnames[n])/>
</cfloop>

Reply to this Comment

Thanks Ben. I always seem to come across your site with questions I have concerning Coldfusion. I appreciate this post very much, it saved me so much time trying to change the query column headings!

Just wanna say thanks for all the help you've given me over the last year, I don't always post thanks when I get help from your site, but I want to do it now.

Thanks!
Kristi

Reply to this Comment

Thanks, this works a treat.

A few days after finding this I had a need to delete a query column. One way would be using a query of query. The much better way is to change 1 line in your function from:
LOCAL.Columns[ LOCAL.ColumnIndex ] = ARGUMENTS.NewColumnName;

to:
arrayDeleteAt(LOCAL.Columns, LOCAL.ColumnIndex);

The column with its data obviously still exists but cannot be accessed because the reference to it has been removed.

Reply to this Comment

"The QueryChangeColumnName() method does not touch the query data, just the query meta data, which is always a small set of data."

This might be nit-picking, but I'm not sure this function affects the query meta data as you said.

For example, if you run the SetColumnNames() method on a query and then use query.getMeta().getColumnLabels(), it will return an array of the original column labels, not the new column labels.

So, I guess the question is, how would you get the properly formatted column names from a query (i.e. in the proper case) where SetColumnNames() has been used?

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.