More on the new SpyAttributes feature of the"new" 3.5 JDBC drivers for CFMX

Note: This blog post is from 2006. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.

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.

For more content like this from Charlie Arehart:

• Signup to get his blog posts by email:
• Follow his blog RSS feed
• View the rest of his blog posts
• View his blog posts on the Adobe CF portal

Need more help with problems?

• If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
• See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed