multiple out parameters implementation - Mailing list pgsql-jdbc

I've managed to hack into the jdbc driver the capability for multiple
out parameters from a postgresql function. Before you say no it can't be
done, read on.

Also before anyone gives me credit for the idea, the original work was
provided by Fujitsu japan, I can only take credit for implementing it in
the current driver.

The way this works is:

1) create a composite type eg:
create type Numeric_Proc_RetType as(it1 numeric(30,15),it2
numeric(30,15),it3 numeric(30,15))

2) create a function which returns this type.
create function
    Numeric_Proc(numeric(30,15),numeric(30,15),numeric(30,15))     returns
Numeric_Proc_RetType as
            'declare work_ret record; begin select * into         work_ret from
Numeric_Tab; return work_ret; end;'
                 language 'plpgsql'

3)create a table

create temp table numeric_tab (MAX_VAL NUMERIC(30,15), MIN_VAL
NUMERIC(30,15), NULL_VAL NUMERIC(30,15) )

Then the following code now works:

CallableStatement call = con.prepareCall( func + "Numeric_Proc(?,?,?) }"
) ;

        call.registerOutParameter(1,Types.NUMERIC);
        call.registerOutParameter(2,Types.NUMERIC);
        call.registerOutParameter(3,Types.NUMERIC);

        call.setBigDecimal(2,new java.math.BigDecimal(1));
        call.setBigDecimal(3,new java.math.BigDecimal(2));
        call.setBigDecimal(4,new java.math.BigDecimal(3));

        call.execute();
        java.math.BigDecimal ret = call.getBigDecimal(1);
    ret = call.getBigDecimal(2);
    ret = call.getBigDecimal(3);

There is more work to be done, but I've found it relatively easy to do,
thanks largely to Oliver's rewrite of the code (Thanks Oliver!).

This is particularly useful to folks that want to port code from other
databases which do support multiple out parameters.

I would like to query the list as to their thoughts, is this a useful
feature for the driver ?

Dave

--
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com