On Sun, Jul 20, 2008 at 11:10 AM, Mark Mandel <mark.man
...@gmail.com> wrote:
> 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
> On Sat, Jul 19, 2008 at 9:58 AM, Mark Mandel <mark.man...@gmail.com> wrote:
>> 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
>> On Fri, Jul 18, 2008 at 8:05 PM, jjan...@googlemail.com
>> <jjan...@googlemail.com> wrote:
>>> 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
>> --
>> E: mark.man...@gmail.com
>> W: www.compoundtheory.com
> --
> E: mark.man...@gmail.com
> W: www.compoundtheory.com
