multiple out parameters implementation

[Dave Cramer]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Kris Jurka]

On Wed, 1 Sep 2004, Dave Cramer wrote:

> I've managed to hack into the jdbc driver the capability for multiple
> out parameters from a postgresql function.
>

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

This is a useful feature because it's currently the only way to port
certain things, but it's clearly not very straightforward and it causes
some non trivial thinking on the parts of the database and Java developers
to get their code to match up.

Until just today I was in favor of this as we don't have any other options
or any hope for a real procedure interface in the near future, but today
on -hackers Gavin Sherry and Neil Conway indicated that they have a
proposal for implementing this feature in the backend. I'd say lets take
a look at the proposal and if we believe they've got a decent shot at
getting this in 8.1 then we hold off on this hack. The method you
proposed is pretty complicated and would introduce a pretty bad backwards
compatibility problem.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly

[Barry Lind]

I agree with Kris' reasoning. I haven't liked this hack from the day it
was first proposed by Redhat/Fijitsu. I had come to accept it as a
necessary evil since I had given up on the backend ever supporting this
natively.

The best situation would be to have this in the backend. Second best
would be to implement Dave's changes and have the backend never support
it. Worst would be to have two incompatible implementations (in both
the driver and the backend) (think bytea vs LO but worse since I am not
sure it would be possible to toggle between two different ways of
dealing with multiple out parameters).

--Barry

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Dave Cramer]

Barry, Kris,

The "hack" is much smaller now thanks to Oliver's changes in the
backend.

It's now only minor changes.

Although, at this point nobody has asked for it. That might change once
8.1 supports them and people start expecting the same behaviour in
previous versions.

Attached is the proof of concept patch, which when I look at it is
larger than I remembered, but alot of the changes would be compatible,
ie all of the changes to getXXX()

Dave