CF8 + MS JDBC 1.2 Driver - .. And for my next trick, I will make this query disappear!

A few weeks ago I mentioned a possible bug I found working with CF8's built in MS SQL driver. Now a recent post, by an adobe forum member named sws, mentioned another strange behavior. This time with the MS JDBC 1.2 driver. So I decided to run a few tests with the three main drivers (CF8, MS SQL JDBC 1.0 and JDBC 1.2) and compare the results.

Now, I expected a few variances here and there. But the results were a bit bizarre from the start. So I just continued running more tests, reviewing the results and scratching my head. After a while I started to wonder if maybe I had fallen down a rabbit hole. A few tests later, I swear the song They're coming to take me away popped into my head. (If you have never heard the song, it is worth a listen. Once. It is .. an odd ... song. Yet, strangely appropriate for situations like this ;)

Anyway, if you take a look at a basic INSERT/VALUES statement everything seems okay until you get to the JDBC 1.2 driver. This was the issue mentioned by sws. CF does return a query, but the column name is GENERATED_KEYS. Huh? What happened to the column alias? Even more confusing is that jdbc has a method called getGeneratedKeys() and GENERATED_KEYS is also the result name used for ids from MySQL databases. So it is difficult to identify the real culprit here.

SQL:	INSERT INTO SomeTable ( Name ) VALUES ( 'Test' ) SELECT SCOPE_IDENTITY() AS NewRecordID
#	dsn	columnList	record Count	data
1.	CF8 (SQL2000)	NEWRECORDID	1	row[1] = 8
2.	CF8 (SQL2005)	NEWRECORDID	1	row[1] = 8
3.	JDBC 1.0 (SQL2000)	NEWRECORDID	1	row[1] = 9
4.	JDBC 1.0 (SQL2005)	NEWRECORDID	1	row[1] = 9
5.	JDBC 1.2 (SQL2000)	GENERATED_KEYS	1	row[1] = 10
6.	JDBC 1.2 (SQL2005)	GENERATED_KEYS	1	row[1] = 10

So I tried adding a SET NOCOUNT statement to see if that helped. Well, not only did it not help, it made the query disappear. Pretty neat trick ;) Perhaps I should have been more specific about what kind of help I wanted.

Now it has been a long day, so at this point I started dreaming up bad infomercials in my head: "Developers, are you bothered by an overabundance of pesky query objects? Well worry no more. The 'MS JDBC Query Remover 2005' is the solution to your problem! Plus, it is only $9.95 and comes with a free set of ginsu knives!"

SQL:	SET NOCOUNT ON INSERT INTO SomeTable ( Name ) VALUES ( 'Test' ) SELECT SCOPE_IDENTITY() AS NewRecordID SET NOCOUNT OFF
#	dsn	columnList	record count	data
1.	CF8 (SQL2000)	NEWRECORDID	1	row[1] = 11
2.	CF8 (SQL2005)	NEWRECORDID	1	row[1] = 11
3.	JDBC 1.0 (SQL2000)	NEWRECORDID	1	row[1] = 12
4.	JDBC 1.0 (SQL2005)	NEWRECORDID	1	row[1] = 12
5.	JDBC 1.2 (SQL2000)	--	1	(query not defined)
6.	JDBC 1.2 (SQL2005)	--	1	(query not defined)

But wait, it gets better. As I continued to try different things, I decided to try using SET NOCOUNT OFF before running the insert. Well, it did not produce a query object. But it did somehow add the column alias to the "result" structure. So if this were actually a documented behavior, I could access the value as #result.NewRecordID# ;) Of course it is not, so I would not seriously use it. Just another neat driver trick I discovered today ;)

SQL:	SET NOCOUNT OFF INSERT INTO SomeTable ( Name ) VALUES ( 'Test' ); SELECT SCOPE_IDENTITY() AS NewRecordID;
#	dsn	columnList	record count	result aliasName
1.	CF8 (SQL2000)	NEWRECORDID	1	(not defined)
2.	CF8 (SQL2005)	NEWRECORDID	1	(not defined)
3.	JDBC 1.0 (SQL2000)	NEWRECORDID	1	(not defined)
4.	JDBC 1.0 (SQL2005)	NEWRECORDID	1	(not defined)
5.	JDBC 1.2 (SQL2000)	--	1	NewRecordID = 207
6.	JDBC 1.2 (SQL2005)	--	1	NewRecordID = 240

Are you starting to see why the song I mentioned popped into my head? ;) Now, I am really not certain whether the problem is with the MS driver, with CF's communication with the driver, or both. For kicks, I may run some tests with the jTDS driver. Just to see what tricks ColdFusion + jTDS has up its sleeve. But I will leave that for another day. I think I have reached my limit on query magic for today.