Re: Callable Statements

Mark French <frenchmb@tpg.com.au> writes:

> Hi,
>
> I currently trying to use Callable statments but have been unable to get
> any rows returned in the result set.  The function and code is
> as follows :
>
> CREATE OR REPLACE FUNCTION verify_version_name(BIGINT) RETURNS REFCURSOR
> AS '
> DECLARE
>     v_load_id ALIAS FOR $1;
>        v_entrys REFCURSOR;
> BEGIN
>    OPEN v_entrys FOR
>        SELECT COUNT(vers.name)
>     FROM ots_stage_scheme_version stg_ver, ots_scheme_version vers
>     WHERE stg_ver.load_id = v_load_id
>     AND stg_ver.name != vers.name;
>    RETURN v_entrys;
> END;
> ' LANGUAGE 'plpgsql';
>
>
>   CallableStatement proc = conn.prepareCall("{ ? = call
> verify_version_name (?) }");
>       proc.registerOutParameter(1, Types.OTHER);
>       proc.setLong(2, 59);
>       proc.execute();
>       ResultSet result = (ResultSet) proc.getObject(1);
>       System.out.println(result);
>       while(result.next())
>       {
>         System.err.println("Value : " + result.getLong(1));
>       }
>
> Does anybody have any ideas as to why the result set is empty (I am
> using the development driver) .

You can only do this within a transaction. Here's a slight alteration
of your code:


 conn.setAutoCommit(false);
 CallableStatement proc
     = conn.prepareCall("{ ? = call verify_version_name (?) }");
 proc.registerOutParameter(1, Types.OTHER);
 proc.setLong(2, 59);
 proc.execute();
 ResultSet result = (ResultSet) proc.getObject(1);
 System.out.println(result);
 while(result.next())
 {
   System.err.println("Value : " + result.getLong(1));
 }
 conn.commit();


This is another reason why you should use mapping over the ResultSet
instead of returning it.



Nic Ferrier

http://www.tapsellferrier.co.uk