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">
userID = <cfqueryparam value="#userID#" cfsqltype="cf_sql_integer" />
AND lastName = <cfqueryparam value="#lastName#" cfsqltype="cf_sql_varchar" />
Your debug output should look something like the following:
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:
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.
- 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).
- 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.
- 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.
- Download the ColdFusion Debug SQL Explorer Extension (net.sourceforge.sqlexplorer.cfdebug_220.127.116.1170103.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.)
- Launch Eclipse and open the SQL Explorer perspective. Window -> Open Perspective -> Other -> SQL Explorer.
- 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.
- 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.