After a night's sleep, I see that it may be simpler to outut the data
directly from the WDDX packet. So instead of throwing the WDDX packet
away, you'd throw away your original query, and parse the packet. This
would avoid the use of Evaluate().
-David
----- Forwarded Message -----
From: David Shadovitz <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Date: Thu, 8 Feb 2001 21:23:51 -0800
Subject: Re: CF Query Column Order - Random?
Let me expand on Michael's mention of WDDX. (Thanks, Michael, for
pointing me in this direction.)
The WDDX packet contains a "recordset" tag which has a "fieldNames"
attribute. The value of this "fieldNames" attribute is a list of the
fields in the same order as they appear in the SELECT clause. So you can
convert the query to WDDX, get the correctly-ordered column names from
the packet, throw out the WDDX packet, and then loop over the column
names to output the original query. (I don't know if that's what Michael
meant, but it works for me.)
Here's some sample code.
1. Make the query
<cfquery name="qEmpData">select empID, empName, empSalary as Salary from
Employees</cfquery>
2. Convert the query to WDDX.
<cfwddx action="cfml2wddx" input=#qEmpData# output='qEmpDataWDDX'>
The packet looks like this:
<wddxPacket version='0.9'><header></header><data><recordset rowCount='5'
fieldNames='EMPID,EMPNAME,SALARY'><field name='EMPID'>...
3. Get the "fieldNames" part of the packet
<cfset fieldNamesStart = "fieldNames='">
<cfset fieldNamesStartPos =
FindNoCase(fieldNamesStart,qEmpDataWDDX)+Len(fieldNamesStart)>
<cfset tmp = Right(qDataWDDX,Len(qEmpDataWDDX)-fieldNamesStartPos)>
<cfset fieldNamesStop = "'">
<cfset fieldNamesLen = FindNoCase(fieldNamesStop,tmp)>
<cfset colList = Mid(qEmpDataWDDX,fieldNamesStartPos,fieldNamesLen)>
4. Ignore the WDDX packet.
5. Loop over the query rows and columns.
<table border="1" cellspacing="1" cellpadding="5">
<!--- Header row --->
<tr><cfloop index="iCol" from="1"
to="#ListLen(colList)#"><th><cfoutput>#Trim(ListGetAt(colList,iCol))#></c
foutput></th></cfloop></tr>
<!--- Data --->
<cfloop query="qEmpData">
<tr>
<cfloop index="iCol" from="1" to="#ListLen(colList)#">
<cfset colName = Trim(ListGetAt(colList,iCol))>
<cfoutput><td>#Evaluate(colName)#</td></cfoutput>
</cfloop>
</tr>
</cfloop>
</table>
To be safe, you could wrap step #3 in a CFTRY/CFCATCH block, and if there
is an error, set colList to the ColumnList query property. You wouldn't
get the nice column ordering, but at least it'd work.
I'm aware of these other methods for getting the query columns in the
right order:
* Parse the SQL statement's SELECT clause into your own ColumnList, and
then loop over that. This can be tricky, since fields may be designated
in many ways, such as {Employees.empID, empID, empID as ID,
Employees.empID "ID",empID "ID", etc.}
* Use CFOBJECT to get an ADO recordset
* Nate's CFX_QueryColumns tag
-David
On Wed, 07 Feb 2001 "Caulfield, Michael" <[EMAIL PROTECTED]>
writes:
>
> I think the order is alphabetical (if I remember correctly). To get
> the actual order you can either use a database server-specific
> method (get colorder from the table syscolumns in SQL Server)
> or you can WDDX the query, which for some reason outputs
> the columns in the right order.
>
> -----Original Message-----
> From: Scott Becker [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, February 07, 2001 11:53 AM
> To: CF-Talk
> Subject: CF Query Column Order - Random?
>
> When you make a <CFQUERY>, the variable queryName.columnList
> variable returns a list of field names from the query. However, this
> order doesn't appear to reflect the order of columns from the SELECT
> statement, or the table itself. Is there some logic to how its ordered?
> And, is there a way to access the columns in the order of the
> SELECT statement?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
