Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

One of the things that I really love about ColdFusion is how easy it is to work with query objects. The ability to run a query on an existing query object is one of those great features that sets CF apart from other languages.

Query of Query Incurs Overhead

One possible use of the "Query of Query" feature is to rename a query object column when it is not possible to do this in native SQL. (While rare this does occur on occasions such as when using MySQL's 'SHOW' command which does not allow column renaming.) As simple as "Query of Query" makes accomplishing this task, it does incur additional overhead and really is overkill for what should be a simple procedure.

Enter Native Java

Fortunately this brings us to the other thing I love about ColdFusion. Since ColdFusion runs on Java and allows us to use Java code directly in our cfml, we can leverage the power of Java to rename the columns in our query object. This means we do not have to incur the performance hit that comes with starting up the "Query of Query" engine to achieve our goals.

The Java Code

To rename a given column in a query object we utilize two java functions.

  • "getColumnNames()" returns an array containing all the existing column names for a given query object.
  • "setColumnNames()" sets the column names in the query object using an array of new column names

Below is a simple function I wrote to find and rename a single column in a query object. I have only tested on Adobe CF9 but this code should be compatible with all versions of CF which run on Java.

<cffunction name="renameColumn" access="public" output="false" returntype="query" hint="Uses java to rename a given query object column">
      <cfargument name="queryObj" required="true" type="query">
      <cfargument name="oldColName" required="true" type="string">
      <cfargument name="newColName" required="true" type="string">
      
      <!--- Get an array of the current column names --->
      <cfset var colNameArray = queryObj.getColumnNames()>
      <cfset var i = 0>
      
      <!--- Loop through the name array and try match the current column name with the target col name--->
      <cfif arrayLen(colNameArray)>
            <cfloop from="1" to="#arrayLen(colNameArray)#" index="i">
                  <!--- If we find the target col name change to the new name --->
                  <cfif compareNoCase(colNameArray[i],arguments.oldColName) EQ 0>
                        <cfset colNameArray[i] = arguments.newColName>
                  </cfif>
            </cfloop>
      </cfif>
      
      <!--- Update the column names with the updated name array --->
      <cfset queryObj.setColumnNames(colNameArray)>
      
      <cfreturn queryObj />
</cffunction>
<!--- Rename a column 'user_id' in the 'users' query object to 'userId' --->
<cfset users = renameColumn(users,'user_id','userId')>

So there you have it a simple, fast way to update the column names of a query object without having to use the "Query of Query" engine.

Related Blog Postings

What Do You Think?

Reader Comments

Scott Speaks's Gravatar
Scott Speaks
Wednesday, April 17, 2013 at 3:17:12 PM EDT

How do I call this function. I am new to coldfusion.

Steven Neiland's Gravatar
Steven Neiland
Wednesday, April 17, 2013 at 3:38:23 PM EDT

Scott,
I have added an example of calling this function but I suggest you visit www.learncfinaweek.com to get started learning the basics.

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub