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.
|1.||CF8 (SQL2000)||NEWRECORDID||1||row = 8|
|2.||CF8 (SQL2005)||NEWRECORDID||1||row = 8|
|3.||JDBC 1.0 (SQL2000)||NEWRECORDID||1||row = 9|
|4.||JDBC 1.0 (SQL2005)||NEWRECORDID||1||row = 9|
|5.||JDBC 1.2 (SQL2000)||GENERATED_KEYS||1||row = 10|
|6.||JDBC 1.2 (SQL2005)||GENERATED_KEYS||1||row = 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!"
|1.||CF8 (SQL2000)||NEWRECORDID||1||row = 11|
|2.||CF8 (SQL2005)||NEWRECORDID||1||row = 11|
|3.||JDBC 1.0 (SQL2000)||NEWRECORDID||1||row = 12|
|4.||JDBC 1.0 (SQL2005)||NEWRECORDID||1||row = 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 ;)
|#||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.