Eclipse Community Forums: Virgo » Table LISTING not found in greenpages example

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

Eclipse User

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

Eclipse User

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

Eclipse User

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] by Moderator

Re: Table LISTING not found in greenpages example [message #885523 is a reply to message #885320]

Wed, 13 June 2012 03:17

Eclipse User

@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

Eclipse User

Sure

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

Eclipse User

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

Eclipse User

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:

-=] Back to Top [=-

Current Time: Tue Jul 22 18:16:20 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter