[Pljava-dev] SPI Updatable recordset

Hi,
I'm trying to use PL/JAVA's SPI connection to create an updatable
recordset, and I keep getting an exception when I try to initialize a
statement with CONCUR_UPDATABLE.

The problematic function is this:

public ResultSet getUpdatableResultSet(String sSQLStatement) throws
SQLException{
return db_conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE)
.executeQuery(sSQLStatement);
}

I have tried using ResultSet.TYPE_FORWARD_ONLY,
ResultSet.TYPE_SCROLL_INSENSITIVE,
and ResultSet.TYPE_SCROLL_SENSITIVE
and all give me the same exception.

The statement works fine when running the code as a standalone program and
using the standard Postgres 8.0 JDBC driver. Executing a SQL DLL command
against a Statement from the SPI connection works just fine.

Any ideas? Is it possible to get an updatable ResultSet from the SPI
connection?

Thanks,

Eric
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20060602/ab38b362/attachment.html>

Hi Eric,
The PL/Java jdbc driver will only support the combination
ResultSet.TYPE_FORWARD_ONLY and ResultSet.CONCUR_READ_ONLY at present.
Other combinations might be implemented in future releases. Patches are
always welcome.

Kind Regards,
Thomas Hallgren

Eric E wrote:
> Hi,
> I'm trying to use PL/JAVA's SPI connection to create an updatable
> recordset, and I keep getting an exception when I try to initialize a
> statement with CONCUR_UPDATABLE.
>
> The problematic function is this:
>
> public ResultSet getUpdatableResultSet(String sSQLStatement)
> throws SQLException{
> return db_conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.CONCUR_UPDATABLE)
> .executeQuery(sSQLStatement);
> }
>
> I have tried using ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.TYPE_SCROLL_INSENSITIVE,
> and ResultSet.TYPE_SCROLL_SENSITIVE
> and all give me the same exception.
>
> The statement works fine when running the code as a standalone program
> and using the standard Postgres 8.0 JDBC driver. Executing a SQL DLL
> command against a Statement from the SPI connection works just fine.
>
> Any ideas? Is it possible to get an updatable ResultSet from the SPI
> connection?
>
> Thanks,
>
> Eric
> ------------------------------------------------------------------------
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>

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

Eric E wrote:
>
> 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.
>
The inheritance sound OK but I'm not in favor of protected fields.
Please use getters (and setters where applicable). It's just as fast.
Any JVM worth its name will inline such methods more or less
immediately. Sometimes the use of private fields will yield better
performance since it's easier for the optimizer to track changes etc.

> 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?
You'll have to replace the m_currentRow with a new Tuple that you create
using TupleDesc.formTuple(Object[]). I think the overhead of doing that
is very low.

Regards,
Thomas Hallgren