Browsing all articles tagged with cfquery

Feb

Tip : Getting unsorted query columns in coldfusion

nerdscubecom Category

Coldfusion Tags

cfquery, Coldfusion, column, getmetadata, sorting, sql

This one gave me a headache for 15 min, making all the possible tricks to make my query return the unsorted columns.

Generally Coldfusion always sort the columns in the query that has been returned, irrespective of the order you have specified in the SQL statement. Most of the time its a blessing, but not always.

There is no documented way to get the unsorted columns otherthan using some thirdparty customtags and function. But there is a tweak for this case.

Tweak #1
Use getMetaData() function to get the column list which is as per the sql statement which you have written.

<!--- use GetMetadata to get the names and data types of the fields in the
            cfdocexamples Employees table --->
<cfquery name="getemployees" datasource="cfdocexamples">
SELECT *
FROM Employees
</cfquery>
<cfset employeemeta=getMetaData(getemployees)>
 
<h4>The Employees table has the following columns</h4>
<cfloop index="i" from="1" to="#arrayLen(employeemeta)#">
    <cfoutput>
        #employeemeta[i].name#
    </cfoutput>
</cfloop>

Tweak#2
Use queryObj.getColumnNames() function to get the unsorted list of column from the query. this function is not documented in CF docs. Thanks to Richard

<cfset queryColumnlist = ArrayToList( qryObj.getColumnNames() )>

Connect me

Twitter

CF Libs

DeMoronize September 2, 2010
Fixes text using Microsoft Latin-1 "Extentions", namely ASCII characters 128-160. […]
readPropertiesFile September 1, 2010
Load and parse a .properties file into a struct […]
lastDayofWeek August 30, 2010
Returns last day of the week, assumes Saturday is the last day. […]
sitemapPing August 26, 2010
Ping multiple search engines with your sitemap.xml file and get the server response. […]
isURL August 5, 2010
A quick way to test if a string is a URL. […]
indentXml July 30, 2010
indentXml pretty-prints XML and XML-like markup without requiring valid XML. […]
verifyMailServer July 1, 2010
Verifies a mail server connection. […]
getFunctionHint July 1, 2010
You give me a function and I return the hint. Useful for demo and documentation pages. […]
getUrlRelativeToWebRoot June 23, 2010
Pass in file path to web root and a file and it returns URL relative to site root. […]

Tip : Getting unsorted query columns in coldfusion

Connect me

Twitter

Recent Posts

Categories

Blogroll

Tags

Archives

CF Libs