Errol Farro
asked on
CFQuery column names case sensitive
When doing a CFDUMP on CFQUERY, the column names are always displayed in UPPERCASE.
Is it possible to get the column names of CFQUERY is the case they were created on an SQL server ?
Example
CREATE TABLE [dbo].[LOGIN](
[lgId] [int] IDENTITY(1,1) NOT NULL,
[lgUserId] [nvarchar](64) NULL,
[lgPassword] [nvarchar](128) NULL,
[lgRole] [nvarchar](16) NULL,
.........
Is it possible for CFQUERY to return the column names as lgId, lgUserId, lgPassword etc... and not LGID, LGUSERID, LGPASSWORD?
Is it possible to get the column names of CFQUERY is the case they were created on an SQL server ?
Example
CREATE TABLE [dbo].[LOGIN](
[lgId] [int] IDENTITY(1,1) NOT NULL,
[lgUserId] [nvarchar](64) NULL,
[lgPassword] [nvarchar](128) NULL,
[lgRole] [nvarchar](16) NULL,
.........
Is it possible for CFQUERY to return the column names as lgId, lgUserId, lgPassword etc... and not LGID, LGUSERID, LGPASSWORD?
is there a specific reason for this
ASKER
The reason is that we have ColdFusion program which creates an excel file. The excel file is then uploaded onto a hosted server using a proprietary tool. The proprietary tool requires the cases of the Excel column names and the hosted table to be the same.
I know we can use qryMae.getMetaData().getCo lumnLabels (), but how do I get the case name of the case sensitive column in the query instead of the upper case name CFQUERY produces.
I know we can use qryMae.getMetaData().getCo
Unfortunately CF doesn't provide a simple (documented) way to access the original column names from queries. As you noted, query.columnList is always upper case - and in alphabetical order.
>> I know we can use qryMae.getMetaData().getCo lumnLabels (),
I know it returns an array, but doesn't that work for you? When used with a regular db query it returns the correct case and column order. To generate your list, just arrayToList().
The only other way I know of is to use the getMetaData() function. It returns an array of the name, datatype, etc... But that seems like a longer version of what you suggested already. Am I missing something?
>> I know we can use qryMae.getMetaData().getCo
I know it returns an array, but doesn't that work for you? When used with a regular db query it returns the correct case and column order. To generate your list, just arrayToList().
The only other way I know of is to use the getMetaData() function. It returns an array of the name, datatype, etc... But that seems like a longer version of what you suggested already. Am I missing something?
<cfset meta = getMetadata(q)>
<cfset colNames = "">
<cfloop array="#meta#" index="col">
<cfset colNames = listAppend(colNames, col.name)>
</cfloop>
ASKER
Your solution will work.
Strategy is to use CFQUERY to create Excel with upper case header and then with the data obtained from getMetaData() replace the first row of the Excel spreadsheet with case sensitive fields.
How do I update the first row of an Excel spreadsheet ?
Strategy is to use CFQUERY to create Excel with upper case header and then with the data obtained from getMetaData() replace the first row of the Excel spreadsheet with case sensitive fields.
How do I update the first row of an Excel spreadsheet ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.