Problem with jTDS driver and CF8

[jja...@googlemail.com]

Hi all,

I am having problems using Transfer with the jTDS JDBC Driver (1.2.2)
and CF8 (update 8.0.1) and MS SQL Server 2005.
It is not possible to insert a new record into the database via the
framework. The error I get is the following:

You have attempted to dereference a scalar variable of type class
java.lang.String as a structure with members.

The error occurred in C:\Inetpub\wwwroot\transfer\com\sql
\TransferInserter.cfc: line 162
Called from C:\Inetpub\wwwroot\transfer\com\sql\TransferInserter.cfc:
line 66
Called from C:\Inetpub\wwwroot\transfer\com\sql\transaction
\Transaction.cfc: line 210
Called from C:\Inetpub\wwwroot\transfer\com\sql\transaction
\Transaction.cfc: line 89
Called from C:\Inetpub\wwwroot\transfer\com\sql\TransferInserter.cfc:
line 50
Called from C:\Inetpub\wwwroot\transfer\com\sql\SQLManager.cfc: line
62
Called from C:\Inetpub\wwwroot\transfer\com\Transfer.cfc: line 183
Called from C:\Inetpub\wwwroot\transfer\com\Transfer.cfc: line 166
Called from C:\Inetpub\wwwroot\TestProject\index.cfm: line 16

160 : else
161 : {
162 : invokeSetPrimaryKey(arguments.transfer,
qAfterInsertTransfer.id);
163 : }
164 : }


The problem is related to the auto-generated keys feature which might
be handled in a different way by the JDBC drivers. I have no problems
if switching to the default driver shipped with CF (I think it's the
one from DataDirect)

If you want to reproduce the problem:
1. Download jTDS from http://jtds.sourceforge.net/
2. Extract the jar file from the zip and copy it into <CF_HOME>\lib
3. Restart CF
4. Create Datasource in CF Admin using the following parameters
(select "other" from driver dropdown list)
JDBC URL: jdbc:jtds:<server_type>://<server>[:<port>][/<database>]
[;<property>=<value>[;...]]
(example: jdbc:jtds:sqlserver://127.0.0.1/TestProject)
Driver Class: net.sourceforge.jtds.jdbc.Driver
Driver Name: JTDS
add username and password.
5. use Transfer with the created datasource and try to insert a new
row

I do not know if there is a parameter to use in the JDBC URL or any
jTDS property to let the driver behave in another way. Any hints on
that are really welcome.

To "manually" reproduce the problem (without using transfer), you can
do the following:

<cfquery name="insertTest" result="insertTestResult"
datasource="TestProjectJTDS">
INSERT INTO Test (
bla
)
VALUES (
'test'
)
SELECT SCOPE_IDENTITY() AS ID
</cfquery>
<cfdump var="#insertTest#" />
<cfdump var="#insertTestResult#" />

This will work with the DataDirect JDBC driver and will produce an
error in jTDS (insertTest is undefined).
A possible workaround could be to do the following:

<cfquery name="insertTest" result="insertTestResult"
datasource="TestProject">
INSERT INTO Test (
bla
)
VALUES (
'test'
)
SELECT SCOPE_IDENTITY() AS ID
</cfquery>
<cfif structKeyExists(insertTestResult, "id")>
<!--- use the id provided by cf in the result structure --->
<cfdump var="#insertTestResult#" />
<cfset newID = insertTestResult.id />
<cfelse>
<!--- use the manually added id from scope_identity --->
<cfdump var="#insertTest#" />
<cfset newID = insertTest.id />
</cfif>

But this is not available in Transfer. So I recommend to add such kind
of logic (or maybe another - better - solution), to support other JDBC
drivers within the framework.

Any comments are appreciated.

Cheers,
Jan

[Mark Mandel]

Transfer does work with the driver that comes with CF (which is what
it is tested on).

Is there a particular reason why you are using the jtds driver?

I don't know anything about the jtds driver, so I couldn't say how you
could enable it so that it would allow multiple statements per line.

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Mark Mandel]

Stupid question... and I can't remember off the top of my head -

If I run the
'SELECT SCOPE_IDENTITY() AS ID'

After the insert statement, but inside the same transaction, that
would work, would it not?

Would that solve all the issues?

Mark

[Mark Mandel]

Sorry, to be clearer -

Run it as:

<cftransaction>
<cfquery>insert...</cfquery>
<cfquery>SELECT SCOPE_IDENTITY() AS ID</cfquery>
</cftransaction>

Are there thread issues there?

Mark

[Matt Quackenbush]

Mark,

I believe the answer to your question is "yes, you are thread safe".  Not sure why you would want the second query though.  SQL Server will run it just fine in a single query.

<cfquery>
insert.... ;
select scope_identity() as id;
</cfquery>

Oh, I just realized that you're discussing a different driver.  Heh.  Not sure about that one.  The one thing that I do notice in the sample that Jan posted is that there are no statement-ending semi-colons (;).  Again, I'm not familiar with that driver, so it may or may not make a difference in the behavior.

One thing I would suggest though, if at all possible: if the jTDS driver requires two separate queries for a "fix", can there be some sort of driver detection that would dictate whether or not there are two queries run?  I would surmise that the majority of CF/SQL Server users are using the standard driver, and having an additional query for all of your inserts could possibly wreak havoc upon an application (under load).

</twocents>

[Mark Mandel]

I guess the question still stands - why are you using jTDS? ..and how
many other people desire to use it?

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[jja...@googlemail.com]

Hi all,

many thanks for the feedback. To answer your question:
As different tests have shown, the jTDS driver is much faster under
several conditions.
In our environment, it also performs well in a dozen of large
applications. Before we started using it (I think 3 years ago), we
performed several tests specific to our applications and found out,
that the driver was up to eight times faster than the one shipped with
CF. That might have changed in the meantime, but we are still very
happy with it. And I also think that there are many other developers
out there using jTDS.

There are links to different benchmarks on http://jtds.sourceforge.net/
(part "Why use jTDS?")
Beside that, the driver is open-source.

To be clear: It is not a Transfer-only related problem. It is
something that has to do with the new CF8 "get-generated-key" feature
(which comes out of the query-result variable).
I found a blog post that may be related to the issue here:
http://www.mischefamily.com/nathan/index.cfm/2008/1/28/Problems-with-CF-8s-Generated-Keys-Feature

To let our applications work with CF8 and jTDS, we changed the code of
each database insert as described in my original post:
(if "id" exists in the query result variable, use it, otherwise use
the one that comes from the manually added "select
scope_identity()...")
This way all of our applications work in the most common system
environments (each combination of CF7 or CF8, CF JDBC driver or jTDS)
I also changed the Reactor framework code (some of our applications
use this ORM framework) to use the same mechanism (all the stuff in
Reactor is auto-generated via XSL - therefore I only had to change a
few lines of XSL-code).
In transfer, it is more complicated and I have not had a deeper look
inside yet... :-)
For the time being we will use the DataDirect driver shipped with CF8
for the applications that use Transfer. But we would be really glad to
switch back to jTDS.

Jan