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.

After having to do this far too often I decided to do something about it. Enter the ColdFusion Debug SQL Explorer Extension. I put this together last night after reading about SQL Explorer's extension points and reading through a few tutorials last week. What does it do? Say you have the following query:

<cfquery name="getUser" datasource="datasource">
SELECT
   *
FROM
   users
WHERE
   userID = <cfqueryparam value="#userID#" cfsqltype="cf_sql_integer" />
   AND lastName = <cfqueryparam value="#lastName#" cfsqltype="cf_sql_varchar" />
</cfquery>

Your debug output should look something like the following:

SELECT
   *
FROM
   users
WHERE
   userID = ?
   AND lastName = ?
      
Query Parameter Value(s) -
Parameter #1(cf_sql_integer) = 1
Parameter #2(cf_sql_varchar) = Test

Well you can now copy this into the SQL Explorer editor, click on the button with the ColdFusion logo and it will convert the editor text to:

SELECT
   *
FROM
   users
WHERE
   userID = 1 /* Parameter #1(cf_sql_integer) = 1 */
   AND lastName = 'Test' /* Parameter #2(cf_sql_varchar) = Test */

You can then execute the query and get your results. Instead of replacing each individual queryparam value you can copy and paste one block of text and let the editor do the work.

It is not perfect. If your query has a question mark in it it will not work correctly and I may not be properly quoting all datatype values, but for the most part it seems to work.

Here are instructions for setting SQL Explorer up with the extension, and connecting to a SQL Server database using the Microsoft SQL Server JDBC drivers. The extension is independent of the JDBC driver so while this example uses the Microsoft drivers, it should work with any JDBC driver that you can get working in SQL Explorer.

  1. Download and install the Eclipse SQL Explorer Plugin.
    • Download the sqlexplorere_plugin_X.X.X.XXXXXXXX.zip file.
    • Extract to your eclipse directory (i.e. C:/eclipse).
  2. Download and install the Microsoft SQL Server 2005 JDBC Driver 1.1 (Note you can use this driver for SQL Server 2000 or 2005).
    • Download the sqljdbc_X.X.XXX.XXXX.exe self extracting zip file.
    • Extract to a temporary location.
  3. Copy the SQL Server JDBC driver to the SQL Explorer lib folder.
    • Find the sqljdbc.jar in the temporary location from step 2 above and copy it to the eclipse/plugins/net.sourceforge.sqlexplorer_X.X.X.XXXXXXXX/lib folder.
  4. Download the ColdFusion Debug SQL Explorer Extension (net.sourceforge.sqlexplorer.cfdebug_1.0.0.20070103.jar) by clicking the download link below and place it in the your eclipse plugin directory. (The download link is in the list of links at the bottom of this post. You may need to right click and select "Save Link As" a jar file.)
  5. Launch Eclipse and open the SQL Explorer perspective. Window -> Open Perspective -> Other -> SQL Explorer.
  6. Set up the SQL Server JDBC Driver.
    • Open preferences. Window -> Preferences.
    • Select SQL Explorer -> JDBC Drivers.
    • Select MSSQL Server JDBC Driver and click the Edit button.
    • Click the Extra Class Path tab and Add the sqljdbc.jar (i.e. C:/eclipse/plugins/net.sourceforge.sqlexplorer_X.X.X.XXXXXXXX/lib/sqljdbc.jar)
    • Click on List Drivers. This should set the Driver Class Name to com.microsoft.sqlserver.jdbc.SQLServerDriver.
    • For Eample URL enter: jdbc:sqlserver://:<1433>;databaseName=. For more info on the JDBC URL see Connecting to SQL Server with the JDBC Driver.
    • Click OK.
  7. Set up the Connection Profile.
    • Find the Connections tab in the SQL Explorer perspective. It should be in the upper left of the Eclipse window.
    • Click on the Create New Connection Profile button or right click in the Connections tab and select New Connection Profile.
    • Give the connection profile a Name.
    • For Driver select Microsoft MSSQL Server JDBC Driver.
    • Enter a JDBC URL (i.e. jdbc:sqlserver://localhost:1433;databaseName=myDatabase).
    • Enter a username and password if needed.
    • Click OK.

Let me know what you think.

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.