There were a few nice changes to cfqueries in CFMX7 that may have been missed by some folks. First - did you know that everytime you run a query, a variable called CFQUERY.executionTime is created? This variable contains the time the query took to execute. Also, in your query variable itself, you have more values like recordCount and columnList.
The problem with this is that you have metadata about the query in two places. Some in the query itself, some in a hard coded variable named CFQUERY. This by itself meant cfquery was partially not thread safe.
Luckily CFMX7 solves this. It allows you to specify the name of the variable to store query metadata. (In fact, all cfml tags, except cftimer I believe, now allow you to specify a result variable.) By using result="data", you have a struct that contains the execution time, the sql (!!), column list, record count, and whether or not the query is cached.
So - this by itself is pretty useful. However, there is another kind of metadata you can get with a query. If you do getMetaData(queryob), you will get an array of structs. Each item in the struct is a column in your query. The order, unlike columnlist, matches the order in your original SQL. Each struct contains the name and type of the column, and if the column is case sensitive or not.
Archived Comments
This is big news -- especially the SQL metadata!
I also like the fact that you can specify the column data types in dynamic queries now. I'm going to run some benchmarks for type-less and typed dynamic queries - you would have to think that the typed dynamic queries would have to be quicker.
Ray, this is really handy for any number of reasons. I am even thinking that it might be worth the trouble to write a CFC that could be temporarily dropped into a given page on some site or other, to get this data for debugging and analysis purposes.
Thanks for pointing this out, and in such a clear manner.
Im not sure you need to write a cfc for this. You could do one of two things.
1.) dump it at the bottom of a page <cfdump var="#getMetaData(query)#">
or
2.) in your onRequestEnd application event do something like this
if isDefined('url.qmetadata') dump var="#getMetaData(url.qmetadata)#
I think these additions should be in the featurelist of CFMX also, .. they seem to look nice, but there are so much more than that.
People working on CF persistence layers will have tears of joy with the SQL metadata, because now you can actually seperate different datasets in persistence layers based on SQL metadata.
I think the SQL input is the greatest addition in SQL metadata. For caching frameworks, and lazy loading, this is awesome.
Great!!
Ray, I know this is an older post, but do you know where I could find a list of all of the possible values for the TypeName that comes in the struct in the array returned by getMetaData() on a query?
Sorry, no. Although they seem to kind of match with the types from cfqueryparam.
ok, thanks. It seems very different on different databases...
Im going to try to use this in an enhancement of my qBrowser and was just wondering.
Thanks,
Thanks so much for brining this out.. I have been looking for this information for two days. All I want is the list of columns, in the order I queried them, in order to dynamically build an table of results.
Thank god it's actually possible, I was losing the will to live.
;-)
Neil, if all you want is a list of columns in order of they way you queried them, use queryname.getColumnNames()
I don't agree with that Ryan. It may work, but it isn't document. Using undocumented code is never a good idea. It may be fun to play with it, but you are risking your code breaking in the future.
This is true. It is somewhat documented though here:
http://livedocs.adobe.com/c...
I believe it is actually there for flash/flex integration though.
Interesting. I'm not sure I'd consider that "official" though if it isn't listed in the reference. I'd probably consider that an accident.
I am having some issues with CF mail spool locking up and not sending e-mails unless a restart of cf services is done. This happens almost everyday. Was wondering if any knows what the cause of this is and how to resolve it. I've looked all over the place and it all seems to just point to restarting cf services, but that is not a best solution for us as we can't restart the services everyday.
Long, this comment is really off topic for this blog entry. I'd recommend ensuring your server is patched up to the latest version.