[Looking for Charlie's main web site?]

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.
In my last entry, I mentioned briefly the new "JDBC Spy" feature that's available in the new 3.5 JDBC drivers for CFMX. I want to point out some more info for those interested in investigating the Spy feature further.

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: Need more help with problems?
  • If you may prefer direct help, rather than digging around here/elsewhere or via comments, I can help via my consulting services
  • See that for more on how I can help a) over the web, safely and securely, b) usually very quickly, c) teaching you as we go, and d) with satisfaction guaranteed
Comments
Fascinating Charlie. Thank you for sharing this. If anyone uses this in production I'd love to hear about it.
# Posted By Raymond Camden | 8/8/06 9:13 AM
Thanks, Ray. Glad you enjoyed it.

I also want to make sure people see the comments from a couple of Adobe folks in the forum entry I pointed to above, who have added some insights since I wrote this:

http://www.adobe.com...

Take their comments to heart (as to why they have not included these updated drivers automatically, even in 7.02) before you go implementing them. In other words, test things carefully.
# Posted By Charlie Arehart | 8/8/06 3:09 PM
It's a shame p6spy is no longer active, I blogged how to get it running with CFMX

http://coolskool.blo...
# Posted By kola | 8/8/06 3:35 PM
Well, to be clear, P6Spy *can* still be used. I just said it was "kind of stagnant" because it's not been updated in a couple of years. Glad to hear of a pointer about it. I hope folks will check out your entry.
# Posted By Charlie Arehart | 8/8/06 3:55 PM
Great stuff Charlie. I remember coming across this some time ago when I updated to MySQL 5.x, and then forgetting about it.

Thanks for the info. This is actually 'need-to-know' stuff.
# Posted By Gary Funk | 8/8/06 5:43 PM
Thanks Gary, and enjoy. Just keep in mind the admonition I added in a comment above, about being careful before putting these updates into production, as suggested in the adobe forum message I point to.
# Posted By Charlie Arehart | 8/8/06 7:23 PM
Going back to this entry and last comments from a couple of years ago, I mentioned at the end of the entry about a tool at p6spy.com, and someone noted that it had gone inactive. Here's good news: it's back in operation.
# Posted By Charlie Arehart | 12/30/08 6:32 PM
Doh! I see now I was mistaken. What has been revived is not p6spy, but rather SQLSpy, which in its new incarnation is SQL Spy 6, thus my confusion. The're not the same, but also not too different. If you were interested in p6spy, you may want to check out sql spy 6. :-) More at http://ispysql.com/i...

Also, I should note that some of the functionality of p6spy is now built into the DataDirect 3.5 drivers, and while CF8 uses 3.6 (so you get that feature there), you need to add the 3.5 drivers to CF 6 or 7 (including 7.01 and 7.02). More in a blog entry I did some time ago at: http://carehart.org/...
# Posted By Charlie Arehart | 12/31/08 10:22 AM
Copyright ©2019 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting