Re: stored procedure calling problem: cursor "c_get_resources" does not exist

[Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>]

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.purpose from pdp.endpoint, pdp.endpoint_resource where endpoint.id = ep_id AND endpoint_resource.endpoint_id = endpoint.id ORDER 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