Return values from sql code

[edulid]

I have sql code, which I need to be called directly, so I have a <customSQL> with the MSSQL code.

This code consists of updates, inserts, and I need the results, so at the end of the code I have a select.

But I cannot access the values returned from this select from the outside, i.e. from a server-side DMI method.

A very simplified version of the code:

update mytable set f=2;
select name from anotherTable;

If my operationBinding is of type "fetch" (becauseo of the select statement), then I get an exception:

=== 2014-12-29 18:35:28,768 [ec-2] DEBUG DataSourceDMI - Invocation threw exception
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:800)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
	at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:671)
	at com.isomorphic.sql.SQLDriver.executeQuery(SQLDriver.java:985)
	at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:563)
	at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:557)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1911)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:448)
	at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:393)
	at de.mks_infofabrik.kids.server.KidsDataSource.executeFetch(KidsDataSource.java:109)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1582)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:723)
	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:2535)
	at de.mks_infofabrik.kids.server.dmi.mehrfachActionHandler.MehrfachStatusAendernHandler.doHandle(MehrfachStatusAendernHandler.java:217)
	at de.mks_infofabrik.kids.server.dmi.mehrfachActionHandler.GruppeHandler.handle(GruppeHandler.java:76)
	at de.mks_infofabrik.kids.server.dmi.SchuelerListDMIHandler.doFetch(SchuelerListDMIHandler.java:140)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.isomorphic.base.Reflection.adaptArgsAndInvoke(Reflection.java:975)
	at com.isomorphic.datasource.DataSourceDMI.execute(DataSourceDMI.java:416)
	at com.isomorphic.datasource.DataSourceDMI.execute(DataSourceDMI.java:64)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2531)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:215)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:172)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:137)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:644)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
	at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
	at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
	at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
	at de.mks_infofabrik.kids.server.filter.GWTCacheControlFilter.doFilter(GWTCacheControlFilter.java:50)
	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
	at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:260)
	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
	at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
	at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:136)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:526)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:655)
	at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1566)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1523)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)

If I delete the "update mytable set f=2;", then I do not get any exception and everything works. I think, because in a "fetch" operationBinding it is not possible to execute an update.

So I changed the type to "update", and now I don't get any exception, but I am not able to access the result of the select statement. In my DMI I write:

LOG.info("Result: " + changeResponse.getData());

But I get:

Result: null

So can I access these records ?

Using SmartGWT 4.1p, v9.1p_2014-11-09, 2014-11-09 power.

[Isomorphic]

Take a look at the advice on Stored Procedures from the Custom Querying overview - it applies to this case even though your procedure is technically just inlined instead of stored per se.

In a nutshell if you want to process the result of this procedure using our standard SQL operations, it needs to be returned in the form of a ResultSet. How to make this happen is a matter of figuring out the behavior from MSSQL and their JDBC driver.

[edulid]

I have a select at the end of the code block, and when I execute the code block I see the results, so the code should return a resultset, or ? What should I change here ?

[Isomorphic]

We agree that one might expect MSSQL to return a ResultSet in this case - seems like the right behavior. Unfortunately, your finding is that it doesn't, and we don't have any control over that - this behavior is controlled by the MSSQL JDBC driver.

We already document the specific JDBC APIs we're calling, so if you find a way to get those APIs to return a ResultSet, then you'll be all set.

[edulid]

We already document the specific JDBC APIs we're calling, so if you find a way to get those APIs to return a ResultSet, then you'll be all set.

ok, and even if this is not controlled by smartgwt, don't you know a way to produce a resultSet from this scenario in order to comply with the jdbc driver?
What would be your approach if you had this problem?

[edulid]

Is it even possible to produce a resultSet from an operation of type="update"? can't it be the case that the select is already producing a resultSet but it is being ignored because the type of the operationBinding is "update" and not "fetch" ?

[Isomorphic]

No, we don't have intimate knowledge of MSSQL's JDBC driver in the area of procedures. Like you, we would be doing web searches like "mssql procedure return jdbc resultset" and variants.

[Isomorphic]

You do indeed need to be using operationType="fetch" if you trying to return a ResultSet and have it processed. Again the JDBC APIs we're calling for "fetch" vs "update" are documented, and the JDBC API does not allow a ResultSet to be returned when you are performing updates.

[edulid]

I think this does the trick (for anyone interested): "set nocount on;". The number of records of the update/inserts is then ignored.

[Isomorphic]

Thanks for posting the solution!

Just to clarify, where does that line go? Can you just put it at the beginning of a multi-line statement in a <customSQL> tag?

[edulid]

Yes, I now have, as you pointed, a "fetch" operation instead of an "update" operation. I put "set nocount on;" at the beginning of the multi-line statement in the <customSQL> tag, and "set nocount off;" just before the select statement. Maybe you can also put it after the select statement, I haven't tried that.