[frs-376] QueryPlan does not work in cursor mode with Microsoft SQL Server

Problem

An exception is thrown when FusionReactor executes the QueryPlan for a JDBC request. This exception is then displayed as the result of the QueryPlan in FusionReactor.

Snippet of Exception. Full Exception below.
 
java.lang.NullPointerException
	at macromedia.jdbc.sqlserver.tds.ddq.o(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddq.a(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddm.a(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddm.t(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddm.d(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddn.a(Unknown Source)
	at macromedia.jdbc.sqlserver.ddj.h(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddel.v(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddel.r(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddel.execute(Unknown Source)
	at com.intergral.fusionreactor.jdbc.QueryPlanExecutor.executeQueryPlan(QueryPlanExecutor.java:271)

Investigation

Although this problem did not break anything, we were curious as to why this was happening. We tried a different scenarios to try and track down the problem and eventually after trying different drivers, application servers and Java versions we where able to track it down to the Adobe-provided SQL Server / Macromedia MS-SQL driver that was available in ColdFusion 10. Further investigation lead to the select method used for the data source, and that it is not just limited to the driver for MS-SQL but affects ALL MS-SQL JDBC drivers.

Details

After all the investigation was complete, we discovered that the issue stemmed from the use of Cursor select mode in the drivers. This affected all the drivers we tried official Microsoft MS-SQL driver, JTDS Driver and the Adobe-provided driver shipped with ColdFusion.

The cause seems to be that when you run the QueryPlan on MS-SQL, it returns several result sets. Unfortunately when the driver is in Cursor mode it cannot handle the extra result sets and they are discarded.

This means that we are unable to get the result of the QueryPlan from the driver as it does not think they exist.

Solution

As it is not possible for us to obtain the data that the driver is refusing to give us, we cannot run the QueryPlan when the driver is in cursor mode. This is unfortunate but unavoidable.

To do this we parse the URL for the JDBC connection and detect the use of cursors. If the cursor mode is active then the QueryPlan is simple not executed.

NullPointerException thrown when QueryPlan is attempted in Cursor mode using Macromedia driver
java.lang.NullPointerException
	at macromedia.jdbc.sqlserver.tds.ddq.o(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddq.a(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddm.a(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddm.t(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddm.d(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddn.a(Unknown Source)
	at macromedia.jdbc.sqlserver.ddj.h(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddel.v(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddel.r(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddel.execute(Unknown Source)
	at com.intergral.fusionreactor.jdbc.QueryPlanExecutor.executeQueryPlan(QueryPlanExecutor.java:271)
	at com.intergral.fusionreactor.jdbc.MeasurableStatement.commonExecuteEnd(MeasurableStatement.java:540)
	at com.intergral.fusionreactor.jdbc.jdbc42.StatementSurrogate2.execute(StatementSurrogate2.java:64)
	at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:359)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1442)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1201)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1131)
	at coldfusion.sql.SqlImpl.execute(SqlImpl.java:406)
	at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1056)
	at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:685)
	at cfcompanies2ecfm2047516333.runPage(/opt/coldfusion10/cfusion/wwwroot/CTA/jdbc/views/companies.cfm:52)
	at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244)
	at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:444)
	at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
	at coldfusion.filter.IpFilter.invoke(IpFilter.java:64)
	at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:422)
	at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
	at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
	at coldfusion.filter.PathFilter.invoke(PathFilter.java:112)
	at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30)
	at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94)
	at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
	at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
	at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)
	at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
	at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
	at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62)
	at coldfusion.CfmServlet.service(CfmServlet.java:204)
	at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
	at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.intergral.fusionreactor.j2ee.filterchain.WrappedFilterChain.doFilter(WrappedFilterChain.java:134)
	at com.intergral.fusionreactor.j2ee.filter.FusionReactorRequestHandler.doNext(FusionReactorRequestHandler.java:747)
	at com.intergral.fusionreactor.j2ee.filter.FusionReactorRequestHandler.doHttpServletRequest(FusionReactorRequestHandler.java:339)
	at com.intergral.fusionreactor.j2ee.filter.FusionReactorRequestHandler.doFusionRequest(FusionReactorRequestHandler.java:215)
	at com.intergral.fusionreactor.j2ee.filter.FusionReactorRequestHandler.handle(FusionReactorRequestHandler.java:784)
	at com.intergral.fusionreactor.j2ee.filter.FusionReactorCoreFilter.doFilter(FusionReactorCoreFilter.java:36)
	at sun.reflect.GeneratedMethodAccessor61.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.intergral.fusionreactor.j2ee.filterchain.WrappedFilterChain.doFilter(WrappedFilterChain.java:71)
	at sun.reflect.GeneratedMethodAccessor60.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.intergral.fusionreactor.agent.filter.FusionReactorStaticFilter.doFilter(FusionReactorStaticFilter.java:53)
	at com.intergral.fusionreactor.agent.pointcuts.NewFilterChainPointCut$1.invoke(NewFilterChainPointCut.java:41)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java)
	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:472)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:414)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:300)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)

Issue Details

Type: Technote
Issue Number: FRS-376
Components: JDBC
Environment:
Resolution: Fixed
Last Updated: 28/Jan/16 6:44 PM
Affects Version: 6.0.0, 6.0.1
Fixed Version: 6.0.2
Server:
Platform:
Related Issues:

Comments are closed.