Coldfusion 9 and Empty result set queries - Part 2

Progress!

I must say I'm a bit perturbed by what I found in researching this problem. I also need to make a clarification in my test code, I over simplified the simple query. If you were to run that query, after setting up the table, you would find that it does indeed work and you'd be saying "that guy's crazy, it works fine!" The problem comes in when records are eliminated due to aggregation, GROUP BY and such.

The following two links will be of help.

Adobe forum where it's being discussed:
http://forums.adobe.com/message/2631903

More importantly, the bug tracker entry.
http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=80384

Notice how they list this as a BENIGN problem and that there is a work around. Benign my ass! I urge everyone to go to the bug tracker and VOTE for this to be fixed.

So, for the work around? One acceptable temporary (IMO) solution is to set up a JDBC DSN to connect to your SQL Server database. In my case I needed a 2008 JDBC driver.

This brings me to another sore point with information on the internet. Far too often I've seen "helpful" people post solutions to problems only to gloss over 90% of the information used to FIX it. Not everyone is born with the knowledge to construct a JDBC connection string, or the ability to pull a driver class out of their ass. So here's my contribution to the half-assed help pile.

This will help those using Coldfusion 9.0

Microsoft JDBC 2.0 and 3.0 driver links.

MS JDBC 3.0 http://www.microsoft.com/downloads/details.aspx?FamilyID=%20a737000d-68d0-4531-b65d-da0f2a735707&displaylang=en#filelist
MS JDBC 2.0 http://www.microsoft.com/downloads/en/confirmation.aspx?familyId=99b21b65-e98f-4a61-b811-19912601fdc9&displayLang=en

1. Download one of those two, which ever you want. I'm using 3.0
2. Extract to a directory, any will do we're going to move the files when we're done anyway
3. Now, if you're using 3.0 you will find, buried in the directory structure that just extracted, two files
   • sqljdbc.jar
   • sqljdbc4.jar
   Only use ONE of these, for CF 9 you will want to use sqljdbc4.jar
4. Copy the sqljdbc4.jar to
   • cf_root/cfusion/lib (server configuration)
   • cf_webapp_root/WEB-INF/cfusion/lib (multiserver or J2EE configuration).
   I'm using Multiserver for this example so I put it in
   • "Z:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\lib\"
5. RESTART COLDFUSION

Now, you will need to log in to the CF Administrator and create a new datasource.

1. Give it a name,
2. and then select "OTHER" from the drop down list
3. and click ADD
4. Now for a JDBC URL, if you're super leet you don't need me to tell you, but for those "lamers" out there here's the JDBC URL that should get you by.
   jdbc:sqlserver://server name;databaseName=database name
   If you happen to be running multiple instances of SQL Server or are on a different port then this should work for you
   jdbc:sqlserver://server name/instance name:port number;databaseName=database name
5. Driver Class will be
   com.microsoft.sqlserver.jdbc.SQLServerDriver
6. Driver Name (you can name it whatever you want) SQL Server 2008 JDBC
7. Username: database user name
8. Password: database user password

The driver works great so far. If I run into any idiosyncrasies I'll post about it!