[Pljava-dev] advice needed

info at wyse-systems.ltd.uk wrote:

>I don't think so. What I need to build is the following (very simple
>example to illustrate my point):
>
>Standard approach:
>~~~~~~~~~~~~~~~~~~
>1. create table details_tbl (title text, forename text, surname text);
>2. create view details_v as select forename || ' ' || surname as name from
>details_tbl where title ilike 'mr';
>3. getting the result: select * from details_v;
>
>pljava approach:
>~~~~~~~~~~~~~~~~
>
>1. same as above (assume the table is properly populated);
>2.
> - CREATE TYPE details_fn AS (name text);
> - CREATE FUNCTION details_fn()
> RETURNS SETOF details_v
> AS 'whatever-package.DetailsView'
> IMMUTABLE LANGUAGE java;
>
> - now, my understanding is that in the Java class DetailsView I have to
>implement a method
>
> public boolean assignRowValues(ResultSet receiver, int currentRow)
>throws SQLException {
> }
>
> which is called until the return value is false (i.e. no more rows are
>to be returned) and this needs to populate the one-row resultset which is
>due to be displayed to the client, i.e.
> select * from details_fn() should, in theory, get the desired results.
>
> public class DetailsView implements ResultSetProvider{
> private ResultSet theResult;
> public DetailsView() {
> Connection con =
>DriverManager.getConnection("jdbc:default:connection");
> Statement st = con.createStatement();
> this.theResult = con.executeQuery("select forename, surname from
>details_tbl where title ilike 'mr'");
> }
> }
>
> The difficulty I am having is that even though I can determine the
>entire rowset at the constructor, when the 'select * from details_fn()'
>statement is run the assignRowValues method gets called to populate a
>single row of the receiver. So, in order to build the entire set I would
>need to do something like:
>
> public boolean assignRowValues(ResultSet receiver, int currentRow)
>throws SQLException {
> if (this.theResult != null && this.theResult.absolute(currentRow) {
> receiver.updateString(1, this.theResult.getString(1) + " " +
>this.theResult.getString(2));
> return true;
> else return false;
> }
>
>If the above is correct as I already pointed out this would be very
>inneficient, because of all the 'absolute' method calls (and subsequent
>scans of the entire result set object).
>
>If I am not correct I welcome suggestions as to how this can be implemented?
>
>
Firs of all, I'm sorry I misread you question. There are two ResultSet's
involved in your scenario, not just the one I thought you where
referring to, namely the parameter. I missed the whole point :-)

You are guaranteed that the currentRow will be increased with exactly
one for each call, so there's no need to do absolute positioning. A
simple next will be sufficient.

Another approach could be to build an ArrayList of objects (some class
that contains the two strings) in the constructor or on the first call
to assignRowValues. It all depends on what you are after (I assume its
more then just implementing a view), how much data that is expected to
build up, etc.

>The reason I've said that the examples are rather daft (and I stick with
>what I've said) is that, yes, even though I *did* look at the code and all
>the examples, in all of them the SETOF returned is determined without any
>involvement of datasets or database iteraction in general, i.e. in
>assignRowValues the receiver set row is determined purely on the basis of
>simple integer additions and the current timestamp - no database iteraction
>whatsoever as shown in my example above. In other words, if I want to
>implement a view result set based on *real* data (and *not* just simple
>integer additions) there is nowhere to look at!
>
>Do you get my point?
>
>
I do. And if I succeed helping you implement what you need then you're
more then welcome to submit additional examples.

>Let me just say that at this point I don't really care about portability -
>the faster, the better! So, based on that statement, am I better off using
>the SPI* methods?
>
>
You already use the SPI* methods. There is no other implementation
available to you. When you do:

Connection con = DriverManager.getConnection("jdbc:default:connection");

you actually get a SPIConnection. The fact that it's all hidden behind standard interfaces has no negative performance impact.

Regards,
Thomas Hallgren