Re: Stored Procedure returns a ResultSet

7.3 does support callable statements, but only 7.4 supports returning
result sets from a callable statement.

--Barry


sc0698 wrote:
> Now the 7.4 pgsql and  jdbc driver are  beta version, they should have some
> problems!
> So I need a stable driver!
>
> I want to use the  7.3.4  pgsql and pg73jdbc3.jar!
>
> You say they support CallableStatement, but  I can't get the right result!
>
> the java program:
>
>            conn.setAutoCommit(false);
>
>            cstmt = conn.prepareCall(m_query);
>
>            cstmt.registerOutParameter(1, Types.OTHER);
>            cstmt.execute();
>
>            ResultSet resultSet = (ResultSet)cstmt.getObject(1);
>
>
>         the Function return refcursor:
>
>         CREATE OR REPLACE FUNCTION Fuc_test4Query()
>         RETURNS refcursor AS '
>         DECLARE
>             r_Result refcursor;
>         BEGIN
>             r_QuerySQL := ''select ID,Name from Handset '';
>             OPEN r_Result FOR EXECUTE r_QuerySQL;
>             return r_Result;
>         END;
>         '
>         language 'plpgsql';
>
>
> Otherwise,  the fuc_test4query1
> How to do with java?
>  thanks!
>
> the Function return setof
>
>   CREATE OR REPLACE FUNCTION public.fuc_test4query1()
>     RETURNS setof test AS
>   '
>   DECLARE
>    r_Result record;
>   BEGIN
>    r_QuerySQL := \'select ID,Name  from Handset \';
>    FOR r_Result IN execute r_QuerySQL LOOP
>
>    RETURN next r_Result;
>
>    END LOOP;
>
>    return NULL;
>   END;
>   '
>     LANGUAGE 'plpgsql' VOLATILE;
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>