Thread: stored procedure calling problem: cursor "c_get_resources" does not exist
Dear All, Let me share the following stored procedure: CREATE OR REPLACE FUNCTION pdp.get_endpoints() RETURNS refcursor AS $$ DECLARE c_get_resources CURSOR (ep_id text) IS SELECT endpoint_resource.resource, endpoint_resource.sequence_index, endpoint.id, endpoint.protocol, endpoint.purposefrom pdp.endpoint, pdp.endpoint_resource where endpoint.id = ep_id AND endpoint_resource.endpoint_id = endpoint.idORDER BY endpoint_resource.sequence_index; BEGIN open c_get_resources( 'nform' ); RETURN c_get_resources; END; $$ LANGUAGE 'plpgsql'; ALTER FUNCTION pdp.get_endpoints(text, text, text) OWNER TO eva; The pgadmin accept it, i can call it using this: SELECT pdp.get_endpoints() As Answer; Works well. By trying to call it using jdbc: CallableStatement cs = db.prepareCall( "{ ? = call pdp.get_endpoints(?, ?, ?) }" ); cs.registerOutParameter(1, Types.OTHER); cs.execute(); ResultSet rs = (ResultSet)cs.getObject(1); I receive this: ERROR: cursor "c_get_resources" does not exist Does anyone an idea how can i make it work? I would really appreciate it. Thanks in advance! Regards, Imre
Re: stored procedure calling problem: cursor "c_get_resources" does not exist
From
Mark Kirkwood
Date:
Imre Fazekas wrote: > Dear All, > > > Let me share the following stored procedure: > CREATE OR REPLACE FUNCTION pdp.get_endpoints() RETURNS refcursor AS $$ > DECLARE > c_get_resources CURSOR (ep_id text) IS > SELECT endpoint_resource.resource, endpoint_resource.sequence_index, endpoint.id, endpoint.protocol, endpoint.purposefrom pdp.endpoint, pdp.endpoint_resource where endpoint.id = ep_id AND endpoint_resource.endpoint_id = endpoint.idORDER BY endpoint_resource.sequence_index; > > BEGIN > > open c_get_resources( 'nform' ); > > RETURN c_get_resources; > END; $$ LANGUAGE 'plpgsql'; > ALTER FUNCTION pdp.get_endpoints(text, text, text) OWNER TO eva; > > The pgadmin accept it, i can call it using this: > SELECT pdp.get_endpoints() As Answer; > Works well. > > > By trying to call it using jdbc: > CallableStatement cs = db.prepareCall( "{ ? = call pdp.get_endpoints(?, ?, ?) }" ); > cs.registerOutParameter(1, Types.OTHER); > cs.execute(); > ResultSet rs = (ResultSet)cs.getObject(1); > > I receive this: > ERROR: cursor "c_get_resources" does not exist > > > Does anyone an idea how can i make it work? I would really appreciate it. > > > Thanks in advance! > > Regards, > > Imre > > > > > Try this: db.setAutoCommit(false); CallableStatement cs = db.prepareCall( "{ ? = call pdp.get_endpoints(?, ?, ?) }" ); cs.registerOutParameter(1, Types.OTHER); cs.execute(); ResultSet rs = (ResultSet)cs.getObject(1); Regards Mark