Friday, March 21, 2008

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

#dsncolumnListrecord Countdata
1.CF8 (SQL2000)NEWRECORDID 1row[1] = 8
2.CF8 (SQL2005)NEWRECORDID 1row[1] = 8
3.JDBC 1.0 (SQL2000)NEWRECORDID 1row[1] = 9
4.JDBC 1.0 (SQL2005)NEWRECORDID 1row[1] = 9
5.JDBC 1.2 (SQL2000)GENERATED_KEYS 1row[1] = 10
6.JDBC 1.2 (SQL2005)GENERATED_KEYS 1row[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


#dsncolumnListrecord countdata
1.CF8 (SQL2000)NEWRECORDID 1row[1] = 11
2.CF8 (SQL2005)NEWRECORDID 1row[1] = 11
3.JDBC 1.0 (SQL2000)NEWRECORDID 1row[1] = 12
4.JDBC 1.0 (SQL2005)NEWRECORDID 1row[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.

5 comments:

Dan G. Switzer, II March 22, 2008 at 7:12 PM  

Make sure you don't have anything in the "Validation Query" setting for your datasource:

http://blog.pengoworks.com/index.cfm/2008/1/30/Two-important-CF8-bugsissues-detailed-yesterday

There's a bug in CF8 which sometimes places the value "0" in this line. If there's a value in there, it will run an additional query after your original query--which could explain the weird things your seeing.

cfSearching March 23, 2008 at 4:01 PM  

@Dan,

Thanks much for the link. You were right. The weird results were related to the second issue. I wrote it up here

CF8 + MS JDBC 1.2 Driver - Generated key issues. Curiouser and Curiouser

Though I still have not figured out how using SET NOCOUNT OFF causes the alias to appear in the "result" structure. But at least now some of the results are explained ;)

Dan G. Switzer, II March 24, 2008 at 9:37 AM  

Glad that helped you track down the problem. That's "0" issue is weird bug that I think affects a lot of servers, but the problem is generally "hidden".

Paul VanderWaal,  January 27, 2009 at 1:32 PM  

Through trial and error we discovered that if you use cfqueryparam around at least one of the input values of the insert query, the query will work just as it did in CF7. The @@Identity is returned without using the any of the changes recommended above. While we have still have to make 363 changes to our code, we will be able to run this more easily on both CF7 and CF8 servers, because the "Result" solution did not work in CF7 unless the name of the result is different than the query name, and then you have to do a cfif based on which CF version, 7 or 8, to determine where to pull the value from. So just use cfqueryparam.

Paul VanderWaal,  January 28, 2009 at 4:56 AM  

Once final comment to my post above. If you are using cfqueryparam in your insert query, the @@Identity is not returned in the Result at all in CF8, so you have to get it from the query object itself.

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep