Coldfusion .columnList Variable Attribute Presents A Problem.

The problem:

I need to grab both column labels and row values from my database based on nothing more than a passed table name. Getting the data is easy, and getting the column labels is easier thanks Coldfusion’s variable attributes but it isn’t quite what I want.

The cfquery results attribute “.columnList” returns the table’s column set alphabetized, and can’t be resorted. Running my simple select * query and outputting the column list gets my the following columns:

CLIENT_ID, CLIENT_REF, OFFICE_CDE, POSTED_TIMESTAMP, SYSTEM_CODE, TERMINAL_NUMBER, TRANSACTION_CODE, USER_ID

That certainly is every column in my table, but they are sorted alphabetically and not presented in the order that they are defined in the database itself. This is where my problem starts. The fields in my database where defined in this order:

CLIENT_ID, POSTED_TIMESTAMP, USER_ID, CLIENT_REF, OFFICE_CDE, TERMINAL_NUMBER, SYSTEM_CODE, TRANSACTION_CODE

A Quick Fix:

I’ve found a temporary solution utilizing the tools of my environment. Making use of the IBM systables, a simply query gives me the column labels in the intended order. When I merge both this query and and a data result query into a structure, I get a nice encapsulated back box that takes a table name and returns the accurate information I’m looking for, determined by the datasource.

SELECT NAME, TBNAME, TBCREATOR, COLNO FROM sysibm.syscolumns ORDER BY COLNO ASC

I’m not entirely happy with solution, first and foremost because this solution obviously ads another query into the process, but also because it kills any portability I might have had for my application. In addition, it also seems that if Adobe would go to the trouble to build in a .columnList variable attribute, they might provide an optional parameter to determined a sort order? Maybe in the next version.

I’m open to any other suggestions.

One Response to “Coldfusion .columnList Variable Attribute Presents A Problem.”

1. Joe DeRose says:

   January 5, 2009 at 5:46 pm

   I am researching a different issue relating to .columnList, but my Google search pulled up your message above, and another one which seems to address your question. It can be found at:
   http://blog.arc90.com/2008/01/obtaining_original_query_colum_1.php

   In brief, it appears that the following output will give you what you’re looking for:
   #arrayToList(QueryName.getColumnList())#