ColdFusion Debug SQL Explorer Extension

Have you ever wanted to be able to copy a query from ColdFusion's debugging output and run it in SQL tool to see the results? No big deal really, unless you happen to be using cfqueryparam. (You are using cfqueryparam aren't you?) If your query does use cfqueryparam you will notice that the SQL listed in the debugging output has question marks wherever you used a queryparam. The type and value of each queryparam is listed in the debugging output just below the query, but in order to run your query in a SQL tool you have replace each of the question marks in the SQL statement with the actual queryparam value, remembering to quote values as necessary. Still not a big deal, until you have to do this for a 500 line SQL statement with 50 or more queryparams. Then it gets to be a little annoying.

[More]

Related Blog Entries

Comments
Eric Rovner's Gravatar Thanks Nathan! This worked perfectly and should save me a ton of time.

A couple install notes that may help other people (especially seeing as I'm QA and a wannabe "enough to be dangerous" developer ;) ), I had a few sticking points with your instructions:

1. Your Step 4 "download link" is actually located as the Download link at the bottom of the page next to the del.icio.us link. Every other package was linked specifically in the step, so I thought you had omitted it. It took me a while (and Phil's help) to realize where the file was.

2. You mention the step 4 file is a jar file but the actual link is a zip file. I didn't realize you had to rename the zip extension to jar. I also didn't realize you shouldn't extract the entire contents of the file to the plugins directory, just copy the zip and rename to jar. Maybe these are common sense development understandings, but being QA, I didn't know.

Then, bingo, everthing worked. I think this should save me a lot of time QA'ing some of these queries as most have multiple parameters. Thanks again!
# Posted By Eric Rovner | 1/24/07 9:33 AM
Nathan Mische's Gravatar Hey Eric, glad your finding the extension useful! I use it everyday and it saves me tons of time, especially when dealing with large queries with a lot of queryparams.

As for #1 above, the link is confusing. As for #2, the download should be a jar file; I'm not sure why it came through as a zip for you. Anyway, I'm glad you got it figured out and I'll update the instructions to try to make it a little more clear what you need to do in step four.

By the way, I have a small update to the extension. Now that I know someone else is using it I'll try to get it posted.
# Posted By Nathan Mische | 1/24/07 11:25 AM
Steve Nelson's Gravatar You can do that same exact thing with about 10 lines of CF code in the debugger file. but it is very useful regardless.
# Posted By Steve Nelson | 4/25/07 1:23 PM
Nathan Mische's Gravatar Steve, I seem to remember you talking about modifying the debugging template several years ago and I actually thought about taking that approach. (At least I think it was you, it was awhile ago:) In the end I decided I wanted something that I could easily use without modifying any server code. I work on different servers all of the time, and while I'm sure most of my clients wouldn't mind, I have a feeling they would prefer I not muck around with their debugging templates.

For an even more flexible solution solution check out my CF Debug Copy extensions for IE and FireFox. I've added links to both in the Related Blog Entries of this post.
# Posted By Nathan Mische | 4/25/07 2:20 PM
Kevin Penny's Gravatar I'd also like to note - that with a MSoft SQL Server 2k5 Free/Express Edition, you may need to reference the Connection Property URL as:
jdbc:sqlserver://yourcomputername;instanceName=SQLEXPRESS;

This helped me after checking out the connection details on the msoft site (notice the prior 2k jdbc had me use the prefix: jdbc:microsoft:sqlserver:// - this one you remove the 'microsoft' from the URL)
# Posted By Kevin Penny | 9/20/07 8:34 AM
Chris's Gravatar OMFG! Thank you! This saved me HOURS!!!!
# Posted By Chris | 5/13/09 5:11 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.