Hello.
The query below is not longer working in V9.
FIND Invoice WHERE EXISTS RegionAccess WHERE (Invoice.ps_Region.RegionChain = RegionAccess.ps_Region.RegionChain )
There is no error in the cofigurator.
When i try to view it in testing mode in a brower, i get this error.
Internal error. Error reading business object. Unknown column 'Invoice.ps_Region_RID' in 'on clause'
Using Invoice.ps_Region in a normal query works with no problem.
Here is the server log.
com.bas.basserver.persistence.PersistenceException: Error reading business object. Unknown column 'Invoice.ps_Region_RID' in 'on clause'
at com.bas.basserver.persistence.F.A(Unknown Source)
at com.bas.basserver.persistence.F.<init>(Unknown Source)
at com.bas.basserver.persistence.EntityDBPersister.executeQuery(Unknown Source)
at com.bas.basserver.persistence.PersistenceManager.executeQuery(Unknown Source)
at com.bas.basserver.executionengine.Z.A(Unknown Source)
at com.bas.basserver.executionengine.Z.A(Unknown Source)
at com.bas.basserver.executionengine.ExecutionEngine.doExecuteQuery(Unknown Source)
at com.bas.basserver.executionengine.ExecutionEngine.executeQuery(Unknown Source)
at jdk.internal.reflect.GeneratedMethodAccessor21.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at com.bas.basserver.bsmanager.E.B(Unknown Source)
at com.bas.basserver.bsmanager.E.A(Unknown Source)
at com.bas.basserver.bsmanager.E.A(Unknown Source)
at com.bas.basserver.iopipes.ComponentSink.processMessage(Unknown Source)
at org.openadaptor.adaptor.STPipelineController.processMessage(STPipelineController.java:68)
at com.bas.openadaptor.NonTransactionalController.processMessage(NonTransactionalController.java:136)
at org.openadaptor.adaptor.AbstractSimplePipe.processMessage(AbstractSimplePipe.java:222)
at org.openadaptor.adaptor.STPipelineController.processMessage(STPipelineController.java:68)
at com.bas.openadaptor.NonTransactionalController.processMessage(NonTransactionalController.java:136)
at org.openadaptor.adaptor.AbstractSimpleSource.processMessage(AbstractSimpleSource.java:403)
at org.openadaptor.adaptor.AbstractSimpleSource.sourceProcess(AbstractSimpleSource.java:1049)
at org.openadaptor.adaptor.AbstractSimpleSource.sourceProcess(AbstractSimpleSource.java:1012)
at com.bas.openadaptor.MTJMSListener$JMSMessageHandler.run(MTJMSListener.java:275)
at java.base/java.lang.Thread.run(Thread.java:833)
And the tomcat log.
WebServlet URL got request with params:
Exception thrown com.bas.connectionserver.server.BASServerException Internal error. Error reading business object. Unknown column 'Invoice.ps_Region_RID' in 'on clause'
com.bas.connectionserver.server.BASServerException: Internal error. Error reading business object. Unknown column 'Invoice.ps_Region_RID' in 'on clause'
at com.bas.connectionserver.server.ConnectionFactory.sendMessage(ConnectionFactory.java:317)
at com.bas.connectionserver.server.ConnectionFactory.sendMessage(ConnectionFactory.java:252)
at com.bas.webapp.common.WebAppUtils.sendMessageToExecutionEngine(Unknown Source)
at com.bas.webapp.common.WebAppUtils.sendMessageToExecutionEngine(Unknown Source)
at com.bas.webapp.thin.handlers.G.L.A(Unknown Source)
at com.bas.webapp.thin.handlers.G.L.A(Unknown Source)
at com.bas.webapp.thin.handlers.G.L.A(Unknown Source)
at com.bas.webapp.thin.handlers.XMLRequestHandler.A(Unknown Source)
at com.bas.webapp.thin.handlers.XMLRequestHandler.handleURLRequest(Unknown Source)
at com.bas.webapp.thin.servlets.WebInterfaceURLServlet.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1707)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:833)
Web server returned error: Internal error. Error reading business object. Unknown column 'Invoice.ps_Region_RID' in 'on clause'
Sub query with reference attributes not working V9
Re: Sub query with reference attributes not working V9
ultimately, this may come down to a bug in the code that requires Vlad's intervention.
normally, THAT involves sending a BSV to him for duplication of the error...
of course thats a major PITA, because you'd also need data that he needs to load (somehow) which will show the issue.
WHAT I WOULD DO
is turn on the Logging for FULL SQL statements,
and find that SELECT statement that Aware is sending to the DB
by double-clicking on that line (in the log), it will open a popup whereby you can COPY the SELECT stmt.
Using your SQL management tool, you should be able to paste that same stmt and see the same error appear.
Then, you'll probably see some kind of typo in the complicated SQL that aware generated.
Once you've done this legwork, you prolly won't need to go thru the hassle of submitting a BSV to Vlad, because he now would see the illegal SQL thats being generated.
PS _ You didn't mention your backend DB
normally, THAT involves sending a BSV to him for duplication of the error...
of course thats a major PITA, because you'd also need data that he needs to load (somehow) which will show the issue.
WHAT I WOULD DO
is turn on the Logging for FULL SQL statements,
and find that SELECT statement that Aware is sending to the DB
by double-clicking on that line (in the log), it will open a popup whereby you can COPY the SELECT stmt.
Using your SQL management tool, you should be able to paste that same stmt and see the same error appear.
Then, you'll probably see some kind of typo in the complicated SQL that aware generated.
Once you've done this legwork, you prolly won't need to go thru the hassle of submitting a BSV to Vlad, because he now would see the illegal SQL thats being generated.
PS _ You didn't mention your backend DB
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL
Re: Sub query with reference attributes not working V9
Thanks Jaymer.
Using MYSQL Server 64x V5.7.26
Aware IM Query
FIND Invoice WHERE EXISTS RegionAccess WHERE (Invoice.ps_Region.RegionChain=RegionAccess.ps_Region.RegionChain) ORDER BY Invoice.ModifiedTimestamp DESC
SQL Query
SELECT Invoice.*
FROM INVOICE AS Invoice
WHERE (((SELECT COUNT(*)
FROM REGIONACCESS AS RegionAccess
LEFT JOIN REGION AS Invoice_ps_Region ON Invoice.ps_Region_RID=Invoice_ps_Region.ID
LEFT JOIN REGION AS RegionAccess_ps_Region ON RegionAccess.ps_Region_RID=RegionAccess_ps_Region.ID
WHERE ((Invoice_ps_Region.RegionChain=RegionAccess_ps_Region.RegionChain AND RegionAccess_ps_Region.ID IS NOT NULL AND Invoice_ps_Region.ID IS NOT NULL)))>0))
ORDER BY Invoice.ModifiedTimestamp DESC
I hope this helps
Using MYSQL Server 64x V5.7.26
Aware IM Query
FIND Invoice WHERE EXISTS RegionAccess WHERE (Invoice.ps_Region.RegionChain=RegionAccess.ps_Region.RegionChain) ORDER BY Invoice.ModifiedTimestamp DESC
SQL Query
SELECT Invoice.*
FROM INVOICE AS Invoice
WHERE (((SELECT COUNT(*)
FROM REGIONACCESS AS RegionAccess
LEFT JOIN REGION AS Invoice_ps_Region ON Invoice.ps_Region_RID=Invoice_ps_Region.ID
LEFT JOIN REGION AS RegionAccess_ps_Region ON RegionAccess.ps_Region_RID=RegionAccess_ps_Region.ID
WHERE ((Invoice_ps_Region.RegionChain=RegionAccess_ps_Region.RegionChain AND RegionAccess_ps_Region.ID IS NOT NULL AND Invoice_ps_Region.ID IS NOT NULL)))>0))
ORDER BY Invoice.ModifiedTimestamp DESC
I hope this helps
Re: Sub query with reference attributes not working V9
Good.
But what happens when you run this?
But what happens when you run this?
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL
Re: Sub query with reference attributes not working V9
Error message:
/* SQL Error (1054): Unknown column 'Invoice.ps_Region_RID' in 'on clause' */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 1 query: 0.000 sec. */
/* SQL Error (1054): Unknown column 'Invoice.ps_Region_RID' in 'on clause' */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 1 query: 0.000 sec. */
Re: Sub query with reference attributes not working V9
Unknown column 'Invoice.ps_Region_RID' in 'on clause'
In this line,
LEFT JOIN REGION AS Invoice_ps_Region ON Invoice.ps_Region_RID=Invoice_ps_Region.ID
Retype Invoice as all caps
See if that makes a difference
It doesn’t look like a typo. In the sense that it’s a syntax error, but that’s the place where it’s complaining about.
And it may be some weirdness with SQL, although you say it worked in V8.
Do you still have a working version 8 that you can do the exact same thing on, and compare that SQL statement to this one?
In this line,
LEFT JOIN REGION AS Invoice_ps_Region ON Invoice.ps_Region_RID=Invoice_ps_Region.ID
Retype Invoice as all caps
See if that makes a difference
It doesn’t look like a typo. In the sense that it’s a syntax error, but that’s the place where it’s complaining about.
And it may be some weirdness with SQL, although you say it worked in V8.
Do you still have a working version 8 that you can do the exact same thing on, and compare that SQL statement to this one?
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL
Re: Sub query with reference attributes not working V9
Upgrading mySQL to version 8 solved the problem.
................
................