Error while calling stored procedure function!

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Error while calling stored procedure function!

krishnan
Hello all,

     I am having a type called zone_data with the following fields in the postgresql database

    CREATE TYPE zone_data AS (
       zone_id integer,
       name character varying,
       profile public.geometry  
     
    );

   then i have created stored procedure function to retrieve the zone_data, the stored procedure function structure as follows,

CREATE FUNCTION zones_data() RETURNS SETOF zone_data
    LANGUAGE plpgsql
    AS $$
DECLARE
        l_exzone_data public.zone_type;
BEGIN
        l_exzone_data.zone_id = 1;
    l_exzone_data.name = 'ZONE1';
    l_exzone_data.profile = st_geomfromtext('POLYGON((9.40 18.50, 21.00 18.50,
    21.00 9.80, 9.40 9.80, 9.40 18.50))', -1);
   
        RETURN NEXT l_exzone_data;
    RETURN;
END;
$$;

When i call this function through NamedQuery using JPA, i am getting the following exception

 [EL Warning]: 2011-09-02 11:05:37.177--UnitOfWork(19939395)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: function st_geomfromtext(unknown, integer) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: PL/pgSQL function "zones_data" line 6 at assignment
Error Code: 0
Call: SELECT * FROM ui.get_all_exclusion_zones_data()
Query: ResultSetMappingQuery(name="GET_ZONE" sql="SELECT * FROM zones_data()")
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: function st_geomfromtext(unknown, integer) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: PL/pgSQL function "zones_data" line 6 at assignment
Error Code: 0
Call: SELECT * FROM zones_data()
Query: ResultSetMappingQuery(name="GET_ZONE" sql="SELECT * FROM zones_data()")
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:686)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:529)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeCall(AbstractSession.java:914)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:262)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelect(DatasourceCallQueryMechanism.java:244)
        at org.eclipse.persistence.queries.ResultSetMappingQuery.executeDatabaseQuery(ResultSetMappingQuery.java:212)
        at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
        at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:589)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2863)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181)
        at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:453)
        at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:669)
        at com.inro.ui.util.DBInterface.getZones(DBInterface.java:300)
        at com.inro.ui.model.UIModel.getZoneData(UIModel.java:173)
        at com.inro.ui.servlet.ExclusionZoneData.processRequest(ExclusionZoneData.java:35)
        at com.inro.ui.servlet.ExclusionZoneData.doGet(ExclusionZoneData.java:59)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
        at java.lang.Thread.run(Thread.java:619)
Caused by: org.postgresql.util.PSQLException: ERROR: function st_geomfromtext(unknown, integer) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: PL/pgSQL function "zones_data" line 6 at assignment
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2077)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1810)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:498)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:891)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:601)
        ... 36 more

Please help me to solve this issue.

Thanks,
Krishnan.