Solved

CFQuery column names case sensitive

Posted on 2014-10-31
5
240 Views
Last Modified: 2014-11-01
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?
0
Comment
Question by:Errol Farro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 40416277
is there a specific reason for this
0
 

Author Comment

by:Errol Farro
ID: 40416312
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().getColumnLabels(), but how do I get the case name of the case sensitive column in the query instead of the upper case name CFQUERY produces.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40416490
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().getColumnLabels(),

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>

Open in new window

0
 

Author Comment

by:Errol Farro
ID: 40416547
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 ?
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 40416625
Depends on how you're generating the spreadsheet.  But based on your previous question, let's assume you have a variable containing a spreadsheet object already ie "yourSheet". Use SpreadsheetAddRow to overwrite the values in the first row.  

    <!--- Important: Set the last parameter to FALSE ie overwrite instead of insert --->
    <cfset SpreadsheetAddRow(yourSheet, commaDelimitedListOfColumnNames, 1, 1, false)>

Open in new window

0

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question