From: info2 at wyse-systems.ltd.uk (info2 at wyse-systems.ltd.uk)
Date: Wed, 16 Feb 2005 06:34:07 -0500
Subject: [Pljava-dev] advice needed
Message-ID: <295550-22005231611347334@M2W077.mail2web.com>

>>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_v 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/ .