Railo tip: get a query's columnlist case-sensitive

Posted by Paul Klinkenberg in Railo on November 16, 2010

A member of the Railo mailing list asked if he could get the columnlist of a query object case-sensitive. Because #queryObject.columnlist# always returns it uppercase.

One good answer was to look at #getMetaData(queryObject)#, which returns an array with structs with keys isCaseSensitive, name, typeName.
So that's an option, to just loop over that array.

But I knew it must be easier, so I looked in the Railo source code, and found this simple solution:

<cfset caseSensitiveColumnList = queryObject.getColumnlist(false) />
<cfset upperCaseColumnList = queryObject.getColumnlist() />

Pretty cool eeh? Start using Railo today!

NOTE: see the comments underneath; if you typed the actual column names in the SELECT statement, like in "SELECT userID, userName from users", then the case you used there will be returned. But if you used "SELECT * from users", then the actual table column names are returned.

| Viewed 5485 times

#1 by Todd Sharp - November 16, 2010 at 7:41 PM

Cool stuff! To do the same thing in Adobe ColdFusion try:

<cfset caseSensitive = query.getMetaData().getColumnLabels() />

#2 by Todd Sharp - November 16, 2010 at 7:45 PM

It should be noted that this method will really only just return the case you used in your SQL statement (which may or may not be the exact case as it exists in your database). So if you have:

select FIRSTNAME, LasTnamE, email

You'd get the exact case that you used back in the array.

Don't know if the same is true with Railo...

#3 by Paul Klinkenberg - November 16, 2010 at 9:18 PM

Hi Todd, that's good to know! There's always an equivalent in the other engine ;-)
Railo also uses the same case as used in the sql statement btw.
If the columns are not explicitly defined, as with "select * from table", then both engines return the correct case (tested with SQL Server 2005).

#4 by Todd Sharp - November 16, 2010 at 9:25 PM

Nice. I confirmed the same behavior with MySQL too. Although nobody should ever use select * ;)

#5 by Paul Klinkenberg - November 16, 2010 at 9:28 PM

I actually wanted to write that down underneath the blog post: "So it does pay to be a lame programmer sometimes" ;-)

#6 by Todd Sharp - November 16, 2010 at 9:40 PM

HA! So true...

#7 by Rainer - March 9, 2011 at 10:22 PM

Very nice indeed :P

Lucee Developer