query fails after inserting via SQL DataSource

[Segmeno]

Hi,

we are currently using smartGWT-2.5 with a MSSQL Server database (version 10.50.2500.0).

When trying to insert a new record to a many-to-many-table the framework throws a NullPointerException.
The actual insert operation succeeds but another query 'SELECT SCOPE_IDENTITY()' fails afterwards.

I found another similar problem here in the forum which has been caused by
a broken Microsoft SQL driver: http://forums.smartclient.com/showthread.php?t=11389&highlight=SELECT+SCOPE_IDENTITY%28%29

Hope you can help me. If you need more information please let me know.
Below the Exception Stack and the datasource definition. I also attached the client message.

=== 2011-12-08 18:52:24,149 [ec-6] INFO  SQLDriver - [builtinApplication.tbSecUserRole_add] Executing SQL update on 'CamSecurity': INSERT INTO dbo.tbSecUserRole (RoleID, UserID) VALUES (1, 26)
=== 2011-12-08 18:52:24,154 [ec-6] DEBUG SQLDataSource - [builtinApplication.tbSecUserRole_add] add operation affected 1 rows
=== 2011-12-08 18:52:24,154 [ec-6] INFO  SQLDriver - [builtinApplication.tbSecUserRole_add] Executing SQL query on 'CamSecurity': SELECT SCOPE_IDENTITY()
=== 2011-12-08 18:52:24,156 [ec-6] WARN  RequestContext - dsRequest.execute() failed: 
java.lang.NullPointerException
	at com.isomorphic.sql.SQLServerDriver.fetchLastPrimaryKeys(SQLServerDriver.java:241)
	at com.isomorphic.sql.SQLDataSource.getLastPrimaryKeys(SQLDataSource.java:568)
	at com.isomorphic.sql.SQLDataSource.getLastRow(SQLDataSource.java:436)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1381)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:280)
	at com.isomorphic.sql.SQLDataSource.executeAdd(SQLDataSource.java:232)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1156)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:721)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
	at com.isomorphic.application.AppBase.execute(AppBase.java:491)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1714)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:199)
	at com.ascom.camtool.gwt.server.security.SecureIDACall.handleDSRequest(SecureIDACall.java:120)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:156)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:121)
	at com.ascom.camtool.gwt.server.security.SecureIDACall.processRequest(SecureIDACall.java:101)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:259)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:581)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:405)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:964)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:515)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:304)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)

The datasource:

<DataSource ID="tbSecUserRole" isPublic="false" serverType="sql" dataSourceVersion="1" dbName="CamSecurity" schema="dbo" tableName="tbSecUserRole">
	<fields>
		<field name="UserRoleID" type="integer" primaryKey="true" />
		<field name="UserID" type="integer" length="80" foreignKey="tbSecUser.UserID" />
		<field name="RoleID" type="integer" length="80" foreignKey="tbSecRole.RoleID" />
		<field name="Flag" type="integer" />
	</fields>
	<generatedBy>TDR-2011-12-07</generatedBy>
</DataSource>

[Isomorphic]

As the other thread mentions, MS has apparently broken @SCOPE_IDENTITY in all JDBC drivers released after 2000.

There are two fixes:

1. use JDBC 3.0 drivers and then there is no need for the @SCOPE_IDENTITY stuff at all. This if preferred.

2. set sql.dbName.useGlobalIdentity: true in server.properties. In this case @@IDENTITY is used instead, but it doesn't work with certain triggers.

[Segmeno]

I forgot to mention that we are using jTDS JDBC Driver. So this seems to have the same issue.

Now we can be sure that this problem also is driver related. Thanks for your quick response.