Updated SQL Explorer Extension

Well, I found out some other people are using my CF Debug SQL Explorer Extension so I'm releasing this update. In my original extension I forgot to escape single quotes, so this version fixes that. (I noticed I forgot to handle this while looking through Ben Nadel's code for a bookmarklet which does something similar to this extension.) This release also includes the source if anyone is interested, just look in the .jar. Installation instructions are still the same and you can find those in the related entry. Enjoy!

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.


Querying Excel Spreadsheets

A few weeks ago I had the need to query an Excel spreadsheet, several actually. There are a few different approaches you could take to get the Excel data into a ColdFusion recordset. You could import the spreadsheet into a database and query the database, or if you are handy with Java you could use Jakarta POI (see Matt Liotta's ExcelQuery CFX tag on OpenXCF for an example), but for what I was doing these options seemed like overkill. The approach I ended up taking used a dynamic datasource which allowed me to query my spreadsheets like so:

<cfquery name="test" datasource="dynamicXLS">
SELECT FirstName, LastName
FROM [Sheet1$]
IN 'C:\myXls.xls' 'EXCEL 8.0;'

You can find out how to do this in a TechNote on the Adobe site. One thing I will note, I'm using Excel 2003 (version 11.0), however the Microsoft Excel Driver used in the Windows XP ODBC Data Source Administrator only allows you to select up to Excel 97-2000. I found that this limited me to using 'EXCEL 8.0;' in my cfquery statements, but it seems to work fine.

UPDATE: I guess I missed this but Rob Gonda had a post on this same topic just a few months ago. He describes one additional method of using the JDBC drivers directly and provides a sample UDF based on this approach.

BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.