[Pljava-dev] advice needed

>>Hi,
>>
>>'quick' query to all of you gurus:
>>
>>I need to return a setof predefined complex type (similar to what a view
>>does in 8.0) using pljava class, but found the pljava interface rather
>>confusing:
>>
>>According to the docs I would need to implement assignRowValues to set the
>>resulting columns for each row returned (defined by the 'currentRow' int
>>parameter of this method). I found this rather inefficient - if I use a
>>ResultSet within my class do I have to call rs.seek(currentRow) each time
>>the assignRowValues method is called (i.e. rs.seek(2), then rs.seek(3)
when
>>the enxt call comes etc)?
>>
>>Am I missing something here?
>>
>>
>Yes, you miss the whole point.

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?

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?

>The ResultSet is used because it exposes
>a standard way of building a tuple. The assingRowValues builds one tuple
>at a time and "returns" that tuple to the PostgreSQL backend. You do
>not' need to do any positioning at all. The ResultSet that is used
>contains exactly one row and it is positioned on that row.
>
>Look at the examples in package org.postgersql.pljava.example
>ComplexReturn.java
>UsingProperties.java
>
>>Also, in the jdbc sub-package there are quite a few SPI* methods which
seem
>>to mirror their counterparts from the SQL standard (or so was my
>>impression).
>>
>>What is the purpose of these and how can I utilise them, am I better of
>>using them instead of the 'standard' ones (I have absolutely *no*
knowledge
>>of PostgreSQL internals)? Is it better to use SPIConnection for e.g.
rather
>>than the 'normal' SQL Connection?
>
>
>You should always use the standard interfaces. The SPI stuff is an
>implementation of those interfaces. Never use the implementation
>directly. If you do, you'll never be able to port your code to other
>databases.

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?

>>The examples given in the examples.jar for complex types return in pljava
>>are rather daft as they do not use sql result set at all (simple int
>>addition and a timestamp) so I can't see how to utilise a ResultSet rows
>>and return them one by one (if that is the idea of assignRowValues).
>>
>>
>You could at least look at the code before you make statements like
>that. ComplexReturn.java and UsingProperties.java both use assignRowValues.

See my comments above.

Regards,

George

--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .

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