Of course, ORM support is just a different way that the CFML engine (through the ORM framework) sends SQL to a database via a regular DSN, just like any other request, so there's nothing really "tricky" about this. It's just about realizing that while you don't write the SQL yourself, it's still generated by the CFML engine/ORM framework, and you may not realize/consider the available tools which can spy on it, just like any other DB processing from within CF. Indeed, some people may not even realize how many options exist to spy on JDBC interactions from their CFML engine to the database engine.

The good news is that there are several approaches, some included in CF (some depending on the edition), and some available separately which would work in any edition of CF or the other CFML engines (Open BlueDragon, Railo, etc.), and with any of the ORM frameworks.

(FWIW, besides the aforementioned Transfer and Reactor, there are still others, which I mention in my CF411 list as CFML ORM Frameworks. Indeed, note that you can run Hibernate on CF prior to CF9.)

Built-in ORM Logging Option

There are several resources where you can learn more on that. Besides the CF9 docs page on the ORM settings, there is also a blog entry by Adobe engineer Rupesh Kumar. I've not yet used it, so don't know how it compares to the other options below. I will leave them for all to consider.

Using FusionReactor or SeeFusion

Users of any CF edition (6+) or CFML engine can use tools like SeeFusion and FusionReactor, which have always had the ability to monitor database interactions by "wrapping" the datasource to be monitored. FusionReactor engineer John Hawksley has posted a recent article specifically on monitoring CF9's ORM interaction, in the FR Devnet site, Using FusionReactor's JDBC Driver Wrapper With ColdFusion 9 ORM. Its concepts would apply to any ORM, of course.

Similarly, I've written generically about its database monitoring feature in What is the FusionReactor datasource monitoring feature? Why would I use it? Powerful stuff. As I point out in that article, the concepts discussed apply as well to SeeFusion's ability to monitor queries by wrapping datasources.

CF Enterprise, with a caveat

I also mention in that article that those running CF 8 (or now 9) Enterprise will find that it has built-in wrapping of datasources, such that its Enterprise Server Monitor reports database interactivity, so that can also monitor the ORM interactions.

A key difference for FR and SeeFusion over that, though, is that while they all have means to highlight queries that exceed certain limits, only FR and SeeFusion have the built-in ability to log every DB interaction (FR to text logs and SF to a database, and they both do so with low overhead). The CF Enterprise Server Monitor has no logging ability at all, and while you can set its interface to lower the threshold for what active queries to show, it has no means to show queries which have executed in the past.

It's just that it can be useful sometimes to see every single DB interaction for a request (or all requests), and it may be all the more interesting for discovering/observing what's happening with ORM interactivity.

Another alternative CF feature

Still another little-known feature for spying on JDBC interactions in CF is by way of the JDBC "spy" feature, which does in fact allow logging of all JDBC interactions mde from within CF. This feature was first enabled by way of the DataDirect 3.5 driver update which was made available (as an optional upgrade for 6 and 7) in the CF 7.02 timeframe. I wrote about the Spy feature back back in Aug 2006.

Since then, CF 8 (and now 9) offer it instead as a new "log activity" option in the "advanced settings" for a datasource definition in the CF Admin (which is disabled by default). I pointed this out in another entry from 2007 as one of many easily missed changes for the CF 8 Admin.

This "log activity" output is not as easy to interpret as FusionReactor's logs, and can indeed be voluminous (moreso than FR's), so be careful. Anyway, it's one of the several ways you can monitor JDBC interactions between CFML and your DB engine. Again, any of these may be useful for monitoring any of your CFML/database interactions.

Generic DB Monitoring tools

Indeed, it's worth noting finally that while the focus here has been watching the DB interaction from CF (and the ORM framework) to the database (by watching the JDBC traffic going out of CF and returning), you could just as well watch the DB interactivity from the DB's perspective instead (watching it coming and and being returned).

There are many tools that can monitor database processing, available for each of the major databases (free and commercial). I list several such tools in one of my CF411 section, Database/SQL Monitoring Tools.

Hope all that's helpful, whether you use ORM or not.

This news has been shared in various blog entries over the past couple of years. I'd like to reprise them here for your edification. Just pay close attention to whether you mean to have them look at CFMX or CFMX 7. They each point to a directory such as C:\CFusionMX\lib or C:\CFusionMX7\lib. Make sure you use the value appropriate to your configuration:

• Pete Freitag showed some CFML code that gives you the answer on screen:
• Brandon Purcell showed a CFML approach that writes its output to the the CFMX log files instead (as of tonight, that blog entry is failing, but you can find its info thanks to the Google cache entry for that page)
• Finally, Sarge showed how to get the info via the command line

First, notice that there is a site at the DataDirect site (from whom Adobe purchases the JDBC drivers):

http://media.datadirect.com/download/docs/jdbc/alljdbc/reference/spy.html

It discusses some additional attributes. And still more useful info is offered at another page on their site:

http://media.datadirect.com/download/docs/jdbc/alljdbc/reference/diagnostics2.html

This actually shows what the log of info would look like, if you want to decide first if its info is worth the bother setting up.

This other page also talks about ways to turn the spy logging on and off at runtime, using Java method calls. Perhaps someone with more familiarity with the underlying Java classes for these drivers can translate the code there into something we can use in CFML. (It's late, and I don't have the energy.)

As such, be aware that the spy feature could generate a lot of data, so you may want to consider creating two datasource for each DB you may want to monitor. Either rename them to enable whichever you want to be in effect, or change if your code lets you specify a DSN via a variable, you can change the value to the "spying" version when you need it.

I did test and determined that also you can just add/remove the connection string from the end of the JDBC URL (see the technote for more), and immediately the logging stopped against the datasource. As a trick, I stuck the connectionstring value into the "description" field of the datasource definition, to keep it readily accessible if I wanted to add it back. (See my previous entry for a warning about putting it into the ConnectionString field instead.)

Finally, I should clarify that this Spy feature is not to be confused with the much older (and more powerful) p6Spy project, which has gone from open source to commercial to kind of stagnant. It still has great use, and in fact has a useful related SQL Profiler that's worth looking at. Still more at http://www.p6spy.com/, and maybe I'll write more about it later.