How to pass parameter in the SQL query for executing parameterized SQL query in microflow? We are connecting to MySQL database using Database Connector. I have used Execute Parameterized Query widget as configured below But that is giving a runtime exception " Could not set parameter at position 1”, detailed stack trace is mentioned below. The value is correctly set in the variable AssetName that is passed to the parameter. Please share any guide or how to do it available on this topic as I am unable to find any detailed documentation. Exception stack trace: An error has occurred while handling the request. [User 'MxAdmin' with session id '3edaa34e-XXXX-XXXX-XXXX-XXXXXXXXc04f' and roles 'Administrator'] -------- com.mendix.webui.WebUIException: Exception while retrieving data for 'AssetTracker.AssetData.grid1' on document 'AssetTracker.AssetData' at com.mendix.webui.actions.client.RetrieveAction.$anonfun$apply$3(RetrieveAction.scala:93) Caused by: com.mendix.modules.microflowengine.MicroflowException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.sql.SQLSyntaxErrorException: (conn=240) Could not set parameter at position 1 (values was 'Test Asset 1002') Query - conn:240(M) - "'SELECT aa.assetname, uu.name FROM asset_tracker.assettracker$assetdetails aa join asset_tracker.assettracker$assetdetails_user ua on aa.id = ua.assettracker$assetdetailsid join asset_tracker.system$user uu on ua.system$userid = uu.id where aa.assetname = ?' " at AssetTracker.Test_ExternalDB_2 (JavaAction : 'Execute parameterized query') Advanced stacktrace: at com.mendix.modules.microflowengine.MicroflowUtil.processException(MicroflowUtil.java:85) Caused by: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.sql.SQLSyntaxErrorException: (conn=240) Could not set parameter at position 1 (values was 'Test Asset 1002') Query - conn:240(M) - "'SELECT aa.assetname, uu.name FROM asset_tracker.assettracker$assetdetails aa join asset_tracker.assettracker$assetdetails_user ua on aa.id = ua.assettracker$assetdetailsid join asset_tracker.system$user uu on ua.system$userid = uu.id where aa.assetname = ?' " at com.mendix.basis.actionmanagement.ActionManager.executeSync(ActionManager.scala:84) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: java.sql.SQLSyntaxErrorException: (conn=240) Could not set parameter at position 1 (values was 'Test Asset 1002') Query - conn:240(M) - "'SELECT aa.assetname, uu.name FROM asset_tracker.assettracker$assetdetails aa join asset_tracker.assettracker$assetdetails_user ua on aa.id = ua.assettracker$assetdetailsid join asset_tracker.system$user uu on ua.system$userid = uu.id where aa.assetname = ?' " at com.mendix.util.classloading.Runner.withContextClassLoader(Runner.java:23) Caused by: java.sql.SQLSyntaxErrorException: (conn=240) Could not set parameter at position 1 (values was 'Test Asset 1002') Query - conn:240(M) - "'SELECT aa.assetname, uu.name FROM asset_tracker.assettracker$assetdetails aa join asset_tracker.assettracker$assetdetails_user ua on aa.id = ua.assettracker$assetdetailsid join asset_tracker.system$user uu on ua.system$userid = uu.id where aa.assetname = ?' " at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62) at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:171) at org.mariadb.jdbc.ClientSidePreparedStatement.setParameter(ClientSidePreparedStatement.java:482) at org.mariadb.jdbc.BasePrepareStatement.setString(BasePrepareStatement.java:1388) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java) at databaseconnector.impl.PreparedStatementCreatorImpl.addPreparedStatementParameters(PreparedStatementCreatorImpl.java:51) at databaseconnector.impl.PreparedStatementCreatorImpl.create(PreparedStatementCreatorImpl.java:30) at databaseconnector.impl.JdbcConnector.executeQuery(JdbcConnector.java:74) at databaseconnector.actions.ExecuteParameterizedQuery.executeAction(ExecuteParameterizedQuery.java:74) at databaseconnector.actions.ExecuteParameterizedQuery.executeAction(ExecuteParameterizedQuery.java:51) at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:71) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:48) at com.mendix.core.actionmanagement.internal.InternalCoreAction.call(InternalCoreAction.scala:25) at com.mendix.basis.actionmanagement.ActionManager.$anonfun$executeSync$2(ActionManager.scala:80) at com.mendix.util.classloading.Runner.withContextClassLoader(Runner.java:19) at com.mendix.basis.actionmanagement.ActionManager.executeSync(ActionManager.scala:79) at com.mendix.basis.actionmanagement.UserActionCallBuilderImpl.execute(UserActionCallBuilderImpl.scala:34) at com.mendix.modules.microflowengine.actions.actioncall.ForegroundJavaAction.doExecute(ForegroundJavaAction.scala:35) at com.mendix.modules.microflowengine.actions.actioncall.ForegroundJavaAction.doExecute(ForegroundJavaAction.scala:11) at com.mendix.modules.microflowengine.actions.actioncall.JavaAction.execute(JavaAction.scala:38) at com.mendix.modules.microflowengine.microflow.impl.MicroflowObject.$anonfun$execute$1(MicroflowObject.scala:31) at scala.Option.flatMap(Option.scala:283) at com.mendix.modules.microflowengine.microflow.impl.MicroflowObject.execute(MicroflowObject.scala:28) at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.$anonfun$executeAfterBreakingIfNecessary$2(MicroflowImpl.scala:167) at scala.Option.flatMap(Option.scala:283) at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAfterBreakingIfNecessary(MicroflowImpl.scala:167) at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAction(MicroflowImpl.scala:114) at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:71) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:48) at com.mendix.core.actionmanagement.internal.InternalCoreAction.call(InternalCoreAction.scala:25) at com.mendix.basis.actionmanagement.ActionManager.$anonfun$executeSync$2(ActionManager.scala:80) at com.mendix.util.classloading.Runner.withContextClassLoader(Runner.java:19) at com.mendix.basis.actionmanagement.ActionManager.executeSync(ActionManager.scala:79) at com.mendix.basis.actionmanagement.MicroflowCallBuilderImpl.execute(MicroflowCallBuilderImpl.scala:38) at com.mendix.webui.actions.client.RetrieveAction.retrieveAllByMicroflow(RetrieveAction.scala:114) at com.mendix.webui.actions.client.RetrieveAction.$anonfun$apply$3(RetrieveAction.scala:53) at scala.util.Either.map(Either.scala:382) at com.mendix.webui.actions.client.RetrieveAction.apply(RetrieveAction.scala:43) at com.mendix.webui.actions.client.RetrieveAction.apply(RetrieveAction.scala:28) at com.mendix.webui.actions.client.RegularClientAction$Helpers$.$anonfun$liftEither$1(RegularClientAction.scala:29) at com.mendix.webui.actions.client.RegularClientAction$Helpers$StateHandler.$anonfun$apply$4(RegularClientAction.scala:55) at com.mendix.webui.requesthandling.helpers.StateHandling.withState(StateHandling.scala:42) at com.mendix.webui.requesthandling.helpers.StateHandling.withState$(StateHandling.scala:39) at com.mendix.webui.actions.client.RegularClientAction$Helpers$StateHandler.withState(RegularClientAction.scala:45) at com.mendix.webui.actions.client.RegularClientAction$Helpers$StateHandler.apply(RegularClientAction.scala:53) at com.mendix.webui.actions.client.RegularClientAction$Helpers$StateHandler.apply(RegularClientAction.scala:45) at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$3(RegularClientAction.scala:109) at scala.util.Try$.apply(Try.scala:210) at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$2(RegularClientAction.scala:109) at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$2$adapted(RegularClientAction.scala:107) at com.mendix.webui.requesthandling.helpers.ContextHandling.$anonfun$inContext$5(ContextHandling.scala:51) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.scala:18) at com.mendix.basis.actionmanagement.ActionMonitoring$.$anonfun$monitor$1(ActionMonitoring.scala:49) at com.mendix.util.classloading.Runner.withContextClassLoader(Runner.java:19) at com.mendix.basis.actionmanagement.ActionMonitoring$.monitor(ActionMonitoring.scala:49) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext(ContextHandling.scala:51) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext$(ContextHandling.scala:26) at com.mendix.webui.actions.client.RegularClientAction.inContext(RegularClientAction.scala:86) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext(ContextHandling.scala:23) at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext$(ContextHandling.scala:18) at com.mendix.webui.actions.client.RegularClientAction.inContext(RegularClientAction.scala:86) at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$1(RegularClientAction.scala:107) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.scala:18) at com.mendix.webui.requesthandling.helpers.ProfileHandling.profileRequest(ProfileHandling.scala:14) at com.mendix.webui.requesthandling.helpers.ProfileHandling.profileRequest$(ProfileHandling.scala:10) at com.mendix.webui.actions.client.RegularClientAction.profileRequest(RegularClientAction.scala:86) at com.mendix.webui.actions.client.RegularClientAction.execute(RegularClientAction.scala:104) at com.mendix.webui.requesthandling.ClientRequestHandler.handleAction(ClientRequestHandler.scala:106) at com.mendix.webui.requesthandling.ClientRequestHandler.processRequest(ClientRequestHandler.scala:79) at com.mendix.externalinterface.connector.RequestHandler.doProcessRequest(RequestHandler.java:35) at com.mendix.external.connector.MxRuntimeConnector.lambda$processRequest$0(MxRuntimeConnector.java:74) at com.mendix.util.classloading.Runner.withContextClassLoader(Runner.java:19) at com.mendix.external.connector.MxRuntimeConnector.processRequest(MxRuntimeConnector.java:73) at com.mendix.basis.impl.MxRuntimeImplBase.processRequest(MxRuntimeImplBase.java:831) at com.mendix.m2ee.appcontainer.server.handler.RuntimeServlet.service(RuntimeServlet.scala:25) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799) at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1626) at org.eclipse.jetty.websocket.server.WebSocketUpgradeFilter.doFilter(WebSocketUpgradeFilter.java:228) at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193) at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1601) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:548) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1434) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1349) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) at org.eclipse.jetty.server.Server.handle(Server.java:516) at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:388) at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:633) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:380) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277) at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311) at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105) at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131) at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:386) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883) at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034) at java.base/java.lang.Thread.run(Thread.java:834)
asked
anushree sarkar
1 answers
0
Hi Anushree,
I see that you’ve used quotes in the sql construct. If you remove the quotes from the construct and just write as you would without them, it would work.