Ask Ben: Changing ColdFusion Query Column Names
Posted October 20, 2006 at 7:55 AM
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():
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <!--- 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:
Launch code in new window » Download code as text file »
- <!--- 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.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
Skin Spider : A Testament To Application Portability
Older Post
Programmatically Deleting Scheduled Tasks In ColdFusion
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
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
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 :)
@Ben - cool - thanks for the links!
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.
@Brian,
I haven't used Verity much at all. I used some existing indexes a while back, but I hardly remember.
Have you tried this with BlueDragon?
BlueDragon doesn't seem to know what to do with "GetColumnNames()".
Any ideas?
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
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?
@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.
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?
@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
