capitalizationGuide | Home | The Lost Art of Steam Heating

Filed under Quick Tips on January 22, 2008 by Javier Julio

Obtaining Original Query Column Order in ColdFusion

Using an undocumented Java method on a ColdFusion query object called getColumnList() returns an array of the original column order specified in the database or SQL statement. Using the built-in [query].columnList returns an alphabetized column list.

Useful Resources


<!--- get all fields from parks table, order will reflect what is defined in database --->
<cfquery name="qParks" dataSource="cfdocexamples">
SELECT *
FROM parks
</cfquery>


<cfoutput>
<dl>
	<dt>Alphabetized Column List</dt>
		<dd>#qParks.columnList#</dd>
	<dt>Original Ordered Column List (specified in database table)</dt>
		<dd>#arrayToList(qParks.getColumnList())#</dd>
</dl>
</cfoutput>



<!--- explicitly specifying the columns required in a certain order --->
<cfquery name="qParkLocations" dataSource="cfdocexamples">
SELECT ParkName, Region, Address1, City, State, ZipCode
FROM parks
</cfquery>


<cfoutput>
<dl>
	<dt>Alphabetized Column List</dt>
		<dd>#qParkLocations.columnList#</dd>
	<dt>Original Ordered Column List (specified in SQL statement)</dt>
		<dd>#arrayToList(qParkLocations.getColumnList())#</dd>
</dl>
</cfoutput>

Post a Comment Digg Del.icio.us

Trackback Pings (TrackBack URL for this entry)

http://www.arc90.com/cgi-bin/mt4/mt-tb.cgi/99.

Comments

Cool Javi!! Ive used the query.getMetaData() before and had to do more code to get this result. Didnt realize it could be called directly from the query.

Thanks Javi. Them New Yorkers must be teaching you something! :)

Posted on January 22, 2008 2:33 PM by Matthew Abbott

Matt,

No problem, happy to share. :) I don't even know if I thought of using getMetaData() but as we talked over it in IM you are right that I would have had to write a lot more code just to get an original ordered column list.

I didn't think I'd make the jump again into the Java innards of CF but this came up when I did a Google search. Got to thank my buddy Ben Nadel for blogging on it! The resource URL that listed out the available Java methods made it to easy. :)

Posted on January 22, 2008 4:39 PM by Javier Julio

I have a problem like this...

I will getting the values from a excel sheet and i will be displaying the values in the portal.

Probem is i will be getting the values in a structure from the excel and retrieve the values as

#studata[1].query.column1[intCount]#
.
.
.

#studata[1].query.column12[intCount]#

Here studata is a struct and the query will be present inside the struct and column1 will retrieve the values from column1 of the query.

The query has no name and we are hardcoding the columns as column1 .... column12. Since the excel sheet contains 12 columns.
We want to display the column values dynamically instead of hardcoding like column1......column12

I am not able to loop like column[index] for column1 becoz coldfusion is throwing error dat it will accept query.column

I think i will be get a good solution for this

Please help me

Posted on September 19, 2008 2:14 AM by shruthi

@shruthi

What you need to do is use bracket notation on the column name as well. For example you would do the following:

#studata[1].query["column#columnIndex#"][intCount]#

Assuming you have everything else setup correctly. That should work. The columnIndex should be the index of the inner loop. The outer loop will be for each query row. Hope this helps out. Let me know how you are doing.

Posted on September 22, 2008 12:00 PM by Javier Julio

Post a Comment:

capitalizationGuide | Main | The Lost Art of Steam Heating