How can I stop Coldfusion from converting query column names to upper case?

Question

When I select data from a MySQL table using the cfquery tag in ColdFusion 8, the column names are all converted to uppercase even though I've stored them in camelCase in the database table. Is there a way to make ColdFusion respect the case of the column names?

The reason I'm asking is because I'm returning the query result as a JSON object and I don't want to use upper case property names on the JavaScript side as I reserve that naming convention for constants.

Tyrosine

Answer 1

Use getMetaData on the query object. It returns an array of columns showing the same case you used in the query.

Scrutineer

Oh I'm sorry - you want the case as it is in the database. Not upper, and not what the SQL showed. The best you can do is use cfdbinfo. That should work. You would need to use that and return it along with your query result. – Scrutineer

To clarify, would that mean that I would have to send two JSON objects basically, one containing the result set and one containing the column names? Then still do some matching algorithm to match the data to the correct properties? Or is there perhaps a way to merge the column names fetched from cfdbinfo with the query result object? – Tyrosine

I'd probably build the JSON by hand, using the column names from the CFDBINFO-look-up, and the values from the query. – Mamie

Answer 2

queryName.getMetaData().getColumnLabels()

return an array of query column label with original case from query

Evonevonne

I think they need the case from the database. In which case they need to use cfdboinfo or mySQL's metadata tables as mentioned. The undocumented metadata method returns the case used in the sql which can be totally different. All depends on how it is typed. – Mayoralty