Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » Virgo » Table LISTING not found in greenpages example(Submit Query search in greenpages generates SQL error)
Table LISTING not found in greenpages example [message #683938] Tue, 14 June 2011 13:40 Go to next message
Glennn  is currently offline Glennn
Messages: 1
Registered: June 2011
Junior Member
I get the following error after completing the Greenpages tutorial, Sec. 5.4, Trying out the JPA middle tier:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.0 (Build 1.0 - 20080707)): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: Table LISTING not found; SQL statement:
SELECT LISTING_NUMBER, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS FROM LISTING WHERE (UPPER(LAST_NAME) LIKE ?) [42102-71]
Error Code: 42102
Call: SELECT LISTING_NUMBER, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS FROM LISTING WHERE (UPPER(LAST_NAME) LIKE ?)
	bind => [%%]
Query: ReadAllQuery(greenpages.jpa.JpaListing)
	org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:322)
	org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:646)
	org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:500)
	org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:522)
	org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:204)
	org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:190)
	org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:261)
	org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:594)
	org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2505)
	org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2463)
	org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:478)
	org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:883)
	org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:666)
	org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:844)
	org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:456)
	org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:906)
	org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2588)
	org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1178)
	org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1162)
	org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1136)
	org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1118)
	org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:399)
	org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:517)
	greenpages.jpa.JpaDirectory.search(JpaDirectory.java:37)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58)
	org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
	org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
	org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	$Proxy146.search(Unknown Source)
	greenpages.web.GreenPagesController.search(GreenPagesController.java:27)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:710)
	org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:167)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:414)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:402)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:771)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:647)
	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:552)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:803)


root cause 

org.h2.jdbc.JdbcSQLException: Table LISTING not found; SQL statement:
SELECT LISTING_NUMBER, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS FROM LISTING WHERE (UPPER(LAST_NAME) LIKE ?) [42102-71]
	org.h2.message.Message.getSQLException(Message.java:92)
	org.h2.message.Message.getSQLException(Message.java:96)
	org.h2.message.Message.getSQLException(Message.java:74)
	org.h2.command.Parser.readTableOrView(Parser.java:4030)
	org.h2.command.Parser.readTableFilter(Parser.java:911)
	org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1406)
	org.h2.command.Parser.parseSelectSimple(Parser.java:1504)
	org.h2.command.Parser.parseSelectSub(Parser.java:1400)
	org.h2.command.Parser.parseSelectUnion(Parser.java:1279)
	org.h2.command.Parser.parseSelect(Parser.java:1267)
	org.h2.command.Parser.parsePrepared(Parser.java:390)
	org.h2.command.Parser.parse(Parser.java:286)
	org.h2.command.Parser.parse(Parser.java:258)
	org.h2.command.Parser.prepareCommand(Parser.java:230)
	org.h2.engine.Session.prepareLocal(Session.java:283)
	org.h2.engine.Session.prepareCommand(Session.java:244)
	org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1020)
	org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:1216)
	org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:163)
	org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:248)
	org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302)
	org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1340)
	org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:648)
	org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:550)
	org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:500)
	org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:522)
	org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:204)
	org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:190)
	org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:261)
	org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:594)
	org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2505)
	org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2463)
	org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:478)
	org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:883)
	org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:666)
	org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:844)
	org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:456)
	org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:906)
	org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2588)
	org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1178)
	org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1162)
	org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1136)
	org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1118)
	org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:399)
	org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:517)
	greenpages.jpa.JpaDirectory.search(JpaDirectory.java:37)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58)
	org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
	org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
	org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	$Proxy146.search(Unknown Source)
	greenpages.web.GreenPagesController.search(GreenPagesController.java:27)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:710)
	org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:167)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:414)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:402)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:771)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:647)
	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:552)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:803)


I've checked all the configuration files in greenpages.db, greenpages.app, greenpages.jpa, etc., and all is according to the documentation in, "Creating an application with EclipseRT Virgo Web Server". The H2 server is running and all the bundles load and are started in OSGI. What could be wrong?
Re: Table LISTING not found in greenpages example [message #684094 is a reply to message #683938] Tue, 14 June 2011 21:58 Go to previous messageGo to next message
Glyn Normington is currently offline Glyn Normington
Messages: 1222
Registered: July 2009
Senior Member
Did you run the data.sh/data.bat database population script? See the docs for details.
Re: Table LISTING not found in greenpages example [message #885320 is a reply to message #683938] Tue, 12 June 2012 16:04 Go to previous messageGo to next message
Jay Flowers is currently offline Jay Flowers
Messages: 9
Registered: June 2012
Junior Member
I get this same error. And yes I did run the data.bat script. If I stop the Virgo server and log into the H2 web console I can successfully query for the table LISTING. I see this in the log file:

[EL Info]: 2012.06.12 15:21:07.792--ServerSession(1089104378)--Thread(Thread[http-bio-8081-exec-6,5,main])--EclipseLink, version: Eclipse Persistence Services - 1.0 (Build 1.0 - 20080707)
[EL Config]: 2012.06.12 15:21:07.801--ServerSession(1089104378)--Connection(1030059569)--Thread(Thread[http-bio-8081-exec-6,5,main])--connecting(DatabaseLogin(
platform=>HSQLPlatform
user name=> ""
connector=>JNDIConnector datasource name=>null
))
[EL Config]: 2012.06.12 15:21:07.925--ServerSession(1089104378)--Connection(1452500205)--Thread(Thread[http-bio-8081-exec-6,5,main])--Connected: jdbc:h2:~/greenpages-db/greenpages
User: GREENPAGES
Database: H2 Version: 1.0.71 (2008-04-25)
Driver: H2 JDBC Driver Version: 1.0.71 (2008-04-25)
[EL Config]: 2012.06.12 15:21:07.957--ServerSession(1089104378)--Connection(1724329986)--Thread(Thread[http-bio-8081-exec-6,5,main])--connecting(DatabaseLogin(
platform=>HSQLPlatform
user name=> ""
connector=>JNDIConnector datasource name=>null
))
[EL Config]: 2012.06.12 15:21:07.958--ServerSession(1089104378)--Connection(522258437)--Thread(Thread[http-bio-8081-exec-6,5,main])--Connected: jdbc:h2:~/greenpages-db/greenpages
User: GREENPAGES
Database: H2 Version: 1.0.71 (2008-04-25)
Driver: H2 JDBC Driver Version: 1.0.71 (2008-04-25)
[EL Info]: 2012.06.12 15:21:07.978--ServerSession(1089104378)--Thread(Thread[http-bio-8081-exec-6,5,main])--file:/C:/Virgo/VIRGO-~2.REL/work/org.eclipse.virgo.kernel.deployer_3.0.3.RELEASE/staging/greenpages-2.6.0.RELEASE/bundle/greenpages-2.6.0.RELEASE-greenpages.jpa/2.5.0/greenpages.jpa.jar/-GreenPages login successful
[EL Fine]: 2012.06.12 15:21:08.055--ServerSession(1089104378)--Connection(1137425244)--Thread(Thread[http-bio-8081-exec-6,5,main])--SELECT LISTING_NUMBER, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS FROM LISTING WHERE (UPPER(LAST_NAME) LIKE ?)
bind => [%ROD%]
[EL Warning]: 2012.06.12 15:21:08.070--UnitOfWork(2017618908)--Thread(Thread[http-bio-8081-exec-6,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.0 (Build 1.0 - 20080707)): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: Table LISTING not found; SQL statement:
SELECT LISTING_NUMBER, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS FROM LISTING WHERE (UPPER(LAST_NAME) LIKE ?) [42102-71]
Error Code: 42102
Call: SELECT LISTING_NUMBER, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS FROM LISTING WHERE (UPPER(LAST_NAME) LIKE ?)
bind => [%ROD%]
Query: ReadAllQuery(greenpages.jpa.JpaListing)
Servlet.service() for servlet in context with path [/greenpages] threw exception [Request processing failed; nested exception is Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.0 (Build 1.0 - 20080707)): org.eclipse.persistence.exceptions.DatabaseException

[Updated on: Tue, 12 June 2012 16:04]

Report message to a moderator

Re: Table LISTING not found in greenpages example [message #885523 is a reply to message #885320] Wed, 13 June 2012 03:17 Go to previous messageGo to next message
Glyn Normington is currently offline Glyn Normington
Messages: 1222
Registered: July 2009
Senior Member
@Glennn and @Jay: please could you provide information about your environments since presumably there's something a little out of the ordinary here: operating system, Java version, Greenpages version, Virgo version. Thanks.
Re: Table LISTING not found in greenpages example [message #885672 is a reply to message #885523] Wed, 13 June 2012 08:22 Go to previous messageGo to next message
Jay Flowers is currently offline Jay Flowers
Messages: 9
Registered: June 2012
Junior Member
Sure Smile
virgo-tomcat-server-3.0.3.RELEASE
jdk1.7.0_4
greenpages-2.6.0.RELEASE.par
Windows 7

I got the greenpages sample by forking from GitHub...
Re: Table LISTING not found in greenpages example [message #885698 is a reply to message #885672] Wed, 13 June 2012 08:57 Go to previous messageGo to next message
Jay Flowers is currently offline Jay Flowers
Messages: 9
Registered: June 2012
Junior Member
I identified and solved my issue.

The script that finds the h2 jar is not precise enough. It needs to find the same version that the greenpages application uses to start the h2 server:

db\h2.bat

I changed the first line in that script to this:

for /r "%HOMEDRIVE%%HOMEPATH%\.m2" %%X in (*com.springsource.org.h2-1.0.71.jar) do (set H2CP=%H2CP%;%%X)

it was this:

for /r "%HOMEDRIVE%%HOMEPATH%\.m2" %%X in (*h2*.jar) do (set H2CP=%H2CP%;%%X)

so when I was starting the server with the run.bat script it was actually using this jar:

start java -cp ";C:\Users\flowersj\.m2\repository\com\h2database\h2\1.3.161\h2-1.3.161.jar" org.h2.tools.Server

when it needed to be like this:

start java -cp ";C:\Users\flowersj\.m2\repository\com\h2database\com.springsource.org.h2\1.0.71\com.springsource.org.h2-1.0.71.jar" org.h2.tools.Server
Re: Table LISTING not found in greenpages example [message #989613 is a reply to message #885698] Thu, 06 December 2012 18:06 Go to previous message
Chuck Williams is currently offline Chuck Williams
Messages: 4
Registered: October 2012
Junior Member
Jay Flowers,

Thanks very much! That solved my problem too. Basically the run script and the pom.xml were using 2 different versions of H2. So you have to make the bat script point to the correct version. Note that after making Jay's change, you may have to pop another command prompt window so that the variables set are unset (the script called h2.bat and sets an H2DB variable.) Thanks again, Jay!
Previous Topic:Managing Your Target Platform with Virgo Tooling
Next Topic:Blueprint Cyclic Dependency
Goto Forum:
  


Current Time: Fri Jul 25 01:18:01 EDT 2014

Powered by FUDForum. Page generated in 0.06090 seconds