Adam Howitt's Blog

Nov 08
2006

JDBC Drivers 3.4 Bug for ColdFusion

At : 12:05 PM | Link | Coldfusion, SQL | Comments (5) | Send | 878 Views |

Not only did Adobe technote http://www.adobe.com/go/1a3c2ad0 not fix the issue I was having with quotes doubling, but it introduced a bug into all of our applications built from our CMS codebase for SQL Server.  I'll pre-empt some comments by saying that I know the following approach is non-standard and there is a workaround but the fact is that it was done and we are looking to fix it.  When we insert rows into tables with uniqueidentifier types we use triggers to return the generated UUID back to ColdFusion in some cases.  For example:

INSERT INTO tb_content_history(history_id,etc..)
(SELECT NEWID(), etc.)

For the curious, the alternative would be to use the SQL variable @@identity which only works on columns with a single identity column (not combined keys):

INSERT INTO tb_content_history(history_id,etc..)
(SELECT NEWID(), etc.)
SELECT @@identity as history_id
 

The bug itself is that the first code sample will fail to deliver a recordset so if your cfquery name was qAddHistory you get an error message if you try to dump out qAddHistory to the screen since the recordset is empty.  I'm not sure if this change in behaviour was deliberate or incidental but either way, my options are to find and fix every instance of the old style code to the @@identity approach or rollback the driver.

For anyone wishing to try the trigger approach here is some example code

CREATE TRIGGER [dbo].[tr_history_id] ON [dbo].[tb_content_history]
FOR INSERT 
AS
SELECT history_id FROM INSERTED

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
[Add Comment] [Subscribe to Comments]
  1. We ended up rolling back to DD3.3 as well, and have created a bug with Adobe showing specific forms of insert queries that did still function with 3.4/3.5, and those that did not (using various combinations of insert, insert into, insert into values, insert into select, etc.) Drop me a line if you would like our test page, it might help shed some light on how your inserts would have to be written in order to still use your triggers and use the newer drivers.

    Posted By Rich R : Posted At 11/8/06 11:37 AM
  2. Did you try using Microsoft's drivers? I typically choose a vendor's drivers over a 3rd party... we use several database platforms (Oracle, MS SQL, PostgreSQL, etc) and I haven't had a single problem when sticking with the vendor implementation of JDBC drivers...

    Posted By Brandon Harper : Posted At 11/8/06 11:53 AM
  3. I would revert to the 3.3 drivers. The 3.5 drivers -- released in the summer -- have a known issue with inserts that return @@identity.

    http://www.adobe.com/go/42dcb10a

    We're in a sticky situation here in that we need install the 3.5 drivers in order to upgrade our Oracle instances to 10gR2 (to fix an Oracle bug) but can't because they'll break most of our SQL Server apps.

    Good luck to you!

    Posted By Peter : Posted At 11/8/06 2:49 PM
  4. Ah, now there is a workaround since my post and Peter's update: "Known issues

    61315 - SQL Server - CFQUERY INSERT does not return a query object (resultset) when the table contains an IDENTITY type column. With 3.3 drivers included with 7.0 and 7.0.1, the query object returned would contain the identity key value and could be referenced with normal #Query.Column# syntax. Update to 61315: A new URL parameterAlwaysReportTriggerResults was added during the 3.4 release. For the old 3.3 behavior, add this parameter to the URL and set it to true. (Due to a bug in the "Connection String" field processing, for ColdFusion MX 6.1, 7.0 and 7.0.1, create an "Other" data source in order to add the parameter.)"

    Posted By Adam Howitt : Posted At 11/15/06 8:31 AM
  5. I've just tried the Microsoft drivers too per your suggestion Brandon and while the adobe workaround of setting the connection string parameter to AlwaysReportTriggerResults solves the problem with my triggers, there still isn't a fix for the doubling quotes :-(

    Posted By Adam Howitt : Posted At 11/15/06 8:34 AM
[Add Comment]