[Pljava-dev] SPI Updatable recordset

[Eric E]

Hi Thomas,
    So I got a chance to work for a few hours on the UpdatebleResultSet, 
and I don't think things are looking too bad.  I do have a couple 
questions, though.
1) SPIResultSet extends ResultSetBase which extends ReadOnlyResultSet.  
For simplicity I am just subclassing SPIResultSet with 
UpdateableSPIResultSet, and overriding the methods in ReadOnlyResultSet 
which throw exceptions when you attempt to update.  Is this the 
preferred way for me to do it, or I should I update somewhere else in 
the class hierarchy?  Naturally, this also requires me to make several 
of SPIResultSet's field variables protected instead of private.

2) The update functions in the standard JDBC driver update the row 
buffer that backs the ResultSet when executing an update, presumably to 
avoid hitting the database again to get the copy fresh.  The row buffer 
is a byte array that pulls out of a Vector of tuples.  Offhand it does 
not look like I can write into the Tuple m_current_row in the same way - 
is this true?  Any good ideas for how to refresh the row with minimal load?

Thanks,

Eric


Eric E wrote:
> On 6/7/06, *Thomas Hallgren* <thomas at tada.se <mailto:thomas at tada.se>> 
> wrote:
>
>     Hi Eric,
>     Sorry for the late reply.
>
>     Eric E wrote:
>     > Hi Thomas,
>     >   Thanks for the input.  On 1, I believe the postgres ODBC driver
>     > exposes xid, xim and/or the oid of the tuple for precisely this
>     reason.
>     Another option is to look at how this is handled by the client JDBC
>     driver. We've stolen some code from it already :-)
>
>
> Yep, I was just doing that myself.  It wasn't until I looked through 
> that and the PL/JAVA JDBC driver that I realized that Sun only defines 
> interfaces for JDBC, not actual code.  The PG client JDBC driver looks 
> like it has a very nice UPDATE SQL builder, just as you described.
> From a quick review, the only changes I can see it needing are when it 
> actually writes the data to the database - it uses 
> (preparedStatement).executeUpdate() to actually execute the changes.  
> If I get time later today, I'll try grafting the code from the client 
> driver onto AbstractSPIResultSet and see what happens.
>
>     > As for 2, I'm thinking I'd start simple by only addressing
>     > single-table updates.  So I can see two (totally inelegant) ways of
>     > handling the non-updatable fields, views, etc. issues.  Would it not
>     > be possible to simply let the failure occur when trying to run the
>     > update?  Or run a preliminary update that changes every field
>     and then
>     > roll it back?
>     Yes, of course. As long as the logic that makes the attempt to create
>     the update statement is reasonably intelligent. It's probably
>     better to
>     make some assumptions in a straight forward but fast
>     implementation then
>     to make extra round trips to find out more about the meta-data
>     about the
>     query.
>
>
> From my initial inspection, it looks like the update logic in the 
> client driver only tries to update the first table in the SELECT used 
> to create the ResultSet.  I presume therefore that if you try to 
> update anything more it will throw an exception.  This seems 
> reasonable to me.
>
>
>     > I'll try to take a look at it some time next next week.
>     >
>     Great. Don't hesitate to ask questions.
>
>
> Thanks, I almost certainly will.
>
> Cheers,
>
> Eric