Column Names

[George]

I'm dealing with a lot of JSON these days. Clearly we all know that CF makes things fun by making object keys ALL caps when creating its structures. When you serialize query data the column names are also all caps. I write this first line to let people know I understand the why related to my question, I'm looking for the fix. 

So when using cfwheels to render json using renderWidth(), I get ALL Caps for columns names with a query return (findALL) and I get my original case (which is camel) when the I am rendering an object findONE). I am needing all the same so as to not confuse my models on the client side. 

<cfset employee = model("employees").findbyKey(999)>

<cfset renderWith(employee)> === ThisGetsOriginalCaseJsonColumnNames

<cfset employee = model("employees").findAll()>

<cfset renderWith(employee)> === THIS GETS UPPERCASE

Quick fix for this? As of now I figure i'm going to have to write a function that alters either the findall or the findone result

[Tom King]

I've seen this before : I had a similar problem, a dynamic form field which I needed to add to the session scope;

I ended up doing this:

<cfloop collection="#form#" item="key"> 

<cfscript>

         value=evaluate(key); 

 "session.data.#key#"=value; 

        </cfscript>

</cfloop>     

That was a specific issue where whenever CF8 got hold of the form struct, it would capitalise it.

For your problem, CF is actually returning the column names in uppercase to start with: you could cheat, and start a manual query (i.e queryNew("colName", "varchar") and then loop your existing query over it and repopulate it? Obviously that's got the massive downside of manually specifying your column names, but you might be able to find a way round that.

Just a thought...

[Andy Bellenie]

When you use this notation: <cfset struct["keyName"] =  foo> then the casing is preserved. 

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfwheels+u...@googlegroups.com.
To post to this group, send email to cfwh...@googlegroups.com.
Visit this group at http://groups.google.com/group/cfwheels?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Tom King]

But Andy, he's basically pulling a query, and CF auto-capitalises the column names. 

i.e not actually setting the column names himself implicitly.

T

[Andy Bellenie]

This function will convert a query into an array and preserve column name casing ready for serialization. You could make it simpler and lowercase the column names instead.

<cffunction name="queryToArray" returntype="array" output="false">

<cfargument name="input" type="query" required="true">

<cfset var loc = {}>

<cfset loc.result = ArrayNew(1)>

<cfloop from="1" to="#arguments.input.recordcount#" index="loc.i">

<cfset loc.row = {}>

<cfloop array="#arguments.input.getColumnNames()#" index="loc.col">

<cfset loc.row[loc.col] = arguments.input[loc.col][loc.i]>

</cfloop>

<cfset ArrayAppend(loc.result, loc.row)>

</cfloop>

<cfreturn loc.result>

</cffunction>

[Chris Peters]

If casing matters for your output (and it usually does), the best way to handle this is to loop through the query and create the data that needs to be serialized manually.

I recommend overriding renderWith's auto stuff by creating an override template at views/users/show.json.cfm (or whatever), then loop through the query manually to create the data struct that needs to be serialized. Then output the data serialized as JSON (or whatever you need).

Example view:

<cfset usersData = []>

<cfloop query="users">

  <cfset user = {}>

  <cfset user["firstName"] = users.firstName>

  <cfset user["lastName"] = users.lastName>

  ...etc...

  <cfset ArrayAppend(usersData, user)>

</cfloop>

<cfoutput>#SerializeJson(usersData)#</cfoutput>

--

[George]

Thanks, I feel like these responses present me with two options, that lead to two questions. (Chris I want to avoid View files because I'm looking to just make a change application wide for consistency, and so far I have avoided writing any View files!) 

OPTION A) I can probably live with all Lower Case or all Upper Case (and ignore my camel case in the DB schema). So my CFWHEELS question here is, since CF Defaults query columns to upper case, can I modify CFWHEELS so that all FindOne (or findbykey) methods will also return its object with upper case keys?

OPTION B.) It would still be nice to have the find all return CamelCase. So my question (not sure if its wheels) is how do I retrieve the CamelCase columns from a query. ANDY's solution uses QUERY.getColumnNames(), this also returns ALL caps for column names.

[Tom King]

I take it you're on ACF?

Railo has this as an option in the compiler:

Convert all struct keys defined with "dot notation" to upper case (CFML Default) 
or keep them in original case (according to the "bracket notation").
Example:
Convert to upper case:
sct.dotNotation --> keyname: "DOTNOTATION"
sct[""bracketNotation""] --> keyname: "bracketNotation"

Keep original case:
sct.dotNotation --> keyname: "dotNotation"
sct[""bracketNotation""] --> keyname: "bracketNotation"

[George]

Tom

I am considering the switch to Railo, but I'm not sure how to use the information you gave me even if I had it. 

Basically my two questions at this point are are either 

a.) How to retrieve the original camelCase columns in a findAll / cfquery (so that I may modify the query) 

OR 

b.) how to modify the wheels framework to return UPPER case key names for a findOne()

Basically write now I'm going through wheels and trying to just change Wheels code on a findOne() / findByKey() to return uppercase field names. However, I hate modifying wheels. I would rather write a wrapper for returned queries to preserve camel case. The reason this is preferred will probably want a wrapper on the query anyway since CF does that thing where it lists columns then data.

[Andy Bellenie]

Use queryName.getColumnNames() to return the column names with their original casing.

[George]

Andy queryName.getColumnNames() does not seem to work. It is still return ALL CAPS

[Andy Bellenie]

What DB are you using?

[George]

MySQL

[Andy Bellenie]

Ok, I've only tested that with MS-SQL. Hmm, last idea for now... what happens if you query the table using cfdbinfo?

[George]

where does FindOne/FindByKey get its column names? Cause the same table is in camel case when I use those methods.

[Andy Bellenie]

It uses cfdbinfo and stores the data within the model in variables.wheels.class.properties

[Randall Meeker]

I tried MSSQL as well and could not get getColumnNames() to work, however I cam accross this that worked.

queryName.getMetaData().getColumnLabels()

[Randall Meeker]

ok, but it will return what is put in my select statement, so I have to be sure to put the right case in my SQL!

On Tue, Jun 4, 2013 at 12:12 AM, Randall Meeker <randal...@gmail.com> wrote:

I tried MSSQL as well and could not get getColumnNames() to work, however I cam accross this that worked.

queryName.getMetaData().getColumnLabels()

--
Randall Meeker