Re: Callable Statements

Mark French <frenchmb(at)tpg(dot)com(dot)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