search

jdeolive / geodb

Spatial database bindings for Java.
MIT License
67 stars 25 forks source link

Exception: Function "ST_DISTANCE" not found; SQL statement: #22

Closed jwgmeligmeyling closed 9 years ago

jwgmeligmeyling commented 9 years ago

For Moodcat (a study project) I use Geodb to test our spacial queries (Hibernate, QueryDSL) against H2 (production runs under Postgres). I currently experience a query that runs on Postgres, but fails in H2 / Geodb. I have no idea if this is due to a configuration mistake or a bug / unimplemented feature in Geodb.

It seems that ST_DISTANCE can not be found at the point where the statement is prepared. Below I have attached the stacktraces, which also includes the prepared statement.

18:24:01.474 ERROR m.m.core.mappers.AbstractExceptionMapper - org.hibernate.exception.GenericJDBCException: could not prepare statement (cd6aae0a-1c15-456b-945b-7736f68573c7)
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763) ~[hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677) ~[hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:458) ~[hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]
    at com.mysema.query.jpa.impl.AbstractJPAQuery.getResultList(AbstractJPAQuery.java:197) ~[querydsl-jpa-3.6.4.jar:na]
    at com.mysema.query.jpa.impl.AbstractJPAQuery.list(AbstractJPAQuery.java:249) ~[querydsl-jpa-3.6.4.jar:na]
    at me.moodcat.database.controllers.RoomDAO.queryRooms(RoomDAO.java:97) ~[classes/:na]
    at me.moodcat.api.RoomAPI.getRooms(RoomAPI.java:71) ~[classes/:na]
    at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66) ~[guice-persist-3.0.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_45]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_45]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_45]
    at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_45]
    at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:137) ~[resteasy-jaxrs-3.0.11.Final.jar:na]
    at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:296) ~[resteasy-jaxrs-3.0.11.Final.jar:na]
    at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:250) ~[resteasy-jaxrs-3.0.11.Final.jar:na]
    at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:237) ~[resteasy-jaxrs-3.0.11.Final.jar:na]
    at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:356) [resteasy-jaxrs-3.0.11.Final.jar:na]
    at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:179) [resteasy-jaxrs-3.0.11.Final.jar:na]
    at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:220) [resteasy-jaxrs-3.0.11.Final.jar:na]
    at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:56) [resteasy-jaxrs-3.0.11.Final.jar:na]
    at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:51) [resteasy-jaxrs-3.0.11.Final.jar:na]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) [javax.servlet-api-3.1.0.jar:3.1.0]
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808) [jetty-servlet-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669) [jetty-servlet-9.2.10.v20150310.jar:9.2.10.v20150310]
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:66) [guice-servlet-3.0.jar:na]
    at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89) [guice-persist-3.0.jar:na]
    at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163) [guice-servlet-3.0.jar:na]
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58) [guice-servlet-3.0.jar:na]
    at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118) [guice-servlet-3.0.jar:na]
    at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113) [guice-servlet-3.0.jar:na]
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) [jetty-servlet-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585) [jetty-servlet-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127) [jetty-server-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127) [jetty-server-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515) [jetty-servlet-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061) [jetty-server-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061) [jetty-server-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) [jetty-server-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215) [jetty-server-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97) [jetty-server-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.server.Server.handle(Server.java:497) [jetty-server-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310) [jetty-server-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257) [jetty-server-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540) [jetty-io-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635) [jetty-util-9.2.10.v20150310.jar:9.2.10.v20150310]
    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555) [jetty-util-9.2.10.v20150310.jar:9.2.10.v20150310]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_45]
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1885) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:910) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2554) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2540) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2365) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573) ~[hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449) ~[hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]
    ... 44 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLException: Function "ST_DISTANCE" not found; SQL statement:
select room0_.id as id1_2_, room0_.currentSong as currentS5_2_, room0_.name as name2_2_, room0_.repeat as repeat3_2_, room0_.location as location4_2_ from room room0_ order by ST_Distance(room0_.location, ?) asc limit ? [90022-187]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.readJavaFunction(Parser.java:2351) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.readFunction(Parser.java:2403) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.readTerm(Parser.java:2737) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.readFactor(Parser.java:2259) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.readSum(Parser.java:2246) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.readConcat(Parser.java:2216) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.readCondition(Parser.java:2066) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.readAnd(Parser.java:2038) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.readExpression(Parser.java:2030) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.parseEndOfQuery(Parser.java:1741) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.parseSelectUnionExtension(Parser.java:1721) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1690) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.parseSelect(Parser.java:1677) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.parsePrepared(Parser.java:433) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.parse(Parser.java:305) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.parse(Parser.java:277) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.command.Parser.prepareCommand(Parser.java:242) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.engine.Session.prepareLocal(Session.java:461) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.engine.Session.prepareCommand(Session.java:403) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1189) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:72) ~[h2-1.4.187.jar:1.4.187]
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:277) ~[h2-1.4.187.jar:1.4.187]
    at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:162) ~[c3p0-0.9.2.1.jar:0.9.2.1]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    ... 61 common frames omitted

Thanks for your effort on this project!

jdeolive commented 9 years ago

Hi. Your project looks cool!

My first guess whenever I see "function x not found" is that perhaps Geodb has not been initialized via the GeoDb.init() static method. Assuming that is not the case here my second guess would perhaps be a parameter mismatch. In the prepared statement above do you have any more info about how the second argument of ST_DISTANCE is being passed in? Is it using one of PreparedStatement.setBytes() or PreparedStatement.setBinaryStream() ?

jwgmeligmeyling commented 9 years ago

Hi!

I think GeoDb.init() is called somewhere under the hood, but I'm not sure where :wink: However, with these pointers I managed to find out that the problem was that I deployed a wrong jar to my Maven repository for Hatbox, and some classes could not be found. It seems everything now works!

jdeolive commented 9 years ago

Great! Good luck with the project!