How to get id column from a database

0
I am trying to read from a database table that contains a column called “id”.  Since id is reserved in Mendix, I want to store the values from the database in an attribute called “_id”. When I run the query using the Database Connector module, I get an error message saying that there is no “id” attribute in my entity, but the query returns such an attribute. I tried to solve this problem by renaming the attribute in the sql statement with “AS”. This should work (see https://community.mendix.com/link/space/databases/questions/126654) However, I still get the error message. Does anyone have any idea what I have overlooked? Thank you in advance.   Here is my sql statement:  'SELECT id AS _id,  comment, name FROM table'   Full stacktrace: com.mendix.webui.WebUIException: Exception while executing runtime operation    at com.mendix.webui.actions.client.RuntimeOperationAction.apply(RuntimeOperationAction.scala:72) Caused by: com.mendix.modules.microflowengine.MicroflowException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.lang.RuntimeException: The entity type 'MainModule.cm2_project' does not contain the primitive 'id' as specified in the query.    at MainModule.ACT_Get_Tables (JavaAction : 'Execute query') Advanced stacktrace:    at com.mendix.modules.microflowengine.MicroflowUtil$.processException(MicroflowUtil.scala:73) Caused by: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.lang.RuntimeException: The entity type 'MainModule.cm2_project' does not contain the primitive 'id' as specified in the query.    at com.mendix.basis.actionmanagement.ActionManager.executeSync(ActionManager.scala:108) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: java.lang.RuntimeException: The entity type 'MainModule.cm2_project' does not contain the primitive 'id' as specified in the query.    at com.mendix.util.classloading.Runner$.withContextClassLoader(Runner.scala:23) Caused by: java.lang.RuntimeException: The entity type 'MainModule.cm2_project' does not contain the primitive 'id' as specified in the query.    at databaseconnector.impl.ResultSetIterator.lambda$getColumnInfo$1(ResultSetIterator.java:84)    at java.base/java.util.Optional.orElseThrow(Optional.java:408)    at databaseconnector.impl.ResultSetIterator.getColumnInfo(ResultSetIterator.java:82)    at java.base/java.util.stream.IntPipeline$1$1.accept(IntPipeline.java:180)    at java.base/java.util.stream.Streams$RangeIntSpliterator.forEachRemaining(Streams.java:104)    at java.base/java.util.Spliterator$OfInt.forEachRemaining(Spliterator.java:699)    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)    at databaseconnector.impl.ResultSetIterator.createColumnInfos(ResultSetIterator.java:49)    at databaseconnector.impl.ResultSetIterator.<init>(ResultSetIterator.java:42)    at databaseconnector.impl.ResultSetReader.<init>(ResultSetReader.java:44)    at databaseconnector.impl.JdbcConnector.executeQuery(JdbcConnector.java:66)    at databaseconnector.actions.ExecuteQuery.executeAction(ExecuteQuery.java:80)    at databaseconnector.actions.ExecuteQuery.executeAction(ExecuteQuery.java:50)    at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:58)    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.java:57)    at com.mendix.basis.actionmanagement.ActionManager.$anonfun$executeSync$2(ActionManager.scala:104)    at com.mendix.util.classloading.Runner$.withContextClassLoader(Runner.scala:20)    at com.mendix.basis.actionmanagement.ActionManager.executeSync(ActionManager.scala:103)    at com.mendix.basis.actionmanagement.UserActionCallBuilderImpl.execute(UserActionCallBuilderImpl.scala:59)    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:29)    at scala.Option.flatMap(Option.scala:283)    at com.mendix.modules.microflowengine.microflow.impl.MicroflowObject.execute(MicroflowObject.scala:26)    at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAfterBreakingIfNecessary(MicroflowImpl.scala:176)    at com.mendix.modules.microflowengine.microflow.impl.MicroflowImpl.executeAction(MicroflowImpl.scala:116)    at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:58)    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.java:57)    at com.mendix.basis.actionmanagement.ActionManager.$anonfun$executeSync$2(ActionManager.scala:104)    at com.mendix.util.classloading.Runner$.withContextClassLoader(Runner.scala:20)    at com.mendix.basis.actionmanagement.ActionManager.executeSync(ActionManager.scala:103)    at com.mendix.basis.actionmanagement.MicroflowCallBuilderImpl.execute(MicroflowCallBuilderImpl.scala:64)    at com.mendix.webui.actions.client.MicroflowRuntimeOperationExecutor.runMicroflow(MicroflowRuntimeOperationExecutor.scala:88)    at com.mendix.webui.actions.client.MicroflowRuntimeOperationExecutor.$anonfun$apply$7(MicroflowRuntimeOperationExecutor.scala:61)    at com.mendix.webui.actions.client.RegularClientAction$Helpers$.$anonfun$liftEither$1(RegularClientAction.scala:30)    at com.mendix.webui.actions.client.RegularClientAction$Helpers$StateHandler.$anonfun$apply$1(RegularClientAction.scala:72)    at com.mendix.webui.requesthandling.helpers.StateHandling.withState(StateHandling.scala:46)    at com.mendix.webui.requesthandling.helpers.StateHandling.withState$(StateHandling.scala:43)    at com.mendix.webui.actions.client.RegularClientAction$Helpers$StateHandler.withState(RegularClientAction.scala:66)    at com.mendix.webui.actions.client.RegularClientAction$Helpers$StateHandler.apply(RegularClientAction.scala:70)    at com.mendix.webui.actions.client.RegularClientAction$Helpers$StateHandler.apply(RegularClientAction.scala:66)    at com.mendix.webui.actions.client.MicroflowRuntimeOperationExecutor.apply(MicroflowRuntimeOperationExecutor.scala:62)    at com.mendix.webui.actions.client.RuntimeOperationAction.apply(RuntimeOperationAction.scala:66)    at com.mendix.webui.actions.client.RuntimeOperationAction.apply(RuntimeOperationAction.scala:25)    at com.mendix.webui.actions.client.RegularClientAction$Helpers$.$anonfun$liftEither$1(RegularClientAction.scala:30)    at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$3(RegularClientAction.scala:121)    at scala.util.Try$.apply(Try.scala:210)    at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$2(RegularClientAction.scala:121)    at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$2$adapted(RegularClientAction.scala:119)    at com.mendix.webui.requesthandling.helpers.ContextHandling.$anonfun$inContext$7(ContextHandling.scala:59)    at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.scala:18)    at com.mendix.basis.actionmanagement.ActionMonitoring$.$anonfun$monitor$1(ActionMonitoring.scala:52)    at com.mendix.util.classloading.Runner$.withContextClassLoader(Runner.scala:20)    at com.mendix.basis.actionmanagement.ActionMonitoring$.monitor(ActionMonitoring.scala:52)    at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext(ContextHandling.scala:59)    at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext$(ContextHandling.scala:33)    at com.mendix.webui.actions.client.RegularClientAction.inContext(RegularClientAction.scala:95)    at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext(ContextHandling.scala:30)    at com.mendix.webui.requesthandling.helpers.ContextHandling.inContext$(ContextHandling.scala:22)    at com.mendix.webui.actions.client.RegularClientAction.inContext(RegularClientAction.scala:95)    at com.mendix.webui.actions.client.RegularClientAction.$anonfun$execute$1(RegularClientAction.scala:119)    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:95)    at com.mendix.webui.actions.client.RegularClientAction.execute(RegularClientAction.scala:116)    at com.mendix.webui.requesthandling.ClientRequestHandler.handleAction(ClientRequestHandler.scala:95)    at com.mendix.webui.requesthandling.ClientRequestHandler.processRequest(ClientRequestHandler.scala:68)    at com.mendix.externalinterface.connector.RequestHandler.doProcessRequest(RequestHandler.java:37)    at com.mendix.external.connector.MxRuntimeConnector.$anonfun$processRequest$1(MxRuntimeConnector.scala:54)    at com.mendix.external.connector.MxRuntimeConnector.$anonfun$processRequest$1$adapted(MxRuntimeConnector.scala:54)    at com.mendix.util.classloading.Runner$.withContextClassLoader(Runner.scala:20)    at com.mendix.external.connector.MxRuntimeConnector.processRequest(MxRuntimeConnector.scala:54)    at com.mendix.basis.impl.MxRuntimeImpl.processRequest(MxRuntimeImpl.scala:513)    at com.mendix.m2ee.appcontainer.server.handler.RuntimeServlet.service(RuntimeServlet.scala:40)    at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)    at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)    at org.eclipse.jetty.websocket.servlet.WebSocketUpgradeFilter.doFilter(WebSocketUpgradeFilter.java:170)    at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:202)    at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1635)    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1580)    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1553)    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)    at org.eclipse.jetty.server.Server.handle(Server.java:563)    at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)    at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501)    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:287)    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)    at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)    at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:421)    at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:390)    at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:277)    at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.run(AdaptiveExecutionStrategy.java:199)    at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969)    at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194)    at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149)    at java.base/java.lang.Thread.run(Thread.java:829)
asked
2 answers
1

It's not really meant to be used by developers, just internally by the Mendix application. 

 

If you do need to read it, you can use the Community Commons module from the Marketplace. In there you can find an action called GetGUID that will return the ID value as an Integer/Long.

 

I hope this helps.

answered
0

As Robert already mentioned, its better to let the platform handle it. If you must either use existing logic / modules like the Community Commons module.

Should there still be a need to execute this query and you know what you are doing, in that case you probably need to check the syntax for the database, sometimes reserved words need to be placed between double quotes. Also the table name cm2_project looks like an association (join) table between cm2 and project, these tables have no id column

answered