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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
_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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

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 RandhawaCEOCommented:
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 RandhawaCEOCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.