java.NullPointerException with MSSQL queries

tonyfitz · June 14, 2023, 12:31am

I recently upgraded my app from an old version of Lucee (lucee-5.2.7.63 to lucee-5.3.10.120) and Coldbox, only to find that some of my database queries no longer work. After many trials and tribulations, I am seeking support from the community for assistance.

For context, I am using a fairly hacky method for dynamically generating connection strings - essentially creating a struct with the datasource information, like below:

local.dsn = StructNew();
local.dsn.database = arguments.databaseName;
local.dsn.host = arguments.databaseServer;
local.dsn.port = 1433;
local.dsn.type = "MSSQL";
local.dsn.dbType = "sqlserver";
local.dsn.username = arguments.credentials.username;
local.dsn.password = arguments.credentials.password;
local.dsn.class = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

I am then creating the query, like so:

local.query = new Query( datasource=arguments.dsn );
local.sqlToExec = "SELECT @@version";
local.query.setSQL(local.sqlToExec);
local.results = QueryNew("");
local.results = local.query.execute().getResult();

And as a result, Java.NullPointerException - full stacktrace below

lucee.runtime.exp.CustomTypeException: at lucee.runtime.tag.Throw.doStartTag(Throw.java:204) at throw_cfm$cf.udfCall(/throw.cfm:11) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.functions.system.CFFunction.call(CFFunction.java:109) at models.api.reports.client.pm11.accountcrosswalkhierarchyleveldao_cfc$cf$5m.udfCall(/models/api/reports/client/pm11/accountCrosswalkHierarchyLevelDAO.cfc:126) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:696) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1931) at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787) at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775) at handlers.api.reports.client.index_cfc$cf$5h.udfCall(/handlers/api/reports/client/index.cfc:55) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at coldbox.system.eventhandler_cfc$cf.udfCall(/coldbox/system/EventHandler.cfc:89) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1950) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at coldbox.system.web.controller_cfc$cf.udfCall3(/coldbox/system/web/Controller.cfc:902) at coldbox.system.web.controller_cfc$cf.udfCall(/coldbox/system/web/Controller.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at coldbox.system.web.controller_cfc$cf.udfCall2(/coldbox/system/web/Controller.cfc:735) at coldbox.system.web.controller_cfc$cf.udfCall(/coldbox/system/web/Controller.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at coldbox.system.web.controller_cfc$cf.udfCall2(/coldbox/system/web/Controller.cfc:467) at coldbox.system.web.controller_cfc$cf.udfCall(/coldbox/system/web/Controller.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1950) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at coldbox.system.bootstrap_cfc$cf.udfCall1(/coldbox/system/Bootstrap.cfc:253) at coldbox.system.bootstrap_cfc$cf.udfCall(/coldbox/system/Bootstrap.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223) at lucee.runtime.type.scope.UndefinedImpl.call(UndefinedImpl.java:786) at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787) at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775) at coldbox.system.bootstrap_cfc$cf.udfCall1(/coldbox/system/Bootstrap.cfc:469) at coldbox.system.bootstrap_cfc$cf.udfCall(/coldbox/system/Bootstrap.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:696) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1931) at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787) at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775) at application_cfc$cf.udfCall(/Application.cfc:45) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:696) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1931) at lucee.runtime.listener.ModernAppListener.call(ModernAppListener.java:444) at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:135) at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44) at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2493) at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2478) at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2449) at lucee.runtime.engine.Request.exe(Request.java:45) at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1216) at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1162) at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97) at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51) at javax.servlet.http.HttpServlet.service(HttpServlet.java:590) at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129) at org.cfmlprojects.regexpathinfofilter.RegexPathInfoFilter.doFilter(RegexPathInfoFilter.java:47) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84) at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62) at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68) at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36) at runwar.undertow.SSLClientCertHeaderHandler.handleRequest(SSLClientCertHeaderHandler.java:144) at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:257) at io.undertow.servlet.handlers.ServletInitialHandler.dispatchToPath(ServletInitialHandler.java:182) at io.undertow.servlet.spec.RequestDispatcherImpl.forwardImpl(RequestDispatcherImpl.java:188) at io.undertow.servlet.spec.RequestDispatcherImpl.forwardImplSetup(RequestDispatcherImpl.java:136) at io.undertow.servlet.spec.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:99) at org.cfmlprojects.regexpathinfofilter.RegexPathInfoFilter.doFilter(RegexPathInfoFilter.java:45) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84) at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62) at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68) at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36) at runwar.undertow.SSLClientCertHeaderHandler.handleRequest(SSLClientCertHeaderHandler.java:144) at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:257) at io.undertow.servlet.handlers.ServletInitialHandler.dispatchToPath(ServletInitialHandler.java:182) at io.undertow.servlet.spec.RequestDispatcherImpl.forwardImpl(RequestDispatcherImpl.java:188) at io.undertow.servlet.spec.RequestDispatcherImpl.forwardImplSetup(RequestDispatcherImpl.java:136) at io.undertow.servlet.spec.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:99) at org.tuckey.web.filters.urlrewrite.NormalRewrittenUrl.doRewrite(NormalRewrittenUrl.java:215) at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:171) at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145) at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92) at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:405) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84) at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62) at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68) at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36) at runwar.undertow.SSLClientCertHeaderHandler.handleRequest(SSLClientCertHeaderHandler.java:144) at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68) at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:117) at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46) at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64) at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60) at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77) at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.servlet.handlers.SendErrorPageHandler.handleRequest(SendErrorPageHandler.java:52) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:275) at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:79) at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:134) at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:131) at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48) at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43) at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44) at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:255) at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:79) at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:100) at io.undertow.server.Connectors.executeRootHandler(Connectors.java:393) at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:852) at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35) at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:2019) at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1558) at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1449) at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1282) at java.base/java.lang.Thread.run(Unknown Source)

I worked around this issue in one instance by implementing queryExecute instead of query.execute().getResults() and by a slightly different implementation of how the datasource is passed in, however I can’t, for the life of me, figure out why this no longer works after the upgrade.

Any insight from the community would be greatly appreciated!

Don’t forget to tell us about your stack!

OS: Windows 10 Pro
Java Version: Java 11.0.14.1 (Eclipse Adoptium) (according to server java list in commandbox cli)
Lucee Version: lucee-5.3.10.120

cfmitrah · June 14, 2023, 6:39am

@tonyfitz I’ve created a ticket in JIRA: [LDEV-4555] - Lucee

Zackster · June 14, 2023, 12:03pm

while it shouldn’t error like this, using a standard datasource config (i.e. from the bottom of the admin edit datasource page) just works

tonyfitz · June 14, 2023, 1:29pm

Upon further review it appears as though the lack of a formally “registered” (for lack of a better term) datasource in Lucee Server Admin is what is causing the issue. For example, simply creating a dummy datasource with a name, and adding a “name” property to the above mentioned struct with the same name used in Admin when creating the datasource stops the error.

It seems as though there is now a dependency on first needing to register a datasource via admin, whereas previously this wasn’t the case.

This post - How to encrypt passwords - #11 by andoradmiraal - seems to suggest this was / is possible.

Zackster · June 14, 2023, 1:33pm

no you don’t need to, you can pass in a struct defining the datasource, it’s just that there’s something out of wack with your “hacky” version compared to what is needed

i.e. this should just work

{
	class: "com.microsoft.sqlserver.jdbc.SQLServerDriver", 
	bundleName: "org.lucee.mssql", 
	bundleVersion: "12.2.0.jre8",
	connectionString: "jdbc:sqlserver://localhost:1433;DATABASENAME=lucee;sendStringParametersAsUnicode=true;SelectMethod=direct",
	username: "lucee",
	password: "hahaa",	
	// optional settings
	connectionLimit:100, // default:-1
	liveTimeout:60, // default: -1; unit: minutes
	storage:true, // default: false
	validate:false, // default: false
};

tonyfitz · June 14, 2023, 2:22pm

Thanks, Zac.

I modified the connection string creator method to the following, which appears to have resolved the issue.

		local.dsn = StructNew();
		local.dsn.database = arguments.databaseMapping.clientDatabaseName;
		local.dsn.host = arguments.databaseMapping.sqlServerName;
		local.dsn.port = 1433;
		local.dsn.type = "MSSQL";
		local.dsn.dbType = "sqlserver";
		local.dsn.connectionString = "jdbc:sqlserver://#arguments.databaseMapping.sqlServerName#:1433;DATABASENAME=#arguments.databaseMapping.clientDatabaseName#;sendStringParametersAsUnicode=true;SelectMethod=direct";
		local.dsn.class = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

Still doesn’t explain the breakage during upgrade, but I’m unblocked now. Thank you for the guidance and support!