ColdFusion dynamic query column names

I am creating a dynamic SQL statement. However, when executing the statement, the column names are returned in alphabetical order.

Below please find example

            <cfset sqlStatement = 'SELECT x, g, a, p FROM table1'>

            <cfquery name="qry1">
                  #PreserveSingleQuotes(sqlStatement)#
            </cfquery>

            Column names are return in a,g,p and x sequence.

How can I get the query to return the column names in the original sequence (x, g, a, p) ?
Errol FarroAsked:
Who is Participating?
 
_agx_Commented:
There are two options.

1) The undocumented getColumnList() method. It returns an array of column names in the order of the sql statement.

<cfset colNames = yourQuery.getColumnList()>
<!--- demo: show results --->
<cfloop array="#colNames#" index="col">
      column name = #col#<br>
</cfloop>

2)  Use the getMetaData() function. It returns an array of structures. The structure contains the column name, plus a few other properties.

<cfset colMeta = getMetaData(yourQuery)>
<!--- demo: show results --->
<cfloop array="#colMeta#" index="col">
      column name = #col.name#<br>
</cfloop>
0
 
gdemariaCommented:
The query doesn't return columns in any particular order - are you referring to the QUERY.COLUMN _LIST which sorts the names alphabetically?

Why do you need them to be in a particular order?   There are lots of ways to manage this including using aliases on the column names.   Provide a bit more information so we can help more..
0
 
Errol FarroAuthor Commented:
For purpose of my application, I need to get the column names in the same sequence as they were noted in the SQL statement.

Scenario 1 is the correct result which I am getting when executing the SQL from SLQ Studio.

However, scenario 2, when executed from Coldfusion, sorts the column names alphabetically. I need Coldfusion to produce the column names in the order of the SELECT statement.




 
  1) Scenario 1 - executed from with SQL STUDIO

SELECT [ciId]
      ,[ciTitle]
      ,[ciFirstName]
      ,[ciLastName]
      ,[ciAddress1]
      ,[ciAddress2]
      ,[ciAddress3]
      ,[ciAddress4]
      ,[ciCity]
  FROM CONTACTINFO

 
  Column Name result:
  ciId      ciTitle      ciFirstName      ciLastName      ciAddress1      ciAddress2      ciAddress3      ciAddress4      ciCity

 
 
 
  2) Scenario 2 - executed in Cold-fusion
   
<cfquery name="qry1">
      SELECT [ciId]
      ,[ciTitle]
      ,[ciFirstName]
      ,[ciLastName]
      ,[ciAddress1]
      ,[ciAddress2]
      ,[ciAddress3]
      ,[ciAddress4]
      ,[ciCity]
  FROM CONTACTINFO
</cfquery>
 
  Column Name result:
  CIADDRESS1      CIADDRESS2      CIADDRESS3      CIADDRESS4      CICITY      CIFIRSTNAME      CIID      CILASTNAME      CITITLE
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
gdemariaCommented:
@Errol, you're second post only explained what it means for the columns to be out of order; which you explained already in the first post, so that really didn't add anything.   You didn't even mention what mechanism you were using to get the column names in a particular order Query.Column_List is the one I asked about.   I would have suggested ways to work around it based on your requirement, but luckily agx knew of a way to list the columns in order.   Agx, you never cease to amaze me.
0
 
_agx_Commented:
Thanks :)  Funny thing is I was about to ask the exact same thing you did. Then thought I remembered something about reporting queries in another thread, and figured this might be needed for generating headers. Of course I was hurrying to post before you beat me to the punch... as usual ;-)

EDIT:


@Errol - Do you have to use dynamic sql?  If so, be sure to thoroughly sanitize any sql first, because that type of query is very vulnerable to sql injection.
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
You can use this cflib UDF

http://www.cflib.org/udf/getquerycolumns

It will give you the the query columns the way you want it...
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
on CF8, getMeta() is missing, but getMetadata() seems to work.
0
 
_agx_Commented:
>> on CF8, getMeta() is missing

I have not confirmed that, but ... there's always a risk using undocumented methods.  However, in this case you don't need a UDF or undocumented methods. As I mentioned earlier, the core getMetaData() function works just fine in any recent version.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.